Seeding Test Databases with pgsql-test

Load test data efficiently using loadJson, loadSql, and loadCsv methods. Create maintainable, realistic test fixtures.

When to Apply

Use this skill when:

• Setting up test data for database tests

• Loading fixtures from JSON, SQL, or CSV files

• Seeding data that respects or bypasses RLS

• Creating per-test or shared test data

Seeding Methods Overview

Method Best For RLS Behavior

loadJson()

Inline data, small datasets Respects RLS (use pg to bypass)

loadSql()

Complex data, version-controlled fixtures Respects RLS (use pg to bypass)

loadCsv()

Large datasets, spreadsheet exports Bypasses RLS (uses COPY)

Seeding with loadJson()

Best for inline test data. Clean, readable, and type-safe.

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

let pg: PgTestClient; let db: PgTestClient; let teardown: () => Promise<void>;

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

// Seed using superuser to bypass RLS await pg.loadJson({ 'app.users': [ { id: '550e8400-e29b-41d4-a716-446655440001', email: 'alice@example.com', name: 'Alice' }, { id: '550e8400-e29b-41d4-a716-446655440002', email: 'bob@example.com', name: 'Bob' } ], 'app.posts': [ { id: 'post-1', title: 'First Post', owner_id: '550e8400-e29b-41d4-a716-446655440001' }, { id: 'post-2', title: 'Second Post', owner_id: '550e8400-e29b-41d4-a716-446655440002' } ] }); });

afterAll(async () => { await teardown(); });

Key features:

• Schema-qualified table names: 'app.users'

• Explicit UUIDs for referential integrity

• Multiple tables in one call

• Order matters for foreign keys

Seeding with loadSql()

Best for complex data or version-controlled fixtures.

Create tests/fixtures/seed.sql :

-- Insert users INSERT INTO app.users (id, email, name) VALUES ('550e8400-e29b-41d4-a716-446655440001', 'alice@example.com', 'Alice'), ('550e8400-e29b-41d4-a716-446655440002', 'bob@example.com', 'Bob'), ('550e8400-e29b-41d4-a716-446655440003', 'charlie@example.com', 'Charlie');

-- Insert posts with foreign key references INSERT INTO app.posts (id, title, owner_id) VALUES ('post-1', 'Alice Post 1', '550e8400-e29b-41d4-a716-446655440001'), ('post-2', 'Alice Post 2', '550e8400-e29b-41d4-a716-446655440001'), ('post-3', 'Bob Post', '550e8400-e29b-41d4-a716-446655440002');

Load in tests:

import path from 'path';

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

await pg.loadSql([ path.join(__dirname, 'fixtures/seed.sql') ]); });

Multiple SQL files:

await pg.loadSql([ path.join(__dirname, 'fixtures/users.sql'), path.join(__dirname, 'fixtures/posts.sql'), path.join(__dirname, 'fixtures/comments.sql') ]);

Files execute in order, so put parent tables first.

Seeding with loadCsv()

Best for large datasets or spreadsheet exports.

Create tests/fixtures/users.csv :

id,email,name 550e8400-e29b-41d4-a716-446655440001,alice@example.com,Alice 550e8400-e29b-41d4-a716-446655440002,bob@example.com,Bob 550e8400-e29b-41d4-a716-446655440003,charlie@example.com,Charlie

Load in tests:

import path from 'path';

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

await pg.loadCsv({ 'app.users': path.join(__dirname, 'fixtures/users.csv'), 'app.posts': path.join(__dirname, 'fixtures/posts.csv') }); });

Important: loadCsv() uses PostgreSQL's COPY command, which bypasses RLS. Always use pg (superuser) client for CSV loading.

Combining Seeding Strategies

Mix methods based on data characteristics:

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

// 1. Load large reference data from CSV await pg.loadCsv({ 'app.categories': path.join(__dirname, 'fixtures/categories.csv') });

// 2. Load complex relationships from SQL await pg.loadSql([ path.join(__dirname, 'fixtures/users-with-roles.sql') ]);

// 3. Add test-specific data inline await pg.loadJson({ 'app.posts': [ { title: 'Test Post', owner_id: testUserId, category_id: 1 } ] }); });

Per-Test Seeding

When different tests need different data, seed in beforeEach() :

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

afterAll(async () => { await teardown(); });

beforeEach(async () => { await pg.beforeEach(); await db.beforeEach(); });

afterEach(async () => { await db.afterEach(); await pg.afterEach(); });

describe('empty state tests', () => { it('handles no data gracefully', async () => { const result = await db.query('SELECT COUNT(*) FROM app.posts'); expect(result.rows[0].count).toBe('0'); }); });

describe('populated state tests', () => { beforeEach(async () => { await pg.loadJson({ 'app.posts': [ { title: 'Test Post', owner_id: userId } ] }); });

it('finds existing posts', async () => { const result = await db.query('SELECT COUNT(*) FROM app.posts'); expect(result.rows[0].count).toBe('1'); }); });

RLS-Aware Seeding

When testing RLS, seed with the appropriate client:

// Bypass RLS for setup (use pg) await pg.loadJson({ 'app.posts': [{ title: 'Admin Post', owner_id: adminId }] });

// Respect RLS for user operations (use db with context) db.setContext({ role: 'authenticated', 'request.jwt.claim.sub': userId });

await db.loadJson({ 'app.posts': [{ title: 'User Post', owner_id: userId }] });

Fixture Organization

Recommended structure:

tests/ ├── fixtures/ │ ├── users.csv │ ├── posts.csv │ ├── seed.sql │ └── complex-scenario.sql ├── users.test.ts ├── posts.test.ts └── rls.test.ts

Best Practices

• Use explicit IDs: Makes referential integrity predictable

• Order by dependencies: Parent tables before child tables

• Keep fixtures minimal: Only seed what tests need

• Use pg for setup: Bypass RLS during seeding

• Use db for testing: Enforce RLS during assertions

• Version control fixtures: SQL/CSV files in repo

Troubleshooting

Issue Solution

Foreign key violation Load parent tables first

RLS blocking inserts Use pg client instead of db

CSV format errors Ensure headers match column names

Data persists between tests Check beforeEach/afterEach hooks

References

• Related skill: pgsql-test-rls for RLS testing patterns

• Related skill: pgsql-test-exceptions for handling errors

• Related skill: pgpm-testing for general test setup