Database Testing

Database Testing

Core Principle

Data is your most valuable asset. Database bugs cause data loss/corruption.

Database testing ensures schema correctness, data integrity, transaction safety, and query performance. Critical for preventing catastrophic data issues.

Schema Testing

Validate database structure:

-- Test schema exists
SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'public' AND table_name = 'users';

-- Test column types
SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'users';

-- Test constraints
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'users';

Test with code:

test('users table has correct schema', async () => {
  const schema = await db.raw(`
    SELECT column_name, data_type, is_nullable
    FROM information_schema.columns
    WHERE table_name = 'users'
  `);

  expect(schema).toContainEqual({
    column_name: 'id',
    data_type: 'integer',
    is_nullable: 'NO'