How to Seed a Database: PostgreSQL Practical Guide

Seeding with your ORM

Most Node.js setups use an ORM or query builder with its own seeding convention. Pick whichever matches your stack.

Prisma

Create prisma/seed.ts and configure it in prisma.config.ts:

// prisma/seed.ts
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();

async function main() {
  const team = await prisma.team.upsert({
    where: { id: 1 },
    update: {},
    create: { name: "Engineering" },
  });

  await prisma.user.upsert({
    where: { email: "alice@example.com" },
    update: {},
    create: {
      email: "alice@example.com",
      teamId: team.id,
      roleId: 1,
    },
  });
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

// prisma.config.ts
import { defineConfig, env } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
    seed: "tsx prisma/seed.ts",
  },
  datasource: { url: env("DATABASE_URL") },
});

Run it:

npx prisma db seed
# Reset and reseed in one step
npx prisma migrate reset

If your Postgres is Prisma's own managed product rather than a self-hosted instance, this same command can fail in a way that has nothing to do with the seed script itself — a dedicated Prisma Postgres walkthrough covers the direct-vs-pooled connection string wrinkle that trips people up first.

Drizzle

Drizzle has no built-in seed command — you write a Node.js script and run it directly:

// scripts/seed.ts
import { db } from "../src/db";
import { teams, users } from "../src/schema";

async function seed() {
  const [team] = await db
    .insert(teams)
    .values({ name: "Engineering" })
    .onConflictDoUpdate({ target: teams.id, set: { name: "Engineering" } })
    .returning();

  await db
    .insert(users)
    .values({ email: "alice@example.com", teamId: team.id, roleId: 1 })
    .onConflictDoNothing();
}

seed().catch(console.error);

tsx scripts/seed.ts
# Or add to package.json scripts: "seed": "tsx scripts/seed.ts"
npm run seed

TypeORM

// src/database/seed.ts
import { AppDataSource } from "./data-source";
import { Team } from "../entities/Team";
import { User } from "../entities/User";

async function seed() {
  await AppDataSource.initialize();

  const teamRepo = AppDataSource.getRepository(Team);
  const userRepo = AppDataSource.getRepository(User);

  const team = await teamRepo.save({ id: 1, name: "Engineering" });
  await userRepo.save({ email: "alice@example.com", team, roleId: 1 });

  await AppDataSource.destroy();
}

seed().catch(console.error);

ts-node src/database/seed.ts

Raw SQL with node-postgres

When you're not using an ORM:

// scripts/seed.ts
import { Pool } from "pg";

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

async function seed() {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    await client.query(`
      INSERT INTO teams (id, name) VALUES (1, 'Engineering')
      ON CONFLICT (id) DO NOTHING
    `);

    await client.query(`
      INSERT INTO users (email, team_id, role_id)
      VALUES ('alice@example.com', 1, 1)
      ON CONFLICT (email) DO NOTHING
    `);

    await client.query("COMMIT");
  } catch (e) {
    await client.query("ROLLBACK");
    throw e;
  } finally {
    client.release();
  }
}

seed()
  .catch(console.error)
  .finally(() => pool.end());

Seed a PostgreSQL database with Seedfast, with no seed file to maintain

Seedfast connects to your PostgreSQL database and reads the live schema (tables, columns, types, constraints, foreign keys), then generates a dataset that satisfies all of it. You describe the domain in plain English, and every row it writes comes out valid and connected.

# Install
npm install -g seedfast
# or: brew install seedfast

# Connect to your database
seedfast connect

# Generate data from the current schema
seedfast seed --scope "small engineering team with a few projects and task assignments"

When a migration adds a new column or table, the next seedfast seed picks it up automatically, without a seed file to chase down.

# Different scopes for different environments
seedfast seed --scope "2 teams, 5 users, minimal product catalog"   # dev
seedfast seed --scope "3 users with 5 completed orders each"         # CI test
seedfast seed --scope "realistic e-commerce, 500 products, reviews"  # staging

Seedfast talks to Postgres directly rather than through ORM abstractions, so it works alongside Prisma, Drizzle, TypeORM, and Sequelize without caring which one owns the schema. Run your migrations first, then run Seedfast.

Making seeds idempotent

An idempotent seed runs successfully whether or not the data already exists. This matters because seeds run in CI on every pipeline, and you want them to succeed on retry without manual cleanup.

ON CONFLICT DO NOTHING

The simplest approach — skip the insert if the row already exists:

INSERT INTO roles (id, name) VALUES
  (1, 'admin'),
  (2, 'editor')
ON CONFLICT (id) DO NOTHING;

It works when you don't need to update existing rows.

ON CONFLICT DO UPDATE (upsert)

When you want the seed data to reflect the latest values:

INSERT INTO roles (id, name) VALUES
  (1, 'admin'),
  (2, 'editor')
ON CONFLICT (id) DO UPDATE
  SET name = EXCLUDED.name;

TRUNCATE + RESTART IDENTITY

Wiping and reseeding from scratch is fast and reliable for development, though it means losing anything already in the tables:

TRUNCATE roles, teams, users RESTART IDENTITY CASCADE;

INSERT INTO roles (name) VALUES ('admin'), ('editor'), ('viewer');
INSERT INTO teams (name) VALUES ('Engineering'), ('Design');
INSERT INTO users (email, team_id, role_id) VALUES
  ('alice@example.com', 1, 1),
  ('bob@example.com', 2, 2);

RESTART IDENTITY resets sequences (auto-increment IDs back to 1). CASCADE truncates dependent tables in the right order so FK constraints don't block the truncate. Use this only when you're starting fresh and don't need to preserve existing data.

COPY FROM for large seed datasets

When you need to seed tens of thousands of rows, INSERT is slow. PostgreSQL's COPY protocol loads data from a file 5–10x faster:

COPY products (name, price, category_id) FROM '/tmp/products.csv' CSV HEADER;

Or pipe data directly without a file:

psql "$DATABASE_URL" -c "COPY products (name, price, category_id) FROM STDIN CSV" < products.csv

It's useful for staging databases that need thousands of realistic rows. For schemas with FK constraints between tables, still seed referenced tables first.

Seeding by environment

Different environments need different data volumes. Trying to run the same seed everywhere usually means either too little data in staging or too long a runtime in CI.

Local development

Local dev seeds should be small and fast, scoped to whatever feature you're actively building:

# Enough to log in and navigate
seedfast seed --scope "2 teams, 5 users per team, 10 products"
# or a hand-written file for simple schemas
psql "$DATABASE_URL" -f seeds/dev.sql

Test / CI

CI wants focused datasets built for one scenario at a time, since each test suite only needs to seed what that scenario touches:

# Minimal dataset for CI
psql "$DATABASE_URL" -f seeds/test-baseline.sql

Or per-test using a tool that resets between runs:

# GitHub Actions
- name: Run migrations
  run: npx prisma migrate deploy

- name: Seed test database
  run: npm run seed:test

- name: Run tests
  run: npm test

Staging

Staging needs enough data to demo the application realistically, which is exactly where hand-written seeds stop scaling: a database for an e-commerce app might need 500 products, realistic price ranges, reviews that reference real products, and users with full order histories.

# Schema-aware staging seed (no seed file to maintain)
seedfast seed --scope "e-commerce with 500 electronics products, reviews, and 50 users with order histories"

When your seed breaks

The most common cause of a broken seed is a migration that adds a NOT NULL column. Your INSERT statements don't include the new column, the database rejects them, and suddenly nobody on the team can run the app locally until someone fixes the seed file.

That's because hand-written seeds are static, describing the schema at the moment you wrote them — and every subsequent migration drifts them a little further from reality.

Fixing it always looks the same — find the failing INSERT, work out what the migration added or changed, then update, test, and commit the seed. On an active codebase with weekly migrations, this becomes a recurring task with no clear owner.

There are two ways out:

Keep the seed file and add discipline. Pair every migration PR with a seed-file update and make it part of your PR checklist. This works for teams with stable schemas and small seed files — and it stops scaling around 20 tables and monthly migrations.

Use Seedfast. It picks up new columns and tables the moment they exist, so there is nothing for a PR checklist to enforce and the seed can't quietly fall out of sync. The best AI test data generator comparison ranks it against the other schema-aware options if you want the survey before the commitment.

What production schemas actually look like

Seedfast has seeded 188 Postgres schemas since November 2025, which gives that "around 20 tables" threshold some empirical footing. The median schema carries 11 tables, roughly a third cross 20, a quarter carry 25 or more, and the largest reached 248, while a typical run wraps up within a few minutes of starting. Volumes stay fixture-sized for most runs, hundreds of rows rather than bulk loads, which is one reason the maintenance cost of a hand-kept file dominates long before insert speed does. These figures come from run metadata alone, with nothing read out of customer databases.

Running seeds in CI/CD

In a pipeline, seeding should be a single step after migrations, with no "run these three scripts in this order" choreography and no shared state between parallel test runs.

# .github/workflows/test.yml
jobs:
  test:
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: testdb

    steps:
      - uses: actions/checkout@v4

      - name: Install dependencies
        run: npm ci

      - name: Run migrations
        run: npx prisma migrate deploy
        env:
          DATABASE_URL: postgres://postgres:postgres@localhost/testdb

      - name: Seed database
        run: npm run seed
        # or: seedfast seed --scope "test baseline"
        env:
          DATABASE_URL: postgres://postgres:postgres@localhost/testdb

      - name: Run tests
        run: npm test
        env:
          DATABASE_URL: postgres://postgres:postgres@localhost/testdb

Keep seeds idempotent (ON CONFLICT DO NOTHING or upsert) so pipeline retries don't fail. For parallel test jobs, use isolated databases per job rather than a shared one — concurrent INSERTs from different test suites against the same tables produce inconsistent state.

For deeper CI/CD patterns, see CI/CD database seeding docs.

Frequently asked questions

docker exec -i <container_name> psql -U postgres -d mydb < seed.sql

docker exec -it <container_name> psql -U postgres -d mydb -f /docker-entrypoint-initdb.d/seed.sql

Related guides

• Database Seeding: From Your First seed.sql to Fully Automated — the conceptual companion to this guide, covering what seeding is, why seeds break, and the full spectrum of approaches
• Database Seeder: 7 Tools and Per-ORM Quick Reference — comparison of seeder tools side-by-side, with command syntax for Laravel, Prisma, Drizzle, TypeORM, and EF Core
• Seed File Maintenance: When Your Seeds Fall Out of Sync — the long-term cost of maintaining static seeds on an active codebase
• Test Data Management: Pillars, Tools, and a Working Framework — the strategy layer above seeding: where test data comes from, how it stays valid, and who governs it
• How to Seed a Neon Database — Neon-specific guide covering pooled vs unpooled connections, branch inheritance, and CI/CD preview branches for serverless Postgres
• How to Seed a Supabase Database — Supabase-specific guide covering seed.sql, supabase db reset, auth.users, and preview branches
• Get started with Seedfast — connect to your PostgreSQL database and run your first schema-aware seed