Prisma with PlanetScale quickstart

June 21, 2023

Overview

This guide will show you how to:

• Create a database with PlanetScale
• Integrate into a Next.js + Prisma application

Prerequisites

• A PlanetScale account
• Node.js
• PlanetScale CLI

Set up your PlanetScale database

First, sign up for a PlanetScale account. You can either do this on the PlanetScale website or using the PlanetScale CLI.

Create a PlanetScale database

Once you're signed in, click on the "Create a database" button. Name your database star-app, select the region closest to you, select the cluster and storage size, and click "Create database".

You can also use the PlanetScale CLI to create a database by running the following command:

pscale db create star-app --region <REGION>

For the REGION value, choose the region closest to you or your application's hosting location. You can find our regions and their slugs on the Regions page. If you do not specify a region, your database will automatically be deployed to US East — Northern Virginia.

Your database will deploy with an initial production branch, main.

Set up branches

Now that you have your PlanetScale database set up, you need to create a development branch to connect to your Prisma application.

Click on your star-app database in the dashboard, and then click on the "Branches" tab in the top nav.

From here, click the "New branch" button. Name the branch initial-setup, keep main as the base branch, select the region closest to you, and click "Create branch".

Or, to create the initial-setup branch in the CLI, run the following command:

pscale branch create star-app initial-setup

The new branch will default to the same region that was selected for main.

Set up Next.js app

Next, you need to set up your Next.js application. If you have an existing app, you can use that. Otherwise, instantiate a new Next app by running the following:

npx create-next-app@latest --use-npm

We are going to use npm in this quickstart, but you can also use yarn if you prefer.

Give your project a name (this tutorial is using star-app). This will install the project with the react, react-dom, and next dependencies.

Once it's finished, enter into the project folder:

cd star-app

Initialize Prisma

Now that you have your Next.js application ready, it's time to integrate Prisma.

First, you'll need to add the Prisma CLI as a development dependency to your project.

npm install prisma --save-dev

Next, you'll need to install the Prisma Client. In your star-app directory, run the following command:

npm install @prisma/client

Then you need to set up your Prisma files. You can do this by running:

npx prisma init

This creates the prisma directory with a file named schema.prisma. This file will hold your Prisma schema configuration, which includes your data sources (PlanetScale), generators (Prisma Client), and data models.

Open up the prisma/schema.prisma file and paste in the following:

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
  relationMode = "prisma"
}

generator client {
  provider = "prisma-client-js"
}

model Star {
  id              Int       @default(autoincrement()) @id
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt
  name            String    @db.VarChar(255)
  constellation   String    @db.VarChar(255)
}

Next, you need to modify your environment variables to set the Prisma data source. Open up .env at the root of your application and replace its contents with the following:

DATABASE_URL = 'mysql://root@127.0.0.1:3309/<DATABASE_NAME>'

Connect to PlanetScale

Now you need to locally proxy into your PlanetScale database branches using the PlanetScale CLI. In a separate terminal, run the following commands:

pscale connect star-app initial-setup --port 3309

Keep this connection running. This establishes a secure connection to PlanetScale and listens on the specified local port. In the terminal, you'll see a local address returned. You can use this to connect to a MySQL client if you'd like.

Push Prisma schema to PlanetScale

Now it's time to push your Prisma schema. In a new terminal tab, run the following command:

npx prisma db push

The recommended workflow with using Prisma alongside PlanetScale is to use prisma db push instead of prisma migrate. You can read more about prisma db push here.

Once this is successful, your PlanetScale database schema now matches the Prisma schema you configured in prisma/schema.prisma.

View your schema in PlanetScale

You can confirm it worked in your PlanetScale dashboard or with the CLI.

Dashboard instructions:

• Select the star-app database
• Click on the "Branches" tab
• Click inital-setup
• Click on "Schema"
• Click the "Refresh Schema" button

You should see one table: Star.

And just like that, you have your development branch with your Prisma schema hosted on PlanetScale.

CLI instructions:

To view your database, you can use the PlanetScale CLI to open a MySQL shell where you can view your tables and schema. Run the following command:

pscale shell star-app initial-setup

Once you're in the MySQL shell, run the following:

SHOW tables;

You should see the Star table. To view the Star schema, run the following:

DESCRIBE Star;

You can exit the shell by running:

exit

Add data

Next, you need to add some data. You have several different options here. This tutorial will cover adding data using three different methods:

• Console in the PlanetScale dashboard
• PlanetScale CLI
• Prisma Studio

Option 1: Add data in PlanetScale dashboard console

By default, web console access to production branches is disabled to prevent accidental deletion. From your database's dashboard page, click on the "Settings" tab, check the box labelled "Allow web console access to production branches", and click "Save database settings".

Then, within your star-app database, click on the "Console" tab, select the initial-setup branch from the dropdown, and click "Connect". This will let you run queries on your database branch.

The Star table is currently empty. To add some data, type in the following and press enter:

INSERT INTO `Star` (id, createdAt, updatedAt, name, constellation)
VALUES  (default, now(), now(), 'Antare', 'Scorpius');

To confirm that it was added, run:

select * from Star;

Option 2: Add data with PlanetScale CLI

To accomplish this same thing in the PlanetScale CLI, run the following in your terminal to enter into the MySQL shell:

pscale shell star-app initial-setup

Next, add a new star to the Star table with:

INSERT INTO `Star` (id, createdAt, updatedAt, name, constellation)
VALUES  (default, now(), now(), 'Graffias', 'Scorpius');

To confirm it was added, run:

select * from Star;

You can exit the shell by running:

exit

Option 3: Add data with Prisma Studio

If you prefer a visual interface, you can also add data using Prisma Studio.

In your terminal, run the following to open up Prisma Studio:

npx prisma studio

This will open Prisma Studio locally in your browser. Click on the "Add record" button.

You can leave id, createdAt, and updatedAt blank as they will auto-fill with default values. Fill in name and constellation as follows:

• name — Sargas
• constellation — Scorpius

To add the record, click the green "Save 1 change" button.

Add an API route

Now that you have some data, you need to add a simple GET endpoint in your Next.js app to retrieve it.

Back in your code editor, create a new file at pages/api/stars.js and paste in the following:

import prisma from '../../lib/prisma'

export default async function assetHandler(req, res) {
  const { method } = req

  switch (method) {
    case 'GET':
      try {
        const stars = await prisma.star.findMany()
        res.status(200).json(stars)
      } catch (e) {
        console.error('Request error', e)
        res.status(500).json({ error: 'Error fetching posts' })
      }
      break
    default:
      res.setHeader('Allow', ['GET'])
      res.status(405).end(`Method ${method} Not Allowed`)
      break
  }
}

Next, you need create a file at lib/prisma.js. This will allow you to reuse the Prisma client. Paste in the following code:

import { PrismaClient } from '@prisma/client'

const prisma = global.prisma || new PrismaClient()

if (process.env.NODE_ENV === 'development') global.prisma = prisma

export default prisma

Finally, start up your Next application by running the following:

npm run dev

You can now see all your Star table data at localhost:3000/api/stars.

Deploy development branch to production

Currently, you're working on the development branch initial-setup, which is branched off of the main branch. The initial-setup development branch is meant for applying schema changes and testing your migrations before you go to production.

Once you're happy with the initial-setup branch, you can roll it up to the main production branch. To do this, you first need to enable safe migrations on the main branch. Once safe migrations is enabled on the main branch, you'll be able to merge changes from initial-setup into main using a deploy request.

To enable safe migrations on the main branch:

• Go back to your star-app database in the PlanetScale dashboard
• In the infrastructure card, make sure the main branch is selected and click the "cog" in the upper right
• Toggle "Enable safe migrations" in the modal
• Click the "Enable safe migrations" button to save and close the modal

Your main branch is now protected from accidental schema changes and enables no-downtime schema migrations.

This production branch is currently empty. You can confirm this by going to the main branch and clicking on the "Schema" tab. Let's fix that.

Create a deploy request

The final step in this process is to roll out your changes in your initial-setup branch to production. To do this, you need to create a deploy request to your main production branch.

In the dashboard, select the initial-setup branch. On the "Overview" tab, you'll see the option to "Create a deploy request". Make sure main is selected in the "Deploy to" dropdown, type in a comment to go with your deploy request, and click "Create deploy request".

PlanetScale will check to make sure there are no conflicts between the two branches. If not, you'll get the message "This deploy request is deployable". You can click on the "Schema changes" tab to see a diff of the schema changes to be deployed.

Now, you're ready to deploy the schema changes in the initial-setup branch to production! On the "Summary" tab of the deploy request, click the button "Add changes to deploy queue". The deployment has now been added to the deploy queue.

Once it's done deploying, you'll get a success message, "These changes have been deployed".

To verify that it worked correctly, go back to your main branch and click on "Schema". You should now see the Star table!

Next steps

Now that you have your Next.js application with a database in production on PlanetScale, you may want to deploy the entire application. You can deploy to Vercel in just a few easy steps by following our Deploy to Vercel guide.

Need help?

Get help from the PlanetScale Support team, or join our GitHub discussion board to see how others are using PlanetScale.