Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 36 ยท Migrations

Seeding and Data Migrations

Write an idempotent Prisma seed script with upsert, configure prisma db seed, and learn the difference between seed data, reference data and one-off data migrations.

A fresh database is empty, and an empty app is hard to develop, demo or test. Seeding means filling the database with starting data using a script you can run repeatedly. A related but different job is a data migration: transforming data that already exists when the schema changes. This lesson covers both and shows how to write seed scripts that are safe to run twice.

The Prisma code is read-only (it needs the npm package and a database). The TryIts are labelled stand-ins on node:sqlite that show the same logic.

The seed script

By convention it is prisma/seed.ts. In Prisma 7 you tell the CLI how to run it in prisma.config.ts:

TypeScript
import { defineConfig } from 'prisma/config';

export default defineConfig({
  schema: 'prisma/schema.prisma',
  migrations: {
    path: 'prisma/migrations',
    seed: 'tsx prisma/seed.ts',
  },
});

Then run it with npx prisma db seed. migrate reset also runs it after replaying migrations. (Prisma 7's migrate dev no longer seeds automatically, so call db seed yourself.) A minimal script:

TypeScript
import { PrismaClient } from './generated/client';
import { PrismaPg } from '@prisma/adapter-pg';

const prisma = new PrismaClient({
  adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }),
});

async function main() {
  await prisma.user.upsert({
    where: { email: '[email protected]' },
    update: {},
    create: { email: '[email protected]', name: 'Ada Lovelace' },
  });
}

main().finally(() => prisma.$disconnect());

Make it idempotent with upsert

A script is idempotent when running it ten times leaves the same result as running it once. Plain create fails the second time (unique constraint) or silently duplicates rows. upsert means "update if it exists, otherwise create", and it needs a field that identifies the row: an @id or @unique field.

  • update: {} leaves an existing row untouched, which suits starter data a user may have edited.
  • update: { name: 'New name' } forces the seed values back, which suits reference data like country lists.
Node.js Stand-in: create vs upsert when seeding twice

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Prisma's upsert maps to the same idea (PostgreSQL and SQLite can run it as one INSERT ... ON CONFLICT statement when the shape allows it, otherwise as a select then write).

Seeding relations

Seed related data in one nested write so everything is created together:

TypeScript
await prisma.user.upsert({
  where: { email: '[email protected]' },
  update: {},
  create: {
    email: '[email protected]',
    name: 'Grace Hopper',
    posts: {
      create: [
        { title: 'Compilers', published: true },
        { title: 'Debugging the first bug', published: false },
      ],
    },
  },
});

For large volumes use createMany({ data: [...], skipDuplicates: true }), which runs one INSERT for many rows. (skipDuplicates is not available on SQLite in older versions; check the docs for your provider.)

For realistic demo data, a faker library produces names and text, but fix its random seed (faker.seed(42)) so every developer gets the same data.

Seed data, reference data, data migrations

These three get mixed up, and mixing them causes production bugs.

Kind Example Runs where How
Demo/test seed fake users and posts development, tests prisma db seed
Reference data roles, countries, plans every environment upsert in a seed or in a migration
Data migration split name into firstName and lastName once per environment SQL in a migration, or a one-off script

Never run the demo seed in production. Reference data that the app cannot work without is safer inside a migration (it ships with the schema) or in a script you run deliberately during deploy.

A data migration in practice

You want to turn name into firstName and lastName. Using expand and contract (see migration files and drift):

  1. Migration A adds nullable firstName and lastName.
  2. A backfill fills them in from name.
  3. Code switches to the new columns.
  4. Migration B makes them required and drops name.

The backfill belongs in raw SQL in migration A (edited with --create-only), because SQL runs inside the database in one statement, much faster than loading every row into Node:

SQL
UPDATE "User"
SET "firstName" = split_part("name", ' ', 1),
    "lastName"  = NULLIF(substr("name", length(split_part("name", ' ', 1)) + 2), '')
WHERE "firstName" IS NULL;

The WHERE firstName IS NULL makes it idempotent.

Node.js Stand-in: backfill a split name column in SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why use upsert instead of create in a seed script?

    upsert updates or creates based on a unique key, so the seed is idempotent.
  2. 2. Where do you configure the seed command in Prisma 7?

    prisma.config.ts holds the CLI configuration, including the seed command, and the command runs with npx prisma db seed.
  3. 3. Which belongs in a migration rather than a demo seed?

    Required reference data must exist in every environment, so it ships with the schema or runs during deploy.
  4. 4. What makes the SQL backfill safe to run twice?

    Limiting the update to rows where the new column is still null means a second run changes nothing.

Summary

  • Seed scripts live in prisma/seed.ts and run with npx prisma db seed, configured in prisma.config.ts.
  • Make seeds idempotent with upsert on an @id or @unique field.
  • Nested writes seed related rows together; createMany handles bulk data.
  • Keep demo data, reference data and data migrations separate; never seed demo data into production.
  • Do data migrations in steps, with idempotent SQL backfills.

Next: Transactions: Atomic Writes in Prisma