Getting started with Prisma Migrate

To use Prisma Migrate, add some models to your 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 using the prisma migrate command:

bunx prisma migrate dev --name init

pnpm prisma migrate dev --name init

yarn prisma migrate dev --name init

npx prisma migrate dev --name init

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

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.

Suppose you add a field to your model:

model User {

  id       Int    @id @default(autoincrement())

  jobTitle String

  name     String

  posts    Post[]

}

You can run prisma migrate again to update your migrations

bunx prisma migrate dev --name added_job_title

pnpm prisma migrate dev --name added_job_title

yarn prisma migrate dev --name added_job_title

npx prisma migrate dev --name added_job_title

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

migrations/
  └─ 20210313140442_init/
    └─ migration.sql
  └─ 20210313140442_added_job_title/
    └─ 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

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.

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.

bunx prisma db pull

pnpm prisma db pull

yarn prisma db pull

npx prisma db pull

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:

bunx prisma migrate diff \

  --from-empty \

  --to-schema prisma/schema.prisma \

  --script > prisma/migrations/0_init/migration.sql

pnpm prisma migrate diff \
  --from-empty \
  --to-schema prisma/schema.prisma \
  --script > prisma/migrations/0_init/migration.sql

yarn prisma migrate diff \
  --from-empty \
  --to-schema prisma/schema.prisma \
  --script > prisma/migrations/0_init/migration.sql

npx prisma migrate diff \
  --from-empty \
  --to-schema prisma/schema.prisma \
  --script > prisma/migrations/0_init/migration.sql

• Review the generated migration.

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:

/* 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.

To apply your initial migration(s):

• Run the following command against your database:

bunx prisma migrate resolve --applied 0_init

pnpm prisma migrate resolve --applied 0_init

yarn prisma migrate resolve --applied 0_init

npx prisma migrate resolve --applied 0_init

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

• Prisma Client CRUD guide once your schema changes are in place and you want to start querying data
• Generating Prisma Client if you need to adjust client output or runtime behavior after migrations
• Quickstart with Prisma Postgres if you want a managed Postgres database without running one yourself
• Review pricing if you're evaluating Prisma Postgres or managed workflows for production use