Detect N+1 queries in SQLAlchemy tests#
Install pytest-queryguard and run your tests as usual. Every test is checked: when the code it calls lazy-loads a relationship once per row, or sends the same statement in a loop, the test fails and the report names the line and the eager load that fixes it. There is no fixture, marker or setting to add.
pip install pytest-queryguard
It needs Python 3.10 or newer, pytest 8 or newer and SQLAlchemy 2.0.13 or
newer, and works with sync and async sessions, 2.0-style select(), the legacy
Query API and Core.
Example#
The models and the session fixture are those of
Getting started: 50 orders, each belonging
to a customer, in an in-memory SQLite database. The function lists the orders
with their customer's name.
app/orders.py:
from sqlalchemy import select
from sqlalchemy.orm import Session
from app.models import Order
def list_orders(session: Session) -> list[dict[str, object]]:
rows = []
for order in session.scalars(select(Order)).all():
rows.append({"id": order.id, "customer": order.customer.name})
return rows
tests/test_order_list.py:
from app.orders import list_orders
def test_list_orders(session):
assert len(list_orders(session)) == 50
The test passes without the plugin. With it, pytest fails it:
queryguard: N+1 detected
Order.customer was lazy-loaded 50 times
first at app/orders.py:10 in list_orders
called from tests/test_order_list.py:5 in test_list_orders
parents loaded at app/orders.py:9 in list_orders
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 loop sent 51 statements: one for the orders, then one per customer. The fix loads the customers with the orders.
app/orders_fixed.py:
from sqlalchemy import select
from sqlalchemy.orm import Session, joinedload
from app.models import Order
def list_orders(session: Session) -> list[dict[str, object]]:
rows = []
for order in session.scalars(select(Order).options(joinedload(Order.customer))):
rows.append({"id": order.id, "customer": order.customer.name})
return rows
The same test against it passes, and with max_queries(1) it also checks
that the function sends a single statement.
tests/test_order_list_fixed.py:
import pytest
from app.orders_fixed import list_orders
@pytest.mark.max_queries(1)
def test_list_orders(session):
assert len(list_orders(session)) == 50
The test database needs at least two parent rows for an N+1 to show: by default a relationship lazy-loaded by two parents from the same query is reported (see How detection works). A repeated statement is reported from five runs.
What else it reports#
- the same
SELECTsent in a loop, such assession.get()per id or a Core ortext()query per row (repeated statements); - a test that sends more statements than a limit you set (query budgets, or Assert the number of SQL queries);
- when you turn them on,
INSERT,UPDATEorDELETEsent once per row (write loops) and queries with no index to use on PostgreSQL, SQLite, MySQL and MariaDB (index check).
Loops written in the test itself or in its fixtures are not reported by default: they never run in production (see Test code).
Next#
- A test failed: now what: check the finding, fix it or leave it out on purpose.
- Recipes for FastAPI, Flask-SQLAlchemy, SQLModel and Litestar.
- An existing suite with many findings: Baseline and Adopting on a legacy codebase.
- Coming from nplusone, which does not see SQLAlchemy 2.0-style queries and does not import on 2.1: Migrating from nplusone.