Database Validation After an API Call

📌 Definition:

📖 Detailed Explanation:

An API returning 200 OK proves the request was accepted, not that the data landed correctly. A payment endpoint can return success while writing the wrong amount, skipping an audit row, leaving status as 'pending', or failing to decrement inventory. Response-only testing misses these because the bug is in persistence, not in the response body. DB validation closes the gap: after the call, you query the affected tables and assert the actual stored state. It's especially critical for asynchronous flows (the API returns 202 Accepted and a worker writes later), for side effects the response doesn't echo (audit logs, inventory, derived columns), and for confirming that a 'delete' actually removed or soft-deleted the row. The discipline is to validate every side effect the operation is contracted to produce, at the data layer, using precise queries.

🔑 Key Points:

• A 2xx response proves acceptance, not correct persistence — validate the stored state
• Catch bugs the response never shows: wrong amount, missing audit row, unchanged status, un-decremented inventory
• Essential for async flows where the write happens after the response returns
• Assert exact values AND relationships (foreign keys, derived columns), not just row existence
• Query by a stable key from the response (the created id), not by guessing

🌍 Real-World Example:

📎 Code Example:

-- After POST /api/orders returned { "id": 5001, "total": 59.98 }

-- 1) The order persisted with the correct status and total
SELECT status, total
FROM orders
WHERE id = 5001;
-- expect: status = 'confirmed', total = 59.98

-- 2) The line items match what was ordered (2 items)
SELECT COUNT(*) AS item_count
FROM order_items
WHERE order_id = 5001;
-- expect: item_count = 2

-- 3) The side effect the response never showed: inventory decremented
SELECT stock
FROM products
WHERE id = 42;
-- expect: stock reduced by the quantity ordered

-- 4) An audit row was written
SELECT COUNT(*) FROM audit_log
WHERE entity = 'order' AND entity_id = 5001 AND action = 'created';
-- expect: 1