Test Database Isolation in pytest: Reads Share, Writes Get Their Own

Share one database across your integration tests and one test's writes break another test's assertions, in an order you'll only see on CI. Give every test a fresh database and the flakiness goes away, but the suite crawls. The examples here are Python and pytest against a GraphQL API backed by Neo4j, with a PostgreSQL rollback variant and a short Playwright fixture version for TypeScript readers. You'll leave with two fixture scopes, a naming rule, and a clear idea of when a transaction rollback is the cheaper option.

The bug: state that outlives its test

Say a test credits an account with $100 and doesn't clean up. The next test in the same class creates a "new" account, expects a zero balance, and finds $100 that no code path in that test put there. Run the two tests in the other order and both pass.

In a payments codebase this is the worst kind of red build, because a phantom balance looks exactly like a real ledger bug. Someone loses a morning finding out it was the test suite.

Two isolation levels, chosen per test

Both extremes are wrong. Stop picking one strategy for the whole suite, give yourself two fixtures, and let each test choose.

Class-scoped, for read-only tests. One database is created once, seeded, and shared by every test in the class. Read-only tests only query it, so they can't corrupt it, and there's no reason to pay for a fresh database per test. pytest tears a class fixture down after the last test in the class.

Function-scoped, for anything that writes. Every mutation test gets a brand-new database, created before the test and dropped after it. function is pytest's default scope. Nothing is shared, so a write can't leak and the tests can run in any order, or in parallel.

 tests/test_orders.py copy
class TestOrderQueries:
    # read-only: class scope, one shared DB, fast
    def test_order_has_fields(self, db_query_defaults):
        result = run_query(order_query, data, database=db_query_defaults)
        assert result.errors is None
 
class TestOrderMutations:
    # writes: function scope, fresh DB per test, isolated
    def test_create_order(self, db_defaults):
        result = run_mutation(create_order, data, database=db_defaults)
        assert result.errors is None
        # safe to check DB state: nothing else touched this DB

The fixtures behind them, as a sketch with the official neo4j Python driver (connection config and seeding are yours to fill in):

 tests/conftest.py copy
import uuid
import pytest
from neo4j import GraphDatabase
 
@pytest.fixture(scope="session")
def conn():
    driver = GraphDatabase.driver(NEO4J_URI, auth=NEO4J_AUTH)
    yield driver
    driver.close()
 
def _create_db(conn):
    name = f"test-{uuid.uuid4().hex[:8]}"  # no underscores: Neo4j rejects them
    conn.execute_query(f"CREATE DATABASE `{name}` WAIT", database_="system")
    return name
 
def _drop_db(conn, name):
    conn.execute_query(f"DROP DATABASE `{name}` IF EXISTS", database_="system")
 
@pytest.fixture(scope="class")
def db_query_defaults(conn):
    name = _create_db(conn)
    seed_reference_data(conn, name)
    yield name
    _drop_db(conn, name)
 
@pytest.fixture  # function scope is the default
def db_defaults(conn):
    name = _create_db(conn)
    seed_reference_data(conn, name)
    yield name
    _drop_db(conn, name)

You only pay for full isolation where you need it. Most suites are mostly reads (mine are), so the suite stays fast and the writes still get a clean room. Two Neo4j details from the naming rules: names allow letters, digits, dots and dashes but not underscores, and a name containing a dash has to be wrapped in backticks. The Neo4j docs also label creating databases as an Enterprise Edition feature, so on Community Edition you'll need the cleanup approach further down.

Use a random database name, and never hardcode it

Whichever scope a test uses, point it at a generated name like test-8a2f3c9d, never your default database. A test then can't reach shared or production data by accident, and the class-scoped and function-scoped databases can't collide.

The one discipline it needs: always read the name from the fixture.

 copy
with conn.session(database=db_defaults) as session:
    ...

 copy
with conn.session(database="main") as session:
    ...

Hardcode "main" and the test quietly runs against the shared database, which is the exact bug the fixtures exist to prevent. It passes locally, too, which is why it survives review.

The cheaper option on PostgreSQL: roll back every test

Creating a database per test is the heavy option. On PostgreSQL there's a lighter one: open a transaction before each test and roll it back afterwards, whatever happened. With psycopg 3, conn.transaction(force_rollback=True) does exactly that, and any conn.transaction() block the code under test opens inside it runs as a SAVEPOINT rather than a real commit.

 tests/conftest.py copy
import psycopg
import pytest
 
@pytest.fixture(scope="session")
def pg():
    with psycopg.connect(PG_DSN, autocommit=True) as conn:
        yield conn
 
@pytest.fixture
def pg_tx(pg):
    with pg.transaction(force_rollback=True):
        yield pg

 tests/test_ledger.py copy
def test_credit_posts_to_balance(pg_tx):
    credit(pg_tx, account_id=1, amount_cents=10_000)
    (balance,) = pg_tx.execute(
        "SELECT balance_cents FROM accounts WHERE id = 1"
    ).fetchone()
    assert balance == 10_000

It's fast, because nothing is created or dropped. It also has limits, and they matter for code that moves money:

• Everything has to go through that one connection. Code that opens its own connection or pool, or commits on its own, escapes the rollback.
• Nothing is ever committed. PostgreSQL checks DEFERRED constraints at commit, so a deferred foreign key or uniqueness rule never fires in this setup.
• Concurrency tests don't fit. A second session can't see uncommitted rows, so double-spend and locking tests need real commits and a function-scoped database.

Neo4j doesn't give you this trick. The Python driver allows one open transaction per session, so there's no nesting to hide the app's own transaction inside, and you can only roll back if the code under test accepts your transaction object. For Neo4j I stick with a database per mutation test. On Community Edition, the fallback is a function-scoped fixture that runs MATCH (n) DETACH DELETE n after each write test (slower, and you give up parallel runs).

The same split in TypeScript

Playwright Test has the same idea under different names. A { scope: 'worker' } fixture is created once per worker process, and the default test scope gives every test its own. Worker scope is broader than a pytest class (it spans every test that worker runs), so keep it for read-only data.

 tests/fixtures.ts copy
import { test as base } from '@playwright/test';
import { randomUUID } from 'node:crypto';
import { createDatabase, dropDatabase } from './db'; // your helpers
 
type TestFixtures = { freshDb: string };
type WorkerFixtures = { sharedDb: string };
 
export const test = base.extend<TestFixtures, WorkerFixtures>({
  sharedDb: [async ({}, use, workerInfo) => {
    const name = await createDatabase(`test-w${workerInfo.workerIndex}-${randomUUID().slice(0, 8)}`);
    await use(name);
    await dropDatabase(name);
  }, { scope: 'worker' }],
 
  freshDb: async ({}, use) => {
    const name = await createDatabase(`test-${randomUUID().slice(0, 8)}`);
    await use(name);
    await dropDatabase(name);
  },
});

Why not mock the database

A mocked database doesn't catch the bugs that actually happen: constraint violations, query mistakes, transaction edge cases, the gap between what your query says and what the engine does. I cover where the line sits in the golden rule of mocking, and the "verify the state" step in the five parts of an integration test only means something when the database it reads is isolated like this. A real database with the right isolation is what makes those tests worth running. (Once they run, read coverage as a trend rather than a number to push up.)

Why it matters for your team: a test that leaks a balance into the next one trains people to ignore red builds on the ledger, which is the one place you can't afford to.

Pick the scope per test, name the database randomly, and read the name from a fixture. That's the whole pattern.