Data Integrity and Database Verification

A 201 Created response says "the API thinks it created a record." It does not say "a record actually exists." Most of the time that's the same thing, but in the cases where it isn't — failed transactions, async writes that silently dropped, soft-deletes that didn't hide the row — your tests will go green while the data is wrong. For high-stakes operations, it's worth backing the API check with a direct look at the database. This lesson explains when database verification earns its keep, the patterns that make it cheap to maintain, and the ones that turn into a headache.

What the API tells you vs what actually happened

A typical "create user" flow:

1. You send POST /api/users.
2. The server validates input, runs business logic, commits a row to the database.
3. The server reads the row back and returns it as JSON with status 201.

The response is the server's report of what it did. In well-behaved systems, that report is true. But these failure modes all leave the response saying "all good":

• The transaction commits, then a follow-up step (e.g. a webhook) errors and the whole transaction gets rolled back — but only after the response was sent.
• An async write goes to a queue. The queue worker fails. Two days later, support is asking why "Alice" doesn't appear in the system.
• A soft-delete keeps the row in the table with deleted_at set; the API still returns it on a follow-up GET. The "delete" lied.
• A bug in the response serializer returns the input echoed back, not what was actually saved.

For low-risk operations these don't matter much. For payments, account changes, deletions, and audit-relevant writes, they matter a lot.

The verification pattern

The shape is always the same:

For a POST /api/users:

# 1. Call the API
response = api.post("/users", json={"name": "Alice", "email": "alice+abc@test.com"})
assert response.status_code == 201
returned = response.json()
 
# 2. Query the database directly
row = db.fetchone("SELECT id, name, email, created_at, deleted_at FROM users WHERE id = %s", returned["id"])
 
# 3. Verify
assert row is not None,                "user row missing in DB"
assert row["email"] == "alice+abc@test.com"
assert row["name"]  == "Alice"
assert row["deleted_at"] is None,      "user soft-deleted on creation"

Three tiny assertions, but they cover the gap between "the API said it worked" and "the data actually changed."

Where database verification is worth it

Not every test needs DB checks. Use them where the cost of a silent failure is high:

• Money flows — orders, payments, refunds, invoices.
• Auth-relevant writes — user creation, password changes, role assignments, MFA enrolment.
• Deletions — confirming the row is gone (or the soft-delete flag is set).
• Cascade effects — deleting a user should also remove their sessions / tokens / carts. Verify the cascade actually happened.
• Audit records — every action that should produce an audit log row.
• Async paths — anything that goes through a queue. Check after the worker has had time to run.

For a simple GET that reads from the same DB you'd be checking, a DB verification mostly duplicates the API. Skip it.

Cleanup is the hard part

The reason DB-touching tests get a bad reputation is cleanup. A test that creates user 42 and doesn't remove it pollutes the next test. After fifty test runs, the DB is full of Alice42, Alice43, Alice44.

Three patterns that work:

• Transactional rollback. Wrap each test in a database transaction that rolls back at the end. Powerful but requires the test framework and the DB driver to cooperate, and doesn't help when the API itself opens its own transactions on the server side.
• Per-test cleanup with unique data. Each test generates a unique value (UUID, timestamp suffix) for the entity it creates, then deletes it in a teardown. Robust and easy to reason about — and the unique data means tests can run in parallel safely.
• Dedicated test environment with periodic resets. A nightly job wipes test data. Useful for environments where tests can't directly delete (no sufficient privileges) but the noise builds up.

The middle one is what most teams settle on. A test fixture provides a unique email, the test creates the user, the teardown deletes by id.

Direct DB access vs internal endpoints

Two ways to read the DB in tests:

• Direct SQL connection. Most flexibility, fastest. Requires DB credentials in the test environment. Couples your tests to the schema — schema migrations may break tests.
• Internal admin/test endpoint (GET /admin/users/123 with privileged auth). Nicer abstraction; the API team can change the schema without breaking tests. Slower; requires the team to maintain the test endpoints.

For a test framework that already has DB access (a backend team, a monorepo with shared models), direct SQL is fine. For external QA, an internal endpoint is usually the better contract.

Never against production

Worth saying clearly: never run integrity-verifying tests against production. A bug in your test could create users, mutate orders, or delete data that real customers depend on. Always test against staging, ephemeral environments, or a dedicated test database with synthetic data.

What to assert beyond field equality

Common things to verify in the DB:

• Row presence/absence — exists after create, gone after hard-delete, flagged after soft-delete.
• Field values — what the user sent matches what was saved (or, more strictly, what the API claimed).
• Timestamps — created_at near "now", updated_at later than created_at, deleted_at set on soft-delete.
• Foreign keys — related rows exist and link correctly.
• Side effects — audit log entry was written, queue message was published, cache was invalidated.

A good rule: ask "what would a senior engineer reviewing this PR want to know was true?" — and assert each of those things.

When the API and the DB disagree

If your tests find that the API returns one value and the DB has another, the bug is real and important — and you need to figure out which side is right.

• API correct, DB wrong → serializer / response-builder bug. The data is fine; the response misrepresents it. Subtle, dangerous (clients are misled).
• API wrong, DB correct → save / write-path bug. The DB has truth; the API is hallucinating. Often caused by returning a stale in-memory object.
• Both wrong → a deeper bug. Usually a transaction / commit issue.

Either way, the test result is "an inconsistency exists." Hand the team enough detail (request, response body, DB row, timestamps) for them to triage.

⚠️ Common mistakes

• Forgetting cleanup. Polluted test environments lead to flaky tests, mysterious failures, and DBAs who hate you. Cleanup must run even when the test fails — try/finally or framework-level teardown.
• Verifying the same field both directions ad infinitum. Three or four meaningful assertions beat twenty pedantic ones. Pick the fields whose corruption would actually matter.
• Running DB tests against shared environments without coordination. Two suites both deleting email='qa-test@example.com' will fight. Use unique-per-run prefixes or dedicated tenants.

🎯 Practice task

Add a database verification step to one test. 30-40 minutes.

1. In your project (or any app you can spin up locally), pick one critical-path endpoint — POST /users, POST /orders, anything that writes data.
2. Confirm you have read access to the underlying DB (psql, mysql, or an admin endpoint). If not, find the equivalent: a search endpoint, an admin UI.
3. Add a test that: calls the endpoint, asserts on the response, then queries the DB and asserts on the row.
4. Add cleanup: delete the created row in a finally block (or framework teardown).
5. Deliberately break it — change an assertion to expect the wrong value. Confirm the failure message is clear enough that the next person can debug it.
6. Stretch: find a place where the API contract permits the response and DB to disagree (e.g. a field the API computes but doesn't persist, or vice versa). Decide which side is the source of truth and write an assertion against it.

That wraps up Chapter 5. Chapter 6 changes the question entirely — from REST to GraphQL, where the request shape is up to the client and the bugs hide in different places.