Fixtures vs Seeds vs Test Data: Know the Diff

Most teams use "fixture," "seed," and "test data" interchangeably — and then spend hours debugging a CI failure that turns out to be a foreign key loaded in the wrong order, or a staging environment that quietly inherited production reference data three deploys ago. The vocabulary collapse is the root cause. When a backend engineer says "seed the DB" and an SDET says "load the fixture," they may be describing completely different operations with completely different lifecycle expectations.

The distinction matters operationally. Fixtures are deterministic, test-scoped snapshots of state — they exist to make a specific assertion repeatable. Seeds are environment bootstrap data — they exist so the application can start. Reference data (country codes, currency enums, product categories) is neither: it's quasi-static lookup state that belongs in migrations, not in either of the other two buckets. Mixing them creates the worst class of test data bug: one that's invisible until it isn't.

By the end of this article you'll have a working mental model and a practical implementation pattern for keeping all three separated — in your schema, your CI pipeline, and your deployment runbook.

Fixtures, Seeds, and Reference Data: Precise Definitions

A fixture is test-scoped, owned by a test or test suite, and should be torn down (or rolled back) when the test completes. In Pytest with SQLAlchemy, that means a transactional fixture that wraps each test in a savepoint. In factory_boy or FactoryBot, it means a factory that creates rows inside a transaction your test controls. The fixture's only job is to make the system-under-test see a predictable world for the duration of one assertion. For a deep dive on transactional rollback patterns, the article on seeding test databases with SQL fixtures covers the savepoint mechanics in detail.

A seed is environment-scoped bootstrap data — the minimum rows an application needs to be functional at startup. Think: default admin user, initial plan tiers, the "system" tenant in a multi-tenant SaaS. Seeds run once per environment stand-up, are idempotent, and should survive across test runs. Reference data — ISO country codes, currency symbols, 3DS transaction status enums — is a third category entirely. It changes on a schema migration cadence, not a test cadence, and belongs in a versioned migration file (Alembic, Flyway, Liquibase), not in a seed script and certainly not in a per-test fixture.

Implementing Clean Separation in a Postgres + Pytest Stack

Start by encoding the separation in your database schema itself. Use a dedicated schema or a table-naming convention so that reference data tables are obviously distinct from transactional tables. Then enforce it in CI.

-- migrations/V012__reference_data_currencies.sql
-- Reference data lives in migrations, versioned, not in seeds or fixtures
INSERT INTO ref.currencies (code, numeric_code, decimal_places)
VALUES
  ('USD', 840, 2),
  ('EUR', 978, 2),
  ('JPY', 392, 0)
ON CONFLICT (code) DO UPDATE
  SET numeric_code    = EXCLUDED.numeric_code,
      decimal_places  = EXCLUDED.decimal_places;

The ON CONFLICT … DO UPDATE makes the migration re-runnable without duplicates — essential when you're spinning up ephemeral Postgres containers in GitHub Actions. Now seeds and fixtures never need to touch ref.* tables; they can assume the migration has already run.

For fixtures, use factory_boy with a SQLAlchemy session scoped to a savepoint:

# conftest.py
import pytest
from sqlalchemy import event
from myapp.db import engine, Session
from myapp.factories import OrderFactory, UserFactory

@pytest.fixture(scope="function")
def db_session():
    conn = engine.connect()
    trans = conn.begin()
    session = Session(bind=conn)
    session.begin_nested()  # SAVEPOINT

    @event.listens_for(session, "after_transaction_end")
    def restart_savepoint(session, transaction):
        if transaction.nested and not transaction._parent.nested:
            session.begin_nested()

    yield session
    session.close()
    trans.rollback()
    conn.close()

@pytest.fixture
def order_with_items(db_session):
    user = UserFactory(session=db_session)
    return OrderFactory(user=user, item_count=3, session=db_session)

This pattern keeps every test isolated without truncating tables between runs. In a suite of 800 tests against a real Postgres instance, switching from TRUNCATE-based teardown to savepoint rollback cut total fixture overhead from roughly 40 seconds to under 4 seconds — because ROLLBACK TO SAVEPOINT is a log operation, not a heap scan. Seeds (the admin user, plan tiers) are loaded once in a scope="session" fixture that runs before the savepoint layer. Reference data is already present from migrations. The three layers never collide.

For seeds specifically, write them as idempotent Python scripts, not raw SQL dumps, so they're auditable in version control and safe to run in any environment:

# scripts/seed_env.py
from myapp.db import Session
from myapp.models import Plan

PLANS = [
    {"slug": "free",  "monthly_usd": 0},
    {"slug": "pro",   "monthly_usd": 29},
    {"slug": "team",  "monthly_usd": 99},
]

def seed_plans(session):
    for p in PLANS:
        session.merge(Plan(**p))  # upsert by PK
    session.commit()

if __name__ == "__main__":
    with Session() as s:
        seed_plans(s)

session.merge() does a SELECT-then-INSERT-or-UPDATE, keeping seeds idempotent across re-runs. Keep this script in scripts/, not in your test suite — it's not a test artifact.

Where Senior Engineers Still Get This Wrong

Putting reference data in seed scripts is the most common mistake, and it happens because the line between "data the app needs" and "data that defines the domain" feels blurry at 2am. The cost surfaces later: a new environment stands up, the migration runs, then the seed script runs and hits a unique constraint violation on the currency codes that the migration already inserted. Now your CI pipeline fails on environment provisioning, not on tests, and the error message points at the seed script rather than the architectural mistake. Move reference data into migrations; it belongs there permanently.

Sharing fixture state across tests is the second failure mode. A scope="module" factory that creates a user once and lets multiple tests mutate it is a seed pretending to be a fixture. One test updates the user's email, the next test asserts on the original email, and you get an order-dependent failure that only reproduces when pytest runs in alphabetical order. If you're seeing NULL semantics breaking aggregate comparisons in shared fixtures, the scope is almost certainly wrong. Keep fixture scope at function unless you have a measured performance reason not to, and treat that reason as technical debt.

Myths That Cause Production Incidents

"A prod data clone is a test environment." It isn't. A production clone is a liability: real PII, real payment tokens (including 3DS Cardinal Commerce transaction IDs and device fingerprints), real customer records. The moment a developer runs a cleanup script against what they think is a test environment and it turns out to be a slightly-stale prod clone, you have a data loss incident. Best practice for production deployment is to maintain an explicit environment tag in your config (not just a database URL) and require that tag to equal test or staging before any destructive seed or fixture operation runs. Candidate applications for accidental test data cleanup are exactly the ones missing this guard.

"Randomness equals coverage." Calling Faker().email() in every factory method sounds thorough but produces non-reproducible failures. When a test fails because a randomly-generated username contained a character your validator rejects, you can't replay it. Use Faker for realistic shape, but fix the seed (Faker(seed=42)) in CI and reserve true randomness for property-based tests with Hypothesis, where the framework handles shrinking and replay automatically. A related myth: snapshots are TDM. A database snapshot captures a moment; it doesn't give you a factory, a lifecycle, or isolation. It's a starting point, not a test data management strategy. Foreign key insertion order bugs — the kind covered in depth when discussing cascade failures from out-of-order FK seeds — are invisible in snapshots until they detonate in CI.

The fix is mostly definitional: fixtures are test-scoped and rolled back, seeds are environment-scoped and idempotent, reference data lives in migrations. Encode that in your project structure and enforce it in CI before it becomes a production incident. If you're inheriting a codebase where all three are tangled together, start by auditing which tables your seed scripts touch — that inventory usually reveals the fastest wins.

Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.