Database Testing Patterns

Production engineering guide for database testing patterns covering patterns, implementation strategies, and operational best practices.

Database testing ensures that data integrity, schema evolution, and query correctness are maintained across development, deployment, and runtime. It matters most during schema migrations, when introducing new services, or when scaling data access across microservices. Unlike API or UI testing, database testing focuses on the state of data—what is stored, how it is structured, and how it behaves under load, failure, and time.

Testing Schema Changes

Schema changes are the most common database testing task. They must verify that changes to tables, indexes, constraints, and sequences are applied correctly and that data remains consistent across versions. This pattern starts with a migration script and ends with a validation query.

Migration scripts are versioned SQL files, typically named 001-create-users.sql, 002-add-username-unique.sql, etc. A test suite runs each migration against a clean database and validates the outcome.

-- 002-add-username-unique.sql
ALTER TABLE users ADD CONSTRAINT username_unique UNIQUE (username);

A test validates that the constraint is applied:

-- test/schema/002-username-unique.sql
SELECT
  COUNT(*) AS failure_count
FROM users
WHERE username IN (
  SELECT username
  FROM users
  GROUP BY username
  HAVING COUNT(*) > 1
)
AND id NOT IN (
  SELECT MIN(id)
  FROM users
  GROUP BY username
);

-- Expected: failure_count = 0

The test is run using a tool like psql in a CI pipeline:

#!/bin/bash


set -euo pipefail

DATABASE_URL=postgres://test:test@localhost:5432/testdb

# Apply migrations
psql "$DATABASE_URL" -f migrations/001-create-users.sql
psql "$DATABASE_URL" -f migrations/002-add-username-unique.sql

# Run validation
psql "$DATABASE_URL" -c "
  \o /tmp/schema-test-output.json
  \t
  \A
  \F,
  SELECT
    'username_unique' AS test,
    COUNT(*) AS failures
  FROM users
  WHERE username IN (
    SELECT username
    FROM users
    GROUP BY username
    HAVING COUNT(*) > 1
  )
  AND id NOT IN (
    SELECT MIN(id)
    FROM users
    GROUP BY username
  )
" > /tmp/schema-test-result.json

# Check result
jq -r '.failures == 0' /tmp/schema-test-result.json

A common failure mode is missing CREATE INDEX statements in migrations. The test passes, but query performance degrades due to unindexed columns. This is silent: the data is correct, but the system is slow. To catch this, add a performance test using EXPLAIN ANALYZE.

EXPLAIN ANALYZE
SELECT id, name
FROM users
WHERE username = 'alice';

The output includes Execution Time, Cost, and Rows. A test fails if Execution Time exceeds 10ms or Cost is higher than expected. The EXPLAIN output can be parsed with jq or awk to validate specific metrics.

Testing Data Integrity with Row-Level Assertions

Data integrity tests verify that data satisfies business rules, constraints, and invariants. These are often written as SQL assertions and run after a data load, batch job, or transaction.

-- test/integrity/001-user-consistency.sql
SELECT
  'user_age_valid' AS test,
  COUNT(*) AS violations
FROM users
WHERE age < 1 OR age > 120;

-- test/integrity/002-transaction-consistency.sql
SELECT
  'transaction_balance_consistent' AS test,
  COUNT(*) AS violations
FROM transactions t
JOIN accounts a ON t.account_id = a.id
WHERE t.amount <> a.balance
  OR t.amount < 0
  OR a.balance < 0;

Each test outputs a JSON or CSV result:

[
  {
    "test": "user_age_valid",
    "violations": 3
  },
  {
    "test": "transaction_balance_consistent",
    "violations": 12
  }
]

A test runner collects all results and fails if any test returns violations > 0. The runner can be a simple bash script using psql and jq:

#!/bin/bash
# ci/integrity-test.sh

DATABASE_URL=postgres://test:test@localhost:5432/testdb

for test_file in test/integrity/*.sql; do
  test_name=$(basename "$test_file" .sql)
  psql "$DATABASE_URL" -c "
    \t
    \A
    \F,
    $(
      cat "$test_file"
    )
  " > /tmp/integrity-$test_name.json
done

# Aggregate results
jq -s '[
  .[] | .[]
]' /tmp/integrity-*.json > /tmp/integrity-results.json

# Fail if any test has violations
jq -e 'any(.[]; .violations > 0)' /tmp/integrity-results.json

A sharp edge: the EXPLAIN ANALYZE output is sensitive to data distribution. A query that runs fast on test data may fail in production due to skewed data. The test must be run on production-sized datasets, not just sample data.

Testing Query Performance Over Time

Performance testing of database queries involves measuring execution time, I/O, and resource usage across varying data volumes. This pattern is used to detect regressions in query plans and to validate index effectiveness.

A test suite runs a set of queries under load, collecting metrics over time. The queries are defined in a queries.yaml file:

queries:
  - name: user_profile_lookup
    sql: |
      SELECT u.id, u.name, u.email, a.balance, a.last_updated
      FROM users u
      JOIN accounts a ON u.id = a.user_id
      WHERE u.username = $1
      ORDER BY a.balance DESC
      LIMIT 10
    parameters:
      username: alice
    expected:
      execution_time_ms: 15
      rows_returned: 10
      cost: 123.4

A runner script executes each query and logs results:

#!/bin/bash
# ci/performance-test.sh

DATABASE_URL=postgres://test:test@localhost:5432/testdb

while IFS= read -r query; do
  query_name=$(echo "$query" | jq -r '.name')
  sql=$(echo "$query" | jq -r '.sql')
  params=$(echo "$query" | jq -r '.parameters | tojson')

  # Run query with timing
  psql "$DATABASE_URL" -c "
    \t
    \A
    \F,
    EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
    $sql
  " -v username="$params" > /tmp/performance/$query_name.json

  # Parse JSON output
  execution_time=$(jq -r '.[0].Execution Time' /tmp/performance/$query_name.json)
  rows_returned=$(jq -r '.[0].Rows' /tmp/performance/$query_name.json)
  cost=$(jq -r '.[0].Cost' /tmp/performance/$query_name.json)

  # Validate expected values
  if [ $(echo "$execution_time" | awk '{print $1}') -gt $(echo "$query" | jq -r '.expected.execution_time_ms') ]; then
    echo "FAIL: $query_name execution time too high"
    exit 1
  fi

  if [ "$rows_returned" -ne $(echo "$query" | jq -r '.expected.rows_returned') ]; then
    echo "FAIL: $query_name returned wrong number of rows"
    exit 1
  fi

done < <(jq -c '.queries[]' queries.yaml)

The output is logged and can be compared across test runs. A regression is detected if execution_time increases by more than 20% compared to a baseline.

A common failure: the query plan changes due to statistics, but the test assumes a fixed plan. The EXPLAIN (ANALYZE, BUFFERS) output includes Buffers and Blocks, which indicate cache behavior. A test fails if Heap Blocks Read is higher than expected, indicating that data is not in memory.

Testing Data Consistency Across Services

In a microservices architecture, data consistency across services is critical. A pattern for this is eventual consistency testing. It verifies that events published by one service are correctly consumed and applied by others.

Events are stored in a events table:

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  type TEXT NOT NULL,
  payload JSONB NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL,
  processed BOOLEAN DEFAULT FALSE
);

A test checks that after publishing an UserCreated event, the users table reflects the data, and a notifications table contains a corresponding entry.

-- test/consistency/eventual-consistency.sql
WITH event AS (
  SELECT id, payload
  FROM events
  WHERE type = 'UserCreated'
    AND occurred_at > NOW() - INTERVAL '1 minute'
    AND processed = TRUE
),
user AS (
  SELECT id, name, email
  FROM users
  WHERE username = (SELECT (payload->>'username')::text FROM event)
)
SELECT
  CASE
    WHEN u.id IS NOT NULL THEN 'success'
    ELSE 'failed'
  END AS result
FROM event e
JOIN user u ON e.payload->>'username' = u.username
WHERE u.email = e.payload->>'email';

This test runs after a service publishes events. It fails if any event does not result in a corresponding user, or if the user data is incorrect.

A key challenge: timing of event processing. Events may be processed with a delay—minutes or hours. The test must account for this by using occurred_at and processed_at timestamps. A test fails if processed_at is more than 5 minutes after occurred_at for any event.

The test can be enhanced with a delayed assertion:

-- test/consistency/eventual-consistency-delay.sql
SELECT
  'event_processing_delay' AS test,
  AVG(processed_at - occurred_at) AS avg_delay
FROM events
WHERE type = 'UserCreated'
  AND processed = TRUE;

-- Expected: avg_delay < INTERVAL '5 minutes'

Testing Schema Evolution with Rollback and Migration Reversibility

Schema evolution tests verify that migrations can be applied and reversed. This pattern ensures that a schema upgrade can be rolled back safely.

A test suite applies a series of migrations, then reverses them, and checks that the database state matches the initial state.

-- migrations/003-add-user-roles.sql
ALTER TABLE users ADD COLUMN role TEXT DEFAULT 'user';
ALTER TABLE users ADD CONSTRAINT role_check CHECK (role IN ('user', 'admin', 'moderator'));

-- migrations/004-drop-user-roles.sql
ALTER TABLE users DROP COLUMN role;

The test runs:

#!/bin/bash
# ci/rollback-test.sh

DATABASE_URL=postgres://test:test@localhost:5432/testdb

# Apply migrations 001 to 004
for i in {1..4}; do
  psql "$DATABASE_URL" -f migrations/$i-*.sql
done

# Validate final state
psql "$DATABASE_URL" -c "
  SELECT
    'roles_column_exists' AS test,
    COUNT(*) AS rows
  FROM information_schema.columns
  WHERE table_name = 'users'
    AND column_name = 'role'
  HAVING COUNT(*) = 1
" > /tmp/rollback-final.json

# Reverse migrations
for i in {4..1}; do
  psql "$DATABASE_URL" -f migrations/$i-drop-*.sql
done

# Validate initial state
psql "$DATABASE_URL" -c "
  SELECT
    'roles_column_dropped' AS test,
    COUNT(*) AS rows
  FROM information_schema.columns
  WHERE table_name = 'users'
    AND column_name = 'role'
  HAVING COUNT(*) = 0
" > /tmp/rollback-initial.json

# Fail if final state is not as expected
jq -e 'any(.[]; .rows == 1)' /tmp/rollback-final.json
jq -e 'any(.[]; .rows == 0)' /tmp/rollback-initial.json

The test fails if a migration is not reversible or if a reverse migration leaves behind data or constraints. A sharp edge: the order of CREATE INDEX and ALTER TABLE statements matters in reverse migrations. If an index is created before a column is added, the reverse migration may fail because the index column does not exist.

Testing Data Backfill and Migration Jobs

When introducing a new column or denormalized data, a backfill job is required. Testing this job ensures that all existing data is transformed correctly.

A backfill job runs UPDATE users SET role = 'admin' WHERE created_at < '2020-01-01' and is tested using a differential test.

-- test/backfill/differential-test.sql
WITH initial AS (
  SELECT
    COUNT(*) AS count,
    SUM(CASE WHEN role IS NULL THEN 1 ELSE 0 END) AS null_roles
  FROM users
  WHERE created_at < '2020-01-01'
),
final AS (
  SELECT
    COUNT(*) AS count,
    SUM(CASE WHEN role IS NOT NULL THEN 1 ELSE 0 END) AS non_null_roles
  FROM users
  WHERE created_at < '2020-01-01'
)
SELECT
  CASE
    WHEN i.count = f.count AND i.null_roles = f.non_null_roles THEN 'success'
    ELSE 'failed'
  END AS result
FROM initial i
JOIN final f ON i.count = f.count;

The test fails if any user in the range has a NULL role after the backfill, or if the number of users does not match expectations.

A subtle failure: the backfill job modifies data, but the test runs before the job commits. The test sees intermediate data, not final data. To fix this, the test must wait for the job to complete, using pg_sleep or polling pg_stat_activity.

-- test/backfill/wait-for-job.sql
SELECT
  COUNT(*) AS jobs_running
FROM pg_stat_activity
WHERE query LIKE 'UPDATE users SET role = %'
  AND state = 'active';

-- Wait until no jobs are running
\set ON_ERROR_STOP on
\set VERBOSITY terse
\set HISTCONTROL ignorespace
\set PROMPT1 '%n@%d:%> '
\set PROMPT2 '%n@%d:%> '
\set PROMPT3 '%n@%d:%> '

\set job_count 0
DO $$
DECLARE
  job_count INTEGER;
BEGIN
  LOOP
    job_count := (SELECT COUNT(*) FROM pg_stat_activity WHERE query LIKE 'UPDATE users SET role = %' AND state = 'active');
    IF job_count = 0 THEN
      EXIT;
    END IF;
    PERFORM pg_sleep(1);
  END LOOP;
END $$;

This ensures that the test runs on a stable, fully updated dataset.

This page was rewritten on 18 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.