Back to Details

database-testing

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

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>
<default_to_action> 在测试数据库操作时：
  1. 验证架构（表、列、约束是否符合预期存在）
  2. 测试数据完整性（唯一约束、外键约束、检查约束）
  3. 验证迁移（正向执行正常、回滚正常、数据保留完整）
  4. 检查事务隔离（ACID属性、并发访问）
  5. 衡量查询性能（索引使用情况、执行时间）
快速数据库测试检查清单：
  • 架构与规范一致
  • 唯一约束防止重复数据
  • 外键防止孤立记录
  • 迁移可回滚
  • 事务在出错时回滚
关键成功因素：
  • 数据库漏洞会导致数据丢失/损坏（后果严重）
  • 上线前在预发布环境测试迁移
  • 事务测试捕获并发漏洞 </default_to_action>

Quick Reference Card

快速参考卡片

When to Use

适用场景

  • New table/schema creation
  • Migration development
  • Data integrity validation
  • Query performance optimization
  • 新建表/架构
  • 迁移开发
  • 数据完整性验证
  • 查询性能优化

Database Test Types

数据库测试类型

TypeFocusWhen
SchemaStructure correctTable creation
IntegrityConstraints workData operations
MigrationUp/down workSchema changes
TransactionACID propertiesConcurrent access
PerformanceQuery speedOptimization
测试类型核心关注点适用时机
架构测试结构符合预期创建表时
完整性测试约束生效数据操作时
迁移测试正向/回滚正常架构变更时
事务测试ACID属性并发访问时
性能测试查询速度优化时

ACID Properties

ACID属性

PropertyTest Focus
AtomicityAll or nothing (rollback on error)
ConsistencyConstraints always valid
IsolationConcurrent transactions don't interfere
DurabilityCommitted data persists
属性测试重点
Atomicity（原子性）要么全部完成要么全部回滚（出错时回滚）
Consistency（一致性）约束始终有效
Isolation（隔离性）并发事务互不干扰
Durability（持久性）已提交数据永久保存

Schema Testing

架构测试

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

数据完整性测试

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

迁移测试

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

事务测试

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

Agent驱动的数据库测试

typescript
// 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");
typescript
// 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

Agent协作提示

Memory Namespace

内存命名空间

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

Fleet Coordination

集群协作

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

Related Skills

相关技能

  • test-data-management - Generate test data
  • performance-testing - Query performance
  • compliance-testing - Data protection
  • test-data-management - 生成测试数据
  • performance-testing - 查询性能测试
  • compliance-testing - 数据保护合规测试

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.
数据库漏洞后果严重。数据丢失无法恢复，数据损坏会悄然扩散，性能问题会不断恶化。
上线前务必测试迁移：正向执行正常、回滚正常、数据保留完整、性能符合要求。绝不部署未经过测试的迁移。
借助Agent：
qe-test-data-architect
可生成符合引用完整性的真实测试数据。
qe-test-executor
可在CI/CD中自动验证迁移。