Skip to main content

Seeding

This guide describes how to seed your database using Prisma Client and Prisma ORM's integrated seeding functionality. Seeding allows you to consistently re-create the same data in your database and can be used to:

  • Populate your database with data that is required for your application to start, such as a default language or currency.
  • Provide basic data for validating and using your application in a development environment. This is particularly useful if you are using Prisma Migrate, which sometimes requires resetting your development database.

How to seed your database in Prisma ORM​

Prisma ORM's integrated seeding functionality expects a command in the "seed" key in the "prisma" key of your package.json file. This can be any command, prisma db seed will just execute it. In this guide and as a default, we recommend writing a seed script inside your project's prisma/ folder and starting it with the command.


"prisma": {
  "seed": "ts-node prisma/seed.ts"
},

info

With TypeScript,ts-node does transpiling and typechecking by default; typechecking can be disabled with the following flag --transpile-only.

Example: "seed": "ts-node --transpile-only prisma/seed.ts"

This can be useful to reduce memory usage (RAM) and increase execution speed of the seed script.

Integrated seeding with Prisma Migrate​

Database seeding happens in two ways with Prisma ORM: manually with prisma db seed and automatically in prisma migrate reset and (in some scenarios) prisma migrate dev.

With prisma db seed, you decide when to invoke the seed command. It can be useful for a test setup or to prepare a new development environment, for example.

Prisma Migrate also integrates seamlessly with your seeds, assuming you follow the steps in the section below. Seeding is triggered automatically when Prisma Migrate resets the development database.

Prisma Migrate resets the database and triggers seeding in the following scenarios:

  • You manually run the prisma migrate reset CLI command.
  • The database is reset interactively in the context of using prisma migrate dev - for example, as a result of migration history conflicts or database schema drift.
  • The database is actually created by prisma migrate dev, because it did not exist before.

When you want to use prisma migrate dev or prisma migrate reset without seeding, you can pass the --skip-seed flag.

Example seed scripts​

Here we suggest some specific seed scripts for different situations. You are free to customize these in any way, but can also use them as presented here:

Seeding your database with TypeScript or JavaScript​

If you're using TypeScript with PostgreSQL, SQLite or MySQL see also: @snaplet/seed.

  1. Create a new file named seed.ts. This can be placed anywhere within your project's folder structure. The example below places it in the /prisma folder.

  2. In the seed.ts file, import Prisma Client, initialize it and create some records. As an example, take the following Prisma schema with a User and Post model:

    schema.prisma
    model User {
      id    Int    @id @default(autoincrement())
      email String @unique
      name  String
      posts Post[]
    }

    model Post {
      id        Int     @id @default(autoincrement())
      title     String
      content   String
      published Boolean
      user      User    @relation(fields: [userId], references: [id])
      userId    Int
    }

    Create some new users and posts in your seed.ts file:

    seed.ts
    import { PrismaClient } from '@prisma/client'
    const prisma = new PrismaClient()
    async function main() {
      const alice = await prisma.user.upsert({
        where: { email: 'alice@prisma.io' },
        update: {},
        create: {
          email: 'alice@prisma.io',
          name: 'Alice',
          posts: {
            create: {
              title: 'Check out Prisma with Next.js',
              content: 'https://www.prisma.io/nextjs',
              published: true,
            },
          },
        },
      })
      const bob = await prisma.user.upsert({
        where: { email: 'bob@prisma.io' },
        update: {},
        create: {
          email: 'bob@prisma.io',
          name: 'Bob',
          posts: {
            create: [
              {
                title: 'Follow Prisma on Twitter',
                content: 'https://twitter.com/prisma',
                published: true,
              },
              {
                title: 'Follow Nexus on Twitter',
                content: 'https://twitter.com/nexusgql',
                published: true,
              },
            ],
          },
        },
      })
      console.log({ alice, bob })
    }
    main()
      .then(async () => {
        await prisma.$disconnect()
      })
      .catch(async (e) => {
        console.error(e)
        await prisma.$disconnect()
        process.exit(1)
      })

  3. Add typescript, ts-node and @types/node development dependencies:

    npm install -D typescript ts-node @types/node

  1. Add the prisma.seed field to your package.json file:

    package.json
    {
      "name": "my-project",
      "version": "1.0.0",
      "prisma": {
        "seed": "ts-node prisma/seed.ts"
      },
      "devDependencies": {
        "@types/node": "^14.14.21",
        "ts-node": "^9.1.1",
        "typescript": "^4.1.3"
      }
    }

    Some projects may require you to add compile options. When using Next.js for example, you would setup your seed script like so:

    package.json
    "prisma": {
      "seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts"
    },

  2. To seed the database, run the db seed CLI command:

    npx prisma db seed

Seeding your database via raw SQL queries​

You can also make use of raw SQL queries in order to seed the database with data.

While you can use a plain-text .sql file (such as a data dump) for that, it is often easier to place those raw queries, if they're of short size, into the seed.js file because it saves you the hassle of working out database connection strings and creating a dependency on a binary like psql.

To seed additional data to the schema.prisma above, add the following to the seed.js (or seed.ts) file:

seed.js
async function rawSql() {
  const result = await prisma.$executeRaw`INSERT INTO "User" ("id", "email", "name") VALUES (3, 'foo@example.com', 'Foo') ON CONFLICT DO NOTHING;`
  console.log({ result })
}

and chain this function to the promise calls, such as the following change towards the end of the file:

seed.js
main()
  .then(rawSql)
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

Seeding your database via any language (with a Bash script)​

In addition to TypeScript and JavaScript, you can also use a Bash script (seed.sh) to seed your database in another language such as Go, or plain SQL.


The following example runs a Go script in the same folder as seed.sh:


seed.sh
#!/bin/sh
# -e Exit immediately when a command returns a non-zero status.
# -x Print commands before they are executed
set -ex
# Seeding command
go run ./seed/

Seeding your database with @snaplet/seed​

@snaplet/seed offers a toolkit designed to understand your database schema, enabling you to generate production-accurate data and efficiently seed your database. It uses the full power of TypeScript to provide you with a type-safe and auto-completed experience to write your seed scripts.

@snaplet/seed supports PostgreSQL, SQLite and MySQL.

  1. Begin by setting up a local database using npx prisma migrate dev. This example will use the following Prisma schema featuring a User and Post model:

    schema.prisma
    model User {
      id    Int    @id @default(autoincrement())
      email String @unique
      name  String
      posts Post[]
    }

    model Post {
      id        Int     @id @default(autoincrement())
      title     String
      content   String
      published Boolean
      user      User    @relation(fields: [userId], references: [id])
      userId    Int
    }

  2. Execute npx @snaplet/seed init prisma/seed to initialize Snaplet Seed in your Prisma project. This command generates the seed client and creates an example seed file at prisma/seed/seed.ts.

  3. Use the generated @snaplet/seed client within the prisma/seed/seed.ts file to compose your seeds.

    Here's how to create new users and posts by modifying the initially generated prisma/seed/seed.ts file:

    prisma/seed/seed.ts
    /**
     * ! Executing this script will delete all data in your database and seed it with 10 users.
     * ! Make sure to adjust the script to your needs.
     * Use any TypeScript runner to run this script, for example: `npx tsx seed.ts`
     * Learn more about the Seed Client by following our guide: https://docs.snaplet.dev/seed/getting-started
     */
    import { createSeedClient } from "@snaplet/seed";

    async function main() {
      const seed = await createSeedClient();
      
      // Truncate all tables in the database
      await seed.$resetDatabase();
      
      // Seed the database with 10 users
      await seed.user((createMany) => createMany(10, {
        // Create 10 posts for each of those users
        posts: (createMany) => createMany(10),
      }))
      
      console.log("Database seeded successfully!");
      
      process.exit();
    };

    main();

  4. Add tsx (or any other typescript runners) as a development dependency:

    npm install -D tsx

  5. Insert the prisma.seed field into your package.json file and configure commands to seed your database:

    package.json
    {
      "name": "my-project",
      "version": "1.0.0",
      "prisma": {
        "seed": "tsx prisma/seed/seed.ts"
      },
      "devDependencies": {
        "@types/node": "^14.14.21",
        "tsx": "^4.7.2",
        "typescript": "^4.1.3"
      }
    }

  6. To seed the database, execute the following CLI command:

    npx prisma db seed

  7. To ensure the seed client remains synchronized with your schema, add a migrate and postmigrate scripts to the package.json:

    package.json
    {
      "name": "my-project",
      "version": "1.0.0",
      "prisma": {
        "seed": "npx tsx prisma/seed/seed.ts"
      },
      "scripts": {
        "migrate": "prisma migrate dev",
        "postmigrate": "npx @snaplet/seed sync"
      },
      "devDependencies": {
        "@types/node": "^14.14.21",
        "tsx": "^4.7.2",
        "typescript": "^4.1.3"
      }
    }

Now, instead of npx prisma migrate dev you can run npm run migrate which will also sync the seed client after schema changes and keep both your database and seed client in sync.

User-defined arguments​

This feature is available from version 4.15.0 and later.

prisma db seed allows you to define custom arguments in your seed file that you can pass to the prisma db seed command. For example, you could define your own arguments to seed different data for different environments or partially seeding data in some tables.

Here is an example seed file that defines a custom argument to seed different data in different environments:

"seed.js"
import { parseArgs } from 'node:util'

const options = {
  environment: { type: 'string' },
}

async function main() {
  const {
    values: { environment },
  } = parseArgs({ options })

  switch (environment) {
    case 'development':
      /** data for your development */
      break
    case 'test':
      /** data for your test environment */
      break
    default:
      break
  }
}

main()
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

You can then provide the environment argument when using prisma db seed by adding a delimiter — -- —, followed by your custom arguments:

npx prisma db seed -- --environment development

Going further​

Here's a non-exhaustive list of other tools you can integrate with Prisma ORM in your development workflow to seed your database: