BeginnerSDET

Build an isolated database fixture chain

Create a pytest fixture chain that spins up a Postgres test database, runs Alembic migrations to bring the schema up to date, seeds 5 rows of test data, yields the session to each test, and rolls back all changes after every test; leaving the database in a clean state for the next one.

Why this matters

Test isolation is the property that makes a test suite trustworthy. When tests share database state, failure in test A causes failure in test B; and the debugging session that follows is miserable. A well-designed fixture chain makes isolation automatic and eliminates an entire class of flaky test failures.

Before you start

Python with pytest, SQLAlchemy, Alembic, and psycopg2 installed
Docker available to run a Postgres container (or a local Postgres instance)
An existing SQLAlchemy model and Alembic migration history
Basic understanding of pytest fixtures and the yield pattern

Step-by-step guide

Write the engine fixture

Create a session-scoped fixture that starts a Postgres container (or connects to a test database), creates the engine, and yields it. Use scope='session' so the database starts once per test run, not once per test. Tear it down after all tests complete.

# conftest.py
import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

TEST_DATABASE_URL = "postgresql://postgres:password@localhost:5432/test_db"

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

@pytest.fixture(scope="session")
def tables(engine):
    # Import your Base and create all tables once per session
    from myapp.models import Base
    Base.metadata.create_all(engine)
    yield
    Base.metadata.drop_all(engine)

Run Alembic migrations in the fixture

After creating the engine, call alembic.command.upgrade(alembic_cfg, 'head') to run all migrations. This ensures your test database schema always matches your application schema. If migrations fail in the fixture, all tests fail immediately; this is the correct behaviour.

from alembic.config import Config
from alembic import command

@pytest.fixture(scope="session")
def apply_migrations(engine):
    alembic_cfg = Config("alembic.ini")
    alembic_cfg.set_main_option("sqlalchemy.url", TEST_DATABASE_URL)
    command.upgrade(alembic_cfg, "head")
    yield
    command.downgrade(alembic_cfg, "base")  # clean up after all tests

Write the transaction fixture

Create a function-scoped fixture that begins a transaction using connection.begin_nested() (a savepoint). Yield the session to the test. After the test returns, call transaction.rollback() to undo every INSERT, UPDATE, and DELETE the test made. The savepoint is the key; it rolls back without dropping the schema.

from sqlalchemy.orm import Session

@pytest.fixture(scope="function")
def db_session(engine, apply_migrations):
    connection = engine.connect()
    transaction = connection.begin()

    Session = sessionmaker(bind=connection)
    session = Session()

    # Nested transaction (savepoint) — rolls back without touching the schema
    nested = connection.begin_nested()

    yield session

    session.close()
    nested.rollback()   # undo all test writes
    transaction.rollback()
    connection.close()

Write the seed fixture

Create a fixture that depends on the transaction fixture and inserts 5 rows of realistic test data. Use realistic values, not 'test1', 'test2'; realistic data surfaces bugs that toy data misses. Yield the list of seeded objects so tests can reference them.

from myapp.models import User

@pytest.fixture(scope="function")
def seeded_users(db_session):
    users = [
        User(name="Alice Chen", email="alice@example.com", active=True),
        User(name="Bob Smith", email="bob@example.com", active=True),
        User(name="Carol Jones", email="carol@example.com", active=False),
        User(name="Dave Kim", email="dave@example.com", active=True),
        User(name="Eve Patel", email="eve@example.com", active=True),
    ]
    db_session.add_all(users)
    db_session.flush()  # assign IDs without committing
    yield users

Write two tests and verify isolation

Write test_count_is_five (assert session.query(Model).count() == 5) and test_can_add_one (insert a row, assert count is 6). Run both. Then run them in reverse order. The count should be 5 at the start of each test regardless of order; if it is not, your rollback is not working.

def test_count_is_five(db_session, seeded_users):
    count = db_session.query(User).count()
    assert count == 5

def test_can_add_one(db_session, seeded_users):
    new_user = User(name="Frank Liu", email="frank@example.com", active=True)
    db_session.add(new_user)
    db_session.flush()
    assert db_session.query(User).count() == 6

# Run: pytest -v test_isolation.py
# Run again in reverse order: pytest -v test_isolation.py --reversed
# Both runs should show test_count_is_five passing with count == 5

Relevant Axiom pages

pytest patterns Database design Database transactions

What to do next

Back to Practice Lab