Fixtures in YAML for Database Tests With pytest and Rollback

Database tests fail for boring reasons: another test left rows behind, a fixture file grew until nobody knew what it contained, or the data that matters to the assertion is three screens away from it. YAML fixtures help with the last one. They are short, they read like the rows they describe, and a test can name the file it needs. The remaining work is isolation, and a transaction that rolls back after each test supplies it. This post builds the pattern with pytest and PostgreSQL 18 and runs it. It belongs to the cluster on using YAML with databases; loading the same files as application seed data is in loading YAML seed data into PostgreSQL.

The fixture file

A fixture is a list of rows for one table, named for the scenario:

- {id: 1, email: [email protected], plan: pro, prefs: {theme: dark}}
- {id: 2, email: [email protected], plan: free}

Flow style keeps each row on one line, so a file of twenty rows is twenty lines you can read as a table. The second row omits prefs, and the loader supplies a default. Nothing in the file says how it is loaded, so the same file can feed a unit test, a demo database and a bug report.

The fixtures that give isolation

pytest fixtures are functions that set up and tear down the world a test needs. This one opens a connection, creates a temporary table and rolls back when the test ends:

import psycopg
import pytest
import yaml
from psycopg.types.json import Jsonb


@pytest.fixture
def db():
    conn = psycopg.connect("", autocommit=False)
    conn.execute("create temp table customers (id int primary key, email text, plan text, prefs jsonb)")
    yield conn
    conn.rollback()
    conn.close()


def load(conn, name):
    fixtures = yaml.safe_load(open(f"fixtures/{name}.yaml"))
    for row in fixtures:
        conn.execute("insert into customers values (%s,%s,%s,%s)",
                     (row["id"], row["email"], row["plan"], Jsonb(row.get("prefs", {}))))

Reading it: autocommit=False means every statement runs inside an implicit transaction until you commit. create temp table lives only in this session. After the yield, conn.rollback() undoes everything the test did, and closing the connection drops the temp table. A test never commits, so nothing persists.

The loader is deliberately small: it opens the YAML, and inserts each row, wrapping the prefs mapping in Jsonb (the reason is in the seed post). A real project's version looks up the table from the file name or a header key, and handles several tables in dependency order.

The tests

def test_pro_customers(db):
    load(db, "customers")
    n = db.execute("select count(*) from customers where plan = 'pro'").fetchone()[0]
    assert n == 1


def test_isolated(db):
    assert db.execute("select count(*) from customers").fetchone()[0] == 0


def test_dark_theme(db):
    load(db, "customers")
    assert db.execute("select email from customers where prefs->>'theme' = 'dark'").fetchone()[0] == "[email protected]"

Running them:

...                                                                      [100%]
3 passed in 0.13s

test_isolated is the one that proves the pattern. It loads nothing and asserts that the table is empty, and it passed after test_pro_customers had loaded two rows. If rollback did not isolate tests, the order of execution would change the result. pytest runs tests in file order by default, so put such an isolation test in the suite on purpose, and run the suite in random order with a plugin from time to time to catch tests that depend on each other.

Speed is the other benefit. Nothing is committed and nothing is deleted. A rollback is almost free, which keeps a suite with hundreds of database tests in seconds instead of minutes. Compare the alternatives: truncating tables after each test is slower, and recreating the database is slower still.

Fixtures should name what the test depends on

The common failure of fixture files is size. A single everything.yaml with 300 rows serves every test and explains none. The rule that keeps fixtures useful: a test loads the smallest file that makes its assertion meaningful, and the file's name says what scenario it sets up (customers_with_one_pro, orders_across_two_months). When a test fails, the reader opens a ten-line file instead of searching a big one.

Two small habits make this work.

Put the interesting value in the fixture, not the assertion. test_dark_theme asserts on an email that appears in the fixture, so a reader sees the cause and the effect together. If the fixture and the assertion drift apart, the test fails, which is what you want.

Share the loader, not the data. Tests share load(); each scenario owns its file. If two tests need almost the same rows, they can share a file, and a test that needs one more row adds it inline after loading.

Test the constraints, not only the happy path

Fixtures built from valid rows prove that queries work on valid data. They say nothing about whether the database rejects invalid data, which is what constraints are for. Add tests that insert a bad row and expect an error:

def test_plan_check(db):
    with pytest.raises(psycopg.errors.CheckViolation):
        db.execute("insert into customers values (3, '[email protected]', 'gold', '{}')")

This needs a check (plan in ('free','pro')) constraint on the table, which the temp table above omits for brevity; with the constraint added to the create temp table statement, the test passed. Better yet, create the temp table by copying the real table's structure (create temp table customers (like customers including all)), so that your tests exercise the production definition and not a hand-written copy that drifts from it. For a larger suite, run the real migrations once per session into a test database, and use transactions per test on top.

Validating the fixtures themselves

A fixture file is data, and it can be wrong: a misspelled key, a missing column, a plan that does not exist. Two cheap protections:

• Load with a strict loader that raises on unknown keys. A row["emial"] typo then fails loudly at load time instead of inserting a null.

• Validate each file against a JSON Schema or a Pydantic model in a single test that globs fixtures/*.yaml. Validating YAML with JSON Schema and validating YAML configuration with Pydantic show the options.

The second one catches a broken fixture before the tests that use it fail in confusing ways. Also run yamllint over the folder, since an indentation slip in a fixture is the most common cause of a mysterious test failure; linting YAML with yamllint has the setup.

Other databases and frameworks

The pattern is not specific to PostgreSQL. SQLite supports transactions and in-memory databases, so an :memory: database per test works even without rollback. MySQL supports it for InnoDB tables. Frameworks have their own fixture systems (Rails has YAML fixtures built in, Django uses JSON, YAML or XML fixtures with loaddata), and they handle foreign keys and ordering for you. Use the framework's mechanism if you have one. The hand-written loader above earns its place when you test SQL directly, outside an ORM.

When YAML fixtures do not suit your database tests

You need thousands of rows. Generate them in code, or load a SQL dump once per session. YAML parsing is slow at that scale.

Rows depend on each other through generated ids. A fixture that needs the id of another row requires a naming scheme and a loader that resolves it. At that point a factory function (make_customer(plan="pro")) is clearer than a data file.

Tests need realistic distributions. A handful of hand-written rows will not reveal a bad query plan. Use a sampled copy of production, anonymized, for performance tests.

The schema changes weekly. Fixtures in a file must track every column. A factory with defaults absorbs new columns in one place.

Tests run in parallel against one database. Rollback isolates transactions, but a shared temp table or sequence can still collide. Give each worker its own schema or database.

The fixture mechanism is described in the pytest fixtures documentation, and transactions in the psycopg transactions guide. To check a fixture file's layout quickly, use the YAML formatter.