database-testing

Database Testing

<default_to_action> When testing database operations:

1. VALIDATE schema (tables, columns, constraints exist as expected)
2. TEST data integrity (unique, foreign key, check constraints)
3. VERIFY migrations (forward works, rollback works, data preserved)
4. CHECK transaction isolation (ACID properties, concurrent access)
5. MEASURE query performance (indexes used, execution time)

Quick DB Testing Checklist:

• Schema matches specification
• Unique constraints prevent duplicates
• Foreign keys prevent orphaned records
• Migrations are reversible
• Transactions roll back on error

Critical Success Factors:

• Database bugs cause data loss/corruption (catastrophic)
• Test migrations in staging before production
• Transaction tests catch concurrency bugs </default_to_action>

Quick Reference Card

When to Use

• New table/schema creation
• Migration development
• Data integrity validation
• Query performance optimization

Database Test Types

Type	Focus	When
Schema	Structure correct	Table creation
Integrity	Constraints work	Data operations
Migration	Up/down work	Schema changes
Transaction	ACID properties	Concurrent access
Performance	Query speed	Optimization

---

Schema Testing

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'
  });

  expect(schema).toContainEqual({
    column_name: 'email',
    data_type: 'character varying',
    is_nullable: 'NO'
  });
});

---

Data Integrity Testing

test('email must be unique', async () => {
  await db.users.create({ email: '[email protected]' });

  await expect(
    db.users.create({ email: '[email protected]' })
  ).rejects.toThrow('unique constraint violation');
});

test('foreign key prevents orphaned records', async () => {
  const user = await db.users.create({ email: '[email protected]' });
  await db.orders.create({ userId: user.id, total: 100 });

  await expect(
    db.users.delete({ id: user.id })
  ).rejects.toThrow('foreign key constraint');
});