Shift-Left Database Testing: Test Your Schema and Migrations Early

26 Sep 2026 — 8 min read

Database changes are the highest-risk category of production deployments. A bad migration can corrupt data, lock tables, and cause downtime that no amount of application-level testing can prevent. Shifting database testing left means validating schema changes, migrations, and data contracts in CI before a single line of application code is deployed.

Key Takeaways

Migrations must run in CI on every PR, not just before deployment. A migration that works in isolation can fail when run on top of the previous 200 migrations. Testing the full migration sequence catches this before it becomes a production incident.

Testcontainers eliminates "works on my machine" for databases. Spinning up a real PostgreSQL instance in CI ensures your tests run against the exact engine version and behavior as production — not SQLite quirks or mock return values.

Schema contracts prevent silent API breakage. When another service reads your database directly, that schema is an API. Schema contract tests catch breaking changes before they break consumers.

Rollback testing is as important as forward migration testing. A migration you cannot roll back safely is a one-way door. Test down migrations explicitly, under load if possible.

pgTAP moves data quality assertions into the database itself. Testing that constraints, triggers, and functions work correctly at the database level catches bugs that ORM-level tests never see.

Why Database Changes Deserve Their Own Testing Layer

Application code bugs fail gracefully. A service crashes, Kubernetes restarts it, logs show the error. Database migration bugs are different: a column drop that cascades incorrectly, a lock that holds for 45 minutes on a busy table, an index that was assumed to exist and was not created — these cause data loss, extended downtime, or silent corruption that takes days to detect.

Most teams treat database migrations as deployment steps rather than code artifacts. They are written once, reviewed briefly, and run in production without a dedicated test pipeline. The result is that migrations are the most tested thing in development (every developer runs them locally) and the least tested thing in CI.

Shifting database testing left means treating migrations, schema definitions, and data constraints as first-class test subjects.

The Foundation: Testcontainers for Real Database Tests

The most important principle in database testing is to test against a real database engine, not a mock or SQLite equivalent. Testcontainers starts a Docker container with your exact database version for each test run:

// test/setup/database.js
const { PostgreSqlContainer } = require('@testcontainers/postgresql');

let container;
let connectionString;

async function startDatabase() {
  container = await new PostgreSqlContainer('postgres:16-alpine')
    .withDatabase('testdb')
    .withUsername('testuser')
    .withPassword('testpass')
    .withExposedPorts(5432)
    .start();

  connectionString = container.getConnectionUri();
  return connectionString;
}

async function stopDatabase() {
  await container?.stop();
}

module.exports = { startDatabase, stopDatabase, getConnectionString: () => connectionString };

// jest.config.js — global setup/teardown
module.exports = {
  globalSetup: './test/setup/database-start.js',
  globalTeardown: './test/setup/database-stop.js',
  testTimeout: 30000,
};

Now every test run starts with a fresh, real PostgreSQL instance at the same version as production. No state leaks between test runs. No behavioral differences from SQLite.

Testing Migrations with Flyway

Flyway manages database migrations as versioned SQL files. The critical thing to test is not just "does this migration run?" but "does this migration run correctly when applied on top of the full migration history?"

db/migrations/
  V1__initial_schema.sql
  V2__add_users_table.sql
  V3__add_orders_table.sql
  V4__add_order_status_index.sql
  V5__add_customer_id_column.sql  ← the migration being tested

// test/migrations/migration.test.js
const { Client } = require('pg');
const { startDatabase } = require('../setup/database');
const { execSync } = require('child_process');

describe('Database migrations', () => {
  let connectionString;
  let client;

  beforeAll(async () => {
    connectionString = await startDatabase();
    client = new Client({ connectionString });
    await client.connect();
  });

  afterAll(async () => {
    await client.end();
  });

  it('applies all migrations cleanly from scratch', async () => {
    execSync(`flyway -url=jdbc:${connectionString} -locations=filesystem:db/migrations migrate`, {
      stdio: 'inherit',
    });

    // Verify migration history
    const result = await client.query(
      "SELECT version, description, success FROM flyway_schema_history ORDER BY installed_rank"
    );

    const failed = result.rows.filter(r => !r.success);
    expect(failed).toHaveLength(0);

    const versions = result.rows.map(r => r.version);
    expect(versions).toContain('5'); // V5 was applied
  });

  it('V5 migration creates customer_id column with correct type', async () => {
    const result = await client.query(`
      SELECT column_name, data_type, is_nullable
      FROM information_schema.columns
      WHERE table_name = 'orders'
        AND column_name = 'customer_id'
    `);

    expect(result.rows).toHaveLength(1);
    expect(result.rows[0].data_type).toBe('uuid');
    expect(result.rows[0].is_nullable).toBe('NO');
  });

  it('V5 migration creates foreign key to users table', async () => {
    const result = await client.query(`
      SELECT
        tc.constraint_name,
        ccu.table_name AS foreign_table,
        ccu.column_name AS foreign_column
      FROM information_schema.table_constraints tc
      JOIN information_schema.constraint_column_usage ccu
        ON ccu.constraint_name = tc.constraint_name
      WHERE tc.table_name = 'orders'
        AND tc.constraint_type = 'FOREIGN KEY'
        AND kcu.column_name = 'customer_id'
    `);

    expect(result.rows[0].foreign_table).toBe('users');
  });
});

Rollback Testing

A migration is only safe if you can reverse it. Test your down migrations explicitly:

it('rolls back V5 migration cleanly', async () => {
  // Start from a migrated state
  execSync(`flyway migrate`);

  // Insert test data that V5 enabled
  await client.query(`
    INSERT INTO orders (id, customer_id, total)
    VALUES ('ord-1', '550e8400-e29b-41d4-a716-446655440000', 99.99)
  `);

  // Run the undo migration
  execSync(`flyway undo -target=4`);

  // Verify rollback
  const columns = await client.query(`
    SELECT column_name FROM information_schema.columns
    WHERE table_name = 'orders'
  `);

  const columnNames = columns.rows.map(r => r.column_name);
  expect(columnNames).not.toContain('customer_id');

  // Verify no orphaned constraints
  const constraints = await client.query(`
    SELECT constraint_name FROM information_schema.table_constraints
    WHERE table_name = 'orders' AND constraint_type = 'FOREIGN KEY'
  `);
  expect(constraints.rows).toHaveLength(0);
});

For Liquibase teams, the equivalent is:

liquibase rollback --tag=before-v5 --changelog-file=db/changelog.xml

pgTAP: Testing Database Logic at the Database Level

pgTAP is a unit testing framework for PostgreSQL. It runs inside the database and tests stored procedures, triggers, constraints, and functions — things that cannot be tested through an ORM.

-- test/database/orders.pg.sql
BEGIN;

SELECT plan(8);

-- Test: NOT NULL constraint on customer_id
SELECT throws_ok(
  $$ INSERT INTO orders (id, total) VALUES ('ord-test', 10.00) $$,
  '23502',
  'null value in column "customer_id" of relation "orders" violates not-null constraint',
  'customer_id must not be null'
);

-- Test: foreign key constraint rejects unknown customer
SELECT throws_ok(
  $$ INSERT INTO orders (id, customer_id, total)
     VALUES ('ord-test', '00000000-0000-0000-0000-000000000000', 10.00) $$,
  '23503',
  NULL,
  'customer_id must reference existing user'
);

-- Test: status column only accepts valid enum values
SELECT throws_ok(
  $$ INSERT INTO orders (id, customer_id, status, total)
     VALUES ('ord-test', (SELECT id FROM users LIMIT 1), 'invalid_status', 10.00) $$,
  '22P02',
  NULL,
  'status must be a valid enum value'
);

-- Test: order total must be positive
SELECT throws_ok(
  $$ INSERT INTO orders (id, customer_id, total)
     VALUES ('ord-test', (SELECT id FROM users LIMIT 1), -5.00) $$,
  '23514',
  NULL,
  'total must be positive'
);

-- Test: updated_at trigger fires on update
SELECT lives_ok(
  $$ UPDATE orders SET status = 'confirmed' WHERE id = 'seed-order-1' $$,
  'update does not throw'
);

SELECT ok(
  (SELECT updated_at > created_at FROM orders WHERE id = 'seed-order-1'),
  'updated_at was updated by trigger'
);

-- Test: calculate_order_total() function
SELECT is(
  calculate_order_total(ARRAY[
    ROW(10.00, 2)::order_item,
    ROW(5.50, 3)::order_item
  ]),
  36.50,
  'calculate_order_total returns correct sum'
);

-- Test: index exists for common query pattern
SELECT has_index('orders', 'idx_orders_customer_id_status',
  'Index on customer_id, status exists for dashboard queries'
);

SELECT finish();

ROLLBACK;

Run pgTAP tests in CI:

# Run all pgTAP test files
pg_prove -d testdb -r test/database/

pgTAP tests find bugs that application-level tests never catch: a trigger that does not fire for UPDATE from a specific role, a constraint that was defined incorrectly and accepts invalid data, a function with off-by-one logic in a complex calculation.

Schema Contract Testing

When multiple services share a database (legacy architecture) or when downstream services read your database via a read replica, your schema is an API. Schema changes that break consumers need to be caught before deployment.

Define your schema contract as a machine-readable artifact:

# schema-contracts/orders-consumer.yaml
# Defines what the reporting service reads from the orders table
consumer: reporting-service
provider: orders-service
contract:
  table: orders
  required_columns:
    - name: id
      type: uuid
      nullable: false
    - name: status
      type: varchar
      nullable: false
    - name: total
      type: numeric
      nullable: false
    - name: created_at
      type: timestamp with time zone
      nullable: false
    - name: customer_id
      type: uuid
      nullable: true  # Consumer handles NULL

// test/contracts/schema-contract.test.js
const contracts = require('../../schema-contracts/orders-consumer.yaml');

describe('Schema contract: reporting-service reads orders', () => {
  for (const column of contracts.contract.required_columns) {
    it(`column ${column.name} exists with correct type`, async () => {
      const result = await client.query(`
        SELECT data_type, is_nullable
        FROM information_schema.columns
        WHERE table_name = $1 AND column_name = $2
      `, [contracts.contract.table, column.name]);

      expect(result.rows).toHaveLength(1);
      expect(result.rows[0].data_type).toBe(column.type);

      if (!column.nullable) {
        expect(result.rows[0].is_nullable).toBe('NO');
      }
    });
  }
});

This test runs in the provider's CI pipeline. When a developer tries to rename total to amount or change its type, the schema contract test fails before the PR merges.

Data Quality Checks in CI

Beyond schema structure, data quality checks validate that business invariants hold after migrations. These run against a database seeded with representative data:

// test/data-quality/invariants.test.js
describe('Data quality invariants', () => {
  beforeAll(async () => {
    // Seed with realistic data volume
    await seedDatabase(client, {
      users: 1000,
      orders: 10000,
      orderItems: 50000,
    });
  });

  it('every order has at least one order item', async () => {
    const result = await client.query(`
      SELECT COUNT(*) as orphaned
      FROM orders o
      LEFT JOIN order_items oi ON oi.order_id = o.id
      WHERE oi.id IS NULL
    `);
    expect(parseInt(result.rows[0].orphaned)).toBe(0);
  });

  it('order total matches sum of item prices', async () => {
    const result = await client.query(`
      SELECT COUNT(*) as mismatched
      FROM orders o
      WHERE o.total != (
        SELECT COALESCE(SUM(oi.price * oi.quantity), 0)
        FROM order_items oi
        WHERE oi.order_id = o.id
      )
    `);
    expect(parseInt(result.rows[0].mismatched)).toBe(0);
  });

  it('no customer has orders with total exceeding credit limit', async () => {
    const result = await client.query(`
      SELECT COUNT(*) as violations
      FROM users u
      JOIN orders o ON o.customer_id = u.id
      WHERE o.total > u.credit_limit
        AND o.status IN ('pending', 'confirmed')
    `);
    expect(parseInt(result.rows[0].violations)).toBe(0);
  });
});

Seed Data Strategies

Tests need data that reflects production complexity without being tied to specific IDs. Structure your seed data for maximum test coverage:

// test/fixtures/seed.js
async function seedDatabase(client, options = {}) {
  const { users = 100, orders = 1000 } = options;

  // Use sequences for predictable IDs in assertions
  await client.query(`
    INSERT INTO users (id, email, credit_limit, tier)
    SELECT
      gen_random_uuid(),
      'user-' || n || '@test.com',
      CASE WHEN n % 10 = 0 THEN 0 ELSE 1000 END,  -- 10% with no credit
      CASE
        WHEN n % 100 = 0 THEN 'enterprise'
        WHEN n % 10 = 0 THEN 'pro'
        ELSE 'free'
      END
    FROM generate_series(1, $1) n
  `, [users]);

  // Seed orders with varied states for testing filters
  await client.query(`
    INSERT INTO orders (id, customer_id, status, total, created_at)
    SELECT
      gen_random_uuid(),
      (SELECT id FROM users ORDER BY RANDOM() LIMIT 1),
      (ARRAY['pending','confirmed','shipped','delivered','cancelled'])[1 + (n % 5)],
      (random() * 500 + 1)::numeric(10,2),
      NOW() - (random() * INTERVAL '365 days')
    FROM generate_series(1, $1) n
  `, [orders]);
}

CI Integration: The Full Pipeline

# .github/workflows/database-tests.yml
name: Database Tests
on:
  pull_request:
    paths:
      - 'db/migrations/**'
      - 'db/changelog.xml'
      - 'test/database/**'
      - 'test/migrations/**'

jobs:
  migration-tests:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16-alpine
        env:
          POSTGRES_DB: testdb
          POSTGRES_USER: testuser
          POSTGRES_PASSWORD: testpass
        ports:
          - 5432:5432
        options: --health-cmd pg_isready --health-timeout 5s

    steps:
      - uses: actions/checkout@v4

      - name: Install dependencies
        run: npm ci

      - name: Run migration tests
        run: npm run test:migrations
        env:
          DATABASE_URL: postgresql://testuser:testpass@localhost:5432/testdb

      - name: Install pgTAP
        run: |
          sudo apt-get install -y postgresql-16-pgtap
          psql $DATABASE_URL -c "CREATE EXTENSION IF NOT EXISTS pgtap"

      - name: Run pgTAP tests
        run: |
          psql $DATABASE_URL -f test/database/seed.sql
          pg_prove -d testdb -r test/database/

      - name: Run schema contract tests
        run: npm run test:contracts

      - name: Run data quality checks
        run: npm run test:data-quality

HelpMeTest: End-to-End Validation After Schema Changes

Database tests verify the schema is correct. End-to-end tests verify the application behaves correctly with the new schema. After a migration ships, HelpMeTest can immediately run your critical user journey scenarios — checkout, order history, account management — against the updated production database to confirm that schema changes did not break behavior that the migration tests could not see.

Triggering a HelpMeTest run as a post-deployment step in your migration pipeline gives you a fast, automated signal that the full stack is healthy before you close the deployment window.

Conclusion

Database testing is not a special case — it is a critical layer of your test pyramid that most teams skip. Every migration is a code change that deserves a test. Every schema is a contract that deserves validation. Every data invariant is a business rule that deserves enforcement.

Testcontainers, Flyway/Liquibase, pgTAP, and schema contracts give you a complete database testing toolkit that integrates cleanly into any CI pipeline. The cost of setting this up is one afternoon. The cost of skipping it is the next migration incident.