database-testing

Database schema validation, data integrity testing, migration testing, transaction isolation, and query performance. Use when testing data persistence, ensuring referential integrity, or validating database migrations.

⭐23stars🔀7forks📁View on GitHub🕐Updated Dec 8, 2025

When & Why to Use This Skill

This Claude skill provides a comprehensive framework for automated database testing, focusing on schema validation, data integrity, and migration safety. It enables teams to verify ACID compliance, test transaction isolation, and optimize query performance, ensuring that database operations are reliable, performant, and free from catastrophic data corruption or loss.

Use Cases

  • Migration Safety Verification: Validating that database migration scripts (both forward and rollback) execute correctly in staging environments without data loss before production deployment.
  • Data Integrity & Constraint Testing: Automatically testing unique constraints, foreign keys, and check constraints to ensure the database prevents orphaned records and invalid data entries.
  • Concurrency & Transaction Testing: Simulating concurrent access to verify transaction isolation levels and ensure ACID properties are maintained under high-load race conditions.
  • Schema Compliance Auditing: Comparing the live database structure against technical specifications to ensure all tables, columns, and data types are correctly implemented.
  • Query Performance Benchmarking: Measuring execution times and analyzing index usage to identify slow queries and optimize database performance for scaling.
namedatabase-testing
description"Database schema validation, data integrity testing, migration testing, transaction isolation, and query performance. Use when testing data persistence, ensuring referential integrity, or validating database migrations."
categoryspecialized-testing
priorityhigh
tokenEstimate900
agents[qe-test-data-architect, qe-test-executor, qe-performance-tester]
implementation_statusoptimized
optimization_version1.0
last_optimized2025-12-02
dependencies[]
quick_reference_cardtrue
tags[database, schema, migration, transactions, integrity, sql, performance]

Database Testing

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

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

ACID Properties

Property Test Focus
Atomicity All or nothing (rollback on error)
Consistency Constraints always valid
Isolation Concurrent transactions don't interfere
Durability Committed data persists

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: 'test@example.com' });

  await expect(
    db.users.create({ email: 'test@example.com' })
  ).rejects.toThrow('unique constraint violation');
});

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

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

Migration Testing

test('migration is reversible', async () => {
  await migrate('add-users-table');

  // Table exists after migration
  const tables = await db.raw(`SELECT table_name FROM information_schema.tables`);
  expect(tables.map(t => t.table_name)).toContain('users');

  await rollback('add-users-table');

  // Table gone after rollback
  const tablesAfter = await db.raw(`SELECT table_name FROM information_schema.tables`);
  expect(tablesAfter.map(t => t.table_name)).not.toContain('users');
});

test('migration preserves existing data', async () => {
  await db.users.create({ email: 'test@example.com' });

  await migrate('add-age-column');

  const user = await db.users.findOne({ email: 'test@example.com' });
  expect(user).toBeDefined();
  expect(user.age).toBeNull(); // New column, null default
});

Transaction Testing

test('transaction rolls back on error', async () => {
  const initialCount = await db.users.count();

  try {
    await db.transaction(async (trx) => {
      await trx('users').insert({ email: 'user1@example.com' });
      await trx('users').insert({ email: 'user2@example.com' });
      throw new Error('Rollback test');
    });
  } catch (error) { /* Expected */ }

  expect(await db.users.count()).toBe(initialCount);
});

test('concurrent transactions isolated', async () => {
  const user = await db.users.create({ balance: 100 });

  // Two concurrent withdrawals (race condition test)
  await Promise.all([
    db.transaction(async (trx) => {
      const current = await trx('users').where({ id: user.id }).first();
      await trx('users').update({ balance: current.balance - 50 });
    }),
    db.transaction(async (trx) => {
      const current = await trx('users').where({ id: user.id }).first();
      await trx('users').update({ balance: current.balance - 50 });
    })
  ]);

  const final = await db.users.findOne({ id: user.id });
  expect(final.balance).toBe(0); // Proper isolation
});

Agent-Driven Database Testing

// Generate test data with integrity
await Task("Generate Test Data", {
  schema: 'ecommerce',
  tables: ['users', 'products', 'orders'],
  count: { users: 1000, products: 500, orders: 5000 },
  preserveReferentialIntegrity: true
}, "qe-test-data-architect");

// Test migration safety
await Task("Migration Test", {
  migration: 'add-payment-status-column',
  tests: ['forward', 'rollback', 'data-preservation'],
  environment: 'staging'
}, "qe-test-executor");

Agent Coordination Hints

Memory Namespace

aqe/database-testing/
├── schema-snapshots/*   - Current schema state
├── migrations/*         - Migration test results
├── integrity/*          - Constraint validation
└── performance/*        - Query benchmarks

Fleet Coordination

const dbFleet = await FleetManager.coordinate({
  strategy: 'database-testing',
  agents: [
    'qe-test-data-architect',  // Generate test data
    'qe-test-executor',        // Run DB tests
    'qe-performance-tester'    // Query performance
  ],
  topology: 'sequential'
});


Remember

Database bugs are catastrophic. Data loss is unrecoverable. Corruption spreads silently. Performance issues compound.

Test migrations before production: Forward works, rollback works, data preserved, performance acceptable. Never deploy untested migrations.

With Agents: qe-test-data-architect generates realistic test data with referential integrity. qe-test-executor validates migrations automatically in CI/CD.