Getting started with Prisma Migrate

This page explains how to get started with migrating your schema in a development environment using Prisma Migrate.

To get started with Prisma Migrate in a development environment:

Create a Prisma schema:

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])

}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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])
}

import 'dotenv/config'
import { defineConfig, env } from "prisma/config";
export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
  },
  datasource: {
    url: env("DATABASE_URL"),
  },
});

For Prisma 7, be sure to have a prisma.config.ts in the root of your project:

import 'dotenv/config'

import { defineConfig, env } from "prisma/config";

export default defineConfig({

  schema: "prisma/schema.prisma",

  migrations: {

    path: "prisma/migrations",

  },

  datasource: {

    url: env("DATABASE_URL"),

  },

});

1. Create the first migration:

   prisma migrate dev --name init

     -- CreateTable
   
   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;

   Note: If you do not provide a --name, Prisma CLI will prompt you for a name.

   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.

2. Add additional fields to your schema:

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

3. Create the second migration:

   prisma migrate dev --name added_job_title

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

You now have a migration history that you can source control and use to deploy changes to test environments and production.

The steps involved in adding Prisma Migrate to your existing project are:

1. Introspect your database to update your Prisma schema
2. Create a baseline migration
3. Update your schema or migration to workaround features not supported by Prisma Schema Language
4. Apply the baseline migration
5. Commit the migration history and 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.

prisma db pull

Baselining is the process of initializing a migration history for a database that:

• Existed before you started using Prisma Migrate
• Contains data that must be maintained (like production), which means that the database cannot be reset

Baselining 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:

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

2. Run the following command to create a migrations directory inside with your preferred name. This example will use 0_init for the migration name:

   mkdir -p prisma/migrations/0_init

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

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

4. Review the generated migration.

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

1. Open the migration.sql file generated in the Create a baseline migration section.
2. 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 partial index:

  /* Generated migration SQL */
  
  CREATE UNIQUE INDEX tests_success_constraint ON posts (subject, target) 
  
    WHERE success; 

• 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. :::info Note that the order of the tables matters when creating all of them at once, since foreign keys are created at the same step. Therefore, either re-order them or move constraint creation to the last step after all tables are created, so you won't facecan't create constraint` errors :::

To apply your initial migration(s):

1. Run the following command against your database:

   • bun
   • pnpm
   • yarn
   • npm

   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

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

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