Skip to content

Automatic Prisma migrations

To ensure PlanetScale works well with the Prisma development process, we implemented the ability to automatically copy Prisma migration metadata as part of our deployment process.

Tip
If you are using PlanetScale with Prisma, go to your database's Settings page in the web app and enable "Automatically copy migration data." Select "Prisma" as the migration framework. When enabled, this setting updates the _prisma_migrations table each time you branch with the latest migration. If disabled, running prisma migrate dev will try to run all migrations every time, instead of only the latest one.

Introduction

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

Quick introduction to Prisma Migrate

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.

Tip
When you merge a deploy request in PlanetScale, the _prisma_migrations table in main gets automatically updated with the migrations from your branch.

Prerequisites for this tutorial

Copied
pscale auth login

Execute your first Prisma migration on PlanetScale

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.

  1. Create a shadow database branch for Prisma from the default main branch:
Copied
pscale branch create prisma-playground shadow
  1. 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"
  1. 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
  1. 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).

Tip
You can learn more about the prisma migrate dev command in the Prisma docs.

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

  1. Now that the intial schema has been added, promote your main branch to production status:
Copied
pscale branch promote prisma-playground main

Execute succeeding Prisma migrations in PlanetScale

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.

  1. 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
  1. In the prisma/schema.prisma file, update the Post model:

Add a new subtitle field to Post:

Copied
subtitle String @db.VarChar(255)
  1. 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.

  1. 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
  1. 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

What's next?

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.

Need help?

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

Was this page useful?
Last updated on November 19, 2021
Help us improve this page
PrivacyTerms© 2021 PlanetScale Inc.