skip to content

What exactly does Django's assertNumQueries count, and why can a view's count differ between a TestCase and production?

level: seniorimportance: should knowfreq 45%

answer

  1. exact equality on one alias
  2. debug cursor forced on
  3. nested atomic becomes a savepoint
  4. process-level lookup caches

basics

~20 s

assertNumQueries counts every SQL statement run on one database alias inside the call or block and requires an exact match. In a TestCase, savepoints from nested atomic blocks and warm or cold ContentType caches can shift that count.

solid answer

~40 s

`assertNumQueries(num, func=None, *args, using='default', **kwargs)` is a `TransactionTestCase` method, so `TestCase` has it too, and works with a callable or as a context manager. It forces the connection's debug cursor on — tests run with `DEBUG=False` — opens the connection first so setup queries are not counted, and on exit compares the statements logged on that one alias with `num` by equality, printing the numbered SQL on failure. It counts everything sent through a cursor, `SAVEPOINT` and `RELEASE SAVEPOINT` included. Because `TestCase` runs each test inside an `atomic` block, an `atomic()` in the code under test becomes a savepoint and adds two statements; outside `TestCase` the same block is outermost and on PostgreSQL logs none. Process-level caches such as the `ContentType` lookup cache also make the count depend on which test ran first.

code

python · 18 lines
python
from django.contrib.contenttypes.models import ContentType
from django.test import TestCase

from catalog.models import Product


class ProductListQueryTests(TestCase):
    @classmethod
    def setUpTestData(cls):
        Product.objects.bulk_create(Product(name=f'p{i}') for i in range(5))

    def setUp(self):
        ContentType.objects.clear_cache()

    def test_product_list_query_count(self):
        with self.assertNumQueries(2):
            response = self.client.get('/products/')
        self.assertEqual(response.status_code, 200)

go deeper

for a junior

Know that Django's TestCase has assertNumQueries, usable as a context manager, and that it fails when the number of queries differs from the expected one.

for a middle

Explain the exact-equality check, the using alias, the forced debug cursor and why a failure message's numbered SQL is the first thing to read.

for a senior

Explain off-by-two failures from savepoints and order-dependent ones from ContentType or Site caches, and make pins stable instead of adjusting the expected number.

for a principal

Decide where exact pins are worth their maintenance and where a budget via CaptureQueriesContext is enough, so query tests catch regressions without blocking harmless refactors.

## The API `assertNumQueries` is defined on **`TransactionTestCase`**, so it is available in `TransactionTestCase` and `TestCase` but not in `SimpleTestCase`, which blocks database access anyway. It has two forms: - **Callable form:** `self.assertNumQueries(3, build_report, account)` calls `build_report(account)` and checks the count. - **Context-manager form:** `with self.assertNumQueries(3): ...` checks everything inside the block, including a full `self.client.get(...)` request with middleware and template rendering. The keyword `using` selects the database alias; the default is `'default'`. To pass a `using` argument to your own function, wrap the call in a `lambda`. ## What it actually counts Under the hood it is `django.test.utils.CaptureQueriesContext` plus an equality assertion: 1. On entry it forces the connection's **debug cursor** on, so statements are logged even though Django runs tests with `DEBUG=False`. 2. It opens the connection first, so connection-initialisation statements are not part of the count. 3. On exit it takes the statements logged on **that one connection** since entry and asserts that their number **equals** `num` — not at most `num`. 4. On a mismatch the failure message lists every captured statement, numbered, which usually shows the culprit at once. 5. If the block raises, the count is not checked and the exception propagates. "Statement" means anything sent through a cursor: `SELECT`, `INSERT`, `UPDATE`, `DELETE`, and also transaction-control SQL such as `SAVEPOINT` and `RELEASE SAVEPOINT`. ## Why the number in a TestCase is not the production number | Source of drift | What happens in a `TestCase` | Effect on the count | |---|---|---| | `atomic()` in the code under test | nested inside the test's own block, so it runs as a savepoint | `SAVEPOINT` and `RELEASE SAVEPOINT` are added | | methods that open `atomic` internally, such as `get_or_create()` on its create path | same savepoint wrapping | the create path logs four statements: `SELECT`, `SAVEPOINT`, `INSERT`, `RELEASE SAVEPOINT` | | `ContentType.objects` lookup cache | lives for the process and is not cleared between tests | the first test to need a content type pays one extra query | | `Site.objects.get_current()` cache | same process-level caching | the same order dependence | | logged-in client requests | the session and, if touched, the user are loaded | extra reads compared with an anonymous request | In production an `atomic()` block that is the outermost transaction is opened and committed through the database driver; on PostgreSQL neither step is logged as a statement, while SQLite logs a `BEGIN`. So a count measured in a `TestCase` is a **regression pin for the test environment**, not a forecast of production statements. ## Making the pin stable - Clear process-level caches in `setUp()`: `ContentType.objects.clear_cache()` and, with the sites framework, `Site.objects.clear_cache()`. - Build data so that the view's query count does not depend on row count, and create more than one row, so a per-row query shows up as a changed count. - Put only the code being measured inside the block; create fixtures before it. - For a budget rather than an exact number, use `CaptureQueriesContext(connection)` and `assertLessEqual(len(ctx), budget)`; `ctx.captured_queries` is a list of dicts with the `sql` of each statement. - On multi-database projects, assert per alias with `using=`, because queries on other aliases are invisible to the default check. ## What interviewers listen for That the check is exact, per alias and independent of `DEBUG`; that savepoints count; and that the candidate can explain an off-by-two or order-dependent failure instead of bumping the number until the test passes.

  • How do you assert an upper bound instead of an exact query count?
    Use `CaptureQueriesContext(connection)` from `django.test.utils` around the code, then `self.assertLessEqual(len(ctx), budget)`. The context exposes `captured_queries`, a list of dicts with each statement's `sql`, which you can print in the failure message just as `assertNumQueries` does.
  • Why does assertNumQueries work even though tests run with DEBUG=False?
    Django normally logs queries only when `DEBUG` is true. The capture context sets the connection's `force_debug_cursor` flag on entry and restores it on exit, so statements are logged for the duration of the block regardless of `DEBUG`.
  • A query-count test passes alone but fails by one in the full suite. What do you check first?
    Process-level caches that survive between tests, above all the `ContentType` lookup cache and the sites framework's current-site cache. Whichever test runs first pays the lookup query. Clear them in `setUp()` so every run starts cold, and the count becomes independent of test order.

saying these in an interview costs you the question

  • assertNumQueries only works when DEBUG is True
  • assertNumQueries checks that at most num queries ran
  • It counts queries on every configured database at once
  • SAVEPOINT statements are not counted because they are not real queries
  • A count measured in a TestCase equals what the view runs in production