Database Testing Skill for Claude

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

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

  // Duplicate should fail
  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 });

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

test('check constraint validates data', async () => {
  // Age must be ≥ 18
  await expect(
    db.users.create({ email: '[email protected]', age: 17 })
  ).rejects.toThrow('check constraint violation');
});

import { migrate, rollback } from './migrations';

test('migration adds users table', async () => {
  // Start fresh
  await rollback();

  // Run migration
  await migrate();

  // Verify table exists
  const tables = await db.raw(`
    SELECT table_name FROM information_schema.tables
    WHERE table_schema = 'public'
  `);

  expect(tables.map(t => t.table_name)).toContain('users');
});

test('migration is reversible', async () => {
  await migrate();
  await rollback();

  // Table should be gone
  const tables = await db.raw(`
    SELECT table_name FROM information_schema.tables
    WHERE table_schema = 'public'
  `);

  expect(tables.map(t => t.table_name)).not.toContain('users');
});

test('migration preserves existing data', async () => {
  // Create data before migration
  await db.users.create({ email: '[email protected]' });

  // Run migration that adds 'age' column
  await migrate('add-age-column');

  // Data should still exist
  const user = await db.users.findOne({ email: '[email protected]' });
  expect(user).toBeDefined();
  expect(user.age).toBeNull(); // New column, null default
});

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

  try {
    await db.transaction(async (trx) => {
      await trx('users').insert({ email: '[email protected]' });
      await trx('users').insert({ email: '[email protected]' });

      // Force error
      throw new Error('Rollback test');
    });
  } catch (error) {
    // Expected
  }

  // No users should be inserted
  const finalCount = await db.users.count();
  expect(finalCount).toBe(initialCount);
});

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

  // Two concurrent withdrawals
  const withdraw1 = db.transaction(async (trx) => {
    const current = await trx('users').where({ id: user.id }).first();
    await sleep(100); // Simulate delay
    await trx('users').where({ id: user.id }).update({ 
      balance: current.balance - 50 
    });
  });

  const withdraw2 = db.transaction(async (trx) => {
    const current = await trx('users').where({ id: user.id }).first();
    await sleep(100);
    await trx('users').where({ id: user.id }).update({ 
      balance: current.balance - 50 
    });
  });

  await Promise.all([withdraw1, withdraw2]);

  // With proper isolation, balance should be 0, not 50
  const final = await db.users.findOne({ id: user.id });
  expect(final.balance).toBe(0); // Not 50!
});

test('user lookup by email is fast', async () => {
  // Seed 10,000 users
  await seedUsers(10000);

  const start = Date.now();
  await db.users.findOne({ email: '[email protected]' });
  const duration = Date.now() - start;

  // Should use index on email
  expect(duration).toBeLessThan(10); // < 10ms
});

test('EXPLAIN shows index usage', async () => {
  const explain = await db.raw(`
    EXPLAIN SELECT * FROM users WHERE email = '[email protected]'
  `);

  // Should show index scan, not sequential scan
  const plan = explain.rows[0]['QUERY PLAN'];
  expect(plan).toContain('Index Scan');
  expect(plan).not.toContain('Seq Scan');
});