How to seed a database with Prisma and Next.js

By James Q Quick | February 11, 2022

Next.js and Prisma are a popular combination for creating modern fullstack web applications. Next.js enables all of the power of React while adding support for Server Side Rendering (SSR), API routes, and more. Prisma is an ORM for JavaScript and TypeScript that allows developers to interact with SQL datbases without having to write raw SQL statements. In this tutorial, we’ll see how to seed a PlanetScale database using Prisma in a Next.js project.

What is database seeding

Database seeding involves populating your database with an initial set of clean data. This is extremely useful in a couple of different use cases:

Initial project setup

Let’s say you join a new project. You start by cloning the source code. Then, you may need to create a new database to work with, which means no data to start with. This makes exploring the app tedious as you have to manually create users, new records, etc. Well, you can automate that by seeding your database.

Automated testing

Another useful scenario is automated testing. Each time you run a new round of tests (this may be triggered manually or as part of your CI/CD workflow), you can seed your database with a controlled data set. This way, you can be sure that each time your tests run, they are being run against the exact same set of data.

Branching in PlanetScale

PlanetScale comes with the unique feature of database branching (similar to Github branches) which allows you to apply your schema to a new database instance. After you create a new branch, you can use a seed script to populate your new database branch with starter data.

To learn more, refer to the official branching documentation.

Setup

To get started, clone our Next.js starter repository.

git clone https://github.com/planetscale/nextjs-starter

This project is already configured with Prisma (we’ll look at the data models in a second). To work with this project and see the seeding take place, you’ll need to create a new database in PlanetScale and a new connection string.

The database

Create a PlanetScale database in the dashboard or by using the CLI. Then, create a connection string for your database by following the connection strings documentation.

Copy the .env.example file as .env:

cp .env.example .env

Then, update the DATABASE_URL property with the following format.

mysql://<USERNAME>:<PLAIN_TEXT_PASSWORD>@<ACCESS_HOST_URL>/<DATABASE_NAME>?sslaccept=strict

In this starter code, we have two different models configured, Product and Category in the schema.prisma file.

model Product {
    id          Int       @id @default(autoincrement())
    name        String
    description String
    price       Decimal
    image       String
    category    Category? @relation(fields: [category_id], references: [id])
    category_id Int
}

model Category {
    id          Int       @id @default(autoincrement())
    name        String
    description String
    products    Product[]
}

Before you can run a seed script, you’ll need to push this schema to your database.

npx prisma db push

Create the seed script

The prisma directory is a convenient place to include a seed script since this is where the schema.prisma file referenced above is located. Inside of this directory of the starter code, you’ll see a seed.js file. Notice also the data.js file which exports sample data that you will use when the seed script is run.

Although the seed script is finished in the starter repository, let’s break down the steps of how you would create it yourself from scratch. First, you’ll need to import the PrismaClient and the categories and products data. Then, you’ll need to generate a new client by calling PrismaClient().

const { PrismaClient } = require('@prisma/client')
const { categories, products } = require('./data.js')
const prisma = new PrismaClient()

After you’ve got your imports, create a load() function. This where the actual database seeding will take place. Make sure to mark the function as async since you will use the await keyword inside of it. Also, don’t forget about error handling. Go ahead and add a try/catch/finally block inside of your function to handle errors and disconnect from your database after the seeding has completed.

const load = async () => {
  try {
  } catch (e) {
    console.error(e)
    process.exit(1)
  } finally {
    await prisma.$disconnect()
  }
}
load()

With the load() function set up, you can start to add data to your database by passing the categories and products arrays to the appropriate createMany() function.

await prisma.category.createMany({
  data: categories
})
console.log('Added category data')

await prisma.product.createMany({
  data: products
})
console.log('Added product data')

Your script should now be set up to add data, but one thing you’ll want to do first is delete any existing data. This way, you can verify that your database will be populated in exactly the same way each time it is seeded. Before the lines you just added for creating data, call deleteMany() for both tables.

await prisma.category.deleteMany()
console.log('Deleted records in category table')

await prisma.product.deleteMany()
console.log('Deleted records in product table')

Lastly, the dummy data maintains a relationship between an individual product and its corresponding category with the category_id property. Because of this, this category_id property is prepopulated with the product records. However, since the id properties of products and categories are auto-incremented, you’ll need to manually reset them to 0. This will ensure that each category_id will correspond to the appropriate category record.

You can reset the auto-incremented values by calling the prisma.$queryRaw function and passing the appropriate SQL statement like so.

await prisma.$queryRaw`ALTER TABLE Product AUTO_INCREMENT = 1`
console.log('reset product auto increment to 1')

await prisma.$queryRaw`ALTER TABLE Category AUTO_INCREMENT = 1`
console.log('reset category auto increment to 1')

Here’s what the full file looks like.

const { PrismaClient } = require('@prisma/client')
const { categories, products } = require('./data.js')
const prisma = new PrismaClient()

const load = async () => {
  try {
    await prisma.category.deleteMany()
    console.log('Deleted records in category table')

    await prisma.product.deleteMany()
    console.log('Deleted records in product table')

    await prisma.$queryRaw`ALTER TABLE Product AUTO_INCREMENT = 1`
    console.log('reset product auto increment to 1')

    await prisma.$queryRaw`ALTER TABLE Category AUTO_INCREMENT = 1`
    console.log('reset category auto increment to 1')

    await prisma.category.createMany({
      data: categories
    })
    console.log('Added category data')

    await prisma.product.createMany({
      data: products
    })
    console.log('Added product data')
  } catch (e) {
    console.error(e)
    process.exit(1)
  } finally {
    await prisma.$disconnect()
  }
}

load()

Configure the seed command

There are a couple of different ways to configure your seed script to run.

Add a new script in the package.json

The first option is to define your own script inside of the package.json. Inside of the scripts section add the following line.

"seed": "node prisma/seed.js"`

This will enable you to run npm run seed to run your seed script. Go ahead and give it a try! You should see success log messages in your console.

Add a prisma.seed field in package.json

The second way to configure your seed script is to tap into the Prisma configuration in your package.json. For this to work you can add the following line at the top level of your package.json

"prisma": {
  "seed": "node prisma/seed.js"
},

With that configuration added, you can now trigger your seed script by running npx prisma db seed. Give that a shot!

So far, this is a pretty similar result to what we had in the previous step. However, there is a bit more happening behind the scenes. Because the prisma.seed property is defined, Prisma will automatically run the seed command when either or the following commands are run: npx prisma migrate dev or prisma migrate reset.

Whether or not you want this to happen is totally up to you. Personally, I prefer to choose when the seeding should take place, so I would prefer the first option by configuring it in the scripts section.

Wrap up

Hopefully, this tutorial gave you a good understanding of how to automically populate your PlanetScale database by configuring a seed script with Prisma. If you have any additional questions, let us know on Twitter.