Database Testing for SDETs: SQL Validation, Data Integrity, and Testcontainers

Your API test says 201 Created. But did the data actually land in the database correctly? Did the foreign keys resolve? Did the trigger fire? Database testing validates what API tests assume.

🎭 Want to master this with real projects? Join the Playwright Automation Mastery course at The Testing Academy.

Why SDETs Need Database Testing

• API returns 200 but writes wrong data to DB
• Race conditions cause duplicate records
• Cascading deletes break referential integrity
• Migrations fail silently on production data shapes
• Stored procedures have untested edge cases

Database Test Patterns

import { Client } from 'pg';

const db = new Client({ connectionString: process.env.DATABASE_URL });
await db.connect();

test('API creates user with correct DB state', async ({ request }) => {
  // Act: Create user via API
  const response = await request.post('/api/users', {
    data: { name: 'Jane', email: 'jane@test.com', role: 'admin' }
  });
  const user = await response.json();

  // Assert: Verify database state directly
  const result = await db.query('SELECT * FROM users WHERE id = $1', [user.id]);
  expect(result.rows[0].name).toBe('Jane');
  expect(result.rows[0].email).toBe('jane@test.com');
  expect(result.rows[0].role).toBe('admin');
  expect(result.rows[0].created_at).toBeDefined();

  // Cleanup
  await db.query('DELETE FROM users WHERE id = $1', [user.id]);
});

Testing Data Integrity

test('cascading delete removes child records', async ({ request }) => {
  // Setup: Create parent with children
  const parent = await createUser(request, { name: 'Parent' });
  await createOrder(request, { userId: parent.id, total: 100 });
  await createOrder(request, { userId: parent.id, total: 200 });

  // Verify children exist
  const before = await db.query('SELECT count(*) FROM orders WHERE user_id = $1', [parent.id]);
  expect(parseInt(before.rows[0].count)).toBe(2);

  // Delete parent
  await request.delete('/api/users/' + parent.id);

  // Verify cascade
  const after = await db.query('SELECT count(*) FROM orders WHERE user_id = $1', [parent.id]);
  expect(parseInt(after.rows[0].count)).toBe(0);
});

test('no orphan records after deletion', async () => {
  const result = await db.query(`
    SELECT o.id FROM orders o
    LEFT JOIN users u ON o.user_id = u.id
    WHERE u.id IS NULL
  `);
  expect(result.rows.length).toBe(0);
});

Testcontainers for Isolated DB Testing

import { PostgreSqlContainer } from '@testcontainers/postgresql';

let container;

test.beforeAll(async () => {
  container = await new PostgreSqlContainer()
    .withDatabase('testdb')
    .start();
  // Run migrations
  await runMigrations(container.getConnectionUri());
});

test.afterAll(async () => {
  await container.stop();
});

SQL Test Checklist

Check	SQL Pattern
No orphans	LEFT JOIN … WHERE parent.id IS NULL
No duplicates	GROUP BY … HAVING count(*) > 1
FK integrity	Foreign key constraint violations
Null checks	WHERE required_column IS NULL
Data ranges	WHERE price < 0 OR quantity < 0
Timestamps	WHERE created_at > updated_at