Testing

Django ORM Advanced Testing: select_related, prefetch_related Assertions, and Query Count Testing

HelpMeTest

18 May 2026 — 5 min read

N+1 query bugs are silent killers. They pass every functional test, look correct in development, and then crater your production database when traffic hits. Django gives you the tools to catch them—but most projects never use them. This guide covers advanced Django ORM testing patterns: asserting query counts, validating select_related and prefetch_related behavior, and writing tests that prevent query regressions.

The N+1 Problem in Django

The classic N+1 manifests like this:

# views.py
def order_list(request):
    orders = Order.objects.all()
    return render(request, "orders.html", {"orders": orders})

{% for order in orders %}
    {{ order.user.email }}  {# Each access hits the database #}
{% endfor %}

With 100 orders, this executes 101 queries: 1 for orders, 100 for users. The fix is select_related("user")—but without a test asserting query count, this regression will be reintroduced the next time someone removes it thinking it's redundant.

django-assert-num-queries

The django-assert-num-queries package provides a pytest fixture that fails tests when query count exceeds your expectation:

pip install django-assert-num-queries

# tests/test_views.py
def test_order_list_uses_select_related(client, assert_num_queries, django_user_model):
    user = django_user_model.objects.create_user(username="test", password="pass")
    Order.objects.bulk_create([Order(user=user, total=i * 10) for i in range(10)])

    with assert_num_queries(2):  # 1 for orders + 1 for users (joined)
        response = client.get("/orders/")
    assert response.status_code == 200

If someone removes select_related from the view, this test fails with:

AssertionError: 11 queries executed, 2 expected
Queries:
  1. SELECT "orders_order"."id", ... FROM "orders_order"
  2. SELECT "auth_user"."id", ... FROM "auth_user" WHERE id = 1
  3. SELECT "auth_user"."id", ... FROM "auth_user" WHERE id = 1
  ...

The query log tells you exactly what went wrong.

Using Django's Built-in assertNumQueries

Django's TestCase has built-in assertNumQueries as a context manager:

from django.test import TestCase

class OrderViewTest(TestCase):
    def setUp(self):
        self.user = User.objects.create_user(username="test")
        Order.objects.bulk_create([
            Order(user=self.user, total=i) for i in range(5)
        ])

    def test_list_view_query_count(self):
        with self.assertNumQueries(2):
            response = self.client.get("/api/orders/")
        self.assertEqual(response.status_code, 200)
        self.assertEqual(len(response.json()), 5)

For pytest-django users, use django_db_reset_sequences or the standard db fixture:

@pytest.mark.django_db
def test_order_list_query_count(client, django_assert_num_queries):
    user = User.objects.create(username="testuser", email="test@test.com")
    Order.objects.bulk_create([Order(user=user, total=i) for i in range(5)])

    with django_assert_num_queries(2):
        response = client.get("/api/orders/")
    assert response.status_code == 200

Testing select_related Behavior

select_related performs a SQL JOIN. Test that it actually loads the related object in the same query:

@pytest.mark.django_db
def test_select_related_loads_user(django_assert_num_queries):
    user = User.objects.create(username="user1", email="u1@test.com")
    Order.objects.create(user=user, total=100)

    # With select_related: 1 query (JOIN)
    with django_assert_num_queries(1):
        order = Order.objects.select_related("user").first()
        email = order.user.email  # No additional query

    assert email == "u1@test.com"

@pytest.mark.django_db
def test_without_select_related_hits_db_per_access(django_assert_num_queries):
    user = User.objects.create(username="user2", email="u2@test.com")
    Order.objects.create(user=user, total=100)

    # Without select_related: 2 queries
    with django_assert_num_queries(2):
        order = Order.objects.first()
        email = order.user.email  # Triggers second query

These two tests document the expected behavior and prevent accidental regressions in both directions.

Testing prefetch_related Behavior

prefetch_related handles many-to-many and reverse FK relationships with separate queries:

@pytest.mark.django_db
def test_prefetch_related_loads_items(django_assert_num_queries):
    user = User.objects.create(username="u3", email="u3@test.com")
    order = Order.objects.create(user=user, total=50)
    Product.objects.bulk_create([Product(name=f"P{i}") for i in range(3)])
    products = Product.objects.all()
    order.items.set(products)

    # prefetch_related: 2 queries (1 for orders, 1 for items)
    with django_assert_num_queries(2):
        orders = Order.objects.prefetch_related("items").filter(user=user)
        for o in orders:
            item_names = [item.name for item in o.items.all()]

    assert len(item_names) == 3

Testing Prefetch Objects for Custom Filtering

Prefetch() lets you apply filters to prefetched querysets. This is important to test—a filtered prefetch doesn't affect the base queryset:

from django.db.models import Prefetch

@pytest.mark.django_db
def test_prefetch_filters_active_items(django_assert_num_queries):
    order = Order.objects.create(user=User.objects.create(username="u4"), total=0)
    OrderItem.objects.create(order=order, name="Active", is_active=True)
    OrderItem.objects.create(order=order, name="Inactive", is_active=False)

    active_items = Prefetch(
        "items",
        queryset=OrderItem.objects.filter(is_active=True),
        to_attr="active_items"
    )

    with django_assert_num_queries(2):
        orders = list(Order.objects.prefetch_related(active_items))

    assert len(orders[0].active_items) == 1
    assert orders[0].active_items[0].name == "Active"

Detecting N+1 with pytest-django-queries

For more detailed query analysis, use pytest-django-queries:

pip install pytest-django-queries

@pytest.mark.django_db
@pytest.mark.count_queries
def test_order_serialization(client):
    users = [User.objects.create(username=f"u{i}", email=f"u{i}@test.com") for i in range(5)]
    for user in users:
        Order.objects.create(user=user, total=100)

    response = client.get("/api/orders/")
    assert response.status_code == 200

Run with pytest --django-db-bench to get a query count report. This integrates with CI to fail builds when query counts regress.

Testing Aggregations and Annotations

Annotated querysets are common sources of unexpected query behavior. Test them explicitly:

from django.db.models import Count, Sum, Avg

@pytest.mark.django_db
def test_user_order_aggregation(django_assert_num_queries):
    user = User.objects.create(username="agg_user", email="agg@test.com")
    Order.objects.bulk_create([
        Order(user=user, total=100),
        Order(user=user, total=200),
        Order(user=user, total=300),
    ])

    with django_assert_num_queries(1):
        result = User.objects.annotate(
            order_count=Count("orders"),
            total_spent=Sum("orders__total"),
            avg_order=Avg("orders__total"),
        ).get(pk=user.pk)

    assert result.order_count == 3
    assert result.total_spent == 600
    assert result.avg_order == 200

One query for all three aggregations—verified.

Testing only() and defer()

only() and defer() affect which fields are loaded. Missing a deferred field access triggers a lazy load—test for it:

@pytest.mark.django_db
def test_only_prevents_full_load(django_assert_num_queries):
    User.objects.create(username="deferred", email="d@test.com", bio="Long bio text...")

    with django_assert_num_queries(1):
        user = User.objects.only("username", "email").first()
        _ = user.username  # Already loaded
        _ = user.email     # Already loaded

    # Accessing a deferred field triggers a new query
    with django_assert_num_queries(1):
        _ = user.bio  # Triggers deferred load

Asserting Query Content with connection.queries

For debugging specific query patterns, inspect django.db.connection.queries directly:

from django.db import connection, reset_queries
from django.conf import settings

@pytest.mark.django_db(transaction=True)
def test_query_contains_join(settings):
    settings.DEBUG = True
    reset_queries()

    User.objects.create(username="qtest", email="q@test.com")
    Order.objects.create(user=User.objects.first(), total=50)

    list(Order.objects.select_related("user").all())

    queries = [q["sql"] for q in connection.queries]
    assert any("JOIN" in q.upper() for q in queries), "Expected JOIN in query"
    assert len(queries) == 1

Note: connection.queries only works with DEBUG = True.

Integrating with HelpMeTest

Unit tests catch N+1 regressions during development. HelpMeTest adds continuous monitoring that runs your end-to-end test flows on schedule—catching slow page loads caused by query regressions that slipped through code review. Set up a health check on your order list endpoint and get alerted when response time degrades.

Summary

Django ORM query count testing prevents the most common performance regressions:

assertNumQueries — built-in Django assertion, works with TestCase and pytest-django
select_related tests — verify JOINs are happening, not lazy loads
prefetch_related tests — verify batch loading for reverse FKs and M2M
Prefetch() with to_attr — test filtered prefetches
Annotation queries — assert aggregations run in 1 query
only()/defer() — explicitly test deferred field access patterns

Write query count assertions for every view and serializer that accesses related objects. A test that takes 30 seconds to write saves hours of production debugging.