Verifying API Responses Against Database Records

This lesson is the headline reason most QA engineers learn SQL. An API returns 201 Created. A response body shows the new record. The test goes green. But did the row actually save? With the right values? Linked to the right user? Only the database can answer those questions truthfully — the API is just a messenger. This lesson covers the verification pattern that backs every important API test with a SQL check, plus the specific queries to run after POST, PATCH, and DELETE operations.

Why API responses aren't enough

A 201 says "the server thinks it created a record." Most of the time that's the same thing as "a record exists." But these failure modes all leave the response saying "all good" while the database disagrees:

• The transaction commits, then a follow-up step (a webhook, an audit-log write) errors and rolls back the whole transaction — after the response has been sent.
• An async write goes to a queue. The queue worker fails silently.
• A bug in the response serializer returns the input echoed back, not what was saved.
• A "delete" sets a soft-delete flag but the row is still there.

For payments, account changes, deletions, and audit-relevant writes, those failure modes matter. The verification pattern catches them.

The verification pattern, in five steps

That's the whole lesson, in a diagram. The rest is showing what the SQL looks like for each kind of API operation.

Verifying a POST (create)

You call POST /api/users with {"name": "Alice", "email": "alice@new.com", "role": "admin"} and get back a 201 with {"id": 6, ...}. Now verify what actually landed:

SELECT id, name, email, role, is_active, created_at
FROM users
WHERE email = 'alice@new.com';

Expected result:

| id | name  | email          | role  | is_active | created_at          |
|----|-------|----------------|-------|-----------|---------------------|
| 6  | Alice | alice@new.com  | admin | TRUE      | 2026-05-06 11:32:14 |

What to assert in the test:

• Exactly one row exists.
• name, email, role match what you sent.
• is_active is the schema default (TRUE) — confirming the API didn't silently override it.
• created_at is within a few seconds of the test's wall-clock time.
• id matches what the API response claimed (6 here).

That's five checks. The API response covered most of them. The DB row tells you the truth.

Verifying a PATCH (partial update)

You call PATCH /api/users/1 with {"role": "viewer"}. The response is 200 OK. Verify the change:

SELECT id, role FROM users WHERE id = 1;

Expected:

| id | role   |
|----|--------|
| 1  | viewer |

For PATCH especially, also verify that nothing else changed. PATCH is supposed to be partial — if other columns shifted, that's a bug:

SELECT id, name, email, role, is_active FROM users WHERE id = 1;
-- Expected: name and email unchanged from before; only role moved.

If the schema has an updated_at column, also verify it moved forward — otherwise the application's update path is skipping its own bookkeeping.

Verifying a DELETE

You call DELETE /api/users/5. The response is 204 No Content. Did the row actually go?

-- Hard delete check
SELECT COUNT(*) FROM users WHERE id = 5;
-- Expected: 0

If your application uses soft delete instead, the row should still be there but with a deleted_at timestamp set:

-- Soft delete check
SELECT id, deleted_at FROM users WHERE id = 5;
-- Expected: 1 row, deleted_at IS NOT NULL, set to roughly "now"

Both are valid behaviours; both have different test implications. Don't assume — confirm which one your application uses.

Verifying related data — the JOIN check

A POST that creates a complex resource often touches several tables. "Place an order" inserts into orders and order_items. Verify both:

SELECT
    o.id,
    o.total,
    o.status,
    COUNT(oi.id) AS item_count,
    SUM(oi.quantity * oi.price) AS items_total
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.id = 42
GROUP BY o.id, o.total, o.status;

Expected:

| id | total  | status  | item_count | items_total |
|----|--------|---------|------------|-------------|
| 42 | 150.00 | pending | 3          |    150.00   |

Three checks in one query: the order exists, the right number of line items were created, and the line items sum to the order total. If items_total != total, the application has miscounted — a real bug, invisible to a happy-path API test.

Timestamp sanity — the "is it actually fresh?" check

Comparing created_at to roughly now is a great sanity check. The exact form depends on your database:

-- PostgreSQL / SQLite — created_at within the last minute
SELECT id, created_at FROM users
WHERE id = 6
  AND created_at >= NOW() - INTERVAL '1 minute';

For SQLite use DATETIME('now', '-1 minute'). For MySQL use NOW() - INTERVAL 1 MINUTE. The point isn't the syntax, it's the assertion — the row was created in this test run, not yesterday by some other test that left it behind.

A small style note — uniqueness in the test result

When you SELECT the row you just created, expect exactly one row — not zero, not two. A simple way to express that in a test:

SELECT COUNT(*) FROM users WHERE email = 'alice@new.com';
-- Expected: 1

If the count is 2, something inserted twice (often a retry-on-failure bug). If the count is 0, the API didn't actually save the row. Both are findings worth investigating.

The full why and when of database verification — including the cleanup patterns and "never run against production" caveats — is covered in the API Testing Masterclass lesson on Data Integrity and Database Verification. That lesson explains the strategic side; this course teaches the SQL.

⚠️ Common Mistakes

• Trusting the API response over the database row. The API can echo back the input, not the saved row. If you only assert on the response, a serializer bug never gets caught. The DB row is the truth.
• Forgetting to verify timestamps. created_at and updated_at are some of the easiest fields for an application to silently skip. If a test writes data and updated_at doesn't move, you've found a real bug — but only if you looked.
• Not cleaning up. A test that creates a row and leaves it behind pollutes the next run. Use a recognisable suffix (+ci@qa.local) and DELETE in a teardown — covered in Chapter 4, Lesson 3.

🎯 Practice Task

25 minutes. Use the e-commerce database — pretend each step is a verification after a fictional API call.

1. Pretend POST /api/users returned 201 with id 6 (which doesn't exist yet). INSERT the user yourself with name 'API Test', email 'api-test@qa.local', role 'tester'. Then write the SELECT that would verify the row landed correctly. Note what you'd assert on.
2. Pretend PATCH /api/users/1 set role to 'viewer'. UPDATE accordingly. Write the SELECT that verifies the role changed and the email is unchanged.
3. Pretend DELETE /api/users/3 ran (Carol). Run the DELETE. Write the verifying COUNT query — should return 0.
4. Pretend POST /api/orders for user 1 returned id 6 with total 99.99 and a single line item for product 5 (quantity 2). INSERT both rows in a transaction (cleanup-friendly). Write the JOIN-with-COUNT verification query that confirms both the order and its line item were saved with consistent totals.
5. Roll back or DELETE everything you inserted so the seed data is intact for the next lesson.
6. Stretch: for the order in step 4, deliberately INSERT a mismatch — order total says 99.99 but the line items sum to 200. Run the verification query and observe how the JOIN result reveals the inconsistency. This is exactly the bug Chapter 5 Lesson 3 will teach you to detect at scale.

Next lesson: the same verification mindset applied to UI flows — when a user clicks Submit in the browser, what should you check in the database afterwards?