In this tutorial, we’re going to learn how to run automatic Prisma migrations in PlanetScale as part of our deployment process.

From a high level, Prisma Migrate is a imperative database schema migration tool that enables you to keep your database in sync with your Prisma schema as it evovles.

• git clone this example project and run npm install.
• Install the PlanetScale CLI.
• Authenticate the CLI with the following command:

Copied
pscale auth login

Prisma migrations follow the PlanetScale non-blocking schema change workflow. First, the migration is applied to a development branch and then the development branch is merged into the main production database.

Let's begin with an example flow for running Prisma migrations in PlanetScale:

1. Create a new prisma-playground database:

Copied
pscale db create prisma-playground

Make sure to go to your database's Settings page to turn on "Automatically copy migration data" for Prisma after the database is created.

2. Create a shadow database branch for Prisma from the default main branch:

Copied
pscale branch create prisma-playground shadow

3. Create an .env file:

Copied
# Environment variables declared in this file are automatically made available to Prisma.
# See the documentation for more detail: https://pris.ly/d/prisma-schema#using-environment-variables

# Prisma supports the native connection string format for PostgreSQL, MySQL, SQLite, SQL Server (Preview) and MongoDB (Preview).
# See the documentation for all the connection string options: https://pris.ly/d/connection-strings

DATABASE_URL="mysql://root@127.0.0.1:3309/prisma-playground"
SHADOW_DATABASE_URL="mysql://root@127.0.0.1:3310/prisma-playground"

4. Connect to the two branches:

Copied
pscale connect prisma-playground main --port 3309

And in another terminal:

Copied
pscale connect prisma-playground shadow --port 3310

5. In another terminal, use the prisma migrate dev command to generate and apply migrations from the schema defined in prisma/schema.prisma:

Copied
npx prisma migrate dev --name init

This Prisma command replays the existing migration history in the shadow database, applies pending migrations, generates a new migration from any changes you made, applies all unapplicated migrations to the main branch, and updates the _prisma_migrations table.

The prisma migrate dev command will create a SQL file like prisma/migrations/20210806002614_init/migration.sql (the exact folder will match the current timestamp and provided name).

Along with the creating the new tables (User, Post, and Profile), Prisma has updated the _prisma_migrations table with status of your first migration.

6. Now that the intial schema has been added, promote your main branch to production status:

Copied
pscale branch promote prisma-playground main

Our first example migration flow went well, but what happens when you need to run further migrations?

Let’s take a look:

1. Create a new development branch from main, add-subtitle-to-posts:

Copied
pscale branch create prisma-playground add-subtitle-to-posts

As part of creating that branch, PlanetScale copies every row from main’s _prisma_migrations table over to your new development branch.

2. Close the proxy connection to your main branch (if open) and connect to the new development and shadow branches:

Copied
pscale connect prisma-playground add-subtitle-to-posts --port 3309

And in another terminal:

Copied
pscale connect prisma-playground shadow --port 3310

4. In the prisma/schema.prisma file, update the Post model:

Add a new subtitle field to Post:

Copied
subtitle  String   @db.VarChar(255)

5. Create a second Prisma migration for your table and call it add-subtitle-to-posts:

Copied
npx prisma migrate dev --name add-subtitle-to-posts

Prisma migrate checks _prisma_migrations to ensure your create_users migration already ran and proceeds to only run your new migration. In some cases, it might detect drift depending on your change.

6. Open a deploy request for your add-subtitle-to-posts branch, so that you can deploy these changes to main.

You can complete the deploy request either in the web app or with the pscale deploy-request command.

Copied
pscale deploy-request create prisma-playground add-subtitle-to-posts

Copied
pscale deploy-request deploy prisma-playground 1

7. Once the deploy request is merged, you can see the results in your main branch's Post and _prisma_migrations tabled:

Copied
pscale shell prisma-playground main

Copied
describe Post;

SELECT * from _prisma_migrations\G

Now that you've successfully conducted your first automatic Prisma migration in PlanetScale and know how to handle future migrations, it's time to deploy your application with a PlanetScale database! Let's learn how to deploy an application with a PlanetScale database to Vercel.

Get help from PlanetScale's support team, or join our GitHub Discussion board to see how others are using PlanetScale.