Database Testing Best Practices: Catch Data Corruption Before It Reaches Production

Your application is only as reliable as the data layer beneath it. A single bad migration, a missed foreign key constraint, or a slow query can bring your entire system to its knees. Yet database testing remains one of the most neglected areas of QA.

Most teams test the happy path: create a record, read it back, maybe update it. But what about concurrent writes? Transaction isolation? Migration rollbacks? Index performance under load? These are the scenarios that cause 3 AM production incidents.

This comprehensive guide covers battle-tested strategies for thorough database testing, from migration validation to performance benchmarking and data integrity checks.

Table of Contents

1. Why Database Testing Matters
2. Migration Testing Strategies
3. Data Integrity Testing
4. Transaction and Concurrency Testing
5. Performance and Query Optimization
6. Test Data Management
7. Backup and Recovery Testing
8. Best Practices

Why Database Testing Matters

The Cost of Database Issues

Issue Type	Impact	Average Downtime	Annual Cost
Failed migration	Data loss/corruption	4-8 hours	$250,000
Missing index	Slow queries	Continuous degradation	$180,000
Broken constraint	Data integrity violation	2-4 hours	$150,000
Deadlock	System hang	1-2 hours	$80,000
Total			$660,000

Common Database Testing Gaps

❌ Untested: Concurrent writes, rollback scenarios, index performance
✅ Tested: Basic CRUD operations

❌ Untested: Migration failures partway through
✅ Tested: Successful forward migrations

❌ Untested: Query performance at scale
✅ Tested: Query correctness with small datasets

Migration Testing Strategies

Automated Migration Testing

// tests/database/migrations.spec.ts
import { test, expect } from '@playwright/test';
import { Client } from 'pg';
import { readFileSync, readdirSync } from 'fs';
import { join } from 'path';

test.describe('Database Migrations', () => {
  let client: Client;

  test.beforeAll(async () => {
    client = new Client({
      host: 'localhost',
      database: 'test_db',
      user: 'postgres',
      password: 'postgres',
    });
    await client.connect();
  });

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

  test('all migrations apply successfully', async () => {
    // Get all migration files
    const migrationsDir = './supabase/migrations';
    const migrationFiles = readdirSync(migrationsDir)
      .filter((f) => f.endsWith('.sql'))
      .sort();

    console.log(`Found ${migrationFiles.length} migrations`);

    for (const file of migrationFiles) {
      const sql = readFileSync(join(migrationsDir, file), 'utf-8');

      try {
        await client.query('BEGIN');
        await client.query(sql);
        await client.query('COMMIT');
        console.log(`✅ ${file} applied successfully`);
      } catch (error) {
        await client.query('ROLLBACK');
        throw new Error(`❌ Migration ${file} failed: ${error.message}`);
      }
    }
  });

  test('migrations are idempotent', async () => {
    const testMigration = `
      CREATE TABLE IF NOT EXISTS test_idempotent (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL
      );
      
      CREATE INDEX IF NOT EXISTS idx_test_name 
        ON test_idempotent(name);
    `;

    // Apply once
    await client.query(testMigration);

    // Apply again - should not error
    await expect(client.query(testMigration)).resolves.not.toThrow();

    // Clean up
    await client.query('DROP TABLE IF EXISTS test_idempotent');
  });

  test('migration has rollback script', async () => {
    // Verify each migration has a corresponding down migration
    const upMigrations = readdirSync('./supabase/migrations').filter((f) => f.endsWith('.up.sql'));

    for (const upFile of upMigrations) {
      const downFile = upFile.replace('.up.sql', '.down.sql');
      const downPath = join('./supabase/migrations', downFile);

      expect(existsSync(downPath), `Missing rollback migration for ${upFile}`).toBe(true);
    }
  });
});

Migration Performance Testing

test('migration completes within acceptable time', async () => {
  const migrationSQL = readFileSync('./supabase/migrations/20240101_add_indexes.sql', 'utf-8');

  const startTime = Date.now();
  await client.query(migrationSQL);
  const duration = Date.now() - startTime;

  // Migration should complete in under 5 seconds
  expect(duration).toBeLessThan(5000);
  console.log(`Migration completed in ${duration}ms`);
});

test('large table migration handles data correctly', async () => {
  // Create table with sample data
  await client.query(`
    CREATE TEMP TABLE migration_test AS
    SELECT generate_series(1, 100000) AS id, 
           md5(random()::text) AS data
  `);

  // Apply migration
  const startTime = Date.now();
  await client.query(`
    ALTER TABLE migration_test 
    ADD COLUMN processed BOOLEAN DEFAULT false
  `);
  const duration = Date.now() - startTime;

  // Verify all rows were migrated
  const result = await client.query(`
    SELECT COUNT(*) FROM migration_test WHERE processed = false
  `);

  expect(result.rows[0].count).toBe('100000');
  console.log(`Migrated 100k rows in ${duration}ms`);
});

Testing Migration Rollbacks

test('migration can be rolled back safely', async () => {
  // Apply migration
  await client.query(`
    CREATE TABLE rollback_test (
      id SERIAL PRIMARY KEY,
      email TEXT UNIQUE NOT NULL
    )
  `);

  // Insert test data
  await client.query(`
    INSERT INTO rollback_test (email) 
    VALUES ('test@example.com')
  `);

  // Verify table exists
  let result = await client.query(`
    SELECT EXISTS (
      SELECT FROM information_schema.tables 
      WHERE table_name = 'rollback_test'
    )
  `);
  expect(result.rows[0].exists).toBe(true);

  // Rollback migration
  await client.query('DROP TABLE rollback_test');

  // Verify table is gone
  result = await client.query(`
    SELECT EXISTS (
      SELECT FROM information_schema.tables 
      WHERE table_name = 'rollback_test'
    )
  `);
  expect(result.rows[0].exists).toBe(false);
});

Data Integrity Testing

Constraint Testing

// tests/database/constraints.spec.ts
test.describe('Data Integrity Constraints', () => {
  test('enforces unique constraints', async () => {
    await client.query(`
      INSERT INTO users (email, name) 
      VALUES ('unique@example.com', 'Test User')
    `);

    // Duplicate insert should fail
    await expect(
      client.query(`
        INSERT INTO users (email, name) 
        VALUES ('unique@example.com', 'Another User')
      `),
    ).rejects.toThrow(/duplicate key value violates unique constraint/);
  });

  test('enforces foreign key constraints', async () => {
    // Insert parent record
    const userResult = await client.query(`
      INSERT INTO users (email, name) 
      VALUES ('parent@example.com', 'Parent User')
      RETURNING id
    `);
    const userId = userResult.rows[0].id;

    // Insert child record - should succeed
    await client.query(
      `
      INSERT INTO posts (user_id, title) 
      VALUES ($1, 'Test Post')
    `,
      [userId],
    );

    // Insert with invalid foreign key - should fail
    await expect(
      client.query(`
        INSERT INTO posts (user_id, title) 
        VALUES (99999, 'Invalid Post')
      `),
    ).rejects.toThrow(/violates foreign key constraint/);
  });

  test('enforces check constraints', async () => {
    // Valid price
    await client.query(`
      INSERT INTO products (name, price) 
      VALUES ('Valid Product', 19.99)
    `);

    // Invalid price (negative)
    await expect(
      client.query(`
        INSERT INTO products (name, price) 
        VALUES ('Invalid Product', -10.00)
      `),
    ).rejects.toThrow(/violates check constraint/);
  });

  test('enforces NOT NULL constraints', async () => {
    await expect(
      client.query(`
        INSERT INTO users (name) 
        VALUES ('No Email User')
      `),
    ).rejects.toThrow(/null value in column "email" violates not-null constraint/);
  });
});

Cascade Deletion Testing

test('cascade deletes child records', async () => {
  // Create user
  const userResult = await client.query(`
    INSERT INTO users (email, name) 
    VALUES ('cascade@example.com', 'Cascade User')
    RETURNING id
  `);
  const userId = userResult.rows[0].id;

  // Create child records
  await client.query(
    `
    INSERT INTO posts (user_id, title) 
    VALUES ($1, 'Post 1'), ($1, 'Post 2')
  `,
    [userId],
  );

  // Verify posts exist
  let result = await client.query(
    `
    SELECT COUNT(*) FROM posts WHERE user_id = $1
  `,
    [userId],
  );
  expect(result.rows[0].count).toBe('2');

  // Delete user
  await client.query('DELETE FROM users WHERE id = $1', [userId]);

  // Verify cascade deletion
  result = await client.query(
    `
    SELECT COUNT(*) FROM posts WHERE user_id = $1
  `,
    [userId],
  );
  expect(result.rows[0].count).toBe('0');
});

Transaction and Concurrency Testing

Transaction Rollback Pattern

// lib/test-transaction.ts
export class TransactionTestHelper {
  constructor(private client: Client) {}

  async inTransaction<T>(fn: () => Promise<T>): Promise<T> {
    await this.client.query('BEGIN');
    try {
      const result = await fn();
      await this.client.query('ROLLBACK');
      return result;
    } catch (error) {
      await this.client.query('ROLLBACK');
      throw error;
    }
  }
}

// Usage
test('test with automatic rollback', async () => {
  const helper = new TransactionTestHelper(client);

  await helper.inTransaction(async () => {
    // All changes rolled back after test
    await client.query(`
      INSERT INTO users (email, name) 
      VALUES ('temp@example.com', 'Temp User')
    `);

    const result = await client.query(`
      SELECT * FROM users WHERE email = 'temp@example.com'
    `);
    expect(result.rows).toHaveLength(1);
  });

  // Verify rollback
  const result = await client.query(`
    SELECT * FROM users WHERE email = 'temp@example.com'
  `);
  expect(result.rows).toHaveLength(0);
});

Concurrent Write Testing

test('handles concurrent writes correctly', async () => {
  // Create product with stock
  await client.query(`
    INSERT INTO products (id, name, stock)
    VALUES (1, 'Test Product', 10)
  `);

  // Simulate 10 concurrent purchases
  const purchases = Array.from({ length: 10 }, (_, i) =>
    client.query(`
      UPDATE products
      SET stock = stock - 1
      WHERE id = 1 AND stock > 0
    `)
  );

  await Promise.all(purchases);

  // Verify final stock
  const result = await client.query(`
    SELECT stock FROM products WHERE id = 1
  `);

  expect(result.rows[0].stock).toBe(0);
});

test('detects and handles deadlocks', async () => {
  const client1 = new Client(connectionConfig);
  const client2 = new Client(connectionConfig);

  await client1.connect();
  await client2.connect();

  try {
    // Create two records
    await client1.query(`
      INSERT INTO test_locks (id, value)
      VALUES (1, 'A'), (2, 'B')
    `);

    // Start transactions
    await client1.query('BEGIN');
    await client2.query('BEGIN');

    // Client 1 locks record 1
    await client1.query('UPDATE test_locks SET value = 'X' WHERE id = 1');

    // Client 2 locks record 2
    await client2.query('UPDATE test_locks SET value = 'Y' WHERE id = 2');

    // Create deadlock scenario
    const promises = [
      client1.query('UPDATE test_locks SET value = 'Y' WHERE id = 2'),
      client2.query('UPDATE test_locks SET value = 'X' WHERE id = 1'),
    ];

    // One should fail with deadlock error
    await expect(Promise.all(promises)).rejects.toThrow(/deadlock detected/);
  } finally {
    await client1.query('ROLLBACK');
    await client2.query('ROLLBACK');
    await client1.end();
    await client2.end();
  }
});

Performance and Query Optimization

Query Performance Testing

// tests/database/performance.spec.ts
test.describe('Query Performance', () => {
  test.beforeAll(async () => {
    // Seed with realistic data volume
    await client.query(`
      INSERT INTO users (email, name)
      SELECT 
        'user' || generate_series || '@example.com',
        'User ' || generate_series
      FROM generate_series(1, 10000)
    `);

    await client.query(`
      INSERT INTO posts (user_id, title, content)
      SELECT 
        (random() * 10000 + 1)::int,
        'Post ' || generate_series,
        md5(random()::text)
      FROM generate_series(1, 50000)
    `);
  });

  test('query with index is fast', async () => {
    // Query using indexed column
    const startTime = Date.now();

    await client.query(`
      SELECT * FROM users WHERE email = 'user5000@example.com'
    `);

    const duration = Date.now() - startTime;

    // Should be fast with index
    expect(duration).toBeLessThan(10);
  });

  test('query without index is slow (and should have one)', async () => {
    const startTime = Date.now();

    // Query on non-indexed column
    await client.query(`
      SELECT * FROM posts WHERE title LIKE '%search%'
    `);

    const duration = Date.now() - startTime;

    if (duration > 100) {
      console.warn(`⚠️ Slow query detected (${duration}ms). Consider adding index.`);
    }
  });

  test('aggregation query performs well', async () => {
    const startTime = Date.now();

    const result = await client.query(`
      SELECT 
        user_id, 
        COUNT(*) as post_count,
        MAX(created_at) as last_post
      FROM posts
      GROUP BY user_id
      HAVING COUNT(*) > 5
      ORDER BY post_count DESC
      LIMIT 10
    `);

    const duration = Date.now() - startTime;

    expect(result.rows.length).toBeGreaterThan(0);
    expect(duration).toBeLessThan(500);
  });
});

Explain Plan Analysis

test('queries use appropriate indexes', async () => {
  const result = await client.query(`
    EXPLAIN (FORMAT JSON, ANALYZE) 
    SELECT * FROM users WHERE email = 'test@example.com'
  `);

  const plan = result.rows[0]['QUERY PLAN'][0];
  const usesIndexScan = JSON.stringify(plan).includes('Index Scan');

  expect(usesIndexScan).toBe(true);
  console.log('Execution time:', plan['Execution Time'], 'ms');
});

test('detects sequential scans on large tables', async () => {
  const result = await client.query(`
    EXPLAIN (FORMAT JSON) 
    SELECT * FROM posts WHERE content LIKE '%search%'
  `);

  const plan = JSON.stringify(result.rows[0]['QUERY PLAN']);

  if (plan.includes('Seq Scan')) {
    console.warn('⚠️ Sequential scan detected. Consider adding index.');
  }
});

N+1 Query Detection

test('avoids N+1 queries', async () => {
  // Track query count
  let queryCount = 0;

  const trackingClient = new Client(connectionConfig);
  const originalQuery = trackingClient.query.bind(trackingClient);

  trackingClient.query = async (...args) => {
    queryCount++;
    return originalQuery(...args);
  };

  await trackingClient.connect();

  // Fetch users with their posts (potential N+1)
  const users = await trackingClient.query(`
    SELECT id, name FROM users LIMIT 10
  `);

  for (const user of users.rows) {
    await trackingClient.query(
      `
      SELECT * FROM posts WHERE user_id = $1
    `,
      [user.id],
    );
  }

  // Should be 11 queries (1 + 10)
  expect(queryCount).toBe(11);
  console.warn(`⚠️ N+1 query detected: ${queryCount} queries`);

  // Better approach: single query with JOIN
  queryCount = 0;
  await trackingClient.query(`
    SELECT u.*, p.id as post_id, p.title
    FROM users u
    LEFT JOIN posts p ON p.user_id = u.id
    WHERE u.id IN (SELECT id FROM users LIMIT 10)
  `);

  expect(queryCount).toBe(1);
  console.log(`✅ Optimized to ${queryCount} query`);

  await trackingClient.end();
});

Test Data Management

Seeding Strategy

// lib/seed.ts
export class DatabaseSeeder {
  constructor(private client: Client) {}

  async seedUsers(count: number) {
    const values = Array.from(
      { length: count },
      (_, i) => `
      ('user${i}@example.com', 'User ${i}', NOW())
    `,
    ).join(',');

    await this.client.query(`
      INSERT INTO users (email, name, created_at)
      VALUES ${values}
      ON CONFLICT (email) DO NOTHING
    `);
  }

  async seedPosts(userCount: number, postsPerUser: number) {
    await this.client.query(
      `
      INSERT INTO posts (user_id, title, content, created_at)
      SELECT 
        u.id,
        'Post ' || generate_series,
        md5(random()::text),
        NOW() - (random() * 365 || ' days')::interval
      FROM users u
      CROSS JOIN generate_series(1, $1)
      LIMIT $2
    `,
      [postsPerUser, userCount * postsPerUser],
    );
  }

  async reset() {
    await this.client.query('TRUNCATE users, posts, comments CASCADE');
  }
}

// Usage
test.beforeEach(async () => {
  const seeder = new DatabaseSeeder(client);
  await seeder.reset();
  await seeder.seedUsers(100);
  await seeder.seedPosts(100, 5);
});

Snapshot Testing

test('database schema matches snapshot', async () => {
  const result = await client.query(`
    SELECT 
      table_name,
      column_name,
      data_type,
      is_nullable,
      column_default
    FROM information_schema.columns
    WHERE table_schema = 'public'
    ORDER BY table_name, ordinal_position
  `);

  expect(result.rows).toMatchSnapshot();
});

Backup and Recovery Testing

test.describe('Backup and Recovery', () => {
  test('can create and restore backup', async () => {
    // Insert test data
    await client.query(`
      INSERT INTO users (email, name) 
      VALUES ('backup@example.com', 'Backup User')
    `);

    // Create backup
    const backup = await client.query(`
      SELECT row_to_json(users.*) as data 
      FROM users 
      WHERE email = 'backup@example.com'
    `);

    // Delete data
    await client.query(`
      DELETE FROM users WHERE email = 'backup@example.com'
    `);

    // Verify deletion
    let result = await client.query(`
      SELECT * FROM users WHERE email = 'backup@example.com'
    `);
    expect(result.rows).toHaveLength(0);

    // Restore from backup
    const data = backup.rows[0].data;
    await client.query(
      `
      INSERT INTO users (email, name, created_at)
      VALUES ($1, $2, $3)
    `,
      [data.email, data.name, data.created_at],
    );

    // Verify restore
    result = await client.query(`
      SELECT * FROM users WHERE email = 'backup@example.com'
    `);
    expect(result.rows).toHaveLength(1);
    expect(result.rows[0].name).toBe('Backup User');
  });
});

Best Practices

Database Testing Checklist

Test Category	What to Test	Priority
Migrations	Forward, backward, idempotency	🔴 Critical
Constraints	Unique, FK, NOT NULL, CHECK	🔴 Critical
Transactions	Rollback, commit, isolation	🟡 High
Performance	Query speed, index usage	🟡 High
Concurrency	Race conditions, deadlocks	🟡 High
Data Integrity	Cascades, triggers, defaults	🟢 Medium
Backup/Recovery	Restore procedures	🟢 Medium

Key Principles

1. Test with realistic data volumes: 10 rows rarely expose performance issues
2. Use transactions for test isolation: Roll back changes to avoid pollution
3. Validate constraints exhaustively: They're your data integrity safety net
4. Performance test at scale: What works for 100 rows may fail at 100,000
5. Test failure scenarios: Deadlocks, timeouts, constraint violations
6. Automate migration testing: Every migration should be validated automatically
7. Monitor query plans: EXPLAIN ANALYZE is your best friend

Conclusion

Database testing is where many QA strategies falter. Don't let inadequate testing at the data layer undermine your entire application. By implementing comprehensive migration testing, rigorous constraint validation, performance benchmarking, and concurrency testing, you protect your application's most critical layer.

Start with migration automation, build up constraint coverage, and add performance benchmarks as your data grows. Your 3 AM self will thank you.

Related articles: Also see performance optimisations to validate with database testing, lock and deadlock patterns to include in your database test suite, and data management strategies that feed reliable data into database tests.

---

Need comprehensive database testing for your application? Try ScanlyApp for automated database testing, migration validation, and performance monitoring. Start for free—no credit card required.