Nextjs 13 With Prisma (MySQL) Planetscale and Vercel

In the last two weeks I’ve been hacking away on a Next.js 13 application to learn more about React server components.

I have used Prisma with a local Postgres database for development. I normally spin up my databases via docker.

When it came to deployment, it was easy enough to throw my app on Vercel, but I have been trouble finding a good free offering for Postgres.

In the past, I have used Heroku for such toy projects, but they’ve stopped their free tier. There have been new companies stepping up, for example, Railway, Render.com, Fly.io or Supabase.

Unfortunately, most of them have also placed limits on their free usages. Take Supabase: their Postgres instance only lives for 90 days, then it will be paused if the database doesn’t receive any requests. For a hobby project, that was not a good option for me. I don’t want to resume the Postgres database manually via the Supabase web interface.

I had high hopes for Fly.io. They offer a usage-based plan with a generous free threshold.
Turns out that you need a dedicated ipv4 address for the database for it to play nicely with Vercel’s edge functions.
This costs USD $2/month, which I don’t want to pay for a pet project. Sorry, folks.

In the end, I’ve switched my database to MySQL and used Planetscale as my production database. The experience was seemless.

In the following article I’ll explain how I would set up a new project with Next.js and MySQL.

This guide assumes that you have Node.js, Docker and docker compose installed and working.

Local Development

1. Create a new Next.js application

npx create-next-app

Add prisma:

npm i -D prisma

2. Setup MySQL via docker compose & Docker

Create a folder for the database:

mkdir db

Add Dockerfile to it (db/Dockerfile):

FROM bitnami/mysql:8.0.34

ADD local-init.sql /docker-entrypoint-initdb.d

We need an initialization script: db/local-init.sql:

CREATE USER 'user'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON * . * TO 'user'@'%';

Why?

We want to reach the database on localhost on our machine. By default, MySQL and MariaDB restrict connections other than to the local machine. The Docker container runs on a separate network. To connect from your local machine, you’ll need to use the % wildcard as the host.

Now we need a docker-compose.yml in the root folder of our project:

services:
  db:
    build:
      context: ./db
      dockerfile: Dockerfile
    ports:
      - 33306:3306
    environment:
      - MYSQL_DATABASE=<database name>
    volumes:
      - db-data:/var/lib/mysql:delegated

volumes:
  db-data:

Replace the <database name> with the name you want for your project.

Now you should be able to use the database for local development via Docker and docker compose:

docker compose up -d

The connection string for MysQL would be:

DATABASE_URL="mysql://user:password@localhost:33306/<database-name>?schema=public"

(Replace the database name.)

Prisma Schema

Create a database schema.

Planetscale has some differences to other databases which we need to take care of. For instance, you cannot use foreign key constraints, but need to emulate relations.

Here is how an example database schema would look like:

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma" // use this to emulate relations
}

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

model User {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  published Boolean  @default(false)
  title     String   @db.VarChar(255)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int

  @@unique([authorId, title]) // important
  @@index([authorId, author]) // important
}

enum Role {
  USER
  ADMIN
}

It’s important to use indexes because Prisma won’t do it for you implicitly when using the relation mode.

Deployment

Planetscale

Create new database and click on the button “Get connection strings”.

There’s an option for Prisma which you can use.

You need one admin account to run the migrations and one read-write one for the actual application. I have simply used the “main” branch for my production database.

Planetscale offers branching strategies (like git) for your database. I didn’t need those, as my development only happens locally, and I need Planetscale only for the final production deployment.

For that, I use a hacky technique.

I temporarily change my connection string in my local .env file to the admin connection string of my Planetscale DB:

DATABASE_URL="mysql://xxxxx:xxxxxx@aws.connect.psdb.cloud/<database-name>?schema=public&sslaccept=strict"

Please replace with your admin connection string. Important: add sslaccept=strict.

Then run migrations:

npx prisma migrate deploy

For an alternative solution, you can read the blog post by shadcn.

Vercel

If you use GitHub or GitLab, it’s easy to deploy your Next.js application to Vercel.

You can import the project and are good to go.

As we’re using Prisma, there’s a little workaround needed for caching the Prisma client. Add the following postinstall script to package.json:

{
  "scripts": {
    "postinstall": "prisma generate"
  }
}

Add the database connection string for your normal read/write account to the Vercel environment variables:

DATABASE_URL="mysql://xxxxx:xxxxxx@aws.connect.psdb.cloud/<database-name>?sslaccept=strict"

Deploy and done.

Recap

Using Planetscale with Vercel is a dream combo. The integration is very easy. I was pleasantly surprised by Planetscale.
Especially in comparison to Fly.io it was very straightforward to spin up a database and connect it to external services (local machine and Vercel). They don’t even require a credit card for their hobby plan.

Links

• Next.js, Prisma, PlanetScale and Vercel