SQL for testers
The interface says a refund succeeded, but the balance is unchanged. The API response looks correct and the logs show no obvious error. A tester who can inspect the relevant records gains a different kind of evidence: what state the system stored.
SQL for testers is not a shortcut to becoming a database administrator. It is a safe way to ask focused questions about the data behind a user journey and compare the answer with what the product displayed.
SQL for testers begins with the shape of the data
Before writing a query, identify the entities, keys and relationship that represent the behaviour under test. For a refund, that might include the order, payment and balance records. The names and schema depend on the system, so do not guess a table from the screen label.
Write the question in plain language first. Then choose the smallest set of columns that can answer it.
Start with safe SELECT queries
Use a read-only connection where possible and begin with a filtered SELECT. Limit the rows, order them deliberately and avoid returning sensitive columns that are not needed. A query should be safe to run again and understandable to the next reviewer.
SELECT id, status, amount
FROM payments
WHERE order_id = :order_id
ORDER BY created_at DESC;The example is a shape, not a promise about a particular schema. Replace names and parameters with the system’s documented model.
Use joins to reconstruct the user journey
A single table rarely explains a cross-service result. Join only the relationships you need, keep the join condition visible and compare the returned values with the request and screen. An unexpected duplicate row can be more informative than a missing status.
When the relationship is optional, decide whether an inner or left join answers the question. The join type is part of the evidence.
Check absence, NULL and boundaries explicitly
- Ask what an absent record should mean.
- Distinguish NULL from an empty string or zero.
- Check the first and last relevant timestamp or amount.
- Compare before and after state without changing either.
Many false conclusions come from treating “no row” as the same as “row with an empty value”. Make the distinction visible in the query and the note.
Save reproducible evidence without copying a database dump
Record the question, safe query shape, parameters with sensitive values masked, timestamp and relevant result. Link it to the request or defect. Preserve enough context for another person to repeat the check, but not more data than the decision requires.
The database is not a second user interface. It is an evidence layer that can confirm, challenge or narrow what the visible journey suggests.
Practice the idea on one real example
Choose one user action with a visible state change. Write the plain-language question, identify the records that should represent it and select only the columns needed for the comparison.
Run a filtered read-only query before and after the action. Check a missing record, a NULL value or a boundary condition that could produce a misleading conclusion.
Save the query shape, masked parameters, time and conclusion. State what the database evidence still cannot tell you about the user experience.
Leave with: a safe SQL practice note with one question, one read-only query and the state it confirms.
Before you move on
- Which records represent the behaviour?
- What is the smallest safe query?
- How is absence different from NULL or empty data?
- What can the evidence prove and what can it not prove?
Continue with API checks, bug reports and portfolio projects in the software-testing library.
Open software-testing guides →