Create the SQLAlchemy schema once, roll back per test

Running metadata.create_all and drop_all for every test is slow. Create the SQLAlchemy schema once per session and roll back each test with join_transaction_mode="create_savepoint".

A common way to isolate SQLAlchemy tests is a function-scoped fixture that calls Base.metadata.create_all(engine) before every test and drop_all(engine) after it. Every test starts with empty tables, which is what you want, but it pays for building and destroying the whole schema to get there.

The faster way gives the same isolation: create the schema once per session, run each test inside a transaction, and roll it back at the end. SQLAlchemy 2.0 documents exactly this recipe for test suites, and it keeps working when the code under test calls session.commit().

Why create_all per test is slow

create_all emits a CREATE TABLE for every table, plus CREATE INDEX, constraints, sequences and enum types. drop_all does the reverse. Each statement is DDL: the database updates its catalogs, allocates files and (on PostgreSQL and MySQL) writes all of it to the write-ahead log. Before creating anything, SQLAlchemy also checks which tables already exist, which is more round trips.

With 40 tables that's well over a hundred statements per test, and the cost grows with the schema, not with what the test does. A test that inserts one row and runs one query can spend most of its time on setup and teardown.

A rollback, by comparison, throws away the few rows the test wrote. It costs about the same whether the schema has 4 tables or 400.

The fix: an outer transaction and SAVEPOINTs

The recipe from the SQLAlchemy docs, as pytest fixtures:

# conftest.py
import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import Session

from myapp.models import Base

DATABASE_URL = "postgresql+psycopg://postgres:postgres@localhost/test_myapp"


@pytest.fixture(scope="session")
def engine():
    engine = create_engine(DATABASE_URL)
    Base.metadata.create_all(engine)
    yield engine
    Base.metadata.drop_all(engine)
    engine.dispose()


@pytest.fixture
def connection(engine):
    connection = engine.connect()
    transaction = connection.begin()
    yield connection
    transaction.rollback()
    connection.close()


@pytest.fixture
def session(connection):
    session = Session(bind=connection, join_transaction_mode="create_savepoint")
    yield session
    session.close()

How it works:

engine is session-scoped: the schema is built once for the whole run.
connection opens a connection and begins a plain (non-ORM) transaction. Nothing the test does is ever committed to the database for real.
session binds an ORM Session to that connection with join_transaction_mode="create_savepoint". The session implements its own begin, commit and rollback as SAVEPOINTs, and leaves the outer transaction as it found it.
After the test, the session is closed and the outer transaction is rolled back, which discards everything, including work the test committed.

Tests use the session fixture like any other session:

from sqlalchemy import func, select

from myapp.models import User


def test_create_user(session):
    session.add(User(name="alice"))
    session.commit()

    assert session.scalar(select(func.count()).select_from(User)) == 1


def test_starts_empty(session):
    assert session.scalar(select(func.count()).select_from(User)) == 0

In SQLAlchemy 1.4 this recipe needed an after_transaction_end event handler to restart the nested transaction. In 2.0 join_transaction_mode replaces that; delete the event handler if you're upgrading old fixtures.

Wire the session into your app

The fixture only helps if the code under test uses it. If your app gets its session from a dependency or a factory, override it in tests. With FastAPI:

import pytest
from fastapi.testclient import TestClient

from myapp.db import get_session
from myapp.main import app


@pytest.fixture
def client(session):
    app.dependency_overrides[get_session] = lambda: session
    yield TestClient(app)
    app.dependency_overrides.clear()

If the app creates sessions from a global sessionmaker, make the bind configurable (for example SessionLocal.configure(bind=connection, join_transaction_mode="create_savepoint") in a fixture) rather than letting it open its own connections, which would see neither the test's data nor its rollback.

Migrations instead of create_all

If production uses Alembic, you can build the test schema with the migrations instead, still once per session, so the tests also prove the migrations produce a working schema:

from alembic import command
from alembic.config import Config


@pytest.fixture(scope="session")
def engine():
    engine = create_engine(DATABASE_URL)
    command.upgrade(Config("alembic.ini"), "head")
    yield engine
    engine.dispose()

Your env.py has to connect to the test database here (for example by reading the URL from an environment variable).

Keep the tests correct

Code that commits still works: the commit releases a savepoint, and the outer rollback undoes it. session.rollback() in the code under test rolls back to the savepoint, not the whole test.
Everything must go through the test's connection. Code that opens its own connection from the engine (a background thread, a second engine, a raw psycopg.connect()) won't see uncommitted test data and its writes won't be rolled back.
Tests that need real commits seen by another connection, or that test transaction behaviour itself, need a separate fixture that creates and cleans up its data explicitly.
On SQLite, the sqlite3 driver's legacy transaction mode doesn't run SAVEPOINTs inside the enclosing transaction, so released savepoints survive the rollback. Configure the engine as described in SQLAlchemy's SQLite transaction docs (on Python 3.12+, connect_args={"autocommit": False}).
AsyncSession accepts the same join_transaction_mode argument; with async engines keep the engine and the event loop at the same scope (see the event loop scope guide).

How flakytest finds it

flakytest puts samples inside SQLAlchemy's create_all and drop_all in the DB schema / flush category. When that time shows up in more than one test and is at least 0.5 seconds and 1% of test time, it suggests Create the SQLAlchemy schema once, roll back per test and lists the tests and fixture lines that build the schema. After the change, flakytest run again and flakytest compare shows the time saved and confirms no test changed outcome.

Related guides

SQLAlchemy testing best practices: How to test SQLAlchemy 2.0 code with pytest: schema once per session, savepoint rollback per test, factory_boy factories, statement counting, AsyncSession and Alembic checks.
Expensive function-scoped pytest fixtures: A pytest fixture without a scope runs again for every test that uses it. Give expensive fixtures module or session scope, or split them into a shared part and a cheap reset.
Stop opening new database connections in tests: Tests that create an engine per test, use NullPool or close Django connections reconnect to the database over and over. Create the engine once per session and keep connections open.
Use TestCase instead of TransactionTestCase: TransactionTestCase and transaction=True empty every table after each test. Django's TestCase rolls back a transaction instead. When to use which, and how to switch.

Updated October 18, 2026 · Suggest a change