How To Test Database Transactions With Pytest And SQLModel

Raw SQL vs ORM tools

Raw SQL involves writing database queries in SQL, offering you precise control and the power to optimize complex queries.

On the other hand, ORM tools, like SQLAlchemy or SQLModel (built off SQLAlchemy with Pydantic), abstract the low-level database interactions into object-oriented code.

Testing Database Operations

Ideally, you would have developed this code using the popular practice of TDD (Test-Driven Development) — whereby you would write a basic method and then write tests to validate a bunch of success or failure scenarios, tweaking your method in the bargain.

What To Test?

Evaluating the functionality we’ve decided that as a bare minimum, we need to cover: Core CRUD Operations and Exceptions and Errors such as:

• read task — task_id not found

• delete task — task_id not found

• invalid task model

• DB Session failure

• DB connection issues

def test_read_task_not_found(db_instance_empty, session, task1, task2):    
    """    
    Test the reading of a task that does not exist    
    """    
    # Write 2 Tasks to DB    
    db_instance_empty.create_task(task=task1, session=session)    
    db_instance_empty.create_task(task=task2, session=session)    
    
    # Search for Task with ID 100    
    with pytest.raises(TaskNotFoundError):    
        db_instance_empty.read_task(task_id=100, session=session)

Test DB Engine and Connectivity

Simple answer, Mock external services and systems.

In-Memory DB vs External DB

Should you use an in-memory DB like SQLite or TinyDB or a traditional one like Postgres or SQL Server?

My take — if you’re testing simple unit test operations and need tests to run fast, an in-memory DB should suffice.