# pytest-queryguard

> A pytest plugin that fails tests with N+1 queries and enforces query budgets for SQLAlchemy 2, in sync, async and FastAPI code. On PostgreSQL 16 and newer and on SQLite it can also fail tests whose queries have no index to use. MIT license. This file describes the repository; `CHANGELOG.md` says what is in which release (the latest release is on PyPI).

It watches the SQL each test function body sends (fixture setup and teardown are not measured) and points to the application line that caused a finding, even when the test database has only a few rows.

## Install

```bash
pip install pytest-queryguard
```

Needs Python 3.10+, pytest 8+ and SQLAlchemy 2.0+. The plugin registers itself through the `pytest11` entry point: there is nothing to import or configure. For async code on SQLAlchemy 2.1+, also install `sqlalchemy[asyncio]` (greenlet).

## Quick start

Every test is checked automatically. This code passes its tests without the plugin:

```python
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 the plugin installed, a test that calls it with 50 orders fails:

```
queryguard: N+1 detected

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

A test fails when, inside the test function, one of these finding kinds is found (thresholds are ini options):

- `relationship-load`: one relationship is lazy-loaded for 2 or more different parent objects (hint: `joinedload` for many-to-one, `selectinload` for collections);
- `repeated-statement`: one `SELECT` (parameters and literals removed; ORM, Core and `text()`) runs 5 or more times; `INSERT`, `UPDATE` and `DELETE` never count towards it. With `queryguard_small_repeat_threshold` (off by default), 2 to 4 runs from one source line with different parameters count too;
- `column-load`: 5 or more instances of one class each reload a deferred column (hint: `undefer`) or attributes expired by `commit()`/`expire()` (hint: `expire_on_commit=False`);
- `write-loop` (opt-in, `queryguard_write_loop_threshold`): the same single-row `INSERT`, `UPDATE` or `DELETE` run many times from one source line;
- `unused-eager-load` (experimental, off by default, `queryguard_unused_eager_load = off|warn|fail`): a relationship loaded with `selectinload`, `joinedload`, `subqueryload` or `immediateload` and never read. See docs/unused-eager-load.md;
- `missing-index` (opt-in): see below.

Runnable projects are in `examples/`. Documentation pages are in `docs/` (build them with `uv run --group docs python scripts/build_docs.py --strict`).

## Markers and context manager

```python
import pytest
from pytest_queryguard import max_queries


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


def test_checkout(service, cart):
    with max_queries(2):  # counts only statements inside the block
        service.checkout(cart)


@pytest.mark.allow_nplusone  # skip N+1 detection; a budget still applies
def test_legacy_report(session): ...


@pytest.mark.allow_nplusone("Order.customer", function="list_orders")  # one finding
def test_one_finding(session): ...


@pytest.mark.allow_missing_index("countries", "currencies")  # no args: whole test
def test_lookup(session): ...
```

- `max_queries` marker: fails the test when its body runs more SQL statements than the limit, writes included, whatever `queryguard_mode` is. The failure lists every statement with its call site.
- `max_db_time(seconds)` (marker and context manager): fails when the database and driver time of the statements exceeds the limit; the failure lists the slowest statements. `Query.duration`, `QueryLog.db_time` and `QueryLog.slowest(n)` expose the timing.
- `max_queries(limit)` context manager: raises `QueryBudgetExceeded` (a subclass of `AssertionError`) above the limit. It does not depend on pytest. It checks the budget only, not N+1.
- Public API (see docs/stability.md): `max_queries()`, `max_db_time()`, `QueryBudgetExceeded`, `record_queries()`, `QueryLog`, `Query`, `CallSite`, `NPlusOneDetected`, `QueryGuardWarning` (N+1 and missing indexes in `warn` mode), `QueryGuardInternalWarning` (internal error; the plugin disables itself instead of failing tests), the four markers (`max_queries`, `max_db_time`, `allow_nplusone`, `allow_missing_index`), the `queryguard_*` ini options and the `--queryguard-*` flags. `QueryLog`, `Query` and `CallSite` are not constructible by users. The object that `with max_queries(n) as c` yields is internal.
- Async: `AsyncSession` and `create_async_engine` work unchanged; failures point to the `await` in your code. FastAPI `TestClient`: queries from any thread are counted.

## Reading the queries of a block

```python
from pytest_queryguard import record_queries


def test_orders_use_one_query(session):  # 50 orders in the database
    with record_queries() as log:
        list_orders(session)  # the fixed version, with joinedload
    assert log.count == 1  # the orders joined with their customers
    assert log.queries[0].sql.startswith("SELECT")
    log.assert_no_nplusone(repeat_threshold=5)
```

Here `list_orders` is the fixed function from the README (`select(Order).options(joinedload(Order.customer))`), not the N+1 version above: that one sends 51 queries for 50 orders and fails `assert_no_nplusone()`.

- `record_queries()` records the statements a block sends, from any thread, and yields a `QueryLog`. It works outside pytest and changes nothing else.
- `log.queries` is a tuple of `Query` objects (`sql`, `callsite` as a `CallSite` with `filename`, `lineno` and `function`, or `None`, and `relationship`, such as `"Order.customer"`, or `None`). `log.count`, `len(log)`, iteration and indexing also work; a `QueryLog` is not a `collections.abc.Sequence`.
- `log.assert_no_nplusone()` raises `NPlusOneDetected` (a subclass of `AssertionError`) with the plugin's two rules. Thresholds are the keyword arguments `relationship_threshold=2`, `repeat_threshold=5`, `column_load_threshold=5`, `small_repeat_threshold` and `write_loop_threshold`; the `queryguard_*` ini options, `queryguard_mode` and `allow_nplusone` are not read.
- After an internal error, a block that ends normally issues `QueryGuardInternalWarning` (the log may be incomplete), and `assert_no_nplusone()` raises `RuntimeError` instead of passing.

## Configuration

Ini options, read from `[tool.pytest.ini_options]`, pytest 9's `[tool.pytest]`, `pytest.ini` or `setup.cfg`. Any invalid value stops pytest with a usage error.

| Option | Values | Default |
|---|---|---|
| `queryguard_mode` | `fail`, `warn`, `off` | `fail` |
| `queryguard_relationship_threshold` | integer >= 2 | `2` |
| `queryguard_repeat_threshold` | integer >= 2 | `5` |
| `queryguard_small_repeat_threshold` | `0` or integer >= 2 | `0` (off) |
| `queryguard_column_load_threshold` | integer >= 2 | `5` |
| `queryguard_write_loop_threshold` | `0` or integer >= 2 | `0` (off) |
| `queryguard_unused_eager_load` | `off`, `warn`, `fail` | `off` |
| `queryguard_fail_on` | finding kinds (`fnmatch`) per line; other kinds only warn | empty (all kinds fail) |
| `queryguard_max_events` | integer >= 0 (`0`: no limit) | `10000` |
| `queryguard_ignore` | `KIND LABEL [file=GLOB] [function=GLOB]` per line | empty |
| `queryguard_baseline` | path relative to the rootdir | empty |
| `queryguard_index_check` | `true`, `false` | `false` |
| `queryguard_index_advisor` | `true`, `false` | `true` |
| `queryguard_hypopg` | `auto`, `on`, `off` | `auto` |
| `queryguard_index_ignore_tables` | table names or `fnmatch` patterns | empty |

```toml
[tool.pytest.ini_options]
queryguard_mode = "warn"
queryguard_repeat_threshold = 10
queryguard_index_check = true
queryguard_index_ignore_tables = ["countries", "lookup_*"]
```

Command-line options:

- `--queryguard-mode=fail|warn|off`: overrides `queryguard_mode` for this run.
- `--queryguard-report`: prints the 10 tests with the most queries at the end of the session.
- `--queryguard-baseline=PATH`: findings listed in the baseline file do not fail or warn; `--queryguard-update-baseline` writes the file from this run (docs/baseline.md).
- `--queryguard-sarif=PATH`: writes the findings as SARIF 2.1.0 for GitHub code scanning (docs/sarif.md).
- `--queryguard-index-check` / `--no-queryguard-index-check`: turn the missing-index check on or off for this run.

Adopting on an existing project: record a baseline, run the GitHub Action in `action/` (pytest, SARIF upload; docs/ci.md), and ignore single findings with `allow_nplusone("Order.customer", kind=..., file=..., function=...)` or `queryguard_ignore`.

## Missing-index check (PostgreSQL 16+ and SQLite)

Off by default. With it on, each distinct `SELECT` in a test body is planned again with `EXPLAIN (GENERIC_PLAN, VERBOSE, FORMAT JSON)` and sequential scans switched off, inside a savepoint on the connection that ran it. The test fails when the plan still reads a whole table to apply a filter, walks a whole index for a one-column equality filter, or (PostgreSQL 16 and 17) uses an index condition that leaves out the first column of a multicolumn btree index.

```
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
  sql: SELECT orders.id, orders.customer_id FROM orders WHERE orders.customer_id = %s::INTEGER
  fix: CREATE INDEX ON orders (customer_id)
```

With the index advisor (`queryguard_index_advisor`, on with the check) each finding also carries a suggested `CREATE INDEX` (equality columns, then a range or `ORDER BY` columns, or a partial index) and an Alembic `op.create_index` call. When the hypopg extension is installed in the database, the suggestion is verified in the same savepoint: whether the planner would use it and the statement's estimated cost before and after (`queryguard_hypopg = auto|on|off`). Without hypopg one notice at the end of the session says how to install it. When the table already has a matching invalid, partial or unused index, no new index is suggested and the finding says why. See docs/index-advisor.md.

Findings follow `queryguard_mode`. Exclude small lookup tables with `queryguard_index_ignore_tables` or `@pytest.mark.allow_missing_index`. Async engines on asyncpg are checked too: asyncpg prepares statements, so the `EXPLAIN` runs in a PL/pgSQL `DO` block on the test's own connection (PL/pgSQL is installed by default; without it one notice is reported). SQLite is checked too, with the same switch: each distinct `SELECT`, `UPDATE` and `DELETE` is explained with `EXPLAIN QUERY PLAN` and the real bound parameters (the statement is not run, so data and transaction are untouched); a table scanned although the statement filters or joins on its columns, or an automatic index, is a finding with a `CREATE INDEX ix_<table>_<columns> ON ...` hint and an Alembic call; it judges less precisely than PostgreSQL (see docs/index-check-sqlite.md). Statements that cannot be checked (other databases, PostgreSQL before 16, async drivers other than asyncpg, statements PostgreSQL cannot plan) do not fail the test; they are summed up once at the end of the session. Cost: a few round trips per distinct statement per test.

## Limitations

- SQLAlchemy 2 only. Raw DB-API connections and other ORMs are not seen.
- Code in `site-packages` counts as library code; a non-editable install of your app makes call sites point to the test file.
- One measurement per process at a time. Under pytest-xdist detection, budgets, baselines and SARIF work (workers send what they saw to the controller), but the report and the index-check summary are not shown.
- unittest `setUp`/`tearDown` queries are counted. A non-strict `xfail` test with an N+1 is reported as xfailed.
- The index check needs PostgreSQL 16 or newer and a sync engine or an async engine on asyncpg; other async drivers (such as psycopg async) are skipped with a notice. It reads index and column definitions from the catalog once per session and drops that cache on DDL it sees through SQLAlchemy; DDL run another way is not seen. It checks only `SELECT` and `WITH` statements and judges plans, not data. Join conditions alone are never reported.
- Partitioned tables are reported under the partition's name.
- The SQLite index check reads the plan SQLite chose, so it is less precise than the PostgreSQL one (see docs/index-check-sqlite.md).
- Unused eager loads are detected only by the experimental, off-by-default check. No Django support. A baseline records the SQL text of `repeated-statement` findings, which SQLAlchemy 2.0 and 2.1 render differently.

## Docs

- [README](https://github.com/MaciejZiel/pytest-queryguard/blob/main/README.md): overview and a first example
- [Documentation pages](https://github.com/MaciejZiel/pytest-queryguard/tree/main/docs): getting started, how detection works, configuration reference, budgets, ignoring, baseline, CI and SARIF, index check (PostgreSQL, SQLite), index advisor, unused eager loads, limitations, FAQ, comparison with other tools, migration from nplusone, stability policy
- [Examples](https://github.com/MaciejZiel/pytest-queryguard/tree/main/examples): runnable projects
- [CHANGELOG](https://github.com/MaciejZiel/pytest-queryguard/blob/main/CHANGELOG.md): changes per release
- [PyPI](https://pypi.org/project/pytest-queryguard/): released versions

## Optional

- [Source](https://github.com/MaciejZiel/pytest-queryguard): repository and issue tracker
