Database Testing with SQL

Introduction to Database Testing

UIs lie; databases do not. Verify that every action writes correct, complete rows and that bad actions write nothing.

SQL Every Tester Must Know

-- order + user + totals check
SELECT o.id, u.email, SUM(i.qty*i.price) AS total
FROM orders o JOIN users u ON u.id=o.user_id
JOIN order_items i ON i.order_id=o.id
GROUP BY o.id, u.email;

-- orphans: items without an order
SELECT * FROM order_items
WHERE order_id NOT IN (SELECT id FROM orders);

CRUD and Data Integrity Testing

• Create - register user, row exists with hashed password, never plain text.
• Read - filters/sorts/pagination match direct queries.
• Update - edit profile updates exactly one row, audit fields change.
• Delete - cancel order removes items (cascade) or blocks with a clear rule.
• Constraints - NULL, unique, foreign keys reject bad writes.

Stored Procedures and Migrations

• Execute procedures with edge inputs; verify outputs and side effects.
• After migrations: row counts match, checksums spot-check, rollback script tested.

API + Database Testing Together

The golden loop: call POST /api/orders, assert 201, then query the DB for the same order, totals and stock decrement. UI, API and DB must agree.

Key Points

• Always verify writes with SELECT, not just the UI message.
• Test constraints by trying to break them.
• API plus DB assertions catch bugs UIs hide.