pytest-queryguard: ORM-level N+1 detection for SQLAlchemy 2 tests

[MaciejZiel]

Hi! I've been working on pytest-queryguard and could use some feedback from other pytest users.

It catches N+1 queries in SQLAlchemy 2 and fails the test. In #14344, @RonnyPfannschmidt mentioned wanting this at the ORM level. That's what I'm trying here.

I didn't want to require query-count assertions for each test. Instead, I listen to do_orm_execute. SQLAlchemy tells me which relationship is being lazy-loaded and for which parent. The test fails once two different parents lazy-load the same relationship. There are no expected counts to update.

If you want a query limit, you can add @pytest.mark.max_queries(n). It's optional. You can use sync or async sessions, and FastAPI's TestClient works too. I've also added a missing-index check for PostgreSQL 16+. That check is off unless you enable it.

It's on PyPI, and the code is on GitHub. I also wrote a post about how it works.

I'm still unsure where to draw the line with fixtures. Right now I only watch the test body, using a pytest_runtest_call wrapper. I leave fixture setup out. Would you want me to count queries from fixtures too?

There are also two cases I don't catch. If the identity map handles a lazy load, there's no SQL for me to see. I also miss eager loads that never get used. Have you tried tracking either of these? I'd be interested to hear what worked.

[Muguett-DBY]

I'd keep fixture setup out of the default. Data seeding can do a lot of intentional database work, and a session-scoped fixture would otherwise charge its setup to whichever test happens to run first.

For a fixture that is the code being tested, the current record_queries() API gives a useful opt-in:

import pytest
from pytest_queryguard import record_queries

@pytest.fixture
def report_data(session):
    with record_queries() as log:
        data = build_report_data(session)  # Include relationship reads here.
    log.assert_no_nplusone()
    return data

One detail: lazy relationship accesses after the with block won't be included. The block also records SQLAlchemy queries from other threads while it is active, so it works best when the fixture is isolated from unrelated concurrent work.

I checked this against current main (61e9445, 0.5.0rc1): an in-memory SQLite fixture produced one query plus three lazy loads, which the explicit log detected, while a one-query budget on the test body still passed. The check used pytest 8.4.2 and SQLAlchemy 2.1.6, with Codex assistance.

[MaciejZiel]

An update, since a lot has changed since I opened this: 1.0.0rc1 is on PyPI now. The public API (markers, options, report format) is frozen, and I'd like feedback from pytest users before 1.0.0, which I plan to release around 2026-11-18.

A test database usually has a handful of rows, so a test like this passes even when list_orders lazy-loads Order.customer for each order:

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

With the plugin installed it fails, and the failure section says where the loads happen and how to fix them:

queryguard: N+1 detected

  Order.customer was lazy-loaded 50 times
  first at app/orders.py:10 in list_orders
    called from tests/test_orders.py:7 in test_list_orders
  parents loaded at app/orders.py:9 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))

@Muguett-DBY, thanks for the answer on fixtures. That's how it ended up: queries from the test's own code (fixtures, factories, conftest.py) don't fail the test, they are counted in a summary at the end of the run, and record_queries() is still there for a fixture that is the code under test. Since October it also flags one SELECT repeated in a loop (ORM, Core or text()), has a max_db_time(seconds) budget next to max_queries(n), works under pytest-xdist, and can write JSON or SARIF for CI. Unused eager loads, which I asked about above, are now an experimental check, off by default.

What it doesn't do: it only sees what your tests execute, not production traffic, and it doesn't rewrite queries for you. Short runs of 2 to 4 identical statements are not reported by default. There is Django ORM support now, but it is experimental and may still change after 1.0.

The things I'd most like opinions on, as the person who maintains it and has mostly heard from SQLAlchemy users so far: the marker names (max_queries and max_db_time, with queryguard_max_queries / queryguard_max_db_time aliases for projects where another plugin already reads max_queries), and whether the failure report reads well next to the rest of pytest's output.

pip install --pre pytest-queryguard

Docs: https://maciejziel.github.io/pytest-queryguard/
Changelog: https://github.com/MaciejZiel/pytest-queryguard/blob/main/CHANGELOG.md