ContextQMD
Back to Prisma

Getting started with Prisma Migrate

apps/docs/content/docs/orm/prisma-migrate/getting-started.mdx

latest7.2 KB
Original Source

Adding to a new project

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

prisma
datasource db {
  provider = "postgresql"
}

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

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

:::tip You can use native type mapping attributes in your schema to decide which exact database type to create (for example, String can map to varchar(100) or text). :::

Create an initial migration

Create an initial migration using the prisma migrate command:

npm
npx prisma migrate dev --name init

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

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:

text
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

prisma
model User {
  id       Int    @id @default(autoincrement())
  jobTitle String // [!code ++]
  name     String
  posts    Post[]
}

You can run prisma migrate again to update your migrations

npm
npx prisma migrate dev --name added_job_title
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.

Adding to an existing project

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.

npm
npx prisma db pull

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 the 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:
npm
npx prisma migrate diff \
  --from-empty \
  --to-schema prisma/schema.prisma \
  --script > prisma/migrations/0_init/migration.sql
  • 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:
sql
/* Generated migration SQL */

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

  • If the changes are significant, it can be easier to replace the entire migration file with the result of a database 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.

:::tip 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 face can't create constraint errors :::

Apply the initial migrations

To apply your initial migration(s):

  • Run the following command against your database:
npm
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 migration history and Prisma schema

Commit the following to source control:

  • The entire migration history folder
  • The schema.prisma file

Going further