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#

Loops written in the test itself or in its fixtures are not reported by default: they never run in production (see Test code).

Next#

Edit this page on GitHub