Testing Against a Real Database

The storage layer's tests run against a real PostgreSQL 18, the same major as pg-primary, in a container that Testcontainers starts once for the whole session. The migrations of Topic 35 run against it first, so the schema under test is the schema the deploy will produce, and then each test runs inside one transaction that is rolled back when the test ends, so no test commits, no test sees another's rows, and there is no cleanup code anywhere in the suite. The cost is about a minute of suite time, 6 seconds of it the container starting. The return is that FOR UPDATE, ON CONFLICT, the unique index of Topic 34 and the cursor query of Topic 16 are tested on the engine that will run them, by the only judge whose opinion counts.

Three kinds of test live here. The 340 that run in a rolled-back transaction and cost 100 milliseconds each. The 9 that need two connections and real commits, because they test what happens when two transactions race, and are marked so that everyone knows they truncate. And the one that starts from an empty database and applies every migration in order, which is the test that fails before the deploy does.

The Container

A session-scoped fixture starts postgres:18 through Testcontainers, waits until it accepts connections, runs the migration runner against it, and hands the connection URL to build_service of Topic 20 as config. That last step is the point. The storage layer does not know it is in a test. It receives a database_url the way it does on api-01, opens the same pool, runs the same SQL, and the only difference between the suite and production is the value of one string. Twenty lines of fixture buy that, and they are shown below with the per-test fixture that follows.

import psycopg, pytest
from testcontainers.postgres import PostgresContainer

# once per session: start postgres:18, migrate it, expose the URL as config
@pytest.fixture(scope="session")
def pg_url():
    with PostgresContainer("postgres:18", driver=None) as pg:   # driver=None: a plain postgresql:// URL for psycopg 3
        url = pg.get_connection_url()
        run_migrations(url)                                    # the same runner the deploy job runs, from empty
        yield url                                              # the container stops when the session ends

# once per test: one connection, one transaction, rolled back no matter what
@pytest.fixture
async def db(pg_url):
    async with await psycopg.AsyncConnection.connect(pg_url) as conn:
        async with conn.transaction(force_rollback=True):      # the storage layer's own blocks become savepoints
            yield OneConnectionPool(conn)                      # same .connection() interface as the real pool

@pytest.fixture
async def svc(db, test_config):
    return await build_service(test_config, pool=db, payrail=FakePayrail(), clock=FrozenClock())

The first fixture runs once. It starts the container, asks it for a URL with no driver name in it, so that psycopg 3 accepts it as is, runs every migration in order against the empty database, and yields the URL for the rest of the session; when the session ends the context manager stops and removes the container. The second fixture runs for every test. It opens one connection, begins a transaction with the flag that rolls it back on the way out even when the test passed, and yields a one-connection pool that offers the same connection() method the real pool does. The third builds the service exactly as Topic 20 does, with that pool in place of the real one, the fake Payrail and the frozen clock. The storage layer's own async with conn.transaction() blocks nest inside the outer transaction, and psycopg turns a nested block into a savepoint, so the code under test commits and rolls back its savepoints as it normally would, inside an outer transaction that never commits.

A Transaction per Test

Each test starts with the schema and nothing else, inserts what it needs, runs the code, asserts, and then the fixture rolls everything back. There is no cleanup function to forget a table in, no shared database where last week's row makes today's test pass, and no ordering dependency, because no test can see anything another test did. The rollback costs a millisecond. Marek's previous suite had a clean_tables() helper that deleted from 9 tables and missed payments when it was added, and for 3 weeks a test passed because a payment row from a different test was still there.

The pattern has one thing it cannot test, and it should be named rather than discovered. Everything inside the test happens in one transaction on one connection, so the test cannot observe behaviour across a commit: what a second transaction sees, what a lock does to a waiter, whether a unique constraint refuses the second insert from another connection. Those are exactly the behaviours the oversell was made of, and they get the next section's fixture instead.

When a Test Needs Two Connections

The oversell regression of Topic 34 needs two transactions racing on one seat row, and two transactions need two connections, each of which really commits or really rolls back. The fixture for it opens two connections outside any outer transaction, the test runs both holds concurrently, and after the assertions the fixture truncates the tables the test touched. These tests are marked commits, there are 9 of them, they cannot run in parallel with the rolled-back ones on the same database, and they are the only tests in the suite that could ever leave a row behind if the truncate failed.

@pytest.mark.commits                       # real transactions; the fixture truncates afterwards
async def test_concurrent_holds_on_one_seat(two_conns, buyers):
    a, b = two_conns
    seat = await make_seat(a, event_id=8812, label="14C")              # committed, so both connections see it

    results = await asyncio.gather(
        hold_seat(a, seat.id, buyers[0]),
        hold_seat(b, seat.id, buyers[1]),
        return_exceptions=True,
    )
    won  = [r for r in results if isinstance(r, Hold)]
    lost = [r for r in results if isinstance(r, SeatUnavailable)]
    assert len(won) == 1 and len(lost) == 1
    assert await count_holds(a, seat.id) == 1                           # the constraint's view, from a third read

The test inserts one seat and commits it, so that both connections can see it. It then starts two holds at once, one on each connection, for the same seat and two different buyers, and collects both outcomes, including the exception one of them will raise. The assertions are the whole incident stated as three facts: exactly one hold succeeded, exactly one was refused with the seat-unavailable error that the transport turns into a 409, and the holds table has one row for the seat. Remove the FOR UPDATE from hold_seat and the first assertion fails with two winners; remove the unique index as well and the third fails with two rows. That is what a regression test is for: not proving the fix works today, but failing on the day someone takes it out.

Fixtures Are Data

A seat, an event, a buyer and an order are built by factory functions with sensible defaults, make_seat(status="available"), make_order(status="pending"), and each factory inserts through the storage layer, never by raw SQL that bypasses it. The difference matters more than it looks. A test that writes INSERT INTO orders (status) VALUES ('shipped') creates a status no code path in the storage layer can write, and then tests how the code handles a state production cannot reach; the test passes and the code path it exercised is dead. A factory that calls OrderRepository.create gets the constraints' refusals, the default values, the public_id and the timestamp from the same code production uses, and the row it makes is one production could have made.

The factories also keep the tests short. A test of the refund path needs an organizer, an event, a seat, a buyer, a paid order with a payment and a ticket, which is 7 rows across 7 tables and 40 lines of setup if written by hand. make_paid_order() does it in one call, with every default sensible and every argument overridable, and the test that follows is 5 lines about refunds rather than 45 lines about setup with 5 about refunds at the end.

Asserting the Query Count

The N+1 of Topic 33 is a bug that no assertion on the result can see, because the result is correct. Fifty orders come back with their tickets whether the code ran 2 queries or 51. The test that catches it counts statements: the db fixture's connection uses a cursor class that increments a counter on every execute, and the test that lists 50 orders asserts db.statements == 2, one for the orders and one for all their tickets by WHERE order_id = ANY(...). Fifty-one fails it. So does 3, which is the assertion's other job: the day someone adds a "harmless" lookup inside the loop, the count moves and the test names the endpoint.

Every list endpoint has one of these, and the number in each assertion is the query plan written down as a fact the suite enforces. Marek's rule is that the count is asserted exactly, never with "at most", because a bound of 10 is a bound that a lazy load gets under for the first 8 orders, and the test that says "at most 10" is the test that passes in development and times out in production with 500 orders.

The Migrations Are Under Test Too

The session fixture starts from an empty database and applies every migration file in order, so a migration that fails, or one that leaves the schema different from what the models expect, fails the first test of the session and every test after it. That is the migration test, and it is free: it runs every time the suite does. Two more tests sit beside it. One compares the migrated schema with the storage layer's expectations, column by column for the eleven canonical tables, so that a model with a column the migration never added is caught in the suite and not by a 500 on the first request. The other is the expand and contract check of Topic 35: for each three-step change, a test runs the previous release's query against the expanded schema and asserts it still works, because during the rolling deploy of Topic 62 the old code and the new schema are live at the same moment.

The version matters as much as the emptiness. The container runs the same major as production, 18, because a suite on last year's major tests a planner and a feature set the deploy will not have. The night a reporting query's MERGE … RETURNING worked in the suite, whose container had been bumped to 17, and failed on a pg-primary still on 16, where MERGE could not return rows, was the night the two tags went into one pull request. The other direction, a feature production has and the suite lacks, fails quieter and later. One string in the fixture, postgres:18, is bumped in the same pull request that bumps the primary.