Intro

After shamelessly copied the guestbook feature from Lee Robinson, I was committed to creating something original for my site. Today, let's discuss how I created a workflow when developing an Endorsement System using the same powerful stack: Next.js + PlanetScale + Prisma. (We probably need a name for this stack soon). Visit Endorsements to see the result ✨.

The tech stack

PlanetScale (still in beta) is a serverless database platform that is gaining interest amongst hobbyists in the industry lately for many good reasons: a generous free tier, the exciting database branching model, the scaling possibility, etc.

Prisma is a collection of tools that abstract away the complex works when working with a database for you: migrations, safe-typing, etc. It is a great complementary to TypeScript applications.

Database design for the Endorsement System

At first, I planned to have only two tables: endorsements and skills for the simplest form of implementation. Later, I have the chance to add skill_category in the mix for better visualization. Here's the database design in dbdiagram.io:

The workflow

Preparations (Do this once)

Register & create your database on PlanetScale.
- In Database -> Settings, you need to turn on Automatically copy migration data. Choose Prisma as Migration Framework. Save the settings.
Create a shadow database. Because PlanetScale is a cloud-hosted database, you need to create this manually: pscale branch <branch> --database shadow
Add these values to .env:

DATABASE_URL="mysql://root@127.0.0.1:3309/<db>"
SHADOW_DATABASE_URL="mysql://root@127.0.0.1:3310/<db>"

Prepare the schema.prisma file with the following configurations:

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

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

You can read more about Referential Integrity in Preview in the 3.1.1 release note of Prisma. They introduce this feature to work around PlanetScale limitation not support defining foreign keys.

Schema migration

TL;DR

The steps are:

Create a new database branch on PlanetScale pscale branch <branch> --database <database>
Connect to the newly created branch and shadow branch using two separate terminals:
- pscale connect <database> <branch_name> --port 3309
- pscale connect <database> shadow --port 3310
Branch on git: git branch new_branch
Start creating migrations by editing schema.prisma
Run npx prisma migrate dev --name <name> in another terminal.
Create deploy request: pscale deploy-request create <database> <branch>
Merge the deploy request and PR on GitHub.
Close the opened connections.
Rinse and repeat.

Longer version - Endorsements

As I mentioned before, I initially wanted two databases for simplicity. My steps:

Create a branch on PlanetScale and named it endorsements.
Connect to that branch and the shadow branch using
- pscale connect <database> endorsements --port 3309
- pscale connect <database> shadow --port 3310
On my git feature branch, I edit the schema as follow:

model skills {
  id           BigInt         @id @default(autoincrement())
  name         String         @db.VarChar(255)
  endorsements endorsements[]
  @@unique([name])
}

model endorsements {
  id          BigInt  @id @default(autoincrement())
  endorsed_by String  @db.VarChar(255)
  email       String  @db.VarChar(255)
  skill       skills? @relation(fields: [skill_id], references: [id])
  skill_id     BigInt?
}

On point. After finished editing the schema, running Prisma Migrate: npx prisma migrate dev --name endorsements created a <timestamp>_endorsements_migration.sql file for me:

-- CreateTable
CREATE TABLE `skills` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `endorsements` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `endorsed_by` VARCHAR(255) NOT NULL,
    `skill_id` BIGINT,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

endorsements is ready to be merged to main! I can then shut down the two opened connections to endorsements and shadow, and also delete endorsements.

The rest of the feature is just Next.js you know and love: API endpoints, getStaticProps, etc. Check out my first PR and the follow-up PR for adding skill_categories.

Tips

Mapping Prisma schema with the databases

You might want to name your database in a hyphen_case, while at the same time also want to stick with the Prisma schema naming convention using PascalCase as I do. In this case, use @@map():


model SkillCategory {
  id                 BigInt  @id @default(autoincrement())
  name               String  @db.VarChar(255)
  skills_in_category Skill[]

  @@map(name: "skill_categories")
}

model Skill {
  id           BigInt        @id @default(autoincrement())
  name         String        @db.VarChar(255)
  endorsements Endorsement[]

  skill_category    SkillCategory? @relation(fields: [skill_category_id], references: [id])
  skill_category_id BigInt?

  @@unique([name])
  @@map(name: "skills")
}

model Endorsement {
  id          BigInt  @id @default(autoincrement())
  endorsed_by String  @db.VarChar(255)
  email       String  @db.VarChar(255)
  skill       Skill?  @relation(fields: [skill_id], references: [id])
  skill_id    BigInt?

  @@map(name: "endorsements")
}

Manipulate data with Prisma Studio

So far we have used Prisma Schema and Prisma Migrate in our workflow. The third tool from Prisma is Prisma Studio, really useful when you want to do direct operations on the database. Make sure you have the correct DATABASE_URL in .env before firing this up.

Conclusion

You should be able to adapt the workflow and use it in your projects by the end of this article. Here's' the workflow again:

Create a new database branch on PlanetScale pscale branch <branch> --database <database>
Connect to the newly created branch and shadow branch using two separate terminals:
- pscale connect <database> <branch_name> --port 3309
- pscale connect <database> shadow --port 3310
Branch on git: git branch new_branch
Start creating migrations by editing schema.prisma
Run npx prisma migrate dev --name <name> in another terminal.
Create deploy request: pscale deploy-request create <database> <branch>
Merge the deploy request and PR on GitHub.
Close the opened connections.
Rinse and repeat.

This workflow enabled a delightful Fullstack experience, as I hardly ever need to leave my VSCode & Terminal to change my schema. If you like this blog post, consider endorse me! 😉

Check out David Parks's post, he wrote an excellent article about the same subject. My article offers slightly newer information on how to set up Prisma regardings the referentialIntegrity value.