Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 38 ยท Testing Real Projects

Testing Database Code

Test code that talks to a database using pytest fixtures: in-memory SQLite, schema setup, rollback per test, factory fixtures, and how to scale the same pattern to PostgreSQL and SQLAlchemy.

Database code is where unit tests meet reality. A repository function that builds a SQL string is easy to get subtly wrong, and mocking the database away proves nothing: a mock happily "accepts" a query with a typo. The best tests run the real SQL against a real (but disposable) database.

The pattern is always the same, whatever database you use. A fixture creates a clean database with the schema, hands a connection to the test, and tears everything down afterwards. Each test starts from a known state and cannot affect any other. This lesson builds that pattern with SQLite, which ships with Python, and then explains how it scales to PostgreSQL and ORMs.

The code under test

We will test a small repository for a bookstore. It takes the connection as a parameter, which keeps it easy to test:

Python
class BookRepo:
    def __init__(self, conn):
        self.conn = conn

    def add(self, title, author, price): ...
    def find_by_author(self, author): ...
    def cheapest(self, limit=3): ...

A database fixture

A fixture with yield builds the database, passes it to the test, then closes it. SQLite's special name :memory: creates a database in RAM that vanishes on close: fast, isolated and tidy.

flowchart LR
    S["fixture setup<br/>connect(':memory:')<br/>create tables"] --> T["test runs<br/>uses conn"]
    T --> D["fixture teardown<br/>conn.close()"]

Setup creates a fresh database; teardown discards it. Every test gets its own.

Python An in-memory database fixture

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last two tests are the kind a mock can never give you: they prove that the database itself enforces the rules in your schema. The test test_database_starts_empty_every_time is only true because each test gets a new fixture value.

Seed data and factory fixtures

Tests that need rows should create exactly the rows they need, so the test reads like a story. Two helpers keep that short.

A seeded fixture loads a small, common dataset for a group of tests. A factory fixture returns a function the test calls with the details that matter, with sensible defaults for everything else:

Python A factory fixture and a transaction rollback

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second test checks atomicity: the debit was executed before the error was raised, and the with conn: block rolled it back. That is a behaviour you can only verify against a real database.

Isolation strategies

Strategy How Speed Good for
New in-memory DB per test connect(":memory:") Very fast SQLite, small schemas
Rollback per test Open a transaction in the fixture and rollback() at teardown Fast Server databases where schema creation is slow
Truncate tables after each test DELETE FROM / TRUNCATE in teardown Medium Code that commits internally
Database per session or worker Create once (scope="session"), reset between tests Fast tests, slow start PostgreSQL / MySQL in CI, parallel runs
Disposable container testcontainers starts a real server in Docker Slower When SQLite behaves differently from production

A session-scoped fixture can create the engine and schema once, and a function-scoped fixture wrapped around it can open a transaction and roll it back:

Python
@pytest.fixture(scope="session")
def engine():
    eng = create_engine(TEST_DATABASE_URL)
    Base.metadata.create_all(eng)
    yield eng
    eng.dispose()

@pytest.fixture
def session(engine):
    connection = engine.connect()
    transaction = connection.begin()
    session = Session(bind=connection)
    yield session
    session.close()
    transaction.rollback()
    connection.close()

This is the standard recipe for SQLAlchemy and Django-style ORMs (pytest-django ships its own db fixture that does exactly this). It was not run here because SQLAlchemy needs a database server for anything beyond SQLite.

Testing against a different database than production

SQLite is convenient, but it is not PostgreSQL. It ignores some type rules, lacks certain features (RETURNING differences, array types, row-level locks) and handles concurrency differently. If production uses PostgreSQL, run at least the repository tests against PostgreSQL in CI (a service container or testcontainers) and keep SQLite only for pure logic. Use the same migrations tool in tests as in production, so tests exercise your real schema, not a hand-written copy.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does a database fixture use yield?

    Code before yield is setup, the yielded value is given to the test, and code after yield is teardown.
  2. 2. What is the advantage of testing a repository against a real in-memory SQLite database instead of mocking the connection?

    A mocked connection accepts any SQL string. A real database catches typos, wrong joins and violated constraints.
  3. 3. What does a factory fixture return?

    Factory fixtures return a callable, keeping each test's data visible in the test and defaults out of the way.
  4. 4. In the transfer example, why check balances after a failed transfer?

    The debit had already been executed when the error was raised; the unchanged balances show the rollback worked.

Summary

  • Run real SQL against a disposable database; use a fixture with yield to create and discard it.
  • In-memory SQLite gives each test a fresh database in microseconds.
  • Factory fixtures create just the rows a test needs; assert on constraints and atomicity, not only happy paths.
  • For server databases, create the schema once per session and isolate tests with rollbacks or truncation.
  • Test against the same engine as production for anything engine-specific.

Next: Testing Async Code