SQL Testing Strategies: A Complete Guide for Database Developers

12 Oct 2026 — 7 min read

SQL is often the least-tested part of an application stack. Business logic lives in stored procedures nobody touches, queries run against production data, and "it works in dev" becomes the only verification. Then a constraint violation or a missing index bites you in production at 2 AM.

This guide covers practical SQL testing strategies that work across PostgreSQL, MySQL, SQL Server, and SQLite.

What SQL Testing Actually Covers

SQL testing is broader than most developers realize. It includes:

Query correctness: Does this SELECT return the right rows?
Constraint validation: Do UNIQUE, NOT NULL, and FK constraints reject invalid data?
Stored procedure logic: Do procedures produce correct output for all input cases?
Index effectiveness: Do queries use the expected indexes?
Transaction behavior: Do concurrent transactions produce correct results?
Schema integrity: Are tables, columns, and relationships defined correctly?

Each of these requires different testing approaches.

Unit Testing SQL Queries

The foundation of SQL testing is testing queries in isolation against controlled data.

The Test Data Problem

SQL queries are hard to test because they depend on database state. The solution is to control that state explicitly:

-- Setup: insert only the data this test needs
BEGIN;

INSERT INTO customers (id, name, region, created_at) VALUES
  (1, 'Acme Corp', 'US', '2024-01-01'),
  (2, 'Beta Ltd', 'EU', '2024-01-15'),
  (3, 'Gamma Inc', 'US', '2024-02-01');

INSERT INTO orders (id, customer_id, total, status) VALUES
  (101, 1, 500.00, 'completed'),
  (102, 1, 250.00, 'completed'),
  (103, 2, 750.00, 'pending'),
  (104, 3, 100.00, 'completed');

-- The query under test
SELECT c.region, SUM(o.total) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.region
ORDER BY revenue DESC;

-- Expected: US=850, EU=0 (pending doesn't count)

ROLLBACK; -- Clean up after test

Wrapping tests in transactions and rolling back keeps the database clean without truncating tables between every test.

pgTAP for PostgreSQL

pgTAP is a TAP-compliant testing framework that runs directly inside PostgreSQL:

-- Install pgTAP extension
CREATE EXTENSION IF NOT EXISTS pgtap;

BEGIN;

SELECT plan(4);

-- Test: query returns correct revenue by region
SELECT results_eq(
  $$ SELECT region, revenue::numeric FROM get_revenue_by_region('completed') ORDER BY region $$,
  $$ VALUES ('EU'::text, 0::numeric), ('US'::text, 850::numeric) $$,
  'Revenue by region sums completed orders only'
);

-- Test: function rejects invalid status
SELECT throws_ok(
  $$ SELECT * FROM get_revenue_by_region('invalid_status') $$,
  'P0001',
  'Invalid order status',
  'Function raises error for unknown status'
);

-- Test: NULL handling
SELECT is(
  (SELECT revenue FROM get_revenue_by_region('completed') WHERE region = 'APAC'),
  NULL,
  'Returns NULL for regions with no orders'
);

-- Test: empty result for no matching orders
SELECT is_empty(
  $$ SELECT * FROM get_revenue_by_region('completed') WHERE revenue < 0 $$,
  'No negative revenue values'
);

SELECT finish();
ROLLBACK;

Run pgTAP tests with pg_prove:

pg_prove -d mydb tests/sql/*.sql

MySQL Unit Testing with MyTAP

MySQL has MyTAP, a similar framework:

-- Test a stored procedure
CALL tap.plan(3);

CALL setup_test_data();

-- Test correct count
CALL tap.eq(
  (SELECT COUNT(*) FROM get_active_users()),
  5,
  'Active user count is correct'
);

-- Test specific user is included
CALL tap.ok(
  EXISTS(SELECT 1 FROM get_active_users() WHERE email = 'test@example.com'),
  'Known active user appears in results'
);

CALL tap.finish();

Testing Stored Procedures and Functions

Stored procedures deserve the same rigor as application code: happy paths, edge cases, and error handling.

Structure Tests Around Behavior

Don't test implementation details — test what the procedure does:

-- Bad: tests internal behavior (fragile)
-- Good: tests observable outcome
CREATE OR REPLACE FUNCTION test_calculate_discount()
RETURNS void AS $$
DECLARE
  v_result RECORD;
BEGIN
  -- Happy path: standard customer gets 10% on orders > $100
  SELECT * INTO v_result FROM calculate_discount(
    p_customer_id => 1,
    p_order_total => 150.00,
    p_customer_tier => 'standard'
  );
  ASSERT v_result.discount_amount = 15.00,
    'Standard customer: 10% on $150 = $15';
  ASSERT v_result.discount_pct = 0.10,
    'Standard customer discount rate is 10%';

  -- Edge case: order just below threshold
  SELECT * INTO v_result FROM calculate_discount(
    p_customer_id => 1,
    p_order_total => 99.99,
    p_customer_tier => 'standard'
  );
  ASSERT v_result.discount_amount = 0.00,
    'No discount below $100 threshold';

  -- Edge case: VIP customer
  SELECT * INTO v_result FROM calculate_discount(
    p_customer_id => 1,
    p_order_total => 50.00,
    p_customer_tier => 'vip'
  );
  ASSERT v_result.discount_amount = 10.00,
    'VIP customers get 20% regardless of amount';

  RAISE NOTICE 'All discount tests passed';
END;
$$ LANGUAGE plpgsql;

Test Error Handling Explicitly

-- Test that procedure raises the right exception
DO $$
BEGIN
  BEGIN
    PERFORM calculate_discount(
      p_customer_id => -1,
      p_order_total => 100.00,
      p_customer_tier => 'standard'
    );
    RAISE EXCEPTION 'Expected exception was not raised';
  EXCEPTION WHEN SQLSTATE 'P0001' THEN
    RAISE NOTICE 'Correct: invalid customer ID rejected';
  END;
END;
$$;

Testing Constraints

Database constraints are your last line of defense against bad data. Test that they actually work.

NOT NULL Constraints

-- Test all NOT NULL constraints on a critical table
DO $$
DECLARE
  constraint_count INTEGER;
BEGIN
  -- Verify constraint exists
  SELECT COUNT(*) INTO constraint_count
  FROM information_schema.columns
  WHERE table_name = 'orders'
    AND column_name = 'customer_id'
    AND is_nullable = 'NO';
  
  ASSERT constraint_count = 1,
    'orders.customer_id must be NOT NULL';

  -- Test it actually rejects NULLs
  BEGIN
    INSERT INTO orders (customer_id, total, status)
    VALUES (NULL, 100.00, 'pending');
    RAISE EXCEPTION 'Expected constraint violation';
  EXCEPTION WHEN not_null_violation THEN
    RAISE NOTICE 'NOT NULL constraint works correctly';
  END;
END;
$$;

Foreign Key Constraints

-- Test FK constraint prevents orphaned records
DO $$
BEGIN
  BEGIN
    INSERT INTO orders (customer_id, total, status)
    VALUES (99999, 100.00, 'pending');  -- nonexistent customer
    RAISE EXCEPTION 'Expected FK violation';
  EXCEPTION WHEN foreign_key_violation THEN
    RAISE NOTICE 'FK constraint prevents orphaned orders';
  END;

  -- Test cascade delete behavior
  INSERT INTO customers (id, name) VALUES (9001, 'Test Customer');
  INSERT INTO orders (customer_id, total, status) VALUES (9001, 50.00, 'pending');
  
  DELETE FROM customers WHERE id = 9001;
  
  ASSERT NOT EXISTS (SELECT 1 FROM orders WHERE customer_id = 9001),
    'Cascade delete removes child orders';
END;
$$;

CHECK Constraints

DO $$
BEGIN
  -- Test valid values pass
  INSERT INTO orders (customer_id, total, status)
  VALUES (1, 100.00, 'pending');
  RAISE NOTICE 'Valid status accepted';

  -- Test invalid values rejected
  BEGIN
    INSERT INTO orders (customer_id, total, status)
    VALUES (1, 100.00, 'not_a_real_status');
    RAISE EXCEPTION 'Expected check constraint violation';
  EXCEPTION WHEN check_violation THEN
    RAISE NOTICE 'CHECK constraint rejects invalid status';
  END;

  -- Test negative total rejected
  BEGIN
    INSERT INTO orders (customer_id, total, status)
    VALUES (1, -1.00, 'pending');
    RAISE EXCEPTION 'Expected check constraint violation';
  EXCEPTION WHEN check_violation THEN
    RAISE NOTICE 'CHECK constraint rejects negative totals';
  END;
END;
$$;

Testing Query Performance

Correctness isn't enough — queries must also perform at scale. Test query plans, not just results.

Assert Index Usage with EXPLAIN

import psycopg2
import json

def test_order_lookup_uses_index(db_conn):
    """Verify customer order lookup uses index, not seq scan."""
    with db_conn.cursor() as cur:
        cur.execute("""
            EXPLAIN (FORMAT JSON, ANALYZE false)
            SELECT * FROM orders
            WHERE customer_id = 1
            ORDER BY created_at DESC
            LIMIT 10
        """)
        plan = cur.fetchone()[0][0]['Plan']
    
    # Find the scan node
    def find_scan(node):
        if node.get('Node Type') in ('Index Scan', 'Index Only Scan', 'Bitmap Index Scan'):
            return node
        for child in node.get('Plans', []):
            result = find_scan(child)
            if result:
                return result
        return None
    
    scan_node = find_scan(plan)
    assert scan_node is not None, \
        f"Expected index scan, got: {plan['Node Type']}"
    assert 'customer_id' in scan_node.get('Index Name', ''), \
        f"Expected customer_id index, got: {scan_node.get('Index Name')}"

Benchmark Queries Against Data Volume

import time
import psycopg2

def test_revenue_report_performance(db_conn, large_dataset):
    """Revenue report must complete in under 2 seconds on 1M orders."""
    with db_conn.cursor() as cur:
        start = time.monotonic()
        cur.execute("""
            SELECT 
                DATE_TRUNC('month', created_at) AS month,
                region,
                SUM(total) AS revenue,
                COUNT(*) AS order_count
            FROM orders o
            JOIN customers c ON c.id = o.customer_id
            WHERE o.created_at >= NOW() - INTERVAL '12 months'
            GROUP BY 1, 2
            ORDER BY 1, 2
        """)
        results = cur.fetchall()
        elapsed = time.monotonic() - start
    
    assert len(results) > 0, "Report returned no data"
    assert elapsed < 2.0, \
        f"Revenue report too slow: {elapsed:.2f}s (limit: 2.0s)"

Integration Testing with Test Databases

For integration tests, spin up a real database rather than mocking:

Using Testcontainers (Python)

import pytest
from testcontainers.postgres import PostgresContainer
import psycopg2

@pytest.fixture(scope="session")
def postgres():
    with PostgresContainer("postgres:16-alpine") as pg:
        yield pg

@pytest.fixture(scope="session")
def db_conn(postgres):
    conn = psycopg2.connect(postgres.get_connection_url())
    # Run migrations
    with conn.cursor() as cur:
        with open("schema.sql") as f:
            cur.execute(f.read())
    conn.commit()
    yield conn
    conn.close()

@pytest.fixture(autouse=True)
def transaction(db_conn):
    """Wrap each test in a transaction, roll back after."""
    db_conn.autocommit = False
    yield
    db_conn.rollback()

def test_create_order(db_conn):
    with db_conn.cursor() as cur:
        cur.execute(
            "INSERT INTO orders (customer_id, total, status) VALUES (%s, %s, %s) RETURNING id",
            (1, 150.00, 'pending')
        )
        order_id = cur.fetchone()[0]
    
    assert order_id > 0

    with db_conn.cursor() as cur:
        cur.execute("SELECT total, status FROM orders WHERE id = %s", (order_id,))
        row = cur.fetchone()
    
    assert row[0] == 150.00
    assert row[1] == 'pending'

CI/CD Integration

SQL tests should run on every pull request:

# .github/workflows/sql-tests.yml
name: SQL Tests

on: [push, pull_request]

jobs:
  sql-tests:
    runs-on: ubuntu-latest
    
    services:
      postgres:
        image: postgres:16-alpine
        env:
          POSTGRES_DB: testdb
          POSTGRES_USER: test
          POSTGRES_PASSWORD: test
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    
    steps:
      - uses: actions/checkout@v4
      
      - name: Apply schema
        run: |
          PGPASSWORD=test psql -h localhost -U test -d testdb -f schema.sql
      
      - name: Run pgTAP tests
        run: |
          PGPASSWORD=test pg_prove -h localhost -U test -d testdb tests/sql/*.sql
      
      - name: Run Python integration tests
        run: |
          pip install pytest psycopg2-binary testcontainers
          pytest tests/integration/ -v
        env:
          DATABASE_URL: postgresql://test:test@localhost/testdb

What to Test vs What to Skip

Test these:

Complex queries with JOINs, aggregations, or window functions
Stored procedures and functions with business logic
All constraint types (NOT NULL, FK, UNIQUE, CHECK)
Index usage for performance-critical queries
Trigger behavior
Concurrent access patterns for high-traffic tables

Skip these:

Simple single-table SELECTs with no logic
ORM-generated queries (test at the ORM layer instead)
Queries against read-only reference data that never changes

Common SQL Testing Mistakes

Testing the ORM, not the SQL: If your application uses an ORM, test the application behavior, not the raw SQL the ORM generates. The ORM's tests cover its SQL generation.

No rollback: Tests that don't clean up after themselves pollute the database and cause test ordering dependencies. Always roll back or truncate.

Testing with production data: Production data contains PII and changes over time. Use generated test fixtures you control completely.

Ignoring NULLs: NULL behavior in SQL is surprising. Test NULL inputs and NULL handling explicitly — they're responsible for a disproportionate number of bugs.

Not testing at scale: A query that runs in 10ms on 1,000 rows may run in 30 seconds on 10 million rows. Load test critical queries against realistic data volumes.

SQL testing catches bugs that application-level tests miss — constraint violations, bad query plans, and concurrent access issues don't show up in mocked tests. The tooling exists (pgTAP, MyTAP, Testcontainers), the patterns are established, and the cost of not testing is a production incident.

Start with the queries your application relies on most heavily, add constraint tests for your critical tables, and run everything in CI. The setup takes a day. The incidents it prevents are worth far more.