In SQLAlchemy 2.1, how do you bind a test's Session to an outer transaction so its writes roll back even after Session.commit()?
answer
- the Session joins, it does not begin
- connect, begin, then bind
- commit becomes RELEASE SAVEPOINT
- teardown rolls back the outer one
basics
~10 sOpen a Connection, call connection.begin(), and build the Session with bind=connection and join_transaction_mode="create_savepoint". The Session's commits then only release SAVEPOINTs; at teardown, rolling back the outer transaction discards everything the test wrote.
solid answer
~30 sThe recipe is `connection = engine.connect()`, `trans = connection.begin()`, then `Session(bind=connection, join_transaction_mode="create_savepoint")`. Because the connection is already in a transaction, the Session does not start its own: in `create_savepoint` mode every Session transaction is a `SAVEPOINT` on top of the outer one, so `session.commit()` emits `RELEASE SAVEPOINT` and `session.rollback()` emits `ROLLBACK TO SAVEPOINT`, leaving the outer transaction open. Committed rows stay visible to the rest of the test because it shares that connection. Teardown runs `session.close()`, `trans.rollback()` and `connection.close()`, so nothing ever commits and each test starts from the same tables without truncating. The database and driver must handle SAVEPOINT correctly.
code
python · 46 linesimport pytest
from sqlalchemy import String, create_engine, func, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
class Base(DeclarativeBase):
pass
class Customer(Base):
__tablename__ = "customer"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(50))
# stands in for the PostgreSQL URL; on SQLite, autocommit=False
# (Python 3.12+) makes the sqlite3 driver honour BEGIN and SAVEPOINT
engine = create_engine("sqlite:///recipe.db", connect_args={"autocommit": False})
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
@pytest.fixture
def session():
connection = engine.connect()
trans = connection.begin()
session = Session(bind=connection, join_transaction_mode="create_savepoint")
yield session
session.close()
trans.rollback()
connection.close()
def register(session: Session, name: str) -> None:
# code under test commits exactly as it does in production
session.add(Customer(name=name))
session.commit()
def test_register_commits(session):
register(session, "ada")
assert session.scalar(select(func.count()).select_from(Customer)) == 1
def test_next_test_starts_empty(session):
assert session.scalar(select(func.count()).select_from(Customer)) == 0go deeper
Recall the three steps: connect, begin, bind a Session with join_transaction_mode="create_savepoint"; and the teardown order: close the Session, roll back, close the connection.
Explain which SQL each Session call becomes under create_savepoint and why committed rows stay visible within the test but vanish at teardown.
Point out what escapes the recipe: sessions bound to the engine, extra connections, after_commit side effects, and drivers whose SAVEPOINT handling breaks the isolation.
Decide where the recipe is mandatory, how the code under test receives the Session, and which behaviour must instead be tested with real commits across connections.
## The problem the recipe solves Repository code usually ends a unit of work with **`Session.commit()`**. A test that runs that code against a real database would leave rows behind, and the next test would start from different data. Truncating every table after each test is slow and easy to get wrong. SQLAlchemy's documented answer is to **join the Session into an external transaction** that the test owns and rolls back at the end. ## The recipe step by step 1. **`connection = engine.connect()`** checks out one connection for the test. 2. **`trans = connection.begin()`** starts the outer transaction, owned by the test rather than the code under test. 3. **`session = Session(bind=connection, join_transaction_mode="create_savepoint")`** builds a Session that uses this connection instead of checking out its own. 4. The test runs the code under test with that Session, commits and rollbacks included. 5. Teardown: **`session.close()`**, then **`trans.rollback()`**, then **`connection.close()`**. In a pytest suite these steps sit in a fixture that yields the Session; the fixture mechanics themselves are ordinary pytest. ## What the Session emits **`join_transaction_mode`** tells a `Session` what to do when its bind is a `Connection` that is **already in a transaction**. With `"create_savepoint"` it always calls `Connection.begin_nested()` for its own transactions: | Call in the code under test | SQL on the connection | Effect on the outer transaction | |---|---|---| | first statement after begin | `SAVEPOINT sa_savepoint_1` | none | | `session.commit()` | `RELEASE SAVEPOINT sa_savepoint_1` | none; the rows stay pending in it | | `session.rollback()` | `ROLLBACK TO SAVEPOINT ...` | none | | `session.close()` | `ROLLBACK TO SAVEPOINT` if one is open | none | | teardown `trans.rollback()` | `ROLLBACK` | discards everything | ## Why commit does not commit - The Session **never owns** the outer transaction, so it has nothing to commit; it only releases its own savepoint. - Released work becomes part of the outer transaction, which is still open, so **later queries in the same test see it**, because they run on the same connection. - Session-level behaviour still runs as in production: objects expire on commit (`expire_on_commit` defaults to `True`), and a `Session` `after_commit` listener fires even though the database committed nothing. A side effect hung on that event will run in the test. - Teardown's `trans.rollback()` is the one call that actually discards the data. ## Limits to know - **SAVEPOINT support is required.** SQLAlchemy's own documentation says the mode relies on working SAVEPOINT support from the database and driver. PostgreSQL qualifies. Python's `sqlite3` driver needs `connect_args={"autocommit": False}` (Python 3.12+) or event hooks that emit `BEGIN`, or the savepoint escapes the outer transaction. - **Code must receive the test's Session.** Code that builds its own Session from a `sessionmaker` bound to the **engine** checks out a different connection; on PostgreSQL its commits are real and survive teardown. - **One connection, one transaction.** Behaviour that needs a second connection, such as row locks blocking another worker or visibility after a real commit, cannot be observed inside this recipe. - **It isolates data, not schema.** Create tables once before the suite, outside the outer transaction. Schema changes made inside it roll back with the data only on databases with transactional DDL, such as PostgreSQL; on MySQL a DDL statement commits implicitly and ends the isolation. ## Handing the Session to the code under test The recipe only isolates work that runs on the test's connection, so the harness must decide how code receives its Session: - **Pass it in.** Repository functions that take a `Session` argument need nothing else. - **Bind a factory.** If code calls a `sessionmaker`, build one per test with `sessionmaker(bind=connection, join_transaction_mode="create_savepoint")` and hand that to the code instead of the application's factory. - **Core code.** Functions that take a `Connection` can be given the test's connection; their own `begin_nested()` calls become further savepoints inside the outer transaction. ## Checking that it works 1. Write one test that commits a row and asserts it is visible. 2. Write a second test that asserts the same table is empty. 3. Run both in one process, in either order. If the second fails, something escaped the outer transaction. ## Where the recipe came from Before 2.0 the recipe also needed an event handler to restart the savepoint after each commit. Since 2.0 the `join_transaction_mode` parameter does that work, and SQLAlchemy runs this recipe in its own CI.
- Why does a row committed by the code under test show up in the same test's later query?`session.commit()` only ran `RELEASE SAVEPOINT`, which folds the work into the outer transaction that is still open. Every later query runs on the same connection inside that transaction, so it sees the uncommitted rows; any other connection would not, and teardown's `trans.rollback()` removes them.
- What happens to a SQLAlchemy Session after_commit listener under this recipe?It fires on every `session.commit()`, because from the Session's point of view its transaction did commit; only a `SAVEPOINT` was released in the database. A listener that sends mail or publishes a message will do so in tests unless the test replaces it.
- Which code paths escape the rollback?Anything that does not use the test's Session or connection: a `sessionmaker` bound to the engine, a direct `engine.begin()`, or a background thread opening its own connection. Those check out other pooled connections and, on PostgreSQL, commit for real.
It is like a rehearsal where actors hand finished scenes to a stage manager who files them in a box that is shredded after the show: each actor sees the hand-off succeed, and nothing reaches the real archive.
- Truncating every table after each test instead of rolling back one outer transaction
- Expecting session.commit() under this recipe to send COMMIT to the database
- Believing session.close() alone discards the rows the test committed
- Letting code under test build its own Session from the engine and expecting it to roll back
- Running the recipe on a driver with broken SAVEPOINT handling and trusting the isolation
Assumes SQLAlchemy 2.1; join_transaction_mode arrived in 2.0, and older recipes restarted the SAVEPOINT from a Session event handler.