Migrate pytest nested-transaction tests to SQLAlchemy 2.0 with SAVEPOINT fixtures and join_transaction_mode

Migrate pytest test suites from SQLAlchemy 1.3/1.4 to 2.0 using an explicit Connection and join_transaction_mode to restore SAVEPOINT-based isolation.

When migrating a test suite from SQLAlchemy 1.3/1.4 to 2.0, patterns that previously relied on nested transactions via Postgres SAVEPOINTs can start producing inconsistent results. A common setup is to pre-populate shared fixtures once per module and then isolate each test with a nested transaction that rolls back. Under 1.4, this often worked with a simple monkeypatch that turned session.commit into session.flush. After switching to 2.0, however, tests can report missing shared data even though no low-level SQLAlchemy errors are raised.

Problem setup

The pattern below shows a package-scoped session that seeds shared data and a function-scoped nested transaction that isolates each individual test. The commit-to-flush monkeypatch ensures nothing escapes the wrapping transaction.

@pytest.fixture(scope="package")
def pkg_sess(suite_engine: Engine, pkg_patch) -> Session:
    link = suite_engine.connect()
    outer_tx = link.begin()
    db_sess = Session(bind=link)
    pkg_patch.setattr(db_sess, "commit", db_sess.flush)
    seed_shared_state(db_sess)
    try:
        yield db_sess
    finally:
        outer_tx.rollback()
        link.close()


@pytest.fixture(scope="function")
def txn_sess(pkg_sess):
    """Create a SAVEPOINT so per-test changes can be rolled back without losing shared data"""
    pkg_sess.begin_nested()
    try:
        yield pkg_sess
    finally:
        pkg_sess.rollback()

What actually goes wrong

Under SQLAlchemy 2.0, the suite starts failing with reports of missing shared rows. The approach that used to keep shared data visible inside nested tests no longer behaves as expected. After extensive trial and error, one reliable observation emerged: multiple layers of SAVEPOINTs could not be reproduced as before. A single package-level nesting with per-test rollback works, but deeper levels, such as package-level shared data plus module-level shared data plus function-level rollback, could not be made to function the same way. As a result, one module that relied on this triple layering had to seed a bit more data per test.

A working approach on SQLAlchemy 2.0

The solution that consistently restores the original intent is to keep an explicit Connection, wrap it in an outer transaction per package or per test as needed, and instruct Session to join using SAVEPOINTs. There is no need to monkeypatch commit anymore.

tests/conftest.py

from sqlalchemy import create_engine
from sqlalchemy.engine import Engine
from sqlalchemy.orm import Session
from sqlalchemy_utils import create_database, database_exists, drop_database

from myapp.db import DeclarativeBase as OrmBase


@pytest.fixture(scope="session")
def qa_engine():
    """Create a dedicated test database, run migrations, and tear it down after the session"""
    eng = create_engine(
        "postgresql+psycopg2://USER:PASSWORD@HOST:PORT/test",
        echo=False,
        future=True,
    )
    if database_exists(eng.url):
        drop_database(eng.url)
    create_database(eng.url)
    OrmBase.metadata.create_all(bind=eng)
    try:
        yield eng
    finally:
        drop_database(eng.url)


@pytest.fixture(scope="function")
def db_session(qa_engine: Engine) -> Session:
    """Open a transaction and use SAVEPOINT for per-test isolation"""
    link = qa_engine.connect()
    tx = link.begin()
    try:
        with Session(bind=link, join_transaction_mode="create_savepoint") as db:
            yield db
    finally:
        tx.rollback()
        link.close()

tests/module_a/conftest.py

from sqlalchemy.engine import Engine
from sqlalchemy.orm import Session


@pytest.fixture(scope="package")
def pkg_link(qa_engine: Engine):
    """Seed the database once per package with the data needed for Module A tests.

    The same connection and outer transaction are reused by tests in this package.
    """
    link = qa_engine.connect()
    top_tx = link.begin()
    top_sess = Session(bind=link, join_transaction_mode="create_savepoint")
    # Create shared data for this package here

    try:
        yield link
    finally:
        top_tx.rollback()
        link.close()


@pytest.fixture(scope="function")
def db_session(pkg_link):
    """Use a nested SAVEPOINT so individual tests can roll back without touching shared data"""
    sp = pkg_link.begin_nested()
    try:
        with Session(bind=pkg_link, join_transaction_mode="create_savepoint") as db:
            yield db
    finally:
        sp.rollback()

How this solves it

The key change is to drop the commit-to-flush monkeypatch and explicitly tell Session how to participate in the already-begun transaction. Using join_transaction_mode="create_savepoint" makes each test operate under a SAVEPOINT while the outer transaction remains open for the package. When the test finishes, the SAVEPOINT is rolled back, leaving the pre-seeded package data intact. Within this setup, there is no need to override commit; when seeding at the package level, commit can be called and the per-test SAVEPOINT will still roll back only the test’s own changes.

Tests at the top level expect an empty database. Tests in Module A expect pre-populated data. Tests in a sibling Module B expect a different set of shared data. The package-level setup enables shared data to be created once per module while preserving per-test isolation.

Limitations observed

Multiple nested layers of SAVEPOINTs could not be reproduced in the same way under 2.0. Package-level nesting with per-test SAVEPOINT rollback works reliably. If an additional nesting level is required, some per-test seeding may be necessary in that module.

Why this matters

Consistent transactional isolation is what keeps tests deterministic and fast. Seeding shared fixtures once per module reduces repetition and runtime, while SAVEPOINT-based rollbacks keep each test’s writes contained. Adapting the test harness to SQLAlchemy 2.0 with explicit connections and join_transaction_mode restores these properties without resorting to monkeypatching core Session behavior.

Practical takeaways

Use an explicit Connection with an enclosing transaction for the scope that owns shared data. Build each test’s Session with join_transaction_mode="create_savepoint" so that rollbacks only affect per-test writes. Skip the commit-to-flush monkeypatch; commits during package-level seeding are fine, and the SAVEPOINT rollback at the function level will leave shared rows intact. If you previously relied on more than one additional SAVEPOINT layer, be prepared to seed a bit of data per test in those modules.

With these adjustments, a 1.3/1.4-style nested-transaction test strategy can be brought forward to SQLAlchemy 2.0 with minimal friction and predictable results.