← QA Library
Chapter 12 / 19 · 8 min read

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

JoinReturnsTypical use
INNER JOINOnly rows with a match in both tablesOrders with their customers
LEFT JOINAll rows from the left table, matches or NULLCustomers, including those with no orders
RIGHT JOINAll rows from the right tableRarely used; swap tables and use LEFT
FULL JOINAll rows from both sidesComparing 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.

Never run an UPDATE or DELETE without a WHERE clause, and never practise on production data.