Databases and SQL for testers
Why testers need SQL, the queries you'll use every week, and how to verify data, integrity and migrations.
The interface shows only part of the truth. The database shows what the system actually saved. A tester who can write a few SQL queries checks results faster, prepares test data in seconds and finds bugs that never surface in the UI.
Relational databases in one minute
Data lives in tables made of rows and columns. Each row has a primary key that identifies it. Foreign keys link tables: an order row stores the customer_id of the customer who placed it. Popular databases are PostgreSQL, MySQL, Microsoft SQL Server and SQLite; the basic SQL is almost the same everywhere.
Queries you'll use every week
-- Find a specific order
SELECT id, status, total, created_at
FROM orders
WHERE id = 1042;
-- Latest orders of a test user
SELECT o.id, o.status, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.email = '[email protected]'
ORDER BY o.created_at DESC
LIMIT 5;
-- How many orders per status today
SELECT status, COUNT(*) AS cnt
FROM orders
WHERE created_at >= CURRENT_DATE
GROUP BY status;
What to check in the database
- The UI saved what it said it saved: values, rounding, time zone, trimmed spaces
- Statuses and history: an order moved through the right states, timestamps are set
- No duplicates: double-clicking “Pay” didn't create two payments
- Integrity: no orphans, such as order items pointing to deleted orders
- Deletion and privacy: “delete account” really removes or anonymises personal data
- Calculated fields: totals in the database match the sum of the items
-- Duplicate payments for the same order
SELECT order_id, COUNT(*)
FROM payments
GROUP BY order_id
HAVING COUNT(*) > 1;
-- Order items whose order no longer exists
SELECT i.id
FROM order_items i
LEFT JOIN orders o ON o.id = i.order_id
WHERE o.id IS NULL;
JOINs without confusion
| Join | Returns | Typical use |
|---|---|---|
| INNER JOIN | Only rows with a match in both tables | Orders with their customers |
| LEFT JOIN | All rows from the left table, matches or NULL | Customers, including those with no orders |
| RIGHT JOIN | All rows from the right table | Rarely used; swap tables and use LEFT |
| FULL JOIN | All rows from both sides | Comparing two data sets |
Test data and safety
Use INSERT and UPDATE to set up states that are slow to reach through the UI — an expired subscription, a user with 1,000 orders. Do it only in test databases, always with a WHERE clause, and preferably inside a transaction you can roll back.
BEGIN;
UPDATE subscriptions SET expires_at = NOW() - INTERVAL '1 day'
WHERE user_id = 77;
-- check the app, then undo
ROLLBACK;
Migrations
When the database structure changes, test the migration itself: run it on a copy of realistic data, check that nothing was lost or corrupted, that the application works with both old and new records, and that the migration can be rolled back. Measure how long it takes — a migration that locks a big table for 20 minutes is a production outage.
NoSQL in brief
Document databases such as MongoDB store JSON-like documents instead of rows; key-value stores such as Redis are often used for caches and sessions. The checks are the same in spirit — saved values, duplicates, consistency, expiry — only the query language differs.


