Learn how to migrate your schema in a development environment using Prisma Migrate

To get started with Prisma Migrate, start by adding some models to your schema.prisma

schema.prisma

datasource db {
  provider = "postgresql"
}

model User { 
  id    Int    @id @default(autoincrement()) 
  name  String
  posts Post[]
}

model Post { 
  id        Int     @id @default(autoincrement()) 
  title     String
  published Boolean @default(true) 
  authorId  Int
  author    User    @relation(fields: [authorId], references: [id]) 
} 

Create an initial migration

Create an initial migration using the prisma migrate command:

This will generate a migration with the appropriate commands for your database.

migration.sql

CREATE TABLE "User" (
  "id" SERIAL,
  "name" TEXT NOT NULL,
  PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "Post" (
  "id" SERIAL,
  "title" TEXT NOT NULL,
  "published" BOOLEAN NOT NULL DEFAULT true,
  "authorId" INTEGER NOT NULL,
  PRIMARY KEY ("id")
);
-- AddForeignKey
ALTER TABLE
  "Post"
ADD
  FOREIGN KEY("authorId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

Your Prisma schema is now in sync with your database schema and you have initialized a migration history:

migrations/
  └─ 20210313140442_init/
    └─ migration.sql

Note: The folder name will be different for you. Folder naming is in the format of YYYYMMDDHHMMSS_your_text_from_name_flag.

Additional migrations

Now say you add additional fields to your model

schema.prisma

model User {
  id       Int    @id @default(autoincrement())
  jobTitle String
  name     String
  posts    Post[]
}

You can run prisma migrate again to update your migrations

migration.sql

  -- AlterTable
ALTER TABLE
  "User"
ADD
  COLUMN "jobTitle" TEXT NOT NULL;

Your Prisma schema is once again in sync with your database schema, and your migration history contains two migrations:

migrations/
  └─ 20210313140442_init/
    └─ migration.sql
  └─ 20210313140442_added_job_title/
    └─ migration.sql

Committing to versions control

Your migration history can be committed to version control and use to deploy changes to test environments and production.

It's possible to integrate Prisma migrations to an existing project.

Introspect to create or update your Prisma schema

Make sure your Prisma schema is in sync with your database schema. This should already be true if you are using a previous version of Prisma Migrate.

Create a baseline migration

Create a baseline migration that creates an initial history of the database before using Prisma migrate. This migrations contains the data that must be maintained, which means the database cannot be reset. This tells Prisma migrate to assume that one or more migrations have already been applied. This prevents generated migrations from failing when they try to create tables and fields that already exist.

To create a baseline migration:

• If you already have a prisma/migrations folder, delete, move, rename, or archive this folder.

• Create a new prisma/migrations directory.

• Then create another new directory with your preferred name. What's important is to use a prefix of 0_ so that Prisma migrate applies migrations in a lexicographic order. You can use a different value such as the current timestamp.

• Generate a migration and save it to a file using prisma migrate diff:

• Review the generated migration.

Work around features not supported by Prisma Schema Language

To include unsupported database features that already exist in the database, you must replace or modify the initial migration SQL:

• Open the migration.sql file generated in the Create a baseline migration section.

• Modify the generated SQL. For example:

  • If the changes are minor, you can append additional custom SQL to the generated migration. The following example creates a trigger function:

migration.sql

/* Generated migration SQL */

CREATE OR REPLACE FUNCTION notify_on_insert() 
RETURNS TRIGGER AS $$ 
BEGIN
  PERFORM pg_notify('new_record', NEW.id::text); 
  RETURN NEW; 
END; 
$$ LANGUAGE plpgsql; 

• If the changes are significant, it can be easier to replace the entire migration file with the result of a database dump:

  • mysqldump
  • pg_dump.

  When using pg_dump for this, you'll need to update the search_path as follows with this command: SELECT pg_catalog.set_config('search_path', '', false);, otherwise you'll run into the following error: The underlying table for model '_prisma_migrations' does not exist.

Apply the initial migrations

To apply your initial migration(s):

• Run the following command against your database:

• Review the database schema to ensure the migration leads to the desired end-state (for example, by comparing the schema to the production database).

The new migration history and the database schema should now be in sync with your Prisma schema.

Commit the migration history and Prisma schema

Commit the following to source control:

• The entire migration history folder

• The schema.prisma file

• Refer to the Deploying database changes with Prisma Migrate guide for more on deploying migrations to production.

• Refer to the Production Troubleshooting guide to learn how to debug and resolve failed migrations in production using prisma migrate diff, prisma db execute and/ or prisma migrate resolve.