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.