Using Next.js with PlanetScale and Prisma

Note: This repository targets PlanetScale Vitess/MySQL. PlanetScale also offers managed Postgres. For more information, see the PlanetScale Postgres documentation.

Important update: This talk contains an outdated workflow of using prisma migrate dev with a shadow branch to update your database schema. We now recommend using prisma db push with no shadow branches. See the documentation on how to do migrations to your schema here:

This repo corresponds to the "Databases as Code with PlanetScale and Prisma" talk from Next.js Conf 2021.

This is a Next.js project bootstrapped with create-next-app.

In the demo from the talk, we created and edited an API route, http://localhost:3000/api/stars. This endpoint can be edited in pages/api/stars.js. The pages/api directory is mapped to /api/*. Files in this directory are treated as API routes instead of React pages.

Set up

Below are the steps I went through in the talk during Next.js Conf. You will likely find the talk to include a few more details around each step. I have created the following steps to give a brief idea for what is needed to go to production.

Prerequisite: You need to have the Prisma and PlanetScale CLIs installed

1. In PlanetScale, create a star-app database
2. In your database's Settings page, check "Automatically copy migration data" and select "Prisma"
3. Create an initial-setup and shadow database branches from main branch

Note: This talk contains an outdated workflow of using prisma migrate dev with a shadow branch to update your database schema. We now recommend using prisma db push with no shadow branches. See the documentation on how to do migrations to your schema here:

4. Locally, run:

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

5. Once this is complete, run:

npm install @prisma\client

6. To create the files needed to use Prisma, run:

npx prisma init

7. Edit the prisma/schema.prisma file:

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

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

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

8. Edit the .env file:

DATABASE_URL="mysql://root@127.0.0.1:3309/star-app"
SHADOW_DATABASE_URL="mysql://root@127.0.0.1:3310/star-app"

9. Next, we will use pscale CLI to locally proxy into our PlanetScale database. In a two different terminal tabs, run:

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

pscale connect star-app shadow --port 3310

10. In a different terminal, run the following to create the initial data model and do your first Prisma migrate:

prisma migrate dev --name init

You will notice a prisma/migrations folder as well as the schema in your initial-setup branch in PlanetScale. 11. Create lib/prisma.js file:

import { PrismaClient } from '@prisma/client'

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

if (process.env.NODE_ENV === "development") global.prisma = prisma;

export default prisma

12. Create pages/api/stars.js file:

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;
    }
}

13. Add data using npx prisma studio, which will open a browser window. After you add data, then close Prisma Studio. You can also check to see that the data is in your intial-setup branch in PlanetScale.
14. Run: npm run dev Navigate to http://localhost:3000/api/stars to see data returned from your API endpoint!
15. You are now ready to open a deploy request in PlanetScale. Once you have opened a deploy request and merged it into your main branch, you are now ready to deploy to Vercel! Remember to add data again to your main database branch; otherwise, your API route will return no data.

Deploy on Vercel

Warning: You need to make sure to follow the steps from the "Databases as Code with PlanetScale and Prisma" talk at Next.js Conf before deploying to Vercel.

Deploy this application quickly to Vercel using the following Deploy button:

In order to connect your database to your application deployed in Vercel, you will need to set the DATABASE_URL variable. This URL is how your Vercel application is able to securely connect to PlanetScale. The URL is in the following format: mysql://[username]:[password]@[host]/star-app?sslaccept=strict

You can create a password in PlanetScale and generate this URL in the Connect modal in the database branch of your choice, select "Prisma" to get the Prisma specific URL:

Note: If you see a 404 page after your build is successful, go into your app's Vercel Settings > General > Build & Development Settings and make sure that Next.js is the selected framework.