beginnerintermediateadvanced

Django · Section 69

Database Testing

Level: advanced
Read: 32 min
Concepts: 3

Every database test runs inside two nested boxes, and knowing which one you are in explains almost every confusing failure. The outer box is the test database — `test_` prefixed onto your real `NAME`, migrated from empty before the suite and destroyed afterwards regardless of whether tests passed. The inner box is the transaction `TestCase` opens around each test method and rolls back at the end, which is what keeps tests from leaking rows into one another and what makes them fast. The trouble starts when the code under test cares about that boundary: `on_commit()` callbacks never fire, `select_for_update()` cannot be shown to block, and deferred constraints never raise, because none of it ever commits. The decision rule is to stay on the fast class until the behaviour under test *is* the commit — reaching first for `captureOnCommitCallbacks(execute=True)`, which emulates a commit inside a rollback test, and only then for `TransactionTestCase` (or `django_db(transaction=True)`), which truncates every table per test in exchange for real commits on real separate connections. Constraints need the same care from the other direction: a test that posts a duplicate through a serializer stays green with no constraint in the database at all, so the test that matters writes the row directly, asserts the constraint by name, and wraps the expected `IntegrityError` in its own `atomic()` so the abort does not poison everything after it. The last piece is what a database test can assert beyond rows. A query count is the one performance check cheap and deterministic enough for every commit — but only if the number is a budget you chose and re-checked at ten times the data, not a number copied from a run. Factories keep setup down to the field each test is actually about. And data migrations are the blind spot nothing else covers: the test database is always built from zero rows, so a `RunPython` is never exercised until a migration test moves the schema back, inserts old-shaped rows, and migrates forward.

Django overview

Python Django Full Course for Beginners | Complete All-in-One Tutorial | 3 HoursDave Gray

What is true here

TestCase rolls back; TransactionTestCase truncates. Pay for the second only when a commit is the thing under test.
captureOnCommitCallbacks(execute=True) covers on_commit side effects without leaving the fast class.
Test constraints by writing rows directly, and wrap the expected IntegrityError in its own atomic().
Choose query budgets rather than recording them, then re-run at 10x rows to prove there is no N+1.
Data migrations get zero coverage from the normal suite — they need a migration test to see any rows at all.

What you will be able to do

Explain why an `on_commit` assertion fails in a `TestCase`, and fix it without slowing the suite
Write a constraint test that still fails when a new write path bypasses your serializer
Turn an N+1 regression into a deterministic red test that runs in milliseconds
Test a data migration against the old-shaped rows it will actually meet in production

One test run, and the two boxes every assertion sits inside

CREATE DATABASE test_shop

once per run — `--keepdb` skips it

Apply every migration

against zero rows — why RunPython is never exercised

BEGIN

TestCase opens a transaction per test method

Your test body

factories build rows · assertions run · query counts captured

ROLLBACK

fast cleanup — nothing was ever committed

Real COMMIT + TRUNCATE

TransactionTestCase / django_db(transaction=True)

on_commit never fires

the most common confusing failure

captureOnCommitCallbacks(execute=True)

emulates the commit, keeps the fast cleanup

DROP DATABASE test_shop

pass or fail, at the end of the run

The database and the transaction around each test

What Django builds before the suite, what each test-case class does between tests, and the boundary that explains most confusing failures.

The test database lifecycle, and transaction behaviour per test class

coreintermediate

Your tests never touch the real database. Django creates a second one named `test_` plus your real database name, runs every migration into it, runs the suite, and destroys it at the end — pass or fail. Inside that database, the class you inherit from decides how each test is cleaned up. `TestCase` wraps each test in a transaction and rolls it back, which is fast. `TransactionTestCase` lets the test really commit and then truncates every table, which is slow but is the only way to test anything that depends on a commit actually happening.

Think of it as

Think of two nested boxes. The outer box is the test *database*: created once before the suite, migrated, and dropped afterwards. The inner box is the test *transaction*: opened before one test method and thrown away after it. Almost everything you write lives in the inner box, and the inner box is why your tests do not leak rows into each other. The moment your code under test cares about the boundary of that box, the box becomes the bug. `transaction.on_commit()` callbacks never fire, because the commit never happens. `select_for_update()` cannot be shown to block, because there is no second committed transaction to block against. A real `IntegrityError` from a deferred constraint arrives at commit time, which never arrives. That is the whole decision rule: if the behaviour under test is *inside* a transaction, use `TestCase` and enjoy the speed; if the behaviour under test *is* the transaction, you have to give it up and use `TransactionTestCase`, paying a truncate per test. The third option is the one people forget — `captureOnCommitCallbacks(execute=True)` emulates a commit inside a fast `TestCase`, which covers most `on_commit` work without the slow class.

bash

python manage.py test --keepdb          # reuse test_<name>, apply new migrations
python manage.py test --parallel        # one test database per process
pytest --reuse-db                       # pytest-django's equivalent of --keepdb

What we're doing: Show the rollback boundary biting, and the three ways past it — in the order you should try them.

orders/tests/test_lifecycle.pypython

@pytest.mark.django_db
def test_placing_an_order_sends_no_email_yet(mailoutbox):
    place_order(customer, cart)          # calls transaction.on_commit(send_receipt)
    assert len(mailoutbox) == 0          # correct: the commit has not happened


@pytest.mark.django_db
def test_placing_an_order_sends_a_receipt(django_capture_on_commit_callbacks, mailoutbox):
    with django_capture_on_commit_callbacks(execute=True) as callbacks:
        place_order(customer, cart)

    assert len(callbacks) == 1
    assert len(mailoutbox) == 1
    assert mailoutbox[0].subject == "Your receipt"


@pytest.mark.django_db(transaction=True)
def test_claiming_a_job_locks_the_row():
    job = Job.objects.create(state="ready")
    blocked = threading.Event()

    def second_worker():
        with transaction.atomic():
            locked = Job.objects.select_for_update(nowait=True)
            with pytest.raises(OperationalError):
                list(locked.filter(pk=job.pk))
        blocked.set()

    with transaction.atomic():
        Job.objects.select_for_update().get(pk=job.pk)
        threading.Thread(target=second_worker).start()
        assert blocked.wait(timeout=5)

1–4: The rollback boundary as a feature, not a problem: this test asserts the receipt is *not* sent before the transaction commits, which is the behaviour `on_commit()` exists to give you.
8–9: The middle option, and the one to reach for first. `execute=True` runs the captured callbacks as the context manager exits, "emulating a commit", inside a fast rollback-based test.
12: Assert on the callbacks list too. A refactor that stops registering the callback at all still leaves `mailoutbox` empty in the wrong way otherwise.
17–18: Only here is `transaction=True` earned: a second thread on a second connection has to *see* a committed row for the lock to mean anything. This test costs a truncate.
22–27: `nowait=True` turns "block forever" into an immediate `OperationalError`, so the test fails in milliseconds instead of hanging the suite.

Why this works: The three tests are the whole decision rule in order of cost: assert the pre-commit behaviour if that is what you mean, emulate the commit if you need the side effect, and pay for a real transaction only when a second connection has to observe the first one.

Asserting on an `on_commit()` side effect inside a plain `TestCase`

Wrong

python

@pytest.mark.django_db
def test_receipt_is_emailed(mailoutbox):
    place_order(customer, cart)
    assert len(mailoutbox) == 1     # AssertionError: 0 == 1

Better

python

@pytest.mark.django_db
def test_receipt_is_emailed(django_capture_on_commit_callbacks, mailoutbox):
    with django_capture_on_commit_callbacks(execute=True):
        place_order(customer, cart)
    assert len(mailoutbox) == 1

What you see: The feature works in a browser and the test fails with an empty outbox. The usual next move is to delete the `on_commit()` wrapper "because it breaks the tests" — which reintroduces the bug of emailing a receipt for an order that then rolls back.

Why: `TestCase` never commits, so `transaction.on_commit()` never runs its callbacks. The failure is real information about the test harness, not about the code. `captureOnCommitCallbacks(execute=True)` runs those callbacks as the block exits and keeps the fast rollback cleanup, so you get the assertion without moving the whole test class to `TransactionTestCase`.

The same test, cleaned up two different ways

TestCase — rollback

+BEGIN before the test, ROLLBACK after it
+Nothing is ever committed to the test database
+`on_commit()` callbacks never fire on their own
+A second connection cannot see the rows this test made
+Fast: no table truncation between tests

TransactionTestCase — truncate

−The test really commits
−Every table is truncated afterwards
−`on_commit()` fires, locks really block
−A second connection sees the committed rows
−Slow: pay a truncate per test, so use it deliberately

Which class (or marker) to reach for, and what it costs

Which class (or marker) to reach for, and what it costs
You are testing	Django class	pytest-django	Cleanup
ordinary reads and writes	`TestCase`	`@pytest.mark.django_db`	rollback — fast
`on_commit()` side effects	`TestCase` + `captureOnCommitCallbacks`	`django_capture_on_commit_callbacks`	rollback — fast
`select_for_update()` blocking	`TransactionTestCase`	`django_db(transaction=True)`	truncate — slow
a real race between two connections	`TransactionTestCase`	`django_db(transaction=True)`	truncate — slow
no database at all	`SimpleTestCase`	no marker	nothing to clean

Together

python

@pytest.mark.django_db(transaction=True)
def test_two_workers_cannot_both_claim_the_job():
    ...   # real commits, so a second connection can see them

Remember: Django builds `test_<your database>`, migrates it, and destroys it after the run — use `--keepdb` (or `pytest --reuse-db`) so you stop paying for the migrate every time. Inside it, `TestCase` rolls back and `TransactionTestCase` truncates. Stay on the fast one until the thing under test *is* the commit: reach for `captureOnCommitCallbacks(execute=True)` for `on_commit` side effects, and only pay for `transaction=True` when a second connection has to see committed rows.

Constraints, violations, and real races

Testing the guarantees the database keeps when your Python is wrong — and the two-connection tests worth their cost.

Testing constraints, unique violations, and real races

coreadvanced

A constraint is a promise the database keeps even when your Python is wrong, so the test for it must go around your Python. Write the duplicate row directly and assert the database raises `IntegrityError` — a test that goes through your serializer only proves the serializer works. Two rules make these tests behave: wrap the failing write in `transaction.atomic()`, because an `IntegrityError` poisons the surrounding transaction and every later query in the test fails with a confusing error; and give a genuine two-connection race `transaction=True`, because a race between two connections needs both of them to see committed rows.

Think of it as

Application validation and a database constraint answer the same question at different times, and they fail differently. `Order.objects.filter(reference=x).exists()` is a *check*, and between your check and your insert another request can insert the same value. `UniqueConstraint` is an *enforcement*, and it cannot be raced because the database evaluates it during the write itself. So the test suite needs both kinds of test: a friendly-error test that the form or serializer reports "this reference is already taken", and a constraint test that bypasses all of that and shows the database refuses the row anyway. Only the second one still passes after somebody adds a management command, a data import or a Celery task that writes without the serializer. The atomic-block rule follows from how PostgreSQL behaves: once a statement in a transaction errors, the transaction is aborted and every subsequent statement fails until you roll back. Django surfaces that as `TransactionManagementError`, and the fix is to scope the expected failure inside its own `atomic()` so the rollback is local. As for races, most are not worth a test — but the ones where money, stock or identity depend on a single row are, and those need real commits on real separate connections. A race test that shares one connection proves nothing, because a single connection serializes itself.

python

with pytest.raises(IntegrityError):
    with transaction.atomic():        # keeps the abort local to this block
        Order.objects.create(reference="A-1")

What we're doing: Prove the constraint holds against a write path that skips every serializer, and against two connections at once.

orders/tests/test_constraints.pypython

@pytest.mark.django_db
def test_duplicate_active_reference_is_refused_by_the_database():
    OrderFactory(reference="A-1", state="active")

    with pytest.raises(IntegrityError, match="unique_active_reference"):
        with transaction.atomic():
            Order.objects.create(reference="A-1", state="active", total=10)

    assert Order.objects.count() == 1     # works: the abort stayed inside atomic()


@pytest.mark.django_db
def test_the_same_reference_is_allowed_once_the_first_is_cancelled():
    OrderFactory(reference="A-1", state="cancelled")
    Order.objects.create(reference="A-1", state="active", total=10)   # no error

    assert Order.objects.filter(reference="A-1").count() == 2


@pytest.mark.django_db(transaction=True)
def test_only_one_of_two_concurrent_claims_wins():
    Stock.objects.create(sku="DESK-1", remaining=1)
    results, barrier = [], threading.Barrier(2)

    def claim():
        barrier.wait()                    # both threads arrive together
        try:
            with transaction.atomic():
                results.append(reserve_last_unit("DESK-1"))
        except IntegrityError:
            results.append("refused")
        finally:
            connection.close()            # or teardown's TRUNCATE hangs

    threads = [threading.Thread(target=claim) for _ in range(2)]
    for t in threads:
        t.start()
    for t in threads:
        t.join(timeout=10)

    assert sorted(results) == ["refused", "reserved"]
    assert Stock.objects.get(sku="DESK-1").remaining == 0

5–7: The write goes straight at the model. A test that posted to the API instead would pass even if the constraint were dropped, because the serializer would catch it first.
6: The inner `atomic()` is what makes line 9 possible. Without it the `IntegrityError` aborts the whole test transaction and the `count()` raises `TransactionManagementError`.
13–14: The negative half, and the one that documents the `condition=Q(state="active")` on the constraint. A partial unique index that was quietly written as a plain one fails here and nowhere else.
20–21: `transaction=True` earns its cost here: thread two has to see thread one's committed row.
26: A `Barrier` beats a `sleep`. Both threads block until both have arrived, so the window is real instead of hopeful.
33: Every thread opens its own connection. Leaving it open makes teardown's `TRUNCATE` wait on a lock that nothing will release.

Why this works: The first two tests pin what the constraint does and does not forbid, without depending on any application code. The third proves the invariant survives two workers hitting it in the same millisecond — which is the only condition under which it was ever in doubt.

Letting an `IntegrityError` escape the test's transaction

Wrong

python

@pytest.mark.django_db
def test_duplicate_is_refused():
    OrderFactory(reference="A-1")
    with pytest.raises(IntegrityError):
        Order.objects.create(reference="A-1")

    assert Order.objects.count() == 1
    # TransactionManagementError: An error occurred in the current transaction.
    # You can't execute queries until the end of the 'atomic' block.

Better

python

@pytest.mark.django_db
def test_duplicate_is_refused():
    OrderFactory(reference="A-1")
    with pytest.raises(IntegrityError):
        with transaction.atomic():
            Order.objects.create(reference="A-1")

    assert Order.objects.count() == 1

What you see: The assertion you care about never runs. The test fails on a `TransactionManagementError` several lines below the interesting one, which reads like a harness bug and sends people looking in the wrong file.

Why: PostgreSQL aborts a transaction as soon as a statement in it errors, and refuses every later statement until it is rolled back. `TestCase` has already opened one transaction around the whole test, so an uncaught `IntegrityError` poisons it. The inner `atomic()` gives the failing statement its own savepoint, which is released on the way out — the outer transaction survives, and the rest of the test can still query.

The race the constraint test cannot see, and the one it can

worker A

worker B

PostgreSQL

1. SELECT … WHERE reference = "A-1"the check
2. 0 rows — looks free
3. SELECT … WHERE reference = "A-1"B checks in the same window
4. 0 rows — also looks free
5. INSERT reference = "A-1"
6. COMMIT — row 1 exists
7. INSERT reference = "A-1"the check is now stale
8. IntegrityError — unique_active_referencethe constraint holds where the check did not

What each layer catches, and the test that proves it

What each layer catches, and the test that proves it
Layer	Fails with	Test it by
`clean()` / serializer validation	`ValidationError`, field-level	posting the duplicate through the API and asserting the field name
`UniqueConstraint`	`IntegrityError`	`Model.objects.create()` twice, inside `atomic()`
`CheckConstraint`	`IntegrityError`	creating a row with the forbidden value directly
`ForeignKey` referential integrity	`IntegrityError`	deleting the parent and asserting `PROTECT` raises
a deferred constraint	`IntegrityError` at commit	`transaction=True`; a rollback test never commits

Together

python

with pytest.raises(IntegrityError, match="unique_active_reference"):
    with transaction.atomic():
        Order.objects.create(reference="A-1", state="active")

Remember: Test a constraint by writing the row directly, so the test still fails when a new write path skips your serializer — and assert the constraint *name*, not only the exception class. Always wrap the expected `IntegrityError` in its own `transaction.atomic()`, or the abort poisons the test transaction and your real assertion never runs. Save `transaction=True` and threads for invariants where two workers genuinely compete, use a `Barrier` rather than a `sleep`, and close each thread's connection in a `finally`.

Counting queries, building rows, and migrating data

Performance as a deterministic assertion, setup that reads as intent, and the migration the normal suite never runs.

Query-count assertions, factories, and migration tests

coreintermediate

`assertNumQueries(7, func)` fails when the code runs a different number of queries than you said. That is how an N+1 regression becomes a red test instead of a slow page, because adding one field to a serializer can turn 7 queries into 700 without changing a single assertion about the response body. Factories (`factory_boy`) build the rows those tests need without a hand-written `create()` for every field, and `SubFactory` builds the related rows too. Migration tests are the third kind: they run a data migration against rows that existed *before* it and assert what it did to them.

Think of it as

A query count is a performance assertion you can afford to run on every commit. Every other performance test needs a timer, a warm cache and a quiet machine; a query count needs none of those and fails deterministically, which is what makes it the one performance check that belongs in a normal unit test. Think of the number as a budget you deliberately set rather than a fact you record: write the count you believe the endpoint should need, and treat any change as something to explain. That framing matters, because the common failure is to run the test, see 43, and paste 43 into the assertion — which locks in the N+1 instead of catching it. Factories exist for a different reason: as a schema grows, the setup lines in a test drift into noise that hides what the test is actually about. A factory says "a valid Order exists" in one line and lets each test override only the field it cares about, so the test reads as its own intent. And migration tests cover the one thing normal tests structurally cannot: your test database is built by running every migration against nothing, so a data migration always runs against zero rows there and is never exercised. The only way to test it is to move the schema to the migration before, insert the old-shaped rows yourself, then migrate forward.

python

with django_assert_num_queries(3):
    client.get("/api/orders/")

self.assertNumQueries(3, lambda: client.get("/api/orders/"))   # unittest style

What we're doing: Pin a list endpoint's query budget so an N+1 fails CI, then test a data migration against rows it can actually see.

orders/tests/test_queries_and_migrations.pypython

class OrderFactory(factory.django.DjangoModelFactory):
    class Meta:
        model = Order

    customer = factory.SubFactory(CustomerFactory)
    reference = factory.Sequence(lambda n: f"A-{n}")
    total = 25


@pytest.mark.django_db
def test_order_list_costs_three_queries_at_any_size(api, django_assert_num_queries):
    OrderFactory.create_batch(2)
    api.force_authenticate(UserFactory(is_staff=True))

    with django_assert_num_queries(3):        # session + count + page
        api.get(reverse("order-list"))

    OrderFactory.create_batch(20)             # 10x the rows, same budget
    with django_assert_num_queries(3):
        api.get(reverse("order-list"))


@pytest.mark.django_db
def test_backfill_migration_normalises_existing_references(migrator):
    old = migrator.apply_initial_migration(("orders", "0031_add_reference_column"))
    Order = old.get_model("orders", "Order")

    Order.objects.create(reference="  a-1  ", total=10)
    Order.objects.create(reference="A-2", total=10)

    new = migrator.apply_tested_migration(("orders", "0032_normalise_references"))

    Order = new.get_model("orders", "Order")
    assert sorted(Order.objects.values_list("reference", flat=True)) == ["A-1", "A-2"]

1–7: One factory replaces the eight setup lines every order test would otherwise repeat. `Sequence` keeps `reference` unique, so tests never collide on the constraint by accident.
11: The count is the budget you chose, with a comment saying what the three queries are. A bare `3` with no explanation is impossible to review when it later becomes `4`.
18–20: The half that makes it an N+1 test rather than a query-count snapshot: running it again at ten times the row count must not change the number.
24–26: Move the schema back to the migration *before* the one under test, and take the historical model from that state — the current model class has fields the old rows do not.
30: Rows inserted at the old schema. This is the state the migration will actually meet in production and the state the normal suite can never produce.
32–34: Assert on the data after migrating forward. A `RunPython` that silently skipped rows with leading whitespace fails right here.

Why this works: The first test turns a performance property into a deterministic assertion that runs in milliseconds; the second covers the one piece of code the standard test database structurally cannot exercise, because it is always migrated from empty.

Recording the query count instead of choosing it

Wrong

python

# ran it, saw 43, pasted 43
with django_assert_num_queries(43):
    api.get(reverse("order-list"))

Better

python

# 3 = session + COUNT + the page itself; anything more is an N+1
with django_assert_num_queries(3):
    api.get(reverse("order-list"))

# and prove it does not grow with the data
OrderFactory.create_batch(20)
with django_assert_num_queries(3):
    api.get(reverse("order-list"))

What you see: The test is green and the endpoint takes four seconds. Six months later someone "fixes" the failing count by editing 43 to 61, and the assertion has now documented three separate regressions as intended behaviour.

Why: A count copied from a run asserts only that behaviour has not changed — including behaviour that was already wrong. The value of the assertion comes from the number being a claim about what the code *should* need, derived from the queries you can name. Pair it with the same request at ten times the row count: a fixed budget that holds as the data grows is the actual definition of "no N+1", and it is the part a single recorded number never checks.

Three database-test shapes, and what only each one can prove

Query-count tests

assertNumQueries(7)

exact — catches N+1 and over-fetching

max_num_queries(12)

for counts that vary by branch

captured_queries

inspect the SQL, not only the count

Factories

OrderFactory()

a valid row in one line

SubFactory

builds the related rows too

override one field

the field under test is the only one named

Migration + integration tests

migrate to N-1, insert, migrate

the only way a RunPython sees rows

real stack, no mocks

view + ORM + database together

few and slow by design

keep them out of the fast suite

The three assertions, and the failure each one catches

The three assertions, and the failure each one catches
Assertion	Catches	Use when
`django_assert_num_queries(7)`	an N+1 introduced by a serializer or template change	the count is genuinely fixed — most list endpoints
`django_assert_max_num_queries(12)`	unbounded growth, while tolerating branch-dependent counts	the count varies with the caller's permissions or feature flags
`CaptureQueriesContext` + inspect `.captured_queries`	the wrong query — a missing `WHERE tenant_id`	the count is right but the SQL is not

Together

python

with django_assert_num_queries(3) as captured:
    client.get("/api/orders/")

assert all("tenant_id" in q["sql"] for q in captured.captured_queries)

Remember: Choose the query budget rather than recording it, name what the queries are in a comment, and run the same request again at ten times the rows — a count that holds as the data grows is what proves there is no N+1. Use factories so each test names only the field it is about. And remember that data migrations get zero coverage from the normal suite, because the test database is always migrated from empty: the only real test moves the schema back one migration, inserts old-shaped rows, and migrates forward.