database-testing

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 |

---

```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('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('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('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

});

```

---

```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");

```

---

Memory Namespace

```

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'

});

```

---

• [test-data-management](../test-data-management/) - Generate test data
• [performance-testing](../performance-testing/) - Query performance
• [compliance-testing](../compliance-testing/) - Data protection

---

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.