pytest-queryguard

A pytest plugin that fails a test when the SQLAlchemy 2 code it runs sends one query per row (N+1), and enforces query budgets. Sync, async and FastAPI code; nothing to import or configure. A test database holds a handful of rows, so such code passes its tests and sends hundreds of queries in production. This test passes without the plugin:

def test_list_orders(session):
    assert len(list_orders(session)) == 50

With the plugin installed it fails, and points to the line to fix:

queryguard: N+1 detected

  Order.customer was lazy-loaded 50 times
  first at app/orders.py:10 in list_orders
  kind: relationship-load  label: Order.customer
  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))

pip install pytest-queryguard

Python 3.10+, pytest 8+, SQLAlchemy 2.0.13+.

For adoption: a baseline so that only new findings fail, SARIF and a GitHub Action for code scanning, and per-finding ignore patterns. What it does not do: it sees only statements that go through SQLAlchemy, only in the test body (not fixtures), and a finding says that a pattern happened, not that production is slow. See Limitations.

Documentation: https://maciejziel.github.io/pytest-queryguard/, starting with Getting started and A test failed: now what.

30-second example

The code behind that failure (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

With joinedload(Order.customer) in the query the same test passes. The whole project (models, fixture, the fixed code, a loop of session.get() and a query budget) is in examples/sync-n-plus-one, and a test runs it and checks this output. For async code on SQLAlchemy 2.1 and newer, also install sqlalchemy[asyncio].

What it finds

Every test is checked automatically, inside the test function:

• relationship-load: one relationship (Order.customer) is lazy-loaded for 2 or more different parent objects. The hint is joinedload for a many-to-one relationship and selectinload for a collection;
• repeated-statement: one SELECT, with its parameters and literals removed, runs 5 or more times. Core and text() statements count, not only ORM ones;
• column-load: 5 or more instances of one class each go back to the database for a deferred column (hint: undefer) or for attributes expired by commit() (hint: expire_on_commit=False);
• write-loop (opt-in): the same INSERT, UPDATE or DELETE, one row per statement, runs many times from one source line;
• unused-eager-load (experimental, off by default): a relationship is eager-loaded and never read;
• missing-index (opt-in): see below.

All thresholds are options. A budget fails a test that runs too many statements, and max_db_time one that spends too long in the database:

@pytest.mark.max_queries(3)
def test_order_page(client):
    client.get("/orders/1")

The failure lists every statement with the line that issued it. FastAPI's TestClient runs the application in another thread; queries from any thread are counted. How detection works explains each rule and why the thresholds are what they are.

Missing indexes

A test database holds a few rows, so a query that reads the whole table is as fast in CI as one that uses an index. With the index check on, every SELECT in a test body is planned again with sequential scans switched off (PostgreSQL 16 and newer), explained with EXPLAIN QUERY PLAN (SQLite), or explained with EXPLAIN (MySQL and MariaDB). If the plan still reads a whole table to apply a filter, no index can serve it, however small the table:

pytest --queryguard-index-check

queryguard: missing index

  orders is read in full: Seq Scan with filter (orders.customer_id = $1)
  first at app/orders.py:11 in orders_of
  kind: missing-index  label: orders
  sql: SELECT orders.id, orders.customer_id FROM orders WHERE orders.customer_id = %s::INTEGER
  fix: CREATE INDEX ON orders (customer_id)
  alembic: op.create_index("ix_orders_customer_id", "orders", ["customer_id"])

  a sequential scan is fine on a small lookup table:
  list it in queryguard_index_ignore_tables

The index advisor suggests the CREATE INDEX and the Alembic call; with the hypopg extension in the test database it also says whether the planner would use the index and what the estimated cost would be before and after. The check is off by default. See PostgreSQL, SQLite, MySQL and MariaDB (tested on live servers by hand, not in CI) and the index advisor; runnable projects are in examples/postgres-missing-index and examples/sqlite-index-check.

Adopting it on an existing project

• Record today's findings once, and only new ones fail: pytest --queryguard-baseline=queryguard-baseline.json --queryguard-update-baseline. See Baseline.
• pytest --queryguard-sarif=queryguard.sarif writes the findings as SARIF for GitHub code scanning, and the action in action runs pytest and uploads it. See CI and GitHub Action and SARIF.
• Leave out single findings with @pytest.mark.allow_nplusone("Order.customer") or queryguard_ignore; make kinds warn with queryguard_fail_on or queryguard_mode = warn. See Ignoring findings.
• Read the statements of a block in a test with record_queries(). See Reading the queries.

[tool.pytest.ini_options]
queryguard_mode = "warn"                  # fail (default), warn or off
queryguard_repeat_threshold = 10
# the file must exist: create it first with --queryguard-update-baseline
queryguard_baseline = "queryguard-baseline.json"
queryguard_index_check = true
queryguard_index_ignore_tables = ["countries", "lookup_*"]

Every option, flag and marker is in the configuration reference.

Documentation

The pages are Markdown in docs; scripts/build_docs.py builds them into a static site (see Development).

How it compares

Other tools find N+1 queries too: queryspy, pytest-querycount, nplusone (last release 2018), django-zeal for Django and others. The comparison page has a feature matrix (dated, with what was run and what was only read) and the results of running 45 scenarios against queryspy 0.5.0 and pytest-querycount 0.4.0 (re-checked on 2026-10-13: no newer releases). In short, in my runs: pytest-queryguard fails on N+1 with no configuration (the other two need a flag or a marker), reported a loop of Core select() statements and a loop with literal values that queryspy missed, and lists the statements in a budget failure (queryspy's marker printed only the counts). queryspy and pytest-querycount also report runs of 2 to 4 identical statements, which pytest-queryguard does only when you set queryguard_small_repeat_threshold. pytest-querycount has a PostgreSQL missing-index check of its own, queryspy has middleware and a request panel for running apps, which pytest-queryguard does not, and django-zeal covers Django. Moving from nplusone: migration guide.

Overhead

On in-memory SQLite, the measurement adds about 9 us per Core statement and about 31 us per ORM statement (+22 % and +40 %). The percentages are high because an in-memory statement costs almost nothing; on a generated suite of 300 tests against file-based SQLite and against PostgreSQL the difference was within run-to-run noise. Method, numbers and the cost of the opt-in checks: Performance and the FAQ.

Limitations

• SQLAlchemy only; raw DB-API connections (sqlite3 and the like) and other ORMs (Django ORM, Peewee) are not seen, and nothing says so.
• Only the test function body is measured, not fixtures. One measurement is active per process; under pytest-xdist each worker measures its own tests.
• The PostgreSQL index check needs PostgreSQL 16 or newer and a sync engine or asyncpg; the SQLite, MySQL and MariaDB checks judge less precisely. It says that no index can serve a filter, not that an existing index is a good one.
• Writes never count towards the repeated-statement rule; the opt-in write-loop kind and budgets cover them.

The full list is on the Limitations page.

Development

The project uses uv.

uv sync
uv run coverage run -m pytest
uv run coverage report
uv run ruff check .
uv run ruff format --check .
uv run mypy src
uv run --group docs python scripts/build_docs.py --strict   # the docs site, into site/

The PostgreSQL tests run when QUERYGUARD_PG_URL points to a server, and are skipped otherwise:

docker run -d --rm --name qg-postgres -p 55432:5432 -e POSTGRES_PASSWORD=pg postgres:17
QUERYGUARD_PG_URL=postgresql+pg8000://postgres:pg@localhost:55432/postgres uv run pytest
docker stop qg-postgres

Coverage is measured with coverage run rather than pytest --cov, because --cov starts after pytest has imported the plugin and misses its import-time lines. Third-party licenses are listed in 3rdparty_licenses.md.

License

MIT, see the LICENSE file.