How to use PlanetScaleDB & Prisma ORM in Next.js?

How to use PlanetScaleDB & Prisma ORM in Next.js?

Published 8th, November, 2022

5 min read

Next.js is an awesome way to develop the front-end for your React.js based applications. Next.js brings with it a number of very handy and easy-to-use features that enable developers integrate even the most time-consuming functionalities in a jiffy! And again, it has brought something new to the table. Read on to know what, how and why!

PlanetScaleDB: Combining cloud-based SQL with containerisation

Next.js, as usual is now compatible with PlanetScaleDB, another latest and awesome technology! PlanetScaleDB is a data-base-as-service application that runs on cloud, rather than local storage, which not only makes it decently costed, but also agile with rapid setup, scaling and validating.

PlanetScaleDB rests on two main foundation; Kubernetes and Vitess. Kubernetes is a cloud-based application container management platform used for rapid deployment and scaling. On the other hand Vitess is a database clustering platform for MySQL. Vitess combines MySQL’s convenience and ease of use with the scaling capabilities of NoSQL databases.

The reason why PlanetScaleDB is gaining so much traction is because the COVID lock-downs changed the idea of a ‘workplace’ to a significant degree leading to rapid digital transformation, which increased the need for applications to use containers, like Kubernetes.

Prisma: Intuitive ORM to simplify database development

Prisma is a great tool developed by none other than Vercel, the official creators of Next.js! Prisma is an ORM tool used to build databases from models defined in the back-end of an application. Prisma is compatible with many SQL-based as well as NoSQL-based systems such as MySQL, PostgreSQL, MongoDB etc and has been designed to work with Node.js and TypeScript, two most notable JavaScript-based frameworks.

Prisma’s functionality includes Prisma’s query building system used mostly with Node.js and TypeScript, the system that enables faster and easier migration and the Prisma Studio, a built-in GUI to manage your databases. Also, Prisma works well with REST API as well as GraphQL APIs.

Can they go hand-in-hand?

There’s no reason to suggest otherwise! Given the fact that Prisma works with SQL databases by default, working with PlanetScaleDB is no exception. Also, PlanetScaleDB being a latest cloud-based database, known to work with JavaScript-based frameworks, it can easily integrate Prisma with it.

Let’s move ahead and check this out!

Using PlanetScaleDB with Prisma

First, we will create our PlanetScale a free account on their website or we can do this through their Command line interface(which we will need to install separately).

And then comes integration of the database with the Next.js application already using Prisma, with Node.js being the back-end.

Open the command line application and type the following:

pscale db create planet-next region <REGION>

Go to the regions page and select your region from a list of slugs for region. Else, it will auto-set US East – North Virginia.

This will also set a default branch for your database called ‘main’.

Setting up your development branch

After our PlanetScale database has been set up, we will create a dedicated development branch which interfaces with Prisma in our application.

  1. Go to your PlanetScale account dashboard and click on the star-app and then click on a tab called ‘Branches’.
  2. Click the New Branch button and name your new branch as begin-branch. However, we will keep main as our base branch.

The above functionality can be executed in the CLI with the command

pscale branch create star-app begin-branch

Setting up our project

If you want to use PlanetScale on your current application, you surely can. Or you can also create a new Next.js app.

We’ll use the following command:

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

Now we create our project named ‘planet-next’. The following CLI will also install react, react-dom and other dependencies for next.

cd planet-next

Initializing Prisma in our Next.js application.

First, we will need to add Prisma CLI as a development, and then we’ll install Prisma Client inside our project folder, respectively.

npm install prisma –save-dev

npm install @prisma/client

We’ll have a Prisma directory created with file name schema.prisma, holding our Prisma Schema Configuration containing our data sources from PlanetScale and Prisma Client generators as well as our database models.

In schema.prisma, paste the following code:

datasource ourDB {
    provider = "mysql”
    url = env("<DATABASE_URL>")
    referentialIntegrity = "prisma"
}

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

model Planet {
    planetID Int @default(autoincrement()) @id
    created DateTime @default(now())
    updated DateTime @updatedAt
    name String @db.VarChar(255)
}

We’ll also need to edit the .env file for modifying our environment variables.

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

Connecting and integrating Prisma with PlanetScale

In another terminal, type the following and keep the terminal running:

pscale connect planet-next initial-setup --port 3309

Pushing our Prisma Schema to PlanetScale

npx prisma db push

Now our Prisma schema matches the one on PlanetScale. It would be better if we checked whether our migration has been successful.

On your dashboard:

On your dashboard, select planet-next database, click Branches and select begin-branch and then click the Refresh Schema button and you can see that the Planet table is already there.

On the CLI on the other hand:

pscale shell planet-next initial-setup

In the MySQL shell type and run these:

SHOW tables;

DESCRIBE Star;

Data goes in

Adding data can be done with either of these methods:

  1. PlanetScale dashboard console
  2. Prisma Studio
  3. PlanetScale CLI

We’ll go for number three In your MySQL shell, run the following command:

pscale shell star-app initial-setup

Add a planet to our current table

INSERT INTO `Planet` (planetID, created, updated, name) VALUES (default, now(), now(),  ‘Pandora’);

Confirm the process by running

select * from Planet;

Creating API route

Here, you can create a simple end-point of the GET method in our app in a file named floatingrock.js

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

export default async function handleRequest(request, response) {
    const {
        method
    } = request

    switch (requestMethod) {
        case 'GET':
            try {
                const planets = await prisma.planet.findMany()
                response.status(200).json(planet)
            } catch (err) {
                console.error('Request error detected', err)
                response.status(500).json({
                    error: 'Error: Cannot fetch posts'
                })
            }
            break
        default:
            response.setHeader('Allow', ['GET'])
            response.status(405).end(`Method ${requestMethod} Not Allowed`)
            break
    }
}

Now create a file to be able to reuse your Prisma client named prismareuse.js.

import {
    PrismaClient
} from '@prisma/client'

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

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

export default prisma

And then restart your application with the command

npm run dev

Project deployment to production


The current branch named begin-branch is in the main branch which is a test branch meant for testing your schema changes and such.

However, once you are sure that everything works just fine, you can push your main branch to production with the following steps.

Go to the ‘planet-next’ database, click the main branches under the Branches tab, click Promote Branch button and then confirm.

Now you need to make a final implementation to your changes in the ‘begin-branch’ branch to your production environment, by creating a deploy request to the “main” production branch.

Go to the Overview tab and click Create A Deployment Request, click Deploy To dropdown and select main, click Create Deployment 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.
PlanetScale takes the charge from here and first of all checks whether the two branches are conflict-free. If everything works, you will get a message stating that the deployment is applicable.

It is easy…no really!

Some developers might find this a tad bit confusing since it mimics the workflow of a CI/CD process. And so we encourage you to go through this tutorial and implement it on your system to see for yourself.

References:

  1. planetscale
  2. vercel