Module 4 · Testing behind the screen Lesson 11 of 15
Database and test-data checks
Some rules cannot be seen from outside: a status that must move through an allowed set, an order total that must equal its lines. Query the database and assert the rule directly.
The concept
Point automated database tests at a throwaway database seeded by the test itself, never at production - a test that writes to live data is a test that will one day cause the incident.
Test data should be created by the test, not borrowed from whatever was in the table, or the suite passes on Monday and fails on Friday for no visible reason.
-- every application must point at a real job
SELECT a.id FROM applications a
LEFT JOIN jobs j ON a.job_id = j.id
WHERE j.id IS NULL;
-- every order total must equal the sum of its lines
SELECT o.id, o.total, SUM(l.price) AS line_total
FROM orders o JOIN order_lines l ON l.order_id = o.id
GROUP BY o.id, o.total
HAVING o.total <> SUM(l.price);Worked example
A query counting orders whose total is not the sum of their line items should return zero rows - a one-line test for a rule no UI test can prove.
Your practice task
Write two SQL checks: one for an orphan row, one for a total that does not match its lines.
Do this before moving on. Reading a solution teaches the shape of it; typing it teaches you.
Key takeaway
Point automated database tests at a throwaway database seeded by the test itself, never at production - a test that writes to live data is a test that will one day cause the incident.
Keep your place in this course
A free account saves which lessons you have finished and unlocks the certificate. The lesson itself stays free to read.
Create free account or sign inCourse complete
You finished every lesson.
Your certificate is ready, and the projects you built are the proof to attach to an application.
Almost there
15 lessons left of 15
Finish every lesson to unlock the certificate. Each one ends in a practice task — do them and the final project brief writes itself.