skip to content

Test Transactions & SQL Logs

Tests wrap each case in an outer transaction and bind the Session to it, so every write rolls back. Interviewers ask how join_transaction_mode works and how to count statements.

on this pageshow

explore

questions

6

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()?

level: middleimportance: must knowfreq 30%

answer

  1. the Session joins, it does not begin
  2. connect, begin, then bind
  3. commit becomes RELEASE SAVEPOINT
  4. teardown rolls back the outer one

basics

~10 s

Open 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 s

The 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 lines
python
import 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)) == 0

go deeper

for a junior

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.

for a middle

Explain which SQL each Session call becomes under create_savepoint and why committed rows stay visible within the test but vanish at teardown.

for a senior

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.

for a principal

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.

open as a page

In SQLAlchemy 2.1, how do you see the SQL an engine sends during a test run, and how does echo=True differ from the 'sqlalchemy.engine' logger?

level: juniorimportance: should knowfreq 35%

basics

~20 s

Pass echo=True to create_engine to print each statement and its parameters to standard output, or set the 'sqlalchemy.engine' logger to INFO (DEBUG adds rows). The logger joins your logging setup; echo adds its own handler and can duplicate lines.

open as a page

In a SQLAlchemy test, how do you count the SQL statements a code path sends with a before_cursor_execute listener, and what inflates the count?

level: middleimportance: should knowfreq 25%

basics

~10 s

Register a before_cursor_execute listener on the test's Connection with event.listen, append each statement, run the code, then event.remove it. SAVEPOINTs from the test recipe, refresh SELECTs after commit, and per-batch INSERTs all count.

open as a page

As the lead of a SQLAlchemy 2.1 service on PostgreSQL, would you run its repository tests on in-memory SQLite or on PostgreSQL, and why?

level: principalimportance: should knowfreq 28%

basics

~20 s

Default to PostgreSQL for every test that touches the database, isolated with the outer-transaction recipe. In-memory SQLite starts faster but compiles and enforces differently, so it can pass code PostgreSQL rejects; keep it, if at all, for dialect-free logic.

open as a page

A SQLAlchemy test binds Session(bind=connection) to an outer transaction without join_transaction_mode; after the code rolls back and retries, its committed row survives teardown. Why?

level: seniorimportance: nice to knowfreq 15%

basics

~10 s

The default join_transaction_mode, "conditional_savepoint", behaves as "rollback_only" when the connection holds no SAVEPOINT: Session.rollback() rolls back the outer transaction itself, and the retry's commit runs in a fresh transaction that really commits.

open as a page

A SQLAlchemy savepoint test recipe isolates tests on PostgreSQL, yet on in-memory SQLite with StaticPool committed rows survive teardown; what is wrong and how do you fix it?

level: seniorimportance: nice to knowfreq 18%

basics

~20 s

Python's sqlite3 driver defaults to legacy transaction control and sends no BEGIN for the outer connection.begin(), so the Session's SAVEPOINT stands alone and its release persists. Pass connect_args={"autocommit": False} on Python 3.12+, or emit BEGIN from engine event hooks.

open as a page