Support for database seeding

[schickling]

Problem

Seeding a database is a process in which a database (usually empty) is populated with an initial set of data. This is often an automated process that is executed upon the initial setup of an application. The data can be dummy data or necessary data such as an initial administrator account.

Use cases:

• testing (most common): populate with data required used in automated tests
• development: populate data useful for development and manual testing
• production: create data that is required for the app to work in a production environment

Considerations:

• the seeds tend to be tied to the lifecycle of the schema, evolving the schema will often trigger changes to the seeds
• the database will sometimes need to be reset and re-seeded, to remove stale/dirty data
• seeding production is probably a different problem than seeding dev/test environments. Frameworks tend to provide different guidance for these scenarios.

Prior art

Prisma1 has a CLI command seed, which reads the configuration from the prisma.yml file. It can execute a seed script written JS.

Possible Solutions

• Fixtures - static files containing the data, i.e. CSV, JSON, YAML
• Seed scripts - code written by the user to populate data using the existing Client API
• ...

[iherger]

Thanks. For development, prisma2 reset would also be quite handy!

[schickling]

Would you want prisma2 reset to only reset the data or also reset the database schema and re-run all migrations?

[iherger]

For my development needs, resetting the data is probably enough (the schema is work in progress, and every now and then it makes sense to just start with the an empty database [seeded with some basic seed data] based on the current schema).

[vdjurdjevic]

Hi. I see that there is seed command (removed from CLI since it's not implemented yet) and that's really great. It was always a pain in the ass for me to write custom seeding scripts for various databases for different projects. I just want to make a few suggestions for the functionality. I don't know what you guys have in mind for this, but this is what I would like to have with prisma2.

1. One common format (JSON object) for specifying seed data.

Something like this:

{ "users": [ { "user-id-1": { "name": "Name", "email": "something@email.com" } } ] }

2. JS function that takes this object, connects to DB, optionally clears DB, seed provided data and disconnects. With this, we could easily seed database inside integration tests, building seed data object per test.

3. Add config to project.prisma that can point to a directory of JSON files (all formatted like described above). CLI could use this config and reuse above function to seed database in watch mode for development, and with env variable, it could also use another set of files to seed production data (incrementally).

Those are the use cases that I had with every project (seeding from code for integration testing, and from version controlled files for development and production).

What do others think? I would like to contribute on this subject if you accept contributions :)

[nahtnam]

Just wanted to share my use-case with this. For dev, seeds are used to fill in all kinds of data so that other developers don't have to manually enter the data. In production we use seeds to fill in static values such as default categories, etc. All of these insertions are actually upserts with a static id. That way if I change a category name it updates the actual seeded value

[nahtnam]

So I ended up just creating this file under prisma/_seeds/production/01-category.tsx and invoke it with ts-node when needed:

EDIT: Instead of using 01, 02, etc, use a timestamp! Here is a quick way to get the current timestamp: node -e "console.log(Date.now())". This way the migrations stay in order and you don't have to worry about expanding to three digits in the future

import { prisma } from "../../prisma";
import { Category } from "@prisma/client";

const seed = async () => {
  const categories: CategoryCreateInput[] = [
    {
      id: 'c3121e0b-9f78-4c99-8f16-18c10ea6ada0',
      name: 'category1'
    },
    {
      id: '32288e9e-ff04-4f91-8117-db85bf6cdf29',
      name: 'category2'
    },
  ];

  for (const category of categories) {
    await prisma.category.upsert({
      create: category,
      update: category,
      where: {
        id: category.id
      }
    })
  }
}

seed().catch(err => {
  throw err;
}).finally(() => {
  prisma.disconnect();
});

[sturmenta]

@nahtnam and you can reset your tables previously with (#742 (comment))

import { PrismaClient } from '@prisma/client';

const tableNames = [
  'User',
  'Store'
];

export default async () => {
  const prisma = new PrismaClient();

  try {
    for (const tableName of tableNames) {
      await prisma.raw(`DELETE FROM "${tableName}";`);

      if (!['Store'].includes(tableName)) {
        await prisma.raw(
          `ALTER SEQUENCE "${tableName}_id_seq" RESTART WITH 1;`
        );
      }
    }
  } catch (err) {
    // eslint-disable-next-line no-console
    console.error(err);
  } finally {
    await prisma.disconnect();
  }
};

[Bram-Zijp]

I noticed the above code samples use await in for loops. For seeding, this might be fine but it's not taking full advantage of parallelization: https://eslint.org/docs/rules/no-await-in-loop

This should be faster when you get lots of migrations:

// index.ts
import { PrismaClient } from "@prisma/client";
import user from "./user";

const main = async () => {
  const prisma = new PrismaClient();

  await Promise.all([user(prisma)]);

  prisma.$disconnect();
};

main();

// user.ts
import { PrismaClient } from "@prisma/client";

const users = [
  {
    name: "Developer",
    password: "Strong-_Password11%262",
    email: "developer@example.org",
  },
];

const main = (prisma: PrismaClient) =>
  Promise.all(
    users.map((user) =>
      prisma.user
        .upsert({
          create: user,
          update: user,
          where: {
            email: user.email,
          },
        })
        .catch(console.error)
    )
  );

export default main;

[vh13294]

Is there a third party library that I can use?
Some are not maintained.

Something that integrate directly to the work flow?
For example, in laravel you could specify .env.testing .env.development to choose your target seeder via command line.

[zub0r]

Why is this vital feature not part of prisma cli?

[heymartinadams]

In case it helps, somewhat related. We’ve created comprehensive seed scripts that test the environment prior to seed execution. We also use a secrets manager DopplerHQ, which helps keep things clean and tidy as it relates to environment variables.

/scripts/prisma/seed/create/index.mjs

// Packages
import shell from 'shelljs'
// Scripts
import setEnv from '../../../utils/setEnv.mjs'

const ScriptPrismaSeedCreate = async () => {
  // Get and manually set environment, each time; we want to make sure we’re using the right environment
  const env = await setEnv()

  // Development, including staging and features
  if (env.startsWith('feature') || env === 'develop' || env === 'staging') {
    shell.exec('doppler run -- node --experimental-modules ./scripts/prisma/seed/create/dev/index.mjs')
  }
  // Production
  else if (env === 'production') {
    shell.exec('doppler run -- node --experimental-modules ./scripts/prisma/seed/create/prd/index.mjs')
  }
}

ScriptPrismaSeedCreate()

/scripts/prisma/seed/create/dev/index.mjs

// Prisma
import pkg from '@prisma/client'
const { PrismaClient } = pkg
const prisma = new PrismaClient()

// Data
import users from './users.mjs'
import venues from './venues.mjs'
import venueRegions from './venueRegions.mjs'

const batch = [
  ...users.map(data => prisma.user.create({ data })),
  ...venueRegions.map(data => prisma.venueRegion.create({ data })),
  ...venues.map(data => prisma.venue.create({ data })),
]

async function start() {
  for await (let item of batch) {
    console.log(`\n☑️️  Added: ${JSON.stringify(item)}`)
  }
}

start()
  .catch(e => console.error(e))
  .finally(() => process.exit(0))

/scripts/utils/setEnv.mjs

// Packages
import inquirer from 'inquirer'
import shell from 'shelljs'
// Utilities
import colors from './colors.mjs'

const ScriptUtilsSetEnv = async () => {
  console.log(
    `\n${colors.green}The following environment is currently being used:${colors.yellow}\n`
  )

  const currentEnv = await shell.exec('doppler run -- printenv DOPPLER_CONFIG')

  console.log(`${colors.end}`)

  const res = await inquirer.prompt([
    {
      type: 'list',
      name: 'env',
      message: 'Please select the environment you’d like to use for this operation:',
      // List your various environments here
      choices: ['feature', 'develop', 'staging', 'production']
    }
  ])

  if (currentEnv.stdout.includes(res.env)) {
    console.log(
      `\n${colors.green}The environment was left as-is:${colors.yellow}\n\n${res.env}\n${colors.end}`
    )

    return res.env
  }

  shell.exec(`\ndoppler configure set enclave.config=${res.env}\n`)

  console.log(
    `\n${colors.green}The environment has been set to:\n\n${colors.yellow}${res.env}${colors.end}\n`
  )

  return res.env
}

export default ScriptUtilsSetEnv

[JakubKoralewski]

@nahtnam

So I ended up just creating this file under prisma/_seeds/production/01-category.tsx and invoke it with ts-node when needed:

How exactly are you calling ts-node / what's your tsconfig.json. I'm trying to use TypeScript for this script, but I'm getting either:

(node:11908) Warning: To load an ES module, set "type": "module" in the package.json or use the .mjs extension.
Cannot use import statement outside a module

or I can't import the singleton prisma client variable.

[vh13294]

@JakubKoralewski

this is my example repo, hope it would be helpful.

https://github.com/vh13294/nestjs-inventory

[albertoperdomo]

Just dropping a note to inform everyone that this is currently being worked on.

We are adding a prisma db seed command, that will automatically pick up a seed.ts, seed.js, seed.go or seed.sh script if it's in the prisma directory.

This will also be triggered after database resets when using Prisma Migrate (sometimes triggered interactively via migrate dev, explicitly triggered via migrate reset).

[nahtnam]

@albertoperdomo Will there be a way to differentiate between dev seeds and prod seeds.

[Bram-Zijp]

@albertoperdomo Will there be a way to differentiate between dev seeds and prod seeds.

Whether they implement this or not, these things should be trivial using environment variables.

[albertoperdomo]

@nahtnam Not out of the box, no. These are currently designed for use in development. It should be fairly trivial however, to either create a separate seed script for production or to write your seed implementation in a way that lets you conditionally do stuff based on the environment, as @Bram-Zijp suggested.

[albertoperdomo]

In 2.15.0 (released today, release notes here), we've introduced a new prisma db seed CLI command.
Documentation:

• guide
• reference

This functionality is currently in Preview.
Please share your feedback via GitHub here or via Slack in the #product-feedback channel - you can join our public Slack community here.