How to set up Next.js with Prisma and PlanetScale

By Camila Ramos | January 20, 2022

What is PlanetScale?

PlanetScale is a database-as-a-service platform that is built on open-source Vitess, the technology that powers YouTube, Slack, and other hyperscale companies with the ability to make millions of queries per second and an infinite number of connections.

What is Prisma?

Prisma is an open-source ORM that allows developers to write queries in Javascript/Typescript. Prisma offers three products:

Prisma Client:- a type-safe API that lets you write queries without writing SQL.
Prisma Migrate: a database migration tool.
Prisma Studio: a visual editor for the data in your database.

In this blog, we’ll be working with Prisma Client and Prisma Studio.

PlanetScale with Next.js

We’ll walk through setting up a new database, installing Prisma, defining your data models, and writing the API route to write to your database. In this example, I will be showing how you can get data from your user via a form and save it into your database.

Creating your database

Head over to PlanetScale.com and create an account.
Create a new database and select the closest region to your physical location. This will help reduce latency.
Select your cluster and storage size.

Setting up Prisma

If you already have an existing Next app, skip this step. Create a new Next app using npx create-next-app@latest . If you want to follow this exact project, create a form component and render it in your index.js file. Run npm run dev to start your local server and navigate to the URL your terminal suggests, usually http://localhost:3000/.
Open a terminal in your project directory and run the following command to generate a Prisma folder as well as a .env file: npx prisma init

Go into your .env file and update your DATABASE_URL variable with the following:

DATABASE_URL="mysql://root@127.0.0.1:3309/YOUR-DB-NAME-HERE"

Defining your schema

Now in your schema.prisma file, update your data source and client to the following:

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["referentialIntegrity"]
}

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

Because PlanetScale doesn’t recommend foreign key constraints and Prisma defaults to using foreign keys to express relations, we need to set this referentialIntegrity property when using Prisma with PlanetScale. If you'd prefer to use foreign key constraints, you can skip this step, but you need to enable foreign key constraint support for your database in your PlanetScale database settings page.

“Referential integrity is a property of a data set that states that all its references are valid. Referential integrity requires that if one record references another, then the referenced record must exist. For example, if a Post model defines an author, then the author must also exist.“ (Source: Prisma docs) To read more about referential integrity, check out these Prisma docs.

In this same file you should define your data models, in this case Inquiry. This model will store the inquirer’s name, email, subject of the inquiry and message. I’ve made the subject field optional and denoted this by using a ? on the field.

You should also add an id, and you can use the default and id attribute. The @id attribute defines a single-field ID on the model. The Default attribute defines a default value for the field, and you can pass in autoincrement to create a sequence of integers and assign the incremented values to the ID value.

Tip

Install the Prisma VS Code extension to get syntax highlighting and autocompletion for your schema file.

model Inquiry {
id      Int   @default(autoincrement()) @id
name    String
email   String
subject String?
message String
}

Running your database locally

Once you’ve defined your data model, open a new terminal in your project directory and run this command: pscale connect YOUR-DB-NAME-HERE main --port 3309 This command will run a local proxy to your database, which allows a simpler way to connect to your database when running your app locally. Instead of having to make multiple connection strings for different database branches you are working in, you can just change the branch in one command line argument.

For this step, ensure that the main branch hasn’t been promoted to production. In the next step, we will synchronize our Prisma schema and our database schema, and you can’t make schema changes to a production branch. You don’t have to worry about this if you’ve been following this guide. You can read more about branching with PlanetScale here.

In a new terminal, run this command to sync your prisma.schema with your PlanetScale schema: npx prisma db push

You should see a success message similar to this:

To verify that your database is in sync with your schema, as well as run any SQL commands, run: pscale shell prisma-playground main

Run this line and replace Inquiry with an entity you defined in your schema.:

describe Inquiry; //Don’t forget the semicolon here.

You can exit the MySQL shell by typing exit and hitting enter.

Now that you have your schema, promote your branch to production: pscale branch promote YOUR-DB-NAME-HERE main

Creating your API route

Now, you can create an API route by creating a file inside your API folder in your Next.js app. Create a file in your API folder with a descriptive name. Here you will set up your Prisma client and define your function for handling requests. I named my file Inquiry.js and here’s what it looks like:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

export default async function handler(req, res) {
  if (req.method === 'POST') {
    return await createInquiry(req, res)
  } else {
    return res.status(405).json({ message: 'Method not allowed', success: false })
  }
}

async function createInquiry(req, res) {
  const body = req.body
  try {
    const newEntry = await prisma.inquiry.create({
      data: {
        name: body.firstName,
        email: body.email,
        subject: body.subject,
        message: body.message
      }
    })
    return res.status(200).json(newEntry, { success: true })
  } catch (error) {
    console.error('Request error', error)
    res.status(500).json({ error: 'Error creating question', success: false })
  }
}

To learn more about how Next.js API routes work check out the documentation.

First we define and instantiate our Prisma client.

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

We create a function called Handler which checks that the request method is POST, then calls the function which actually writes to our database, createInquiry. If the method is not POST, it sends back a response with a status code of 405 and a message letting the user know that this operation is not allowed.

export default async function handler(req, res) {
  if (req.method === 'POST') {
    return await createInquiry(req, res)
  } else {
    return res.status(405).json({ message: 'Method not allowed', success: false })
  }
}

We define an asynchronous function, createInquiry, which will take the data in the request body and send it to our database.
Wrapped in a try/catch block, we define a new variable newEntry and create a new entry in our Inquiry table.
We define what properties in the body contain the pieces of data we are looking for and assign those to the fields we want to create in our database: name, email, subject, and message. If this is successful, we return a response status code of 200. If it was not successful, we log the error and respond with a server code of 500, signaling to the user that there was some unknown error.
- This is beyond the scope of this blog, but in your application you should always validate the user's input before writing to the database.

async function createInquiry(req, res) {
  const body = req.body
  try {
    const newEntry = await prisma.inquiry.create({
      data: {
        name: body.firstName,
        email: body.email,
        subject: body.subject,
        message: body.message
      }
    })
    return res.status(200).json(newEntry, { success: true })
  } catch (error) {
    console.error('Request error', error)
    res.status(500).json({ error: 'Error creating question', success: false })
  }
}

Check that your API route is working as expected by using something like Insomnia or Postman. Make sure that you start your server by running npm run dev, and in Insomnia paste in the URL to your API endpoint. In my case, the API is expecting a request object with a firstName, email, subject, and message.

{
  "firstName": "Camila",
  "email": "testing@gmail.com",
  "subject": "Come speak",
  "message": "We have an opportunity for you"
}

Testing my API with Insomnia would look like this for me. Once you get back a 200, you’ve confirmed that your endpoint is working as expected.

To confirm that your data was written to your database, you can open a new terminal in your project and run npx prisma studio. This will open up a visual instance of your data in the browser to verify your data is in there.

Saving data from your front-end to your database

You’ll have to build out a way to get this data from your user in the front end and then pass that data to your API. In this example, we are working with a form. We know we have all the data and are ready to write to the database when the user hits submit. With this, one approach would be to write a function that gets executed when the form is submitted, making the call to our API with the data gathered from our user in the form. Here’s what mine looks like:

const handleSubmit = async (e) => {
  e.preventDefault()
  const body = { firstName, email, subject, message }
  try {
    const response = await fetch('/api/inquiry', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify(body)
    })
    if (response.status !== 200) {
      console.log('something went wrong')
      //set an error banner here
    } else {
      resetForm()
      console.log('form submitted successfully !!!')
      //set a success banner here
    }
    //check response, if success is false, dont take them to success page
  } catch (error) {
    console.log('there was an error submitting', error)
  }
}

const resetForm = () => {
  setFirstName('')
  setEmail('')
  setSubject('')
  setMessage('')
}

In this case, I am using the useState hook and setting the state for each variable (firstName, email, subject, and message) by passing an anonymous function to the onChange property of the form inputs.

<input
  type='text'
  name='first-name'
  id='first-name'
  autoComplete='given-name'
  onChange={(e) => setFirstName(e.target.value)}
  value={firstName}
  className='bg-zinc-300 text-gray-200-900 focus:ring-indigo-400 focus:border-indigo-400 border-warm-gray-300 block w-full rounded-md px-4 py-3 shadow-sm'
/>

You’ll have to call the handleSubmit function somewhere to execute this code. Because I’m using a form, I can pass the function call to the onSubmit property like this:

<form action="#" method="POST" onSubmit={(e) => handleSubmit(e)}

All done! Now you’re ready to deploy your database to work in production and take your app live.

Deploying to production

Navigate back to your PlanetScale database. Hit the connect button and select Prisma from the dropdown menu. Hit the button to generate a new password, and be sure to copy/paste this somewhere for you to access later.

In my example, I’m using Netlify. You can use Vercel to deploy your app, and the steps will be similar. In my case, the project was already deployed, so I’ll have to go back and make some changes to my environment variables and redeploy. If you are deploying this project for the first time, you can set the environment variables in your initial configuration and won’t have to redeploy as outlined below.

Create a Netlify account and connect the GitHub repo that is connected to this project. Navigate to Site Settings.

Using the side navigation, go to Build and deploy, and select Environment.

Add a variable called DATABASE_URL and set the value to be the URL you were given from your PlanetScale-generated password. Be sure to remove the quotes that wrap the URL.

Save these changes. In the Deploys tab, hit the button that says Trigger Redeploy. Now you’re ready to either push this code to your main branch or merge the branch you’re working on into main to see your new database live.

Give yourself a pat on the back because you just deployed your first PlanetScale database 🥳.

Try it out

Follow this guide and spin up a working app in just a few minutes! Create a new database, define your data models, and write your API to write to your database directly from your Next app. Tweet the team with any questions you have @planetscale.