21Database Testing

21.1 Why DB testing exists

An API can return 200 OK while the underlying DB silently corrupts data. UI can show "Saved" while the row never landed. DB tests verify the actual state of the world, not what the surface layer claims.

What to validate

• Data integrity — the row was written with the correct values, foreign keys point to valid records, NOT NULL constraints are honoured
• Counts — exactly one row inserted, zero rows after delete
• State transitions — order goes from pending → paid → shipped
• Audit / history — every change writes to audit_log; soft-deletes set deleted_at instead of removing
• Cross-table consistency — JOIN integrity, no orphans
• Calculated fields — totals match line items × prices

21.2 Where DB checks fit in an E2E flow

21.3 Connecting from a Node test (PostgreSQL example)

// pg connection pool as a worker-scoped fixture
import { test as base, expect } from '@playwright/test';
import { Pool } from 'pg';

type DBFixtures = { db: Pool };

export const test = base.extend<{}, DBFixtures>({
  db: [async ({}, use) => {
    const pool = new Pool({
      host: process.env.DB_HOST,
      port: 5432,
      user: process.env.DB_USER,
      password: process.env.DB_PASS,
      database: 'app_test',
      max: 5,
    });
    await use(pool);
    await pool.end();
  }, { scope: 'worker' }],
});
export { expect };

// Use it
test('order persists with correct total', async ({ page, db, request }) => {
  // 1. Trigger the write via UI
  await page.goto('/cart');
  await page.getByRole('button', { name: 'Place order' }).click();
  await expect(page.getByText('Order placed')).toBeVisible();

  // 2. Confirm the DB has it
  const { rows } = await db.query(
    `SELECT id, total, status FROM orders
     WHERE user_email = $1 ORDER BY created_at DESC LIMIT 1`,
    ['qa@test.com']
  );
  expect(rows).toHaveLength(1);
  expect(rows[0].status).toBe('pending');
  expect(Number(rows[0].total)).toBe(99.00);
});

21.4 JDBC example (Java)

import java.sql.*;

public class OrderDbCheck {
  private static final String URL = "jdbc:postgresql://localhost:5432/app_test";

  public Order findByEmail(String email) throws SQLException {
    try (Connection conn = DriverManager.getConnection(URL, "qa", "qa-pass");
         PreparedStatement ps = conn.prepareStatement(
           "SELECT id, total, status FROM orders WHERE user_email = ? " +
           "ORDER BY created_at DESC LIMIT 1")) {
      ps.setString(1, email);
      try (ResultSet rs = ps.executeQuery()) {
        if (!rs.next()) throw new AssertionError("No order for " + email);
        return new Order(rs.getString("id"),
                         rs.getBigDecimal("total"),
                         rs.getString("status"));
      }
    }
  }
}

// Test (TestNG)
@Test public void orderPersists() {
  // ... API call to create order ...
  Order o = new OrderDbCheck().findByEmail("qa@test.com");
  assertEquals(o.status, "pending");
  assertEquals(o.total, new BigDecimal("99.00"));
}

21.5 SQL essentials for SDETs

SELECT with WHERE

SELECT id, email, created_at
FROM users
WHERE email = 'alice@test.com';

COUNT

-- Did the test only insert one row?
SELECT COUNT(*) FROM orders WHERE user_email = 'qa@test.com';

-- Group by status
SELECT status, COUNT(*) FROM orders GROUP BY status;

JOIN — verify cross-table relationship

-- Every order has a valid user
SELECT o.id, u.email, o.total
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.created_at > NOW() - INTERVAL '1 minute';

-- Find orphans (orders pointing to deleted users)
SELECT o.id
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

Aggregations

-- Total spend per user
SELECT user_id, SUM(total) AS lifetime_value, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
ORDER BY lifetime_value DESC
LIMIT 10;

Audit trail check

-- Every change to orders.status should write to audit_log
SELECT al.action, al.changed_at
FROM audit_log al
WHERE al.entity = 'order' AND al.entity_id = 'ORD-123'
ORDER BY al.changed_at;

Soft-delete verification

-- After delete, row still exists but deleted_at is set
SELECT id, deleted_at
FROM users
WHERE email = 'qa@test.com';
-- expected: deleted_at IS NOT NULL

21.6 Typical SDET DB assertion patterns

Pattern 1 — verify UI/API write

test('signup creates a user record', async ({ page, db }) => {
  await page.goto('/signup');
  await page.getByLabel('Email').fill('new@test.com');
  await page.getByLabel('Password').fill('p4ssw0rd!');
  await page.getByRole('button', { name: 'Sign up' }).click();
  await expect(page.getByText('Welcome')).toBeVisible();

  const { rows } = await db.query(
    `SELECT email, email_verified FROM users WHERE email = $1`,
    ['new@test.com']
  );
  expect(rows[0].email).toBe('new@test.com');
  expect(rows[0].email_verified).toBe(false);   // initial state
});

Pattern 2 — state-machine validation

test('order moves pending → paid', async ({ page, db, request }) => {
  // create order via API
  const created = await (await request.post('/api/orders', { data: { items: [{ sku: 'A', qty: 1 }] } })).json();

  // verify initial state
  let r = await db.query(`SELECT status FROM orders WHERE id = $1`, [created.id]);
  expect(r.rows[0].status).toBe('pending');

  // simulate webhook
  await request.post('/api/webhooks/stripe', { data: { type: 'payment_succeeded', orderId: created.id }});

  // verify transitioned state (poll for eventual consistency)
  await expect.poll(async () => {
    const x = await db.query(`SELECT status FROM orders WHERE id = $1`, [created.id]);
    return x.rows[0].status;
  }, { timeout: 5_000 }).toBe('paid');
});

Pattern 3 — count / no duplicates

test('repeated webhook does not double-insert', async ({ db, request }) => {
  const payload = { id: 'evt_123', type: 'charge.refunded', orderId: 'ORD-1' };
  await request.post('/api/webhooks/stripe', { data: payload });
  await request.post('/api/webhooks/stripe', { data: payload });   // replay

  const { rows } = await db.query(
    `SELECT COUNT(*)::int AS n FROM refunds WHERE order_id = 'ORD-1'`
  );
  expect(rows[0].n).toBe(1);   // idempotent
});

21.7 Pitfalls

• Test isolation — tests writing to shared tables collide. Solutions: a unique tenant per parallelIndex slot, per-test schema, or transactions you roll back.
• Connection limits — DBs have max connections. Use a small pool per worker, share via worker-scoped fixture.
• Data leftovers — clean up in teardown (DELETE WHERE created_by = 'qa') so the next run starts clean.
• Asserting against prod data — never. Always against a dedicated test DB with seeded data.
• Read replicas — if reads come from a replica, allow for replication lag; poll.