Key takeaways
- With basic SQL (
SELECT,WHERE,JOIN,COUNT) you can check what the application really saved, find test data and spot data problems the UI hides. - Good test data is realistic, covers edge cases, is under your control and can be recreated on demand.
- Don't copy real personal data into test environments without strong safeguards. Use synthetic or properly anonymised data instead.
Why testers need SQL
The UI shows you a summary. The database shows you the truth. With a few queries you can:
- Verify results: was the order saved with the right total, status and customer?
- Check side effects: did stock go down? Was the loyalty points row created?
- Find test data: a customer with exactly three orders, or an expired subscription.
- Spot data problems: duplicates, missing links, values that should never exist.
The SQL you need first
Imagine two tables: orders and order_items.
Read rows with a filter:
SELECT id, customer_id, total, status
FROM orders
WHERE customer_id = 1042
ORDER BY created_at DESC;
Count things:
SELECT status, COUNT(*)
FROM orders
GROUP BY status;
Combine tables with a JOIN:
SELECT o.id, o.total, i.product_id, i.quantity, i.price
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.id = 5001;
Find problems, for example orders with no items at all:
SELECT o.id
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
WHERE i.id IS NULL;
That last pattern, a LEFT JOIN plus IS NULL, finds "orphans": rows missing the data they should be linked to. It's one of the most useful queries a tester can learn.
What makes good test data
| Quality | Means | Example |
|---|---|---|
| Realistic | Looks like real use | Names with diacritics, long addresses, customers with many orders |
| Covers edge cases | Includes the unusual | Empty profile, maximum-length fields, zero-value orders, leap-day birthdays |
| Controlled | You know exactly what's there | Each test creates its own customer instead of using "whoever is in the database" |
| Repeatable | Can be recreated on demand | A script or API calls that build the data from scratch |
| Safe | Contains no real personal data | Synthetic names, test card numbers from the payment provider |
Where test data comes from
- Created by the test itself, through the API or fixtures. The best option for automated tests: independent and repeatable.
- Generated synthetic data, using libraries like Faker to produce realistic names, addresses and dates in bulk.
- Anonymised production copies, with personal data masked or replaced. Useful for realistic volumes and patterns, but anonymisation is harder than it looks. Combining a postcode, a birth date and an order history can still identify someone.
A team copied the production database into a test environment to get "realistic data". Personal data wasn't masked, because "it's only internal".
A tester ran a test of the new newsletter feature. It worked perfectly: the newsletter was sent to thousands of real customer email addresses from the test environment, with test content and broken links.
The company had to apologise to customers and report the incident to its data protection officer. Afterwards, the team generated synthetic customers for testing, and all test environments routed outgoing email to a capture tool instead of the internet.
Lesson: real personal data in test environments turns a normal test into a real-world incident. Synthetic data, and test environments that can't contact real people, prevent it.
Using SQL in a test
- Before: note the current state. For example, stock for product 42 is 10.
- Act through the application: buy 2 of product 42.
- After: query again. Stock should be 8; the order row and two item rows should exist; the total should equal the items' sum.
- Look around: no duplicate order rows, no orphan items.
Do
Let each automated test create the data it needs through the API, and check the result with a precise query.
Don't
Depend on data someone created by hand in a shared environment. When it changes, your tests fail for reasons that have nothing to do with the product.
Check yourself
You want to check that no customer has two accounts with the same email address. Which SQL query could you use?
Show answer
Group by email and keep the groups with more than one row:
SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1;
Any result is a duplicate worth investigating. Also check whether the comparison ignores upper and lower case, because Ana@example.com and ana@example.com are usually the same address.
