Testing SQL

Application code gets unit tests as a matter of course. SQL often gets none: a query is run once by eye, the number looks right, and it ships. Six months later someone changes a view, a report quietly shifts by four percent, and nobody finds out for a quarter.

SQL is code, and it can be tested. The ideas are the same as anywhere else. What changes is the setup, because the thing under test lives in a database with state.

What is worth testing#

Not every query needs a test. These are the ones that pay off.

• Constraints and keys. That the rules you rely on are actually enforced: negative prices rejected, duplicate emails rejected, orphans impossible.
• Reports and business rules. Revenue excludes cancelled orders. A customer's tier is computed correctly at the boundaries.
• Functions, procedures and triggers. They contain logic, so they can be wrong.
• Migrations. That they apply cleanly from scratch and on top of existing data.
• Performance guards. That a critical query still uses its index.

Skip tests for trivial SELECTs. Test where a mistake is expensive or easy to make.

Assertion queries#

The lightest tool needs no framework. Write a query that returns rows only when something is wrong. An empty result means the check passed.

-- Every order must have at least one line
SELECT o.order_id
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id
WHERE oi.order_id IS NULL;

-- No product may have negative stock
SELECT product_id, stock_qty FROM products WHERE stock_qty < 0;

-- No line may have a zero or negative price or quantity
SELECT order_id, product_id FROM order_items WHERE unit_price <= 0 OR quantity <= 0;

On the course data all three return zero rows. Run a folder of such checks after every load or deploy, and treat any returned row as a failure. This is the idea behind data quality tests in tools like dbt (not_null, unique, relationships, accepted_values), and it works equally well in a plain script.

Because the query shows the offending rows, a failure arrives with its own evidence.

pgTAP#

pgTAP is a PostgreSQL extension that gives you test functions with proper pass/fail reporting, written in SQL.

CREATE EXTENSION IF NOT EXISTS pgtap;

A test file:

BEGIN;
SELECT plan(5);

SELECT has_table('customers');
SELECT has_column('customers', 'email');
SELECT col_is_unique('customers', 'email');

SELECT is(
    (SELECT COUNT(*)::int FROM customers),
    10,
    'seed data has ten customers'
);

SELECT throws_ok(
    $$INSERT INTO products (name, category, unit_price) VALUES ('Bad', 'Test', -1)$$,
    '23514',
    NULL,
    'negative price is rejected'
);

SELECT * FROM finish();
ROLLBACK;

How it reads:

• plan(5) declares how many assertions to expect. If fewer or more run, the file fails. That catches tests that exit early.
• Each assertion function (has_table, is, throws_ok) returns one TAP line, ok or not ok.
• throws_ok runs a statement and checks for an error with the given SQLSTATE. 23514 is check_violation.
• Everything sits inside BEGIN ... ROLLBACK, so the test leaves no trace.

Run a directory of them with the pg_prove command:

pg_prove -d shop tests/*.sql

Other useful assertions: results_eq(sql, sql) compares two result sets, is_empty(sql) asserts no rows, function_returns(...), has_index(...), fk_ok(...).

Testing a business rule with real data:

BEGIN;
SELECT plan(2);

SELECT is(order_total(9), 607.50::numeric, 'order 9 totals 607.50');

CALL cancel_order(12);
SELECT is(
    (SELECT stock_qty FROM products WHERE product_id = 9),
    9,
    'cancelling order 12 returns the chair to stock'
);

SELECT * FROM finish();
ROLLBACK;

The chair's stock starts at 8 and order 12 holds one, so it comes back to 9. Because the whole test is rolled back, it can be re-run forever against the same data.

Testing from application code with pytest#

If your team already tests in Python, run SQL tests there. The central trick is a fixture that gives each test its own transaction and rolls it back afterward.

import pytest
import psycopg
from decimal import Decimal

TEST_DSN = "postgresql://postgres:postgres@localhost:5432/shop_test"

@pytest.fixture
def conn():
    with psycopg.connect(TEST_DSN) as c:
        yield c
        c.rollback()           # undo whatever the test did

def test_negative_price_rejected(conn):
    with pytest.raises(psycopg.errors.CheckViolation):
        conn.execute(
            "INSERT INTO products (name, category, unit_price) VALUES ('Bad', 'Test', -1)"
        )

def test_order_total(conn):
    total = conn.execute("SELECT order_total(9)").fetchone()[0]
    assert total == Decimal("607.50")

def test_cancelled_orders_excluded_from_revenue(conn):
    revenue = conn.execute("""
        SELECT SUM(total) FROM order_totals WHERE status <> 'cancelled'
    """).fetchone()[0]
    assert revenue == Decimal("3139.50")

Each test starts from the same known state, so tests do not interfere with each other, and they run in milliseconds.

The limit of rollback isolation: code that commits inside the test (a procedure that calls COMMIT, or a second connection) escapes it. For those, give each test run a fresh database instead.

Test data#

Tests need data they can rely on. Three approaches, from simplest.

A fixed seed script. Like the course's seed.sql. Every test can say "customer 10 has no orders" and be right. Keep it small and stable, and change it deliberately.

Fixtures per test. Each test inserts exactly the rows it needs. Clearer, and the test documents its own scenario. Slower to write.

Template databases. Build a fully seeded database once, then clone it in a moment for each run:

CREATE DATABASE shop_test TEMPLATE shop_seeded;

Dropping and re-cloning is far faster than reloading the data, which makes disposable databases practical. Tools like Testcontainers go further and start a throwaway PostgreSQL container for the test run, so the tests carry their own database and never touch a shared one.

Do not test against production data. It is not stable, it leaks private information, and you do not want a test run near it.

Testing migrations#

A migration is code that runs once, in production, with no do-overs. Test it harder than most.

1. Apply from scratch. Run every migration, in order, against an empty database. It must succeed.
2. Apply on top of realistic data. Restore a recent, anonymized copy, run the pending migrations and check the result. This is where timing and constraint violations appear.
3. Compare the result to the expected schema. Dump the schema (pg_dump -s) after migrating and diff it against a checked-in snapshot, so unintended drift fails the build.
4. Test the application against both the old and new schema if you use expand and contract, since both will run side by side during deploy.

Guarding performance#

A query that is fast today can become slow after someone drops an index or changes a condition. For the few queries that must stay fast, assert on the plan.

def test_customer_lookup_uses_index(conn):
    plan = conn.execute(
        "EXPLAIN (FORMAT JSON) SELECT * FROM big_orders WHERE customer_id = 4242"
    ).fetchone()[0]
    assert "Seq Scan" not in str(plan)

Use this sparingly and on realistically sized test data. On a tiny table the planner correctly prefers a sequential scan, so the test would fail for the wrong reason.

What makes SQL tests good#

• Deterministic. Same data in, same answer out. Avoid NOW(), random() and unordered results in assertions. Add an ORDER BY, or compare as sets.
• Isolated. Each test leaves nothing behind.
• Fast. Rollback or template cloning, not full reloads.
• Specific. One behavior per test, with a name that says which one.
• Run in CI. Against a disposable database, on every change to a migration or a query file.

Common mistakes#

Testing against a shared database. Tests interfere, and failures depend on who ran what.

Asserting on unordered results. The test passes today and fails after a vacuum.

Tests that depend on the current date. Fix the date, or pass it as a parameter.

Leaving test data committed. The next run starts from a dirty state.

Only testing the happy path. Cover the rejects: the constraint that should fire, the cancel that should fail.

Never testing migrations on real-sized data. Constraint violations and lock times only show up there.

Practice#

1. Write five assertion queries for the course schema (each returning rows only on failure) and verify they all return nothing. Then break the data on purpose and watch one fail.
2. Install pgTAP and write a test file that asserts order_items has a composite primary key, customers.email is unique, and inserting a duplicate email raises error 23505.
3. Write a pytest fixture that rolls back after each test, then add a test for cancel_order that proves cancelling a delivered order raises an error.
4. Create a seeded template database and use it to make a fresh shop_test in one command. Time it against reloading seed.sql.
5. Write a test that fails if order_totals stops excluding the cancelled order from a revenue sum, and explain which earlier mistake in the course it protects against.