skip to content

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%

answer

  1. the driver, not SQLAlchemy
  2. legacy transaction control
  3. SAVEPOINT without a BEGIN
  4. autocommit=False in connect_args

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.

solid answer

~40 s

SQLAlchemy's `connection.begin()` relies on the driver to start the transaction, and `sqlite3` in its default legacy mode emits `BEGIN` only before DML such as INSERT, UPDATE and DELETE, skipping it for SELECT, DDL and `SAVEPOINT`. In the recipe the first statement is the Session's `SAVEPOINT`, so it runs outside any transaction, SQLite treats it as its own transaction, and `RELEASE SAVEPOINT` makes the work permanent; teardown's `ROLLBACK` has nothing to undo. On Python 3.12+ create the engine with `connect_args={"autocommit": False}`; on Python 3.11, set `isolation_level = None` in a `connect` listener and emit `BEGIN` from a `begin` listener. `StaticPool` with `check_same_thread=False` is a separate concern: it keeps every checkout on the one connection that holds the `:memory:` database.

code

python · 34 lines
python
from sqlalchemy import String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
from sqlalchemy.pool import StaticPool


class Base(DeclarativeBase):
    pass


class Customer(Base):
    __tablename__ = "customer"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))


def rows_after_teardown(connect_args):
    engine = create_engine("sqlite://", poolclass=StaticPool, connect_args=connect_args)
    Base.metadata.create_all(engine)
    connection = engine.connect()
    trans = connection.begin()
    session = Session(bind=connection, join_transaction_mode="create_savepoint")
    session.add(Customer(name="ada"))
    session.commit()
    session.close()
    trans.rollback()
    connection.close()
    with engine.connect() as conn:
        return conn.scalars(select(Customer.name)).all()


# sqlite3's default legacy transaction control: the row leaks
print(rows_after_teardown({"check_same_thread": False}))
# Python 3.12+: autocommit=False makes sqlite3 honour BEGIN, the row is gone
print(rows_after_teardown({"check_same_thread": False, "autocommit": False}))

go deeper

for a junior

Remember the in-memory SQLite test engine: StaticPool plus connect_args={"check_same_thread": False}, and that on Python 3.12+ it also needs "autocommit": False for savepoints.

for a middle

Explain what each setting does: StaticPool shares one connection, check_same_thread lifts the thread check, autocommit=False makes sqlite3 honour BEGIN.

for a senior

Diagnose the leak from the statement order in the log, fix it for the Python version in use, and add a test that proves isolation on SQLite.

for a principal

Weigh whether keeping a SQLite test path is worth the driver quirks it needs, against running the suite only on the production database.

## Two separate problems in one setup An in-memory SQLite test engine usually looks like this: ```python from sqlalchemy import create_engine from sqlalchemy.pool import StaticPool engine = create_engine( "sqlite://", poolclass=StaticPool, connect_args={"check_same_thread": False}, ) ``` Each part answers a different question: - **`"sqlite://"`** (or `sqlite:///:memory:`) is a database that lives inside **one** DBAPI connection. Another connection opens its own, empty database. - **`StaticPool`** returns the **same** DBAPI connection on every checkout, so tables created by `create_all()` are visible to every later Session. Without it SQLAlchemy picks **`SingletonThreadPool`** for a memory database, one connection per thread, and code running in another thread fails with `no such table`. - **`check_same_thread=False`** lets that single connection be used from a thread other than the one that created it; otherwise `sqlite3` raises `ProgrammingError: SQLite objects created in a thread can only be used in that same thread`. None of this touches **transaction control**, which is where the leak comes from. ## Why the rows leak Python's `sqlite3` driver defaults to **legacy transaction control** (`Connection.autocommit` is `sqlite3.LEGACY_TRANSACTION_CONTROL` on Python 3.12+). In that mode it emits `BEGIN` only before DML statements such as INSERT, UPDATE and DELETE, and skips it for SELECT, DDL and `SAVEPOINT`. SQLAlchemy's `Connection.begin()` sends nothing itself and leaves the `BEGIN` to the driver. 1. The test calls `connection.begin()`: no `BEGIN` reaches SQLite yet. 2. The Session, in `create_savepoint` mode, sends `SAVEPOINT sa_savepoint_1`. The driver does not precede it with `BEGIN`, so the savepoint stands alone. 3. The INSERT runs inside that savepoint. 4. `session.commit()` sends `RELEASE SAVEPOINT sa_savepoint_1`, which makes the work permanent, because no enclosing transaction exists. 5. Teardown's `trans.rollback()` finds nothing to undo. SQLAlchemy's SQLite documentation lists exactly this: a SAVEPOINT emitted before a BEGIN works on its own but does not take part in the enclosing transaction. ## The fixes | Python version | Fix | How | |---|---|---| | 3.12 and later | modern sqlite3 transaction control | `create_engine(url, connect_args={"autocommit": False})` | | 3.11 (SQLAlchemy 2.1's minimum) | SQLAlchemy emits BEGIN | a `connect` listener sets `dbapi_connection.isolation_level = None`; a `begin` listener runs `conn.exec_driver_sql("BEGIN")` | Do **not** use `isolation_level="AUTOCOMMIT"`: that commits every statement, and SQLAlchemy's documentation notes it is incompatible with the non-legacy hooks above. ## Checking the fix - Run one test that commits and a second that asserts the table is empty, against the SQLite engine, in the same run. This pair is the end-to-end check. - Do **not** trust `echo` here. The log is identical in both modes: `BEGIN (implicit)`, `SAVEPOINT sa_savepoint_1`, the INSERT, `RELEASE SAVEPOINT sa_savepoint_1`, `ROLLBACK`. `BEGIN (implicit)` records that SQLAlchemy left the `BEGIN` to the driver, not that the driver sent one, which is why this leak survives code review. - Keep the PostgreSQL run in CI: SQLite passing is evidence about SQLite only. ## The working test engine in one place ```python engine = create_engine( "sqlite://", poolclass=StaticPool, connect_args={"check_same_thread": False, "autocommit": False}, ) ``` - `poolclass=StaticPool` keeps one connection, so the `:memory:` database survives between Sessions. - `check_same_thread=False` lets that connection cross threads. - `autocommit=False` makes the outer `connection.begin()` real, so the recipe's savepoints nest inside it. ## A check that does work The DBAPI connection knows the truth. `sqlite3` connections expose `in_transaction`, and SQLAlchemy exposes the DBAPI connection as `connection.connection.dbapi_connection`: ```python connection = engine.connect() trans = connection.begin() print(connection.connection.dbapi_connection.in_transaction) ``` Under legacy control this prints `False` straight after `begin()`, and still `False` after a SELECT. With `connect_args={"autocommit": False}` it prints `True`. An assertion on it in the harness setup turns a silent leak into an immediate failure on any machine where the driver behaves differently. ## What 2.1's documentation adds - SQLAlchemy 2.1's SQLite documentation calls `StaticPool` **an older approach**, suitable only when all access is serialized, as in a single-threaded test suite. For concurrent threads or coroutines it recommends a shared-cache URL, `sqlite:///file::memory:?cache=shared&uri=true`, which gets `QueuePool` and a separate transaction per connection. - `StaticPool` shares **one** transaction state: a `ROLLBACK` issued by any Session on the engine also discards uncommitted work from every other Session. - SQLAlchemy 2.1 warns when SQLite URI arguments such as `mode` or `cache` appear without `uri=true`. Earlier releases dropped them silently, so `sqlite:///file:mydb?mode=memory` connected to a file on disk named `file:mydb`, and a test meant to run in memory left a database file behind.

  • Why does an in-memory SQLite test on default pooling raise no such table from another thread?
    For a memory database SQLAlchemy picks `SingletonThreadPool`, which keeps one connection per thread, and each `sqlite3` connection to `:memory:` has its own database. The thread that ran `create_all()` has the tables; another thread's connection sees an empty database. `StaticPool` with `check_same_thread=False`, or a shared-cache URI, fixes it.
  • What does StaticPool cost a test that runs two Sessions at once?
    Both Sessions share one DBAPI connection and therefore one SQLite transaction. A `ROLLBACK` from either discards the other's uncommitted work, and interleaved commits interfere. SQLAlchemy's documentation limits `StaticPool` to fully serialized access.

  • Assuming connection.begin() always sends BEGIN to SQLite at once
  • Believing StaticPool or check_same_thread controls transaction behaviour
  • Fixing the leak with isolation_level="AUTOCOMMIT"
  • Treating a green SQLite run as proof the recipe isolates tests on PostgreSQL
  • Sharing one StaticPool engine across concurrent Sessions and expecting separate transactions

Assumes SQLAlchemy 2.1 on Python 3.12 or later; 2.1 still supports Python 3.11, where sqlite3 has no autocommit attribute and the BEGIN event recipe is needed.