Every project starts with a single seed.sql file. It is beautiful. Twenty INSERT statements with carefully chosen values, a few foreign keys that actually reference existing rows, and a comment explaining what each record represents. Life is good.

Six months later, that file is 2,000 lines long. Nobody knows which rows the tests depend on. Adding a new entity means wading through a maze of circular foreign key references. Your CI pipeline runs the seed file and gets primary key conflicts because a developer added a row by hand to their local database and exported the wrong dump. The test data is not reproducible, it is not versioned with the application code in any meaningful way, and every new engineer on the team spends their first week debugging a seed script instead of writing features.

There is a better way. Use factory functions. Define your test data as code, not as a flat file of SQL. This post is the production pattern for PostgreSQL seeding in TypeScript, with relationship management, sequence synchronization, and test isolation built in.

The three problems with flat SQL seed files

Before we talk about the solution, let’s name the specific pain points that a seed.sql file creates in a codebase that is actively developed.

Problem 1: Brittle primary key references. The file inserts a user with id = 1. Then an organization with owner_id = 1. Then a project with org_id = 1. Everything is fine until someone inserts a new table at the top of the file and the IDs shift. Now owner_id = 1 points to a different user or, worse, to nothing because the sequence auto-increment moved past that value.

Problem 2: No test isolation. Integration tests often share the same seed data set. Test A inserts a row expecting the total count to be 50. Test B, running in a different order on CI, also inserts a row and the count jumps to 51. Test A fails, but only on CI, and only sometimes. You spend an hour debugging before you realize the issue is shared mutable state in the seed data.

Problem 3: Stale data rot. The users table gets a new column. The application code and migrations are updated. The seed file is not. The next person to run npm run db:seed gets a NOT NULL violation on the new column and has to figure out what value to provide. This cycle repeats until nobody on the team trusts the seed file, and new hires are told “just use a production dump.”

The fix for all three problems is to treat seed data as executable code with deterministic relationships and per-test isolation.

Part 1: The factory pattern

A factory is a function that generates a row. You call it with any overrides you need, and it fills in the rest with sensible defaults. The defaults are what make factories powerful: they let you express only what is relevant to the test while guaranteeing that the database constraints are satisfied.

Here is a minimal user factory in TypeScript using raw SQL queries through the pg driver:

import { Pool, QueryResult } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

interface UserSeed {
  email: string;
  name: string;
  role: 'admin' | 'member';
}

async function createUser(overrides: Partial<UserSeed> = {}): Promise<UserSeed & { id: number }> {
  const row: UserSeed = {
    email: `user_${Date.now()}_${Math.random().toString(36).slice(2, 8)}@example.com`,
    name: 'Test User',
    role: 'member',
    ...overrides,
  };

  const result: QueryResult = await pool.query(
    `INSERT INTO users (email, name, role) VALUES ($1, $2, $3) RETURNING id, email, name, role`,
    [row.email, row.name, row.role]
  );

  return result.rows[0];
}

The defaults are designed to be unique (the random suffix on the email) and valid (the role is one of the enum values). The caller only provides what matters for the specific test:

const admin = await createUser({ role: 'admin' });
const guest = await createUser({ name: 'Guest User' });

The pattern scales to any table. You add a factory for each entity, and the factories compose through relationships.

Part 2: Handling foreign key relationships

The real value of factories appears when you need to create related rows. A post belongs to a user. A comment belongs to a post and a user. With a seed SQL file, you have to insert the parent rows first and manually track their IDs. With factories, the parent creation is implicit.

interface PostSeed {
  title: string;
  content: string;
  author_id?: number;
  published: boolean;
}

async function createPost(overrides: Partial<PostSeed> = {}): Promise<PostSeed & { id: number }> {
  const author = overrides.author_id
    ? { id: overrides.author_id }
    : await createUser();

  const row = {
    title: 'Test Post Title',
    content: 'Test post content that is long enough to pass validation.',
    author_id: author.id,
    published: true,
    ...overrides,
  };

  const result = await pool.query(
    `INSERT INTO posts (title, content, author_id, published)
     VALUES ($1, $2, $3, $4) RETURNING *`,
    [row.title, row.content, row.author_id, row.published]
  );

  return result.rows[0];
}

Now a test that needs a post with comments does not have to manually wire up the IDs:

const post = await createPost({ title: 'Debugging Nightmares' });
const comment1 = await createComment({ post_id: post.id });
const comment2 = await createComment({ post_id: post.id, author_id: post.author_id });

The factory handles the implicit dependency. If you need a specific author, pass author_id. If you do not care, the factory creates one for you.

Part 3: The builder wrapper for complex setups

When a test needs a specific constellation of related entities, raw factory calls can become verbose. A builder pattern gives you a fluent interface that collects the setup and executes it in dependency order.

class TestScenario {
  private users: Partial<UserSeed>[] = [];
  private posts: Partial<PostSeed>[] = [];
  private comments: Partial<CommentSeed>[] = [];

  withUser(overrides: Partial<UserSeed>): this {
    this.users.push(overrides);
    return this;
  }

  withPost(overrides: Partial<PostSeed>): this {
    this.posts.push(overrides);
    return this;
  }

  withComment(overrides: Partial<CommentSeed>): this {
    this.comments.push(overrides);
    return this;
  }

  async build(): Promise<{
    users: UserSeed[];
    posts: PostSeed[];
    comments: CommentSeed[];
  }> {
    const users = await Promise.all(
      this.users.map((u) => createUser(u))
    );

    const posts = await Promise.all(
      this.posts.map((p) => createPost(p))
    );

    const comments = await Promise.all(
      this.comments.map((c) => createComment(c))
    );

    return { users, posts, comments };
  }
}

Test code becomes declarative:

const scenario = await new TestScenario()
  .withUser({ role: 'admin', name: 'Alice' })
  .withUser({ role: 'member', name: 'Bob' })
  .withPost({ title: 'Alice Post', author_id: 0 }) // 0 is placeholder, resolved in build
  .build();

The builder can handle the tedious parts of dependency resolution automatically, like matching placeholder IDs to the actual created rows.

Part 4: Resetting sequences

Factories that insert rows into tables with serial primary keys will advance the sequence. After a few hundred test runs, the IDs drift into the tens of thousands. This is not a correctness problem, but it makes debugging harder because the IDs in error messages bear no relation to the order you created the rows.

Add a helper that resets all sequences to a known state before seeding:

async function resetSequences(): Promise<void> {
  const result = await pool.query(`
    SELECT schemaname || '.' || tablename AS table_name
    FROM pg_tables
    WHERE schemaname = 'public'
  `);

  for (const { table_name } of result.rows) {
    await pool.query(
      `SELECT setval(pg_get_serial_sequence($1, 'id'), coalesce(max(id), 0) + 1, false)
       FROM ${table_name}`,
      [table_name]
    );
  }
}

Call resetSequences() at the start of your seed script or test suite setup. The first row inserted in each table will have id = 1, making test output readable.

Part 5: Test isolation with savepoints

The biggest source of flaky integration tests is shared database state between test cases. Factories make it easy to create data, but you still need to clean it up afterward.

The fastest isolation strategy is a savepoint per test:

async function setupTestDatabase(): Promise<void> {
  const client = await pool.connect();
  await client.query('BEGIN');

  return {
    client,
    async cleanup(): Promise<void> {
      await client.query('ROLLBACK');
      client.release();
    },
  };
}

Each test starts a transaction, creates whatever data it needs using factories, runs assertions, and rolls back the transaction at the end. No cleanup scripts, no truncation, no accidental interactions between tests.

describe('Post API', () => {
  let db: Awaited<ReturnType<typeof setupTestDatabase>>;

  beforeEach(async () => {
    db = await setupTestDatabase();
  });

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

  it('returns posts by author', async () => {
    const author = await createUser({ name: 'Alice' }, db.client);
    await createPost({ author_id: author.id, title: 'First Post' }, db.client);
    await createPost({ author_id: author.id, title: 'Second Post' }, db.client);

    const result = await getPostsByAuthor(author.id);
    expect(result).toHaveLength(2);
  });
});

Notice the second argument to createUser and createPost above. The factories should accept an optional client or pool so that they participate in the test transaction. Here is the pattern:

async function createUser(
  overrides: Partial<UserSeed> = {},
  client?: Pool | PoolClient
): Promise<UserSeed & { id: number }> {
  const db = client ?? pool;
  // ... rest of the function uses db instead of pool
}

Part 6: Bulk seeding for performance

Factories that fire individual INSERT queries are slow when you need to create hundreds of rows. A benchmark test that generates 500 users, 1,000 posts, and 3,000 comments would take several seconds with individual round trips.

For bulk seeding, batch the inserts:

async function createManyUsers(
  count: number,
  overrides: Partial<UserSeed> = {}
): Promise<UserSeed[]> {
  const values: string[] = [];
  const params: any[] = [];
  let paramIndex = 1;

  for (let i = 0; i < count; i++) {
    const email = `bulk_${i}_${Date.now()}@example.com`;
    const name = overrides.name ?? `User ${i}`;
    const role = overrides.role ?? 'member';

    values.push(`($${paramIndex}, $${paramIndex + 1}, $${paramIndex + 2})`);
    params.push(email, name, role);
    paramIndex += 3;
  }

  const result = await pool.query(
    `INSERT INTO users (email, name, role) VALUES ${values.join(', ')} RETURNING *`,
    params
  );

  return result.rows;
}

The pattern composes: you can batch-insert users and then batch-insert posts that reference those user IDs by index. This cuts the seeding time for large test datasets from minutes to seconds.

Part 7: Seed script that replaces your seed.sql

For local development, you still want a single command that fills the database with representative data. A well-structured seed script uses factories with deterministic defaults so the data is consistent across machines and checkouts.

// scripts/seed.ts
import { Pool } from 'pg';
import { createUser } from '../test/factories/users';
import { createPost } from '../test/factories/posts';
import { createComment } from '../test/factories/comments';
import { resetSequences } from '../test/factories/helpers';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function seed(): Promise<void> {
  console.log('Resetting sequences...');
  await resetSequences();

  console.log('Creating admin user...');
  const admin = await createUser({
    email: 'admin@practical.dev',
    name: 'Admin User',
    role: 'admin',
  });

  console.log('Creating sample data...');
  const users = await Promise.all([
    createUser({ email: 'alice@practical.dev', name: 'Alice' }),
    createUser({ email: 'bob@practical.dev', name: 'Bob' }),
    createUser({ email: 'carol@practical.dev', name: 'Carol' }),
  ]);

  const posts = await Promise.all(
    users.map((user, i) =>
      createPost({
        title: `Post by ${user.name}`,
        author_id: user.id,
        published: i % 2 === 0,
      })
    )
  );

  for (const post of posts.slice(0, 3)) {
    await createComment({
      post_id: post.id,
      author_id: admin.id,
      content: 'Great post! Looking forward to more content like this.',
    });
  }

  console.log(`Seeded: 1 admin, ${users.length} users, ${posts.length} posts.`);
}

seed()
  .then(() => {
    console.log('Seed complete.');
    process.exit(0);
  })
  .catch((err) => {
    console.error('Seed failed:', err);
    process.exit(1);
  });

This script is deterministic (same values every time), version-controlled alongside the application code, and easy to extend when new tables are added.

Part 8: Integrating with your test framework

The factories and builders become the foundation of your test suite. Every integration test gets a fresh transaction, creates only the data it needs, and cleans up automatically.

Here is the full setup pattern for Vitest:

// test/setup.ts
import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.TEST_DATABASE_URL });

beforeAll(async () => {
  // Run migrations on the test database
  await pool.query('RUN SOME MIGRATION OR SCHEMA SETUP');
});

afterAll(async () => {
  await pool.end();
});

export { pool };

// test/helpers/db.ts
import { pool } from '../setup';
import { PoolClient } from 'pg';

let client: PoolClient | null = null;

export async function beginTransaction(): Promise<void> {
  client = await pool.connect();
  await client.query('BEGIN');
}

export async function rollbackTransaction(): Promise<void> {
  if (client) {
    await client.query('ROLLBACK');
    client.release();
    client = null;
  }
}

export function getTestClient(): PoolClient {
  if (!client) throw new Error('No active transaction. Call beginTransaction() first.');
  return client;
}

Then in your tests:

import { describe, it, expect, beforeEach, afterEach } from 'vitest';
import { beginTransaction, rollbackTransaction, getTestClient } from '../helpers/db';
import { createUser } from '../factories/users';

describe('User Repository', () => {
  beforeEach(async () => {
    await beginTransaction();
  });

  afterEach(async () => {
    await rollbackTransaction();
  });

  it('creates a user with default values', async () => {
    const user = await createUser({}, getTestClient());
    expect(user.id).toBeGreaterThan(0);
    expect(user.role).toBe('member');
  });
});

Every test runs in its own transaction. No test can affect another. The factories accept the transaction client so all reads and writes happen inside the same database session.

What you get

Switching from a flat seed.sql file to factory-based seeding gives you these guarantees:

• Deterministic data. The same seed script produces the same rows every time, on every machine.
• Isolated tests. Each test creates only what it needs and rolls back. Zero shared state.
• Composable relationships. Factories handle foreign keys implicitly. You add a table, you add a factory, and every dependent factory works immediately.
• CI-friendly. No production dump files, no manual export steps. The seed script is pure code that runs anywhere Postgres runs.
• Debuggable failures. When a test fails, the error includes meaningful column values, not auto-incremented IDs that match nothing.

The factory pattern is not new. Ruby on Rails developers have used it for years through FactoryBot. Node.js libraries like factory.io and typeorm-seeding exist, but they add abstraction layers that break down when your schema gets complex. The raw SQL factory pattern I have shown here is library-agnostic, works with any query builder or ORM, and gives you full control over the INSERT statements your database actually executes.

Start with one factory for your most-used table. Add the builder next. Replace the seed.sql file last. By the time you are done, your test data will be as well-structured as your production code, and your CI pipeline will thank you.

A note from Yojji

Building robust test infrastructures with reproducible data is a hallmark of teams that ship with confidence rather than crossing their fingers. Yojji’s engineering teams treat test data management as a first-class concern in every project, integrating patterns like factory-based seeding into their client engagements from day one. With deep experience in the JavaScript ecosystem and production PostgreSQL, they help teams move from fragile seed scripts to reliable, maintainable test suites that actually prevent regressions instead of creating them.

Yojji is an international custom software development company with offices in Europe, the US, and the UK, specializing in full-cycle product development and dedicated team augmentation.