# 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:

```python
# 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:

1. `engine` is session-scoped: the schema is built once for the whole run.
2. `connection` opens a connection and begins a plain (non-ORM) transaction. Nothing the test does is ever committed to the database for real.
3. `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.
4. 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:

```python
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:

```python
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:

```python
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](https://flakytest.dev/guides/pytest-asyncio-event-loop-scope/)).

## 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.

## Further reading

- [SQLAlchemy testing best practices](https://flakytest.dev/guides/sqlalchemy-testing-best-practices/)
- [SQLAlchemy: Joining a Session into an External Transaction (such as for test suites)](https://docs.sqlalchemy.org/en/20/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites)
- [SQLAlchemy: Transactions with SQLite and the sqlite3 driver](https://docs.sqlalchemy.org/en/20/dialects/sqlite.html#transactions-with-sqlite-and-the-sqlite3-driver)
- [pytest: fixture scopes](https://docs.pytest.org/en/stable/how-to/fixtures.html#scope-sharing-fixtures-across-classes-modules-packages-or-session)