SQL for Testers: Four Checks That Find the Data Bugs a Screen Hides

Introduction

Some bugs never reach a screen. An order item that belongs to no order doesn't appear on any page. Two customers who share an email address only cause trouble when one of them resets a password. An order total that doesn't match its items looks fine until someone adds them up.

A tester who can query the database can find these directly. This guide uses a small shop database with seeded data bugs and four rules it must follow. For each rule you'll write a query that returns the rows breaking it, see a common first attempt, and see what it misses.

Everything here ran in SQLite, through Python's built-in sqlite3 module, so there's nothing to install beyond Python. The same queries also run in the SQL lab, in your browser.

The idea: a check returns the rows that break a rule

A screen test asks "does this page show the right thing?" A data check asks "which rows break this rule?" Write it so that:

• no rows means the rule holds, and
• every row it returns is a bug report, with the id you need to find it.

That shape matters. A query that counts problems (SELECT count(*) …) tells you something is wrong, but not what. A query that returns nothing on a correct database and exactly the bad rows on a broken one can go straight into a test suite, which is where this guide ends.

Set up: the shop database and a runner

Download the practice shop's data from the SQL lab: shop.sql. It creates five tables (customers, products, orders, order_items, and payments) and fills them with a few dozen rows, some of which break the rules below.

Save this runner next to it as run_check.py. It loads the script into an in-memory database, runs the query in the file you name, and prints each row:

import sqlite3
import sys
 
db = sqlite3.connect(":memory:")
with open("shop.sql", encoding="utf-8") as script:
    db.executescript(script.read())
 
with open(sys.argv[1], encoding="utf-8") as check:
    cursor = db.execute(check.read())
 
print([column[0] for column in cursor.description])
rows = cursor.fetchall()
for row in rows:
    print(row)
print(len(rows), "row" if len(rows) == 1 else "rows")

Python prints None for a SQL NULL and puts quotes around text, so a blank value shows up as ' ' instead of disappearing. Save each query below in its own .sql file and run it with python run_check.py <file>.

Rule 1: every customer has an email address

"Has an email" means not missing and not blank. A common first attempt:

SELECT id, name, email
FROM customers
WHERE email = NULL OR email = '';

['id', 'name', 'email']
0 rows

No rows, so the rule holds? It doesn't. In SQL, NULL means "unknown", and comparing anything with unknown gives unknown, which a WHERE clause treats as false. email = NULL is never true, even when the email is NULL. SQLite's expression documentation states the rule: operators evaluate to NULL when an operand is NULL, and IS and IS NOT are the exceptions that compare NULL as a value.

Use IS NULL, and trim() so that an address of spaces counts as blank:

SELECT id, name, email
FROM customers
WHERE email IS NULL OR trim(email) = '';

['id', 'name', 'email']
(7, 'Ida Rhodes', None)
(8, 'Joan Clarke', '   ')
2 rows

Two customers: one with no email at all, and one whose email is three spaces. email = '' would have missed the second one too, because three spaces aren't an empty string.

Rule 2: no two customers share an email address

The rule says "ignoring upper and lower case", because [email protected] and [email protected] reach the same inbox. A first attempt:

SELECT email, count(*) AS customers
FROM customers
GROUP BY email
HAVING count(*) > 1;

['email', 'customers']
('[email protected]', 2)
1 row

It found one duplicate. GROUP BY email compares the text exactly, so the two Graces land in different groups and look unique. Group by lower(email) instead, and go back for the customers in each group, because a report needs to say which accounts clash:

SELECT id, name, email
FROM customers
WHERE lower(email) IN (
  SELECT lower(email)
  FROM customers
  GROUP BY lower(email)
  HAVING count(*) > 1
)
ORDER BY lower(email), id;

['id', 'name', 'email']
(3, 'Alan Turing', '[email protected]')
(10, 'A. Turing', '[email protected]')
(2, 'Grace Hopper', '[email protected]')
(9, 'Grace M. Hopper', '[email protected]')
4 rows

Four customers, two addresses. SQLite's lower() only folds ASCII letters by default (core functions), which is enough for these addresses; real data with accented characters needs more care.

Rule 3: every order item belongs to an order

The tables don't declare foreign keys, and SQLite doesn't enforce them unless they are declared and switched on (foreign key support). So an item can point at an order that doesn't exist. A first attempt joins the two tables and looks for the missing order:

SELECT i.id, i.order_id
FROM order_items i
JOIN orders o ON o.id = i.order_id
WHERE o.id IS NULL;

['id', 'order_id']
0 rows

This one can never return a row. An inner join keeps only the items that have a matching order, so the rows you're looking for are gone before the WHERE clause runs. A LEFT JOIN keeps every item and fills the order's columns with NULL where there's no match:

SELECT i.id, i.order_id
FROM order_items i
LEFT JOIN orders o ON o.id = i.order_id
WHERE o.id IS NULL;

['id', 'order_id']
(1010, 199)
(1011, 250)
2 rows

Two items point at orders 199 and 250, which don't exist.

Rule 4: an order's total matches its items

The full rule: an order's total is the sum of quantity × unit price over its items, and a cancelled order's total is 0, whatever items it still lists. A first attempt:

SELECT o.id, o.status, o.total_cents, sum(i.quantity * i.unit_price_cents) AS items_cents
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id
HAVING o.total_cents <> sum(i.quantity * i.unit_price_cents);

['id', 'status', 'total_cents', 'items_cents']
(104, 'cancelled', 0, 3998)
(109, 'paid', 5998, 6298)
2 rows

Two rows, and half of them are wrong. Order 104 is a cancelled order with a total of 0, which is exactly what the rule asks for: that row is a false alarm. And the query misses an order it can't see. The inner join drops any order with no items, so an order that charges $24.99 for nothing never reaches the comparison.

Keep every order with a LEFT JOIN, treat "no items" as 0 with coalesce(), and apply the cancelled rule:

SELECT o.id, o.status, o.total_cents,
       coalesce(sum(i.quantity * i.unit_price_cents), 0) AS items_cents
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY o.id
HAVING o.total_cents <> CASE
  WHEN o.status = 'cancelled' THEN 0
  ELSE coalesce(sum(i.quantity * i.unit_price_cents), 0)
END;

['id', 'status', 'total_cents', 'items_cents']
(109, 'paid', 5998, 6298)
(110, 'shipped', 2499, 0)
2 rows

Order 109 charged $59.98 for a $62.98 mug and hoodie. Order 110 charged $24.99 and has no items. The cancelled order is gone from the list, because it follows the rule.

Money is stored in whole cents here, so the comparison is exact. If your database stores prices as decimals in floating-point columns, compare rounded values instead of testing for exact equality.

Put the checks in your test suite

Each check already has the shape of a test: it should return no rows. With pytest, every rule becomes one test case. Save this as test_data_rules.py next to shop.sql:

import sqlite3
 
import pytest
 
# Each check returns the rows that break one rule, so a healthy database returns none.
CHECKS = {
    "every customer has an email": """
        SELECT id FROM customers
        WHERE email IS NULL OR trim(email) = ''
    """,
    "every order item has an order": """
        SELECT i.id FROM order_items i
        LEFT JOIN orders o ON o.id = i.order_id
        WHERE o.id IS NULL
    """,
}
 
 
@pytest.fixture(scope="module")
def db():
    connection = sqlite3.connect(":memory:")
    with open("shop.sql", encoding="utf-8") as script:
        connection.executescript(script.read())
    yield connection
    connection.close()
 
 
@pytest.mark.parametrize("rule", CHECKS)
def test_no_row_breaks_the_rule(db, rule):
    broken = [row[0] for row in db.execute(CHECKS[rule])]
    assert broken == [], f"{rule}: {len(broken)} rows break it, ids {broken}"

Run it with python -m pytest test_data_rules.py -q. On the practice data both tests fail, and the message names the rule and the rows:

E       AssertionError: every order item has an order: 2 rows break it, ids [1010, 1011]
E       assert [1010, 1011] == []
...
FAILED test_data_rules.py::test_no_row_breaks_the_rule[every customer has an email]
FAILED test_data_rules.py::test_no_row_breaks_the_rule[every order item has an order]
2 failed in 0.15s

Against your own system, point the fixture at a test database your pipeline has just filled, never at production. For where that data should come from, see test data management.

Exercise: two more rules

The SQL lab has all six of the shop's rules, including two this guide doesn't solve:

• every paid or shipped order has a payment, and
• no order is placed before its customer registered.

Each one has a trap of the same kind as the four above. The lab runs your check on a clean copy of the database (any row there is a false alarm), on the data you can download, and on a second copy with different rows and ids, so a check that names specific ids won't pass. It shows the answer and the common wrong version only when you ask.

What these checks don't tell you

A check that returns no rows proves only that none of the rows it looks for are there. It says nothing about rules nobody wrote down, and a check with a mistake in it returns no rows too, as the first query in each section shows. That is why every check here was run against data where we knew which rows were bad before trusting it.

The answers are written for SQLite. Other databases differ in functions and in how they compare text, so check a query against your database's own documentation before relying on it there.

Conclusion

Write each data rule as a query that returns the rows breaking it, and test the query before you trust it: on data where you know the answer, a check that returns nothing is as suspicious as a test that never fails. IS NULL instead of = NULL, lower() when case doesn't matter, and a LEFT JOIN when you're looking for something missing will get you past most first attempts.

Sources and further reading

• SQLite: SQL language expressions, including IS and IS NOT
• SQLite: NULL handling in SQLite versus other database engines
• SQLite: core functions (trim, lower, coalesce)
• SQLite: foreign key support
• SQLite: SELECT, including joins and GROUP BY
• Python documentation: sqlite3
• pytest documentation: parametrizing tests