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.
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.
"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.
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.
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.
@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.
Bad — the test passes and nothing is ever saved:
@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 # passesGood — a fresh session for the assertion, so only a real commit is visible:
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.
Let tests commit, and delete the data afterwards.
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.
Full isolation: each test — or more practically, each parallel worker — gets its own schema.
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.
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.
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.
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.
Database isolation does not cover everything, and the leftovers are a recurring source of order dependence.
Each needs its own reset, and the pattern is the same — a fixture that tears down what it set up:
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.
Do not assume it; check it, and check it on a schedule rather than once.
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.
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.
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# 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@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@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"
))@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)@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 automaticallypytest --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