Python Database Testing Anti-Patterns: Mocking vs Real DB, Fixture Isolation, and Test Speed

Most Python database test suites share the same set of mistakes. Tests that mock Session.query(). Fixtures that share state between tests. Integration tests that take 20 minutes because developers tried to be too thorough. This guide identifies the most damaging anti-patterns and shows how to replace them with patterns that actually catch bugs.

Anti-Pattern 1: Mocking the Database

The most common mistake in Python database testing is mocking SQLAlchemy or asyncpg at the session or connection level:

# WRONG: This test proves nothing
@patch("myapp.database.db_session")
def test_get_user(mock_session):
    mock_session.query.return_value.filter.return_value.first.return_value = User(
        id=1, email="test@example.com"
    )
    user = get_user(1)
    assert user.email == "test@example.com"

This test passes regardless of whether get_user has a bug. It will pass even if you completely rewrite the query to use different filters—the mock returns whatever you programmed it to return. You are testing your mock, not your code.

Why mocking fails for database code:

1. Query builder chains are complex. mock.query.return_value.filter.return_value.first.return_value gets the return value when the exact chain is called—if your code uses .filter_by() instead of .filter(), the mock returns a different Mock object, not your data
2. SQL-level errors (constraint violations, deadlocks, type mismatches) never appear in mocked tests
3. ORM relationship loading behavior can't be tested when sessions are mocked
4. Query count regressions (N+1) are invisible

The fix: Use in-memory SQLite or Testcontainers with transactional rollback for speed. A real database test that runs in 50ms is better than a mock test that runs in 5ms but proves nothing.

Anti-Pattern 2: Not Isolating Tests from Each Other

The second most damaging pattern is test contamination—tests that depend on database state left by previous tests:

# WRONG: Tests share a session and commit to it
class TestUserRepository:
    def setup_method(self):
        self.session = Session()  # Same session across all tests

    def test_create_user(self):
        user = User(email="a@test.com")
        self.session.add(user)
        self.session.commit()  # State persists after test

    def test_list_users(self):
        # This test passes only if test_create_user ran first
        users = self.session.query(User).all()
        assert len(users) == 1  # Breaks if test order changes

This creates order-dependent tests that fail non-deterministically when pytest randomizes execution order (pytest-randomly) or when tests are parallelized.

The fix: Every test runs in its own transaction that rolls back after completion:

@pytest.fixture
def db_session(engine):
    connection = engine.connect()
    transaction = connection.begin()
    session = Session(bind=connection)
    yield session
    session.close()
    transaction.rollback()
    connection.close()

Each test gets a pristine database. Commits inside the test are rolled back when the fixture tears down.

Anti-Pattern 3: Using the Production Database for Tests

# WRONG: Test suite targets production
DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://user:pass@prod-db:5432/app")

This belongs in the "obvious but still happens" category. Developers run tests locally against their dev database, which is fine—until CI is configured with the production connection string "just for now." Test data pollution and accidental truncates have ended on-call shifts.

The fix: Enforce a separate test database at the configuration level:

# conftest.py
import pytest

@pytest.fixture(scope="session", autouse=True)
def enforce_test_database():
    db_url = os.getenv("DATABASE_URL", "")
    assert "test" in db_url or "localhost" in db_url or ":memory:" in db_url, \
        f"Tests must use a test database, got: {db_url}"

This fixture fails the entire test run if the database URL doesn't contain "test" or "localhost". CI can override this for integration environments with an explicit TEST_DATABASE_URL variable.

Anti-Pattern 4: Over-Seeding with Too Much Data

Loading your full production seed dataset for every test:

# WRONG: 10,000 rows of fixtures for a test that checks one user
@pytest.fixture(autouse=True)
def load_fixtures(db_session):
    with open("fixtures/full_seed.json") as f:
        data = json.load(f)
    for row in data:
        db_session.add(User(**row))
    db_session.commit()

This makes tests slow (10,000 rows × 500 tests = enormous overhead) and creates invisible dependencies. A test that queries User.objects.first() may accidentally depend on which user is first in the fixture file.

The fix: Create only the data each test needs, directly in the test or in a minimal fixture:

def test_user_can_update_email(db_session):
    user = User(email="old@example.com", name="Test")
    db_session.add(user)
    db_session.flush()

    update_email(user.id, "new@example.com", session=db_session)

    db_session.refresh(user)
    assert user.email == "new@example.com"

The test creates exactly one user and tests exactly one behavior. No fixture file, no mystery dependencies.

Anti-Pattern 5: Testing at the Wrong Layer

# WRONG: Testing SQL logic via the UI
def test_user_count_displayed_on_dashboard(browser):
    browser.get("/admin/dashboard")
    count = browser.find_element_by_id("user-count").text
    assert count == "1,247"  # This number changes every day

This test:

1. Fails every time a new user registers
2. Takes 10 seconds to run
3. Tests three unrelated things (rendering, routing, and database query)

The fix: Test database logic at the repository layer, not through the browser. Reserve end-to-end tests for user flows, not data counts.

# RIGHT: Test the query logic directly
def test_user_count_query(db_session):
    User.objects.bulk_create([User(email=f"u{i}@t.com") for i in range(10)])
    assert User.objects.count() == 10

# RIGHT: Test the API endpoint returns a count field
def test_dashboard_api_returns_user_count(client, db_session):
    User.objects.create(email="t@t.com")
    response = client.get("/api/dashboard/stats/")
    assert "user_count" in response.json()
    assert response.json()["user_count"] >= 1

Anti-Pattern 6: Not Testing Constraint Violations

Most test suites test the happy path and ignore database constraints entirely:

# WRONG: No tests for what happens when constraints are violated
def test_create_user_happy_path(db_session):
    user = UserFactory()
    assert user.email is not None
    # 0 tests for unique constraint, NOT NULL, foreign key, check constraints

Constraint violations are the second-most-common source of production errors in database-backed applications (after N+1 queries). Test them explicitly:

def test_duplicate_email_raises_integrity_error(db_session):
    db_session.add(User(email="dupe@test.com", name="Original"))
    db_session.flush()

    db_session.add(User(email="dupe@test.com", name="Duplicate"))
    with pytest.raises(IntegrityError):
        db_session.flush()

def test_null_name_raises_integrity_error(db_session):
    db_session.add(User(email="null@test.com", name=None))
    with pytest.raises(IntegrityError):
        db_session.flush()

def test_invalid_user_id_raises_foreign_key_error(db_session):
    with pytest.raises(IntegrityError):
        db_session.add(Order(user_id=99999, total=100))
        db_session.flush()

Anti-Pattern 7: Ignoring Test Speed Until It's Too Late

Many teams start with real database tests, watch the suite slow to 20 minutes, then swing to mocking everything. The right approach is staying fast from the beginning.

Speed strategies that don't sacrifice coverage:

Benchmark your fixture creation. If conftest.py takes 30 seconds before a single test runs, you have a seeding problem—not a database problem.

Anti-Pattern 8: Not Testing Migrations

Migrations break in ways that unit tests don't catch:

• Column renames break queries that reference the old name
• ALTER TABLE ADD COLUMN NOT NULL without a default fails on non-empty tables
• Index creation on large tables times out
• Migration reversal (downgrade) is never tested

The fix: Include a migration test in CI:

def test_migrations_run_cleanly(tmp_path):
    """Verify all migrations apply and reverse cleanly."""
    from alembic.config import Config
    from alembic import command

    alembic_cfg = Config("alembic.ini")
    alembic_cfg.set_main_option("sqlalchemy.url", "sqlite:///test_migration.db")

    # Apply all migrations
    command.upgrade(alembic_cfg, "head")

    # Reverse all migrations
    command.downgrade(alembic_cfg, "base")

    # Apply again (double-check idempotency)
    command.upgrade(alembic_cfg, "head")

Balancing Speed and Coverage

The right ratio for a mature Python application:

• Unit tests (mocked external calls, real database, in-memory SQLite): 70% of tests, run in seconds
• Integration tests (real PostgreSQL via Testcontainers, all services): 25% of tests, run in minutes
• End-to-end tests (browser automation, full stack): 5% of tests, run in HelpMeTest on schedule

The mistake is making unit tests slower to add "more coverage." Coverage comes from writing more tests at the right layer—not from adding a Testcontainers PostgreSQL instance to every test function.

Summary

Avoid these database testing mistakes:

1. Mocking sessions — proves nothing, miss real SQL bugs
2. Shared state between tests — creates non-deterministic failures
3. Production database — enforce test DB in configuration
4. Over-seeding fixtures — create only the data each test needs
5. Testing at the wrong layer — database logic at repo layer, not UI
6. Ignoring constraints — test unique, NOT NULL, and FK violations explicitly
7. Slow suites — use in-memory SQLite + transactional rollback for speed
8. Untested migrations — run upgrade + downgrade in CI

Each of these anti-patterns has cost real teams real production incidents. The patterns above prevent them.