Recipe: a session per request in the test's transaction#

A common way to isolate tests is to open one transaction per test, run everything in it and roll it back at the end. The quickest way to get the application into that transaction is to override its session dependency with the test's own Session:

app.dependency_overrides[get_session] = lambda: session

Then every request of the test shares one Session. For pytest-queryguard the test is one unit of work: a test that calls an endpoint five times runs the endpoint's lookups five times in one Session, and they are reported as a loop. queryguard_scope = session (see Tests with several sessions or requests) does not help, because there is only one Session to count. The same override in a suite with many such tests gives a repeated-statement finding for almost every lookup an endpoint makes.

The fix is in the fixture, not in the application: give each request its own Session, bound to the test's connection, with join_transaction_mode="create_savepoint". Each request's session then works inside the outer transaction (its commit() releases a savepoint, the outer transaction stays open), and the test still rolls everything back at the end. With queryguard_scope = session, each request is counted on its own, so only a real loop inside one request is reported.

The project below was run for this page with SQLAlchemy 2.0.13 and 2.1.1, FastAPI 0.141 and httpx 0.28; a test builds it from the files of this page and runs it. The layout is pytest.ini, app/__init__.py and tests/__init__.py (both empty), app/models.py, app/main.py, tests/conftest.py, tests/test_shared_session.py and tests/test_session_per_request.py. Install the plugin with FastAPI and httpx:

pip install pytest-queryguard fastapi httpx

The application#

pytest.ini counts each Session on its own. The filterwarnings line hides the warning of Starlette 1.2 and later about its test client finding httpx without httpx2; the FastAPI recipe explains it and its alternative, installing httpx2:

[pytest]
pythonpath = .
queryguard_scope = session
filterwarnings =
    ignore:Using `httpx` with `starlette.testclient` is deprecated:UserWarning

app/models.py:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Customer(Base):
    __tablename__ = "customer"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]


class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id"))
    customer: Mapped[Customer] = relationship()

app/main.py has one endpoint that loads one order and its customer. Each request runs two statements, which is fine:

from collections.abc import Iterator
from typing import Annotated

from fastapi import Depends, FastAPI
from sqlalchemy import create_engine, select
from sqlalchemy.orm import Session, sessionmaker

from app.models import Order

app = FastAPI()
sessions = sessionmaker(create_engine("sqlite:///app.db"))


def get_session() -> Iterator[Session]:
    with sessions() as session:
        yield session


SessionDep = Annotated[Session, Depends(get_session)]


@app.get("/orders/{order_id}")
def order_detail(order_id: int, session: SessionDep) -> dict[str, str]:
    order = session.scalars(select(Order).filter_by(id=order_id)).one()
    return {"customer": order.customer.name}

The fixtures#

tests/conftest.py opens one connection and one transaction per test, seeds it and rolls it back at the end. shared_client hands every request the same Session; client gives each request a new one in the same transaction:

from collections.abc import Iterator

import pytest
from app.main import app, get_session
from app.models import Base, Customer, Order
from fastapi.testclient import TestClient
from sqlalchemy import Connection, Engine, create_engine, event
from sqlalchemy.orm import Session
from sqlalchemy.pool import StaticPool


@pytest.fixture(scope="session")
def engine() -> Iterator[Engine]:
    engine = create_engine(
        "sqlite://",
        connect_args={"check_same_thread": False},
        poolclass=StaticPool,
    )

    # SQLite only: let SQLAlchemy emit BEGIN, so that SAVEPOINT works
    @event.listens_for(engine, "connect")
    def _no_driver_transactions(dbapi_connection, _record):
        dbapi_connection.isolation_level = None

    @event.listens_for(engine, "begin")
    def _begin(connection):
        connection.exec_driver_sql("BEGIN")

    Base.metadata.create_all(engine)
    yield engine
    engine.dispose()


@pytest.fixture
def connection(engine: Engine) -> Iterator[Connection]:
    """One transaction per test, rolled back at the end."""
    with engine.connect() as connection:
        transaction = connection.begin()
        with Session(connection, join_transaction_mode="create_savepoint") as seed:
            seed.add_all(Customer(id=i, name=f"customer {i}") for i in range(1, 6))
            seed.add_all(Order(id=i, customer_id=i) for i in range(1, 6))
            seed.commit()
        yield connection
        transaction.rollback()


@pytest.fixture
def shared_client(connection: Connection) -> Iterator[TestClient]:
    """Every request gets the same Session: what not to do."""
    with Session(connection, join_transaction_mode="create_savepoint") as session:
        app.dependency_overrides[get_session] = lambda: session
        with TestClient(app) as client:
            yield client
    app.dependency_overrides.clear()


@pytest.fixture
def client(connection: Connection) -> Iterator[TestClient]:
    """Every request gets its own Session, in the test's transaction."""

    def override() -> Iterator[Session]:
        with Session(connection, join_transaction_mode="create_savepoint") as session:
            yield session

    app.dependency_overrides[get_session] = override
    with TestClient(app) as client:
        yield client
    app.dependency_overrides.clear()

Notes on it:

One shared session#

tests/test_shared_session.py requests five orders through the shared Session:

def test_five_orders(shared_client):
    for order_id in range(1, 6):
        assert shared_client.get(f"/orders/{order_id}").status_code == 200

It fails, although the endpoint has no loop: all five requests ran in one Session, so its two statements are counted five times each:

queryguard: N+1 detected

  Order.customer was lazy-loaded 5 times in one Session
  first at app/main.py:25 in order_detail
  parents loaded at app/main.py:24 in order_detail
  kind: relationship-load  label: Order.customer
  docs: https://maciejziel.github.io/pytest-queryguard/how-detection-works.html#relationship-load
  sql: SELECT customer.id, customer.name FROM customer WHERE customer.id = ?
  fix: load it with the parent query, e.g.
       select(Order).options(joinedload(Order.customer))

  the same statement ran 5 times in one Session or connection checkout
  first at app/main.py:24 in order_detail
  kind: repeated-statement  label: SELECT orders.id, orders.customer_id FROM orders WHERE orders.id = ?
  docs: https://maciejziel.github.io/pytest-queryguard/how-detection-works.html#repeated-statement
  fix: fetch all rows in one query (WHERE id IN (...)) instead of in a loop

A session per request#

tests/test_session_per_request.py runs the same requests with client:

def test_five_orders(client):
    for order_id in range(1, 6):
        assert client.get(f"/orders/{order_id}").status_code == 200


def test_the_seed_of_the_last_test_was_rolled_back(client):
    # the fixture seeds the same primary keys again; it would fail if the
    # rows of the test before were still there
    assert client.get("/orders/1").json() == {"customer": "customer 1"}

Both pass: each request is its own scope, and none reaches a threshold. A loop inside one request is still reported, because it runs in one Session.

What this changes and what it does not#

Edit this page on GitHub