Fixtures in JSON for Database Tests With PostgreSQL

Tests that touch a database need rows to start from. The ways of getting them there have a familiar split: write inserts in code, load a SQL file, or describe the rows as data and let a loader do the inserting. JSON is a good format for the third because the data is readable, it diffs well in review, and the database can parse it itself. That last part is the one that makes it fast, and it also has a weakness I found by accident.

I built a small schema with three tables and a JSON fixture and ran the loader under pytest.

The fixture

One file with a key per table:

{"customers": [{"id": 1, "name": "Ana", "email": "[email protected]"},
               {"id": 2, "name": "Bo",  "email": "[email protected]"}],
 "orders":    [{"id": 10, "customer_id": 1, "status": "paid", "placed_at": "2026-01-02T03:04:05Z"},
               {"id": 11, "customer_id": 2, "status": "new",  "placed_at": "2026-01-03T00:00:00Z"}],
 "items":     [{"order_id": 10, "sku": "A1", "qty": 2, "price": 9.50},
               {"order_id": 10, "sku": "B2", "qty": 1, "price": 12},
               {"order_id": 11, "sku": "A1", "qty": 1, "price": 9.5}]}

Ids are explicit in the parents so the children can refer to them. Order matters: parents first, so the foreign keys are satisfied when the children arrive. A JSON object preserves the order you wrote its keys in, but do not rely on it. The loader below names the tables in order.

Load it with the database's own JSON functions

Instead of looping over rows in Python, send the whole document as one parameter and let PostgreSQL unpack it:

insert into fx_customers
  select * from jsonb_populate_recordset(null::fx_customers, %(d)s::jsonb->'customers');
insert into fx_orders
  select * from jsonb_populate_recordset(null::fx_orders, %(d)s::jsonb->'orders');
insert into fx_items(order_id, sku, qty, price)
  select order_id, sku, qty, price
  from jsonb_to_recordset(%(d)s::jsonb->'items') as x(order_id int, sku text, qty int, price numeric);

jsonb_populate_recordset(null::table, json) takes the row type from the table, so the table definition is the type contract and the JSON keys map to columns by name. For the items table I used jsonb_to_recordset with a column list because id is a serial that the fixture leaves out. In the test the document goes through psycopg's Jsonb adapter:

def load(conn, d):
    for st in LOAD_STMTS:
        conn.execute(st, {"d": Jsonb(d)})

(The driver refuses several statements in one prepared call, so they run one by one.) Then the tests read like tests:

def test_totals(loaded):
    rows = loaded.execute("""select o.id, sum(i.qty*i.price) from fx_orders o
        join fx_items i on i.order_id = o.id group by 1 order by 1""").fetchall()
    assert [(r[0], float(r[1])) for r in rows] == [(10, 31.0), (11, 9.5)]

Speed

Set based loading was the fast path. For 5,000 customers, 5,000 orders and 15,000 items, 25,000 rows:

25,000 rows via jsonb_populate_recordset:  0.214 s
25,000 rows, one INSERT per row:           2.42 s

Eleven times faster, with the JSON parsed once on the server, and one round trip per table rather than one per row. For a small fixture of ten rows it makes no difference. It matters when a test suite loads a realistic data set into many tests, and when you load the same fixture into a development database.

The schema is the validator

A good property of loading through the table type is that the constraints do the checking. Three bad fixtures, each with one change:

orders[0].status = "refunded"        CheckViolation
orders[0].customer_id = 99           ForeignKeyViolation
customers[0] without "name"          NotNullViolation

The tests assert on those exceptions, and the point is the direction: a fixture that does not respect the schema fails loudly at load time, not in the middle of an unrelated assertion. A hand-written fixture drifts from the schema over time. A migration adds a NOT NULL column, and every fixture without it fails until updated, which is exactly the signal you want.

What it does not catch

One failure was silent. I misspelled a key in the fixture:

d["customers"][0]["nmae"] = "typo"
load(db, d)
db.execute("select name from fx_customers where id = 1").fetchone()[0]   # "Ana"

The load succeeded and name was still Ana. jsonb_populate_recordset takes the keys that match a column and ignores the rest, so a misspelled key adds nothing, and since name was also present, nothing failed. In another fixture where the typo replaced the real key, the column would get NULL or its default, and the NOT NULL constraint would catch it only if there is one (the missing name case raised NotNullViolation). For nullable columns the fixture quietly loses data.

The cheap protection is to check the fixture's keys against the table before loading:

cols = {r[0] for r in conn.execute(
    "select column_name from information_schema.columns where table_name = %s", (table,))}
unknown = {k for row in d[table] for k in row} - cols
assert not unknown, f"{table}: unknown keys {sorted(unknown)}"

Put that in the loader so every fixture gets it. It is the same lesson as strict decoding in application code (handling unknown JSON fields in Go): the default of ignoring what you do not recognize hides typos.

Isolation between tests

The db fixture creates the schema and yields the connection, and rolls back at the end:

@pytest.fixture
def db():
    with psycopg.connect(DSN) as conn:
        conn.execute(open("fx_schema.sql").read())
        conn.commit()
        yield conn
        conn.rollback()

Because the fixture rows are inserted inside the test's transaction and never committed, the next test starts empty: test_isolated asserted count(*) from fx_customers == 0 and passed after test_totals had loaded two customers. All seven tests were independent without truncating anything. For schemas large enough that creating them per test is slow, create once per session and keep the rollback per test. The caveat is code under test that commits (or opens its own connection), which would see and leave real data; for that, use truncation or a fresh database per test module.

Other ways to keep fixtures honest

• Generate the common part. A fixture with 500 near-identical rows should be a generator with a seed (mock data generators for JSON), and a few hand-written rows for the cases that matter.

• Name rows by role. Use ids like 10 for "the paid order" and document them in the file, or use a key and a lookup. Tests that refer to customer 1 without saying why are hard to change.

• Version the fixture with the schema. Keep it in the same commit as the migration that changes the table.

• Keep production data out. Fixtures are the one place a real email or name tends to leak into a repository.

For other databases the pattern changes in detail but not idea: JSON_TABLE in MySQL 8.0.4+ (JSON_TABLE in MySQL) and json_each in SQLite (expanding JSON arrays with json_each) do what jsonb_to_recordset does here. The function reference is the PostgreSQL manual on JSON functions, and the driver's JSON adaptation is in the psycopg 3 documentation. To write the schema for a fixture file, the JSON schema generator helps. See testing code that produces JSON for the rest of the test toolkit.

When JSON fixtures are not the best way to seed a test

You test the loader. If the code under test is the import, the fixture must be in the input format of the import.

Rows depend on each other in complicated ways. A factory function (make_order(customer=...)) creates what a test needs and nothing more. Big shared fixtures make every test depend on rows it does not read.

The data is generated by the database. Sequences, defaults and triggers produce values a fixture should not hard-code. Let the database create them and read them back.

The suite is tiny. For three tests with two rows each, a few INSERT statements in the test are the clearest option.