Pytest Database Fixtures That Stay Fast and Honest

Every pytest fixture has a scope, and scope decides how often the setup and teardown code runs. The pytest docs list five values — function, class, module, package, and session — ordered from narrowest to broadest. A function fixture (the default) is torn down at the end of each test. A session fixture is built once when first requested and destroyed only when the whole run ends.

import pytest

@pytest.fixture(scope="function")   # the default — rebuilt for every test
def payload():
    return {"email": "ada@example.com"}

@pytest.fixture(scope="module")     # once per test file
def api_client():
    client = make_client()
    yield client
    client.close()

@pytest.fixture(scope="session")    # once for the entire run
def engine():
    engine = create_engine(TEST_DATABASE_URL)
    yield engine
    engine.dispose()

For database work the choice is not cosmetic. Building a SQLAlchemy Engine and opening a Postgres connection is expensive enough to dominate the clock on any suite past a few dozen tests, so the engine and any read-only reference data belong at session scope, while the per-test transaction that isolates writes belongs at function scope. The table below is the trade each level makes.

The pattern that holds up keeps the expensive, stable things at a broad scope and layers a narrow-scoped fixture on top for isolation — the trick behind the rollback pattern below.

Here is the single most useful pattern for a Postgres test suite, and the one thin tutorials skip. Instead of resetting the database between tests, you open a transaction before each test, run the test inside it, and roll it back afterward. Postgres then discards everything the test wrote and hands the next test the same baseline it started from, without truncating a single table. A rollback is far cheaper than a TRUNCATE ... CASCADE plus a reseed, so the suite stays fast as it grows.

The catch is that ORM code wants to call session.commit(), and a naive setup would commit straight through to the database and defeat the rollback, a problem SQLAlchemy heads off with SAVEPOINTs. You begin a real transaction on a raw connection, bind the Session to that connection, and tell the session to implement its own commit/rollback as SAVEPOINTs inside your outer transaction.

In SQLAlchemy 2.0 this is one parameter. The official recipe for joining a Session into an external transaction passes join_transaction_mode="create_savepoint", and the docs note that the event handlers older versions needed to reset the nested transaction are no longer required.

import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import Session

TEST_DATABASE_URL = "postgresql+psycopg2://app:app@localhost:5432/app_test"

@pytest.fixture(scope="session")
def engine():
    engine = create_engine(TEST_DATABASE_URL)
    yield engine
    engine.dispose()

@pytest.fixture
def db_session(engine):
    connection = engine.connect()
    transaction = connection.begin()          # outer transaction, never committed
    session = Session(bind=connection, join_transaction_mode="create_savepoint")
    try:
        yield session
    finally:
        session.close()
        transaction.rollback()                # discard everything the test wrote
        connection.close()

A test that uses db_session can insert rows, call session.commit() as many times as its code path needs, and read back exactly what committed — all against a SAVEPOINT. When the fixture tears down, the outer transaction.rollback() throws the whole thing away. Because the engine is session-scoped and only db_session is function-scoped, you pay for the connection a single time while every test still gets its own clean transaction.

If you're on Django, pytest-django gives you the same isolation without wiring the transaction yourself. Mark a test with @pytest.mark.django_db and, per the pytest-django database docs, it runs inside a transaction that is rolled back at the end — the same mechanism Django's own TestCase uses.

import pytest
from django.contrib.auth import get_user_model

@pytest.mark.django_db
def test_new_user_is_active():
    User = get_user_model()
    user = User.objects.create_user("ada@example.com", password="pw")
    assert user.is_active

This marker has one important exception worth flagging. When your code relies on transaction.on_commit() hooks, or a test spins up the live_server fixture, or you specifically want to assert real commit-and-rollback behavior, the rollback wrapper works against you, because nothing ever commits and those paths never fire. For those cases pass transaction=True, which flushes the database between tests instead of wrapping them:

@pytest.mark.django_db(transaction=True)
def test_email_is_queued_on_commit():
    # on_commit hooks actually run here, because the test really commits
    register_user("ada@example.com")
    assert len(mail.outbox) == 1

The docs are blunt about the cost. Flushing the database between tests runs much slower than rolling a transaction back, so reach for transaction=True only when you're actually testing transaction behavior, and default to the plain marker everywhere else.

Rollback isolation has one failure mode worth knowing before it bites you in production. A foreign key declared DEFERRABLE INITIALLY DEFERRED is not checked when you run the INSERT; PostgreSQL's docs state plainly that "DEFERRED constraints are not checked until transaction commit." A rollback fixture never commits its outer transaction, and even the session.commit() calls inside a test only release SAVEPOINTs rather than committing the top-level transaction. So a row that dangles a deferred foreign key at nothing will sail through a rollback-isolated test and then blow up the first time real application code commits it.

When a test genuinely needs to exercise that deferred check, force it with SET CONSTRAINTS ALL IMMEDIATE at the point you want the constraint evaluated, or run that specific test with real commits via @pytest.mark.django_db(transaction=True) instead of the rollback path.

-- inside the test's transaction, before you expect the check to fire
SET CONSTRAINTS ALL IMMEDIATE;
-- a dangling deferred FK now raises here instead of hiding until commit

Most suites never hit this, because most schemas don't defer their constraints. If yours does — cyclic references between two tables are the usual reason — keep at least one commit-based test on that path so the rollback pattern isn't silently covering for a bug.

Rollback keeps tests from stepping on each other, but it puts nothing in the database to begin with. Something still has to establish the realistic starting state your integration tests query against — the customers and their orders, the reference tables they join to — and the wrong place to do that is inside a per-test fixture that regenerates it every time.

Seed it once, ahead of the run — one CLI command before you invoke pytest locally, or a step after migrations and before the test job in CI. Seedfast reads your live schema and generates realistic, connected rows that satisfy its constraints, so a single seedfast seed fills the whole database with a baseline your tests can read without you hand-writing a row of it. Pytest's scoping and the rollback fixture take it from there, every test seeing the baseline and rolling back its own writes on top.

# conftest.py — assert the baseline is present, fail loud if it isn't
import pytest
from sqlalchemy import text

@pytest.fixture(scope="session", autouse=True)
def seeded_baseline(engine):
    with engine.connect() as conn:
        rows = conn.execute(text("SELECT count(*) FROM customers")).scalar_one()
    if rows == 0:
        pytest.exit(
            "Test database is empty. Seed a baseline first:\n"
            "  seedfast seed --scope 'a few customers with orders and line items'",
            returncode=1,
        )

That autouse session fixture doesn't generate anything — it checks that the baseline seeded outside the test run actually landed and stops the suite with a useful message if someone forgot to seed. Compare that shape with the alternative some tools push, where every fixture calls out to a generation service at test time and couples your setup to a network call on the hot path. One seed step up front, described in plain English with the --scope flag, keeps generation out of the loop, and the getting-started guide covers seedfast connect and seedfast seed for the first run.

These three approaches solve overlapping problems, and the honest answer is that a mature suite often uses more than one. The question is which job each is actually good at.

Raw SQL fixtures are the fastest thing to write and the first to rot. A hand-maintained INSERT script freezes the table shape on the day you wrote it, and the next NOT NULL column turns it into a failing insert three layers removed from the test. factory_boy earns its place for the object under test — when a test needs "an admin user with a suspended subscription", a factory builds precisely that, with sequences and SubFactory relationships, and reads clearly at the call site.

import factory
from myapp.models import User

class UserFactory(factory.django.DjangoModelFactory):
    class Meta:
        model = User

    email = factory.Sequence(lambda n: f"user{n}@example.com")
    role = "member"
    is_active = True

# in a test: the exact object this test asserts on
user = UserFactory(role="admin", is_active=False)

Where factory_boy stops paying is the background. Populating fifty tables of realistic, foreign-key-valid data so a reporting query has something to run against means hand-defining a factory per table and keeping each in step with migrations — the volume a schema-aware baseline covers without the upkeep. The two are complementary rather than rival. Seed the connected bulk once, build the single asserted-on object with a factory inside the test, and treat it as test data management split by job rather than one tool doing both.

Default to seeding once and isolating with rollback. It's the fast path, and it fits the large majority of integration tests, which read the baseline, write a little, assert, and don't care whether their neighbor's rows exist. Session-scoped baseline plus function-scoped transactional rollback is the shape to reach for first.

Re-seeding for an individual test earns its cost only when the test needs a genuinely different data shape than the baseline provides. An empty-state UI test wants a customer with zero orders. For pagination you might need one customer sitting on two hundred, and a permissions test often depends on a specific matrix of roles the baseline doesn't happen to contain. In each of those, build the exact rows with a factory inside a function-scoped fixture, still under the rollback so the special-case data disappears at teardown.

The trap is the middle ground where a suite re-seeds the whole database per test out of caution — that's how a five-minute run becomes forty. If a test queries for "a customer with at least one order" instead of assuming customer 42, it can share the baseline, and most tests can be written that way with a little discipline about not hardcoding IDs. The E2E test fixtures guide works the same principle from the browser-testing side.

The CI story is short because most of it isn't pytest-specific. You bring up a Postgres service container, gate it on pg_isready so no step races a database that isn't listening, migrate, seed the baseline, then run pytest. The seed step lands last, immediately before the tests read the database. The YAML below uses Seedfast as that seed step; any seed script slots into the same position.

      - run: pip install -r requirements.txt
      - run: alembic upgrade head                     # migrate first
        env:
          DATABASE_URL: postgresql+psycopg2://app:app@localhost:5432/app_test

      - run: npx seedfast seed --scope "a few customers with orders and line items" --output json
        env:
          SEEDFAST_API_KEY: ${{ secrets.SEEDFAST_API_KEY }}
          SEEDFAST_DSN: postgresql://app:app@localhost:5432/app_test

      - run: pytest -q                                # then read the database
        env:
          TEST_DATABASE_URL: postgresql+psycopg2://app:app@localhost:5432/app_test

The full mechanics of the service container, the health-check gate, and the migrate-then-seed ordering get their own walkthrough in seeding a Postgres test database in GitHub Actions — there's no reason to repeat it here. The Seedfast-specific CI setup, including the API key and non-interactive scoping, lives in the CI/CD database seeding docs. Whichever way you seed, the running order is the load-bearing part, and this is database seeding applied to a pytest job like any other.

The complete file below wires together a session-scoped engine, an autouse guard that fails fast when the baseline is missing, and the function-scoped pytest Postgres fixture (db_session) that every test depends on. Drop it at the root of your test tree and the whole pytest test database setup fits in one file: seed once before the run, and each test gets the baseline plus perfect isolation.

# conftest.py
import os
import pytest
from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session

TEST_DATABASE_URL = os.environ.get(
    "TEST_DATABASE_URL",
    "postgresql+psycopg2://app:app@localhost:5432/app_test",
)


@pytest.fixture(scope="session")
def engine():
    """Built once for the whole run — opening connections is the expensive part."""
    engine = create_engine(TEST_DATABASE_URL)
    yield engine
    engine.dispose()


@pytest.fixture(scope="session", autouse=True)
def seeded_baseline(engine):
    """Assert the baseline seeded before pytest ran (locally via the CLI, or a
    prior CI step) is actually present. Fail loud rather than run empty tests."""
    with engine.connect() as conn:
        customers = conn.execute(
            text("SELECT count(*) FROM customers")
        ).scalar_one()
    if customers == 0:
        pytest.exit(
            "Test database is empty. Seed a baseline first:\n"
            "  seedfast seed --scope 'a few customers with orders and line items'",
            returncode=1,
        )


@pytest.fixture
def db_session(engine):
    """Function-scoped session bound to an outer transaction that is always
    rolled back. Tests may call session.commit() freely — it commits to a
    SAVEPOINT, and the outer rollback discards everything at teardown."""
    connection = engine.connect()
    transaction = connection.begin()
    session = Session(bind=connection, join_transaction_mode="create_savepoint")
    try:
        yield session
    finally:
        session.close()
        transaction.rollback()
        connection.close()

A test then reads like plain application code, and the isolation is invisible:

def test_order_total_sums_line_items(db_session):
    customer = db_session.execute(
        text("SELECT id FROM customers LIMIT 1")
    ).scalar_one()

    db_session.execute(
        text(
            "INSERT INTO orders (customer_id, status) "
            "VALUES (:cid, 'open') RETURNING id"
        ),
        {"cid": customer},
    )
    db_session.commit()  # commits to a SAVEPOINT, not the real database

    open_orders = db_session.execute(
        text("SELECT count(*) FROM orders WHERE status = 'open'")
    ).scalar_one()
    assert open_orders >= 1
    # teardown rolls the outer transaction back — the next test never sees this order

Run it, and the moment the fixture tears down, the order this test created is gone. There's nothing to clean up or truncate, and the baseline the whole suite reads against sits exactly as seedfast seed left it.

The shape that holds up is layered. At the bottom sit a session-scoped engine and a baseline seeded once; on top of them, function-scoped transactional rollback provides isolation while a factory handles the single object a given test asserts on. Get that stack right and your fixtures stop being a maintenance surface — they go back to being plain dependency injection, which is all they were ever meant to be.

If the "seed a realistic baseline once" step is the piece you're missing, point Seedfast at a local or CI database and let it read the schema and fill it. The getting-started guide takes you from seedfast connect to a seeded database, and the 30-day free trial doesn't ask for a card.