Managing Database State in Pytest: Function vs. Session Scopes

The Cost of Clean State

A common friction point in integration testing is the trade-off between test isolation and execution speed. If you create a fresh database connection and migrate the schema for every single test function, your suite will eventually crawl to a halt. However, if you share one connection across the entire session, a single test that deletes a row or modifies a setting can cause dozens of unrelated tests to fail unpredictably.

The solution lies in how you leverage pytest fixtures and their scopes. By strategically nesting scopes, you can maintain a high-performance suite without sacrificing the reliability of your assertions.

Understanding Fixture Scopes

Pytest uses dependency injection, meaning a test function simply asks for a resource by naming it as an argument. The scope parameter in the @pytest.fixture decorator determines how often that resource is recreated:

• function: The default. Setup and teardown run for every single test.
• class: Setup runs once per test class.
• module: Setup runs once per .py file.
• session: Setup runs once for the entire test run.

When a fixture uses the yield keyword instead of return, pytest treats everything after the yield as the teardown phase, ensuring resources like network sockets or file handles are closed regardless of whether the test passed or failed.

Implementing a Layered Database Strategy

The most efficient pattern for database testing is a layered approach: use a session-scoped fixture for the heavy lifting (connecting and migrating) and a function-scoped fixture for the data state (transactions).

# conftest.py
import pytest
from my_app import Database, Session

@pytest.fixture(scope="session")
def db_engine():
    # Expensive setup: Connect and run migrations
    engine = Database.connect("sqlite:///:memory:")
    engine.create_all()
    yield engine
    # Cleanup: Close connection
    engine.dispose()

@pytest.fixture(scope="function")
def db_session(db_engine):
    # Lightweight setup: Start a transaction
    connection = db_engine.connect()
    transaction = connection.begin()
    
    yield connection
    
    # Rollback changes so the next test starts with a clean slate
    transaction.rollback()
    connection.close()

How this works in practice

In this configuration, db_engine runs exactly once. The db_session fixture depends on it, so it is injected into every test. Because the session-level fixture handles the schema and the function-level fixture handles the transaction, you get the speed of a persistent connection with the isolation of a fresh database.

Evaluating the Trade-offs

While this layered approach is powerful, it introduces specific risks:

Approach	Pros	Cons
Pure Function Scope	Perfect isolation; no state leakage.	Extremely slow; high overhead.
Pure Session Scope	Maximum execution speed.	High risk of "flaky" tests due to shared state.
Layered (Engine + Transaction)	Fast and isolated.	Complex setup; doesn't test commit behavior.

A critical limitation of the transaction-rollback pattern is that it cannot test code that explicitly manages its own transactions or performs COMMIT operations. If your application code calls commit(), the rollback() in the fixture will not be able to undo those changes, leading to state leakage.

Verifying Fixture Behavior

To confirm your scopes are working as intended, run your tests with the -v (verbose) flag. To specifically debug the lifecycle, add print statements to your fixtures and run:

# Run from the project root with appropriate permissions
pytest -s tests/

The -s flag prevents pytest from capturing stdout, allowing you to see exactly when the session-scoped engine is created versus when the function-scoped sessions are opened and closed. If you see the "Connecting to DB" message repeating for every test, your db_engine is incorrectly scoped to function.