Transactions & Stored Procedures

Use transactions for test isolation and understand stored procedures for complex validation.

Transactions for Test Isolation

Wrap each test in a transaction and roll it back — database returns to original state without deleting anything:

import { Client } from 'pg';
import { test, expect } from '@playwright/test';

let db: Client;

test.beforeAll(async () => {
  db = new Client({ connectionString: process.env.DATABASE_URL });
  await db.connect();
});

test.afterAll(async () => db.end());

test.beforeEach(async () => {
  await db.query('BEGIN');
});

test.afterEach(async () => {
  await db.query('ROLLBACK');
});

test('insert then assert within transaction', async () => {
  await db.query(
    `INSERT INTO products (name, price) VALUES ($1, $2)`,
    ['Test Widget', 9.99]
  );
  const res = await db.query(
    `SELECT * FROM products WHERE name = 'Test Widget'`
  );
  expect(res.rows).toHaveLength(1);
  // ROLLBACK in afterEach removes this row — no cleanup needed
});

ACID Properties

CTEs (Common Table Expressions)

CTEs make complex validation queries readable:

-- Find users who ordered but never paid
WITH ordered_users AS (
  SELECT DISTINCT user_id FROM orders WHERE status = 'completed'
),
paying_users AS (
  SELECT DISTINCT user_id FROM payments WHERE status = 'success'
)
SELECT u.email
FROM ordered_users o
LEFT JOIN paying_users p ON o.user_id = p.user_id
JOIN users u ON o.user_id = u.id
WHERE p.user_id IS NULL;

test('no completed orders without payment', async () => {
  const { rows } = await db.query(`
    WITH ordered AS (SELECT DISTINCT user_id FROM orders WHERE status = 'completed'),
         paid    AS (SELECT DISTINCT user_id FROM payments WHERE status = 'success')
    SELECT o.user_id FROM ordered o LEFT JOIN paid p USING(user_id) WHERE p.user_id IS NULL
  `);
  expect(rows).toHaveLength(0);
});

Window Functions

Useful for asserting order or ranking:

-- Check order of events
SELECT
  id,
  event_type,
  created_at,
  LAG(created_at) OVER (ORDER BY created_at) AS prev_event_at
FROM audit_events
WHERE entity_id = 42
ORDER BY created_at;

Stored Procedures

Call stored procedures from tests to exercise complex business logic:

test('stored proc calculates discount correctly', async () => {
  // Insert test data
  await db.query(`INSERT INTO customers (id, tier) VALUES (999, 'gold')`);

  // Call stored procedure
  const { rows } = await db.query(
    `SELECT calculate_discount($1, $2) AS discount`,
    [999, 100.00]
  );
  expect(rows[0].discount).toBe(15.00);  // gold tier = 15%

  // Cleanup (or use transaction pattern)
  await db.query(`DELETE FROM customers WHERE id = 999`);
});

Testing Constraints

Verify that database constraints work:

test('unique email constraint prevents duplicate users', async () => {
  await db.query(`INSERT INTO users (email) VALUES ('dup@test.com')`);

  await expect(
    db.query(`INSERT INTO users (email) VALUES ('dup@test.com')`)
  ).rejects.toThrow('duplicate key value violates unique constraint');
});

test('NOT NULL constraint on required fields', async () => {
  await expect(
    db.query(`INSERT INTO orders (user_id) VALUES (1)`)  // total is NOT NULL
  ).rejects.toThrow('null value in column "total"');
});