intermediate30 minby DevFox

Database State and Isolation

Tests that pass alone and fail together are almost always sharing state. Transactions, truncation, per-test schemas and why ordering dependence is a bug in the suite.

Testing

"It passes on my machine and fails in CI." "It passes alone and fails in the suite." "It failed once and I have not been able to reproduce it."

Those three sentences describe one problem in three costumes, and the problem is almost always shared state in the database. By the end of this lesson you will know the three isolation strategies, which one to reach for, and the specific trap that lets a test pass against code that never saves anything.

Why a real database breaks independence

The integration lesson argued for using a real database: substituting SQLite removes the seam you were testing. That argument holds, and it introduces the difficulty this lesson is about.

A database is durable by design. Whatever a test writes is still there for the next one, so the suite accumulates state as it runs, and the state depends on which tests ran and in what order.

Three failure modes follow, and each has a recognisable signature:

Order dependence. Test B relies on data test A created. Both pass in alphabetical order; reverse it and B fails.

Collision. Two tests both create ada@example.com. Alone, fine. Together, a unique-constraint violation.

Pollution. A test asserts a list has three items. It has three when the test runs alone and seventeen after ten other tests have added records.

None of these is a mystery once you know to look. All of them present as "flaky", which is why they survive for months.

Strategy 1: a transaction per test

The default answer, and the one to reach for first. Each test runs inside a transaction that is never committed; the rollback at the end returns the database to exactly its previous state.

python

@pytest.fixture(scope="session")
def engine():
    with PostgresContainer("postgres:16") as postgres:
        engine = create_engine(postgres.get_connection_url())
        run_migrations(engine)          # once for the whole run
        yield engine


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

    yield session

    session.close()
    transaction.rollback()              # nothing was ever written
    connection.close()

It is fast — no data reaches disk, and no cleanup query runs — and the isolation is complete: uncommitted changes are invisible to any other connection, so tests can run in parallel on separate connections without seeing each other.

The catch is that the code under test must use your session. If it opens its own connection, that connection is outside your transaction, and it will neither see your setup data nor be rolled back. Which leads directly to the trap.

The trap: a commit that never happens

Bad — the test passes and nothing is ever saved:

python

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


def test_the_endpoint_persists_a_contact(client, database):
    client.post("/contacts", json={"name": "Ada"})
    assert database.query(Contact).count() == 1     # passes

Good — a fresh session for the assertion, so only a real commit is visible:

python

@pytest.fixture
def database(engine):
    yield Session(bind=engine)
    truncate_all_tables(engine)         # cleanup, not rollback


def test_the_endpoint_persists_a_contact(client, engine):
    client.post("/contacts", json={"name": "Ada"})

    with Session(bind=engine) as fresh:          # a NEW session
        assert fresh.query(Contact).count() == 1

The first test cannot fail. Inside one transaction, a flush is visible to the same session whether or not anything is ever committed — so an endpoint that writes and then quietly discards its transaction produces a green test. The whole suite can pass while the application saves nothing, and the bug reaches production having been "covered".

The second version reads through a separate session, which can only see committed data. It fails against the broken code, which is the entire point.

Strategy 2: truncate between tests

Let tests commit, and delete the data afterwards.

python

@pytest.fixture
def database(engine):
    yield Session(bind=engine)
    with engine.begin() as connection:
        connection.execute(text(
            "TRUNCATE TABLE contacts, orders, users, accounts "
            "RESTART IDENTITY CASCADE"
        ))

Slower than rollback, because writes actually happen and the truncation runs, and it is the honest choice whenever commit behaviour matters — any test that goes through the application's own transaction handling.

Two details make it workable. RESTART IDENTITY resets sequences, so ids do not drift upward across a run and a test can safely expect id 1. CASCADE handles foreign keys. And truncating a fixed list of tables in one statement is much faster than deleting per table.

A convenient variant is to truncate before each test rather than after. Then a failing test leaves its data in place for you to inspect, which is worth a great deal during diagnosis.

Strategy 3: a schema or database per test

Full isolation: each test — or more practically, each parallel worker — gets its own schema.

python

@pytest.fixture(scope="session")
def database_url(worker_id):            # pytest-xdist gives this
    name = f"test_{worker_id}"
    create_database(name)
    run_migrations(name)
    yield url_for(name)
    drop_database(name)

Per parallel worker is the sweet spot: four workers, four schemas, and within each worker one of the cheaper strategies. Per individual test is usually too slow — creating a schema and running migrations is measured in seconds.

Transaction per test

Fastest. Nothing is ever committed.

No data reaches disk and no cleanup query runs.

And that is also its limit: any code under test that opens its own connection sees none of it, and any bug in your commit handling is invisible.

Truncate between tests

Medium. Commits are real.

The honest choice whenever commit behaviour matters — any test that goes through the application's own transaction handling.

Needs its own schema per parallel worker.

Schema per worker

Slowest. Complete separation.

Four workers, four schemas, and one of the cheaper strategies inside each. Per individual test is too slow — creating a schema and running migrations is measured in seconds.

All three isolate completely. They differ in speed, and in whether a commit ever really happens.

Things that outlive a rollback

Database isolation does not cover everything, and the leftovers are a recurring source of order dependence.

text

a cache               Redis, or an in-process cache. A rollback does
                      not evict a cached value, so the next test
                      reads data that no longer exists.
uploaded files        object storage, or a temp directory
a message queue       a job enqueued by a rolled-back test still runs
module-level state    a singleton, a memoised value, a monkeypatch
                      that was not undone
time                  a frozen clock left frozen
environment variables set for one test and never restored

Each needs its own reset, and the pattern is the same — a fixture that tears down what it set up:

python

@pytest.fixture(autouse=True)
def reset_cache(redis_client):
    yield
    redis_client.flushdb()


@pytest.fixture
def uploads(tmp_path, monkeypatch):
    monkeypatch.setattr(settings, "upload_dir", tmp_path)
    return tmp_path                    # removed automatically

autouse=True is worth knowing: the fixture applies to every test without being requested, which is right for a reset nobody should have to remember.

Proving isolation

Do not assume it; check it, and check it on a schedule rather than once.

bash

pytest --random-order          # order dependence (pytest-randomly)
pytest -n 4                    # parallel safety (pytest-xdist)
pytest --count 2               # the same test twice (pytest-repeat)
pytest tests/integration/test_orders.py::test_listing   # alone

Those four commands find the three failure modes between them, and running random order in CI is what stops new coupling being introduced.

When a test does fail only in company, the fastest way to find the culprit is bisection: run the first half of the suite plus the failing test, then the other half. Some frameworks automate it — pytest -p no:randomly --lf, or a plugin — but by hand it is four or five runs.

Cheat sheet

text

# Three signatures of one problem
order dependence  passes alphabetically, fails reversed
collision         two tests create the same unique value
pollution         "the list has 3 items" — it has 17 in the suite
#   all three present as "flaky"

# Strategy 1: transaction per test, rolled back  <- the default
#   fastest, complete isolation, parallel-safe
#   requires the code under test to use YOUR session
@pytest.fixture
def database(engine):
    connection = engine.connect()
    transaction = connection.begin()
    yield Session(bind=connection)
    transaction.rollback()

# THE TRAP
#   inside one transaction, a flush is visible to the same session
#   whether or not anything commits — so a test can pass against
#   code that never saves.
#   assert through a NEW session, or use truncation for that test.
#   check your suite: remove a commit and see if anything fails

# Strategy 2: truncate between tests
#   commits really happen; needed when commit behaviour is the point
TRUNCATE TABLE a, b, c RESTART IDENTITY CASCADE
#   truncate BEFORE each test — a failure leaves data to inspect

# Strategy 3: schema per parallel worker
#   full isolation; per-test is too slow, per-worker is the sweet spot

#                     speed    commits real  parallel
# transaction          fastest  no            yes
# truncate             medium   yes           per schema
# schema per worker    slowest  yes           yes

# What a rollback does NOT reset
cache (Redis or in-process)   a cached value survives the rollback
uploaded files
queued jobs                   a rolled-back test's job still runs
module-level state            singletons, memoised values, patches
a frozen clock
environment variables
#   each needs its own teardown fixture; autouse=True for resets
#   nobody should have to remember

# A fresh cache every test = the cache-HIT path is never tested

# Prove it, repeatedly
pytest --random-order    order dependence
pytest -n 4              parallel safety
pytest --count 2         repeat safety
pytest path::test_name   alone
#   run random order in CI so new coupling cannot creep in

Where to go next

State is contained. The next lesson moves up a level to testing an HTTP API — status codes, schemas, error bodies and the authorisation cases everyone forgets — which is the highest-value automated testing for most backend services.

Before that, run your own suite with --random-order and in parallel. If either fails, you have found a real coupling today rather than on a Friday afternoon in three weeks, and the diagnosis is much cheaper when you went looking for it.