QA Wiki

Appearance

SQL for QA ​

The UI and the API tell you what the system claims to have done; the database tells you what it has actually done. A QA who can query the database can verify persistence, prepare test data and diagnose bugs without depending on anyone.

The 20 % of SQL that solves 80 % of the job ​

You don't need to be a DBA. This covers almost all the day-to-day:

I need to…SQL pattern
Verify a record was createdSELECT with WHERE
Verify a state changeSELECT status FROM … WHERE id = …
Count how many there areCOUNT(*) with GROUP BY
Detect duplicatesGROUP BY … HAVING COUNT(*) > 1
Detect orphans (integrity)LEFT JOIN … WHERE child.id IS NULL
Find data for a testSELECT … WHERE <criteria> LIMIT 5

Examples over a minimal schema ​

With two example tables, customers and service_orders (a customer has many service orders, and each order moves through created → validated → provisioning → active):

sql
-- Was the customer created when they signed up for the 1 Gbps fibre plan?
SELECT id, document, status, created_at
FROM customers
WHERE document = '12345678A';

-- Did the service order reach 'active' after the full provisioning flow?
SELECT status FROM service_orders WHERE id = 10442;

-- Duplicate customers that validation should have prevented
SELECT document, COUNT(*)
FROM customers
GROUP BY document
HAVING COUNT(*) > 1;

-- Orphan service orders: they point to a customer that no longer exists
SELECT o.id
FROM service_orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

-- Test data: active customers with orders still in provisioning
SELECT c.id, c.document
FROM customers c
JOIN service_orders o ON o.customer_id = c.id
WHERE c.status = 'active' AND o.status = 'provisioning'
LIMIT 5;

The NULL trap ​

NULL equals nothing, not even another NULL. Two consequences that produce false greens:

  • WHERE column = NULL never returns anything: you write WHERE column IS NULL.
  • COUNT(column) ignores NULLs; COUNT(*) counts every row. If you validate totals, choose deliberately.

Safety rules in real environments ​

  • Read-only access whenever possible. An UPDATE without a WHERE in a shared environment is a classic horror story for a reason.
  • LIMIT by default on big tables: a heavy query on a shared environment can degrade performance for the whole team.
  • Careful with personal data: the result of a query with real emails or names doesn't get pasted into a ticket or a chat. Anonymize, or reference by id.

How it fits automation ​

In API tests, the most complete assertion validates both sides: the response (contract, status, body) and the persistence (what ended up in the database). It's also the setup-and-teardown tool: locating data that meets the test's conditions, and verifying the initial state before executing.

Common mistakes ​

  • Validating only the response. A 200 with a correct body doesn't guarantee it was persisted correctly (or that it wasn't persisted twice).
  • Tests that depend on live data. If the test's query assumes "customer 42 exists and has 3 service orders", the test breaks the moment someone touches that data. Create or locate your data, don't assume it.
  • SELECT * on huge tables, out of habit. Ask for the columns you need.
  • Copying queries without understanding the JOIN. An INNER JOIN where a LEFT JOIN was needed hides exactly the rows you were looking for (the ones without a match).

Key idea

The API tells you what the system claims; the database, what it actually did. When the two versions don't match, there's a bug — and a good one.

References ​