Structuring complex test scenarios with proper isolation, transaction management, and multi-client patterns.

Overview

Complex RLS and database tests often require careful setup: creating users, seeding data, and testing access patterns. This skill covers the patterns for structuring these scenarios with proper test isolation.

The Two-Client Pattern

The getConnections() function returns multiple clients with different privilege levels:

import { getConnections, PgTestClient } from 'pgsql-test';

let db: PgTestClient; // App-level client (RLS-enforced) let pg: PgTestClient; // Superuser client (bypasses RLS) let teardown: () => Promise<void>;

beforeAll(async () => { ({ db, pg, teardown } = await getConnections()); });

afterAll(() => teardown());

When to Use Each Client

Use pg (superuser) for:

• Test setup that needs to bypass RLS

• Creating test users and seed data

• Administrative operations

• Verifying data exists regardless of RLS

Use db (app-level) for:

• Testing actual RLS behavior

• Simulating real application queries

• Verifying access control works correctly

Example: RLS Test Setup

const TEST_USER_1 = '00000000-0000-0000-0000-000000000001'; const TEST_USER_2 = '00000000-0000-0000-0000-000000000002';

beforeAll(async () => { ({ db, pg, teardown } = await getConnections());

// Use pg (superuser) to create test users - bypasses RLS await pg.query( INSERT INTO users (id, username) VALUES ($1, 'user1') ON CONFLICT DO NOTHING, [TEST_USER_1] ); await pg.query( INSERT INTO users (id, username) VALUES ($1, 'user2') ON CONFLICT DO NOTHING, [TEST_USER_2] );

// Create test data owned by user 1 await pg.query( INSERT INTO documents (owner_id, title) VALUES ($1, 'User 1 Doc'), [TEST_USER_1] ); });

// Now test RLS with db client it('user 1 can see their documents', async () => { db.setContext({ role: 'authenticated', 'jwt.claims.user_id': TEST_USER_1 }); const docs = await db.any('SELECT * FROM documents'); expect(docs.length).toBe(1); });

it('user 2 cannot see user 1 documents', async () => { db.setContext({ role: 'authenticated', 'jwt.claims.user_id': TEST_USER_2 }); const docs = await db.any('SELECT * FROM documents'); expect(docs.length).toBe(0); });

Transaction Management

Test Isolation with beforeEach/afterEach

Each test runs in its own transaction that rolls back after the test:

beforeEach(async () => { await db.beforeEach(); // BEGIN + SAVEPOINT });

afterEach(async () => { await db.afterEach(); // ROLLBACK TO SAVEPOINT + COMMIT });

This ensures tests don't affect each other - any data created during a test is rolled back.

What beforeEach/afterEach Do

// db.beforeEach() executes: await this.begin(); // BEGIN transaction await this.savepoint(); // SAVEPOINT "lqlsavepoint"

// db.afterEach() executes: await this.rollback(); // ROLLBACK TO SAVEPOINT "lqlsavepoint" await this.commit(); // COMMIT (the outer transaction)

The publish() Method

When you need data created in one client to be visible to another client (or to persist beyond the current transaction), use publish() :

it('cross-connection visibility', async () => { // Create data with db client db.setContext({ role: 'authenticated', 'jwt.claims.user_id': USER_ID }); await db.query(INSERT INTO items (name) VALUES ('test item'));

// Data is not yet visible to pg client (different connection) let pgItems = await pg.any('SELECT * FROM items WHERE name = $1', ['test item']); expect(pgItems.length).toBe(0);

// Publish makes data visible to other connections await db.publish();

// Now pg can see it pgItems = await pg.any('SELECT * FROM items WHERE name = $1', ['test item']); expect(pgItems.length).toBe(1); });

What publish() Does

// db.publish() executes: await this.commit(); // Make data visible to other sessions await this.begin(); // Start fresh transaction await this.savepoint(); // Maintain rollback harness await this.ctxQuery(); // Reapply setContext() settings

Setup Patterns

Pattern 1: Simple Setup in beforeAll

For tests that share the same seed data:

beforeAll(async () => { ({ db, pg, teardown } = await getConnections());

// Seed data once await pg.query(INSERT INTO users (id, name) VALUES ($1, 'Alice'), [USER_ID]); });

afterAll(() => teardown());

beforeEach(() => db.beforeEach()); afterEach(() => db.afterEach());

Pattern 2: Complex Setup with Transactions

For setup that requires multiple steps with intermediate commits:

beforeAll(async () => { ({ db, pg, teardown } = await getConnections());

// Start transaction on pg for setup await pg.begin(); await pg.savepoint();

// Create users await pg.query(INSERT INTO users (id, name) VALUES ($1, 'Alice'), [USER_1]); await pg.query(INSERT INTO users (id, name) VALUES ($1, 'Bob'), [USER_2]);

// Commit pg's work so db can see it await pg.commit(); await pg.begin(); await pg.savepoint();

// Now db can work with the users await db.begin(); await db.savepoint();

db.setContext({ role: 'authenticated', 'jwt.claims.user_id': USER_1 }); // ... additional setup with RLS context

await db.commit(); await db.begin(); await db.savepoint(); });

Pattern 3: Per-Describe Setup

For describe blocks that need their own isolated setup:

describe('Admin scenarios', () => { let adminId: string;

beforeAll(async () => { // Create admin user for this describe block const result = await pg.one<{ id: string }>( INSERT INTO users (name, is_admin) VALUES ('Admin', true) RETURNING id ); adminId = result.id; });

beforeEach(() => db.beforeEach()); afterEach(() => db.afterEach());

it('admin can see all data', async () => { db.setContext({ role: 'authenticated', 'jwt.claims.user_id': adminId }); // ... }); });

Scenario Testing Pattern

For testing complex workflows with multiple actors:

describe('Organization membership scenarios', () => { const OWNER_ID = '00000000-0000-0000-0000-000000000001'; const MEMBER_ID = '00000000-0000-0000-0000-000000000002'; let orgId: string;

beforeAll(async () => { ({ db, pg, teardown } = await getConnections());

// Create test users
await pg.query(`INSERT INTO users (id, name) VALUES ($1, 'Owner')`, [OWNER_ID]);
await pg.query(`INSERT INTO users (id, name) VALUES ($1, 'Member')`, [MEMBER_ID]);

});

afterAll(() => teardown()); beforeEach(() => db.beforeEach()); afterEach(() => db.afterEach());

it('owner creates organization', async () => { db.setContext({ role: 'authenticated', 'jwt.claims.user_id': OWNER_ID });

const result = await db.one<{ id: string }>(
  `INSERT INTO organizations (name) VALUES ('Acme') RETURNING id`
);
orgId = result.id;

expect(orgId).toBeDefined();

});

it('owner can add members', async () => { db.setContext({ role: 'authenticated', 'jwt.claims.user_id': OWNER_ID });

// First recreate the org (previous test rolled back)
const org = await db.one<{ id: string }>(
  `INSERT INTO organizations (name) VALUES ('Acme') RETURNING id`
);

await db.query(
  `INSERT INTO memberships (org_id, user_id) VALUES ($1, $2)`,
  [org.id, MEMBER_ID]
);

const members = await db.any(
  `SELECT * FROM memberships WHERE org_id = $1`,
  [org.id]
);
expect(members.length).toBe(1);

}); });

Best Practices

• Use pg for setup, db for testing RLS behavior

• Always call beforeEach() /afterEach() for test isolation

• Use publish() when data needs to be visible across connections

• Keep test user IDs as constants for consistency

• Structure complex scenarios with clear beforeAll setup

• Remember that each test's changes are rolled back - don't depend on previous test state

• Use descriptive test names that explain the scenario being tested