Why your tests don't catch N+1 queries (and how to make them)

N+1 queries are easy to merge with a green CI run. The tests covered the endpoint and checked the response. They did not check how many queries it took to produce that response, so nothing failed. This post covers why the usual test setup hides N+1 queries, how to make a test fail on them, and a related problem the same setup hides: queries that have no index to use.

The examples use SQLAlchemy 2 and pytest. The tool at the end is pytest-queryguard, a plugin I wrote; the techniques before it work without it.

The bug

Two models, a blog post and its author:

class Author(Base):
    __tablename__ = "author"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]


class Post(Base):
    __tablename__ = "post"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey("author.id"))
    author: Mapped[Author] = relationship()

And a function that builds a feed:

def feed(session: Session) -> list[dict[str, str]]:
    posts = session.scalars(select(Post)).all()
    return [{"title": p.title, "author": p.author.name} for p in posts]

select(Post) loads the posts. p.author is a lazy relationship, so the first time the comprehension touches it for a given author, SQLAlchemy sends SELECT ... FROM author WHERE author.id = ?. One query for the list, then one more for each author it has not seen yet. That is the N+1.

Why the test passes

The fixture creates 3 authors and 6 posts, and the test checks the length:

def test_feed(session):
    assert len(feed(session)) == 6

It passes. It would pass in production too: the function returns the right data. The difference is cost. With 3 authors the function sends 4 queries and finishes in microseconds against SQLite. With 500 posts by 400 authors on a real database over a network, it sends 401 round trips per request.

Three things in a normal test setup hide this:

1. The data is tiny. A test database holds a handful of rows on purpose, so the extra queries cost nothing measurable. No test times out and nobody notices.
2. Assertions check results, not work. len(...) == 6 is true whether it took 1 query or 400.
3. The identity map can hide the queries completely. This one surprised me while preparing the example. If the test reuses the session that inserted the rows, the authors are already in that session's identity map, so p.author is answered from memory and no lazy-load SQL is sent at all. I ran the same lazy feed code in a test that inserts and reads in one session, with the plugin installed and --queryguard-report:

   variant_identity_map/test_identity_map.py .                              [100%]

   ============================== queryguard report ===============================
      17  variant_identity_map/test_identity_map.py::test_feed_same_session
   ============================== 1 passed in 0.02s ===============================

The 17 statements are the table setup, the inserts and a single SELECT of the posts. The bug is in the code, but the database never sees it, so nothing watching the SQL can see it either. The fix is in the fixture: insert with one session, close it, and give the code under test a fresh one. The rest of this post uses a fixture that does that.

Making the test fail

The usual answer, which you will find on Stack Overflow going back to 2013, is to listen to SQLAlchemy's before_cursor_execute event and count statements:

from sqlalchemy import event
from sqlalchemy.engine import Engine

statements = []
event.listen(Engine, "before_cursor_execute",
             lambda conn, cursor, stmt, *args: statements.append(stmt))

Then assert the count. That works, and if a counter is all you need, those few lines are enough. When I tried this in my own projects, I kept wanting three more things: a check on every test without writing a number for each one; a message that says which relationship and which line caused the repeats; and something that understands async sessions and FastAPI's TestClient, which runs your app in another thread.

pytest-queryguard does that. Install it and run the same, unchanged test:

pip install pytest-queryguard
pytest

tests/test_feed.py F                                                     [100%]

=================================== FAILURES ===================================
__________________________________ test_feed ___________________________________
queryguard: N+1 detected

  Post.author was lazy-loaded 3 times
  first at app/feed.py:9 in feed
  sql: SELECT author.id, author.name FROM author WHERE author.id = ?
  fix: load it with the parent query, e.g.
       select(Post).options(selectinload(Post.author))
=========================== short test summary info ============================
FAILED tests/test_feed.py::test_feed - Failed: queryguard: N+1 detected
============================== 1 failed in 0.02s ===============================

There is no configuration and no marker. The plugin listens to two SQLAlchemy events for the duration of each test function (fixture setup is not counted): do_orm_execute on Session, which reports lazy loads of a relationship, and before_cursor_execute on Engine, which sees every statement. A test fails when one relationship is lazy-loaded for 2 or more parent objects, or when one SELECT, with its parameters removed, runs 5 or more times. Both thresholds are settings.

The relationship rule is the useful one here. It fires with 3 authors, on test data, because it does not need the repetition to be large; it only needs the pattern.

The fix

The message already suggests it. Load the authors together with the posts:

def feed(session: Session) -> list[dict[str, str]]:
    stmt = select(Post).options(selectinload(Post.author))
    posts = session.scalars(stmt).all()
    return [{"title": p.title, "author": p.author.name} for p in posts]

selectinload sends a second query, SELECT ... FROM author WHERE author.id IN (...), for all the authors at once. Two queries, whether there are 6 posts or 6000. joinedload is the other common choice and works well for many-to-one relationships like this one; selectinload is usually the safer default for collections.

Query budgets

The N+1 rules catch loops. They do not catch a page that slowly grows from 2 queries to 7 because someone added a count, a permission check and another relationship. For pages where you care about the number, write it down:

@pytest.mark.max_queries(2)
def test_post_page(session):
    assert post_page(session, 1)["author_posts"] == 2

Against this version of post_page:

def post_page(session: Session, post_id: int) -> dict[str, object]:
    post = session.get(Post, post_id)
    author = post.author
    count = session.scalar(
        select(func.count()).select_from(Post).where(Post.author_id == author.id)
    )
    return {"title": post.title, "author": author.name, "author_posts": count}

pytest --queryguard-report:

tests/test_feed.py .F                                                    [100%]

=================================== FAILURES ===================================
________________________________ test_post_page ________________________________
queryguard: expected at most 2 queries, got 3

  1. SELECT post.id, post.title, post.author_id FROM post WHERE post.id = ?  (app/feed.py:14 in post_page)
  2. SELECT author.id, author.name FROM author WHERE author.id = ?  (app/feed.py:15 in post_page)
  3. SELECT count(*) AS count_1 FROM post WHERE post.author_id = ?  (app/feed.py:16 in post_page)
============================== queryguard report ===============================
    3  tests/test_feed.py::test_post_page
    2  tests/test_feed.py::test_feed
=========================== short test summary info ============================
FAILED tests/test_feed.py::test_post_page - Failed: queryguard: expected at m...
========================= 1 failed, 1 passed in 0.03s ==========================

The failure lists every statement with the line that sent it, and the report at the bottom ranks tests by query count, which is a quick way to find the heaviest tests in a suite you did not write. Loading the author with the post, session.get(Post, post_id, options=[joinedload(Post.author)]), brings it to 2 and the test passes. There is also a max_queries() context manager for budgeting one block of code instead of a whole test.

The other thing tiny databases hide: missing indexes

That count query has a second problem that no amount of query counting will find. There is no index on post.author_id. With 6 rows, PostgreSQL reads the whole table and nobody can tell; with 10 million rows, the same query reads 10 million rows.

You cannot catch this by looking at EXPLAIN in a test, because on a tiny table a sequential scan is the planner's correct choice even when an index exists. The trick is to ask a different question: plan the query again with enable_seqscan switched off. If PostgreSQL still reads the whole table to apply the filter, then no index can serve that filter, however small the table.

pytest-queryguard 0.2 does this when you ask for it (PostgreSQL 16 or newer, a sync driver; this run used pg8000 against PostgreSQL 17 in Docker):

pytest --queryguard-index-check

tests/test_feed.py .F                                                    [100%]

=================================== FAILURES ===================================
________________________________ test_post_page ________________________________
queryguard: missing index

  post is read in full: Seq Scan with filter (post.author_id = $1)
  first at app/feed.py:16 in post_page
  sql: SELECT count(*) AS count_1 FROM post WHERE post.author_id = %s::INTEGER
  fix: CREATE INDEX ON post (author_id)

  a sequential scan is fine on a small lookup table:
  list it in queryguard_index_ignore_tables
=========================== short test summary info ============================
FAILED tests/test_feed.py::test_post_page - Failed: queryguard: missing index
========================= 1 failed, 1 passed in 0.15s ==========================

Note that test_feed passed: its IN query filters on the author's primary key. After adding index=True to Post.author_id:

tests/test_feed.py ..                                                    [100%]

============================== 2 passed in 0.17s ===============================

Each distinct SELECT is explained once per test, inside a savepoint on the connection that ran it, so it sees the test's uncommitted rows and leaves the transaction as it was. On SQLite the check does not fail anything; it prints one line at the end of the session saying the statements were not checked.

PostgreSQL 16 matters because of EXPLAIN (GENERIC_PLAN), which plans a statement with $1 placeholders without values. Before 16 there is no clean way to do this from the SQL your ORM actually sent.

Limitations

Worth knowing before you adopt any of this:

• SQLAlchemy only. Raw DB-API calls and other ORMs are invisible to the plugin. For Django, use assertNumQueries or a Django-specific tool.
• It only sees SQL that is sent. The identity-map case above passes. So does code that is never executed by a test.
• Writes are never reported as N+1. At cursor level a unit-of-work flush looks the same as an application write loop, so INSERT/UPDATE/DELETE are left to budgets.
• No unused-eager-load detection. nplusone does this; pytest-queryguard does not.
• The index check needs PostgreSQL 16+ and a sync engine. Async drivers prepare every statement, and PostgreSQL rejects EXPLAIN (GENERIC_PLAN) sent that way, so async statements are skipped with a notice. It judges plans, not data: it tells you no index can serve a filter, not that an existing index is good. Joins without a WHERE on the unindexed side are not reported, because that plan depends on statistics.
• It costs time. In the project's own measurement, the index check added about 2–5 ms per distinct statement against a local Docker PostgreSQL. N+1 detection alone is cheap.
• Small lookup tables are fine to scan in production too. List them in queryguard_index_ignore_tables or mark a test with @pytest.mark.allow_missing_index.

What about nplusone?

nplusone did this job for years and still gets a lot of downloads. Its last release was 1.0.0 in 2018. It patches Query internals that SQLAlchemy 1.4 rewrote, so on 1.4 and 2.0 it does not detect lazy loads and does not tell you it stopped working (see nplusone issue #37). If you have it in a SQLAlchemy 2 project, check that it still fails on a known N+1 before relying on it.

Summary

• Use a fresh session for the code under test, or the identity map hides lazy loads.
• Make tests fail on the N+1 pattern, not on slowness; test data is too small to be slow.
• Put a query budget on the endpoints you care about.
• On PostgreSQL, plan your test queries with sequential scans disabled to find missing indexes while the tables are still small.

pytest-queryguard is MIT-licensed and on PyPI. Source, full documentation and the issue tracker: https://github.com/MaciejZiel/pytest-queryguard. Bug reports and "it missed this N+1" examples are the most useful thing you can send.