Prisma Migrate

Prisma Migrate is an imperative database schema migration tool that enables you to make changes to your database schema. Migrations are auto-generated based on schema changes but are fully customizable. A simple Prisma Migrate workflow includes:

  1. Detecting changes in your Prisma schema, which is the source of truth for your data model
  2. Generating .sql migration files with changes (which you can optionally modify)
  3. Applying the migration files to your database

Prisma Migrate was introduced as a Preview feature in 2.13.0 and replaces legacy Prisma Migrate.

Basic example

The following example demonstrates how to get started with Prisma Migrate:

  1. Create a Prisma schema:

    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])
    }
  2. Migrate your schema - you will be prompted to name the migration file (for example, first_migration):

    $prisma migrate dev --preview-feature
    Show CLI output

    Prisma Migrate creates a migration.sql file with the following SQL and runs it against your database:

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

    After your first migration, your Prisma Schema and database are now in sync.

  3. Change the schema by adding two new fields:

    • One optional field (bio)
    • One mandatory field with a default value (jobTitle)
    datasource db {
    provider = "postgresql"
    url = env("DATABASE_URL")
    }
    model User {
    id Int @id @default(autoincrement())
    name String
    bio String?
    posts Post[]
    jobTitle String @default("Blogger")
    }
    model Post {
    id Int @id @default(autoincrement())
    title String
    published Boolean @default(true)
    authorId Int
    author User @relation(fields: [authorId], references: [id])
    }
  4. Migrate your schema again:

    $prisma migrate dev --preview-feature
    Show CLI output

    Prisma Migrate creates a second migration.sql file with the following contents and runs it against your database:

    ALTER TABLE "User" ADD COLUMN "bio" TEXT,
    ADD COLUMN "jobTitle" TEXT NOT NULL DEFAULT E'Blogger';

    Your Prisma Schema and database are now in sync again.

  5. Push the schema and the prisma/migrations folder to a shared repository - your team can now re-play the changes you made on their local database and contribute their own migrations:

    $prisma migrate dev --preview-feature
    Show CLI output

This example shows a 'happy path' without any complicating factors such as a populated database. For more advanced examples, see Prisma Migrate flows.

Shadow database

Prisma Migrate uses a second, temporary database when you run development-focused commands such as prisma migrate dev --preview-feature and prisma migrate reset --preview-feature. The shadow database is created and deleted each time you run a command, and is used for tasks such as:

  • Replaying the migration history
  • Detecting drift

Note: The shadow database is not required for production-focused commands: prisma migrate resolve --preview-feature and prisma migrate deploy --preview-feature.

Shadow database user permissions

In order to create and delete the shadow database when using development commands such as migrate dev and migrate reset, Prisma Migrate currently requires that the database user defined in your datasource has permission to create databases.

DatabaseDatabase user requirements
SQLiteNo special requirements.
MySQLDatabase user must have CREATE, DROP ON *.* privileges
PostgreSQLThe user must be a super user or have CREATEDB privilege. See CREATE ROLE (PostgreSQL official documentation)
SQL ServerThe user must be a site admin or have the SERVER securable. See the official documentation.

Prisma Migrate throws the following error if it cannot create the shadow database with the credentials your connection string supplied:

Error: A migration failed when applied to the shadow database
Database error: Error querying the database: db error: ERROR: permission denied to create database

The resolve this error:

  • If you are working locally, we recommend that you change the database user's privileges.
  • If you are developing against a cloud-based database (for example, on Heroku), we recommend that you change to a local web environment (such as running your development database on Docker) for the time being and follow the discussions on GitHub issue #4571.
  • If you are developing against a cloud-based database (for example, on Heroku) and cannot switch, please remain on version 2.12.0 and follow the discussions on GitHub issue #4571.
  • If you are developing against a cloud-based database (for example, on Heroku) and are currently prototyping such that you don't care about generated migration files and only need to apply your Prisma data model to the database schema, you can run prisma db push instead of the prisma migrate dev command.

Important: The shadow database is only required in a development environment (specifically for the prisma migrate dev --preview-feature command) - you do not need to make any changes to your production environment.

Native types field attributes

In 2.15.0 and later, you can use Prisma Migrate and the native types Preview together. Native type field attributes allow you to specify which specific native type should be created in the database.

See type mappings for a complete list of supported native types for each Prisma type.

In the following example, the name and title fields have a @VarChar(X) type attribute:

datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model User {
id Int @id @default(autoincrement())
name String @db.VarChar(200)
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String @db.VarChar(150)
published Boolean @default(true)
authorId Int
author User @relation(fields: [authorId], references: [id])
}

Prisma Migrate uses the specified types when it creates a migration:

-- CreateTable
CREATE TABLE "User" (
"id" SERIAL,
"name" VARCHAR(200) NOT NULL,
PRIMARY KEY ("id")
);
-- CreateTable
CREATE TABLE "Post" (
"id" SERIAL,
"title" VARCHAR(150) 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;

Type mappings

If you are using the nativeTypes preview, refer to the following documentation: Native types mapping reference

If you are not using the nativeTypes preview, refer to the following documentation for information about how Prisma Migrate maps Prisma types to native database types:

Migration flows

See migration flows for more advanced flows, including:

  • Drafting and editing a migration.sql file before applying it
  • Working with a populated database

CLI command reference

See Prisma Migrate CLI reference .

Guides

Limitations and known issues

You cannot switch database providers

Prisma Migrate generates SQL files that are specific to your provider. This means that you cannot use PostgreSQL in production and SQLite in development, because the migrations cannot run.

In 2.15.0 and later, Prisma Migrate detects when the migrations do not match the configured provider and prints a helpful error message. For example, if your migrations are for a PostgreSQL database but you are using a provider is set to mysql:

Error: P3014
The datasource provider `mysql` specified in your schema does not match the one specified in the migration_lock.toml. You will encounter errors when you try to apply migrations generated for a different provider. Please archive your current migration directory at a different location and start a new migration history with `prisma migrate dev`.

Lack of rollbacks / down migrations

Prisma Migrate does not currently roll back a migration without resetting the database. For a simple and integrated way to re-create data in your development database when the database is reset, check out our seeding guide.

Data loss when resetting database

In a development environment, Prisma Migrate sometimes prompts you to reset the database. Resetting drops and recreates the database, which results in data loss. The database is reset when:

  • You call prisma migrate reset --preview-feature explicitly
  • You call prisma migrate dev --preview-feature and Prisma Migrate detects drift in the database or a migration history conflict

The prisma migrate dev --preview-feature and prisma migrate reset --preview-feature commands are designed to be used in development only, and should not affect production data.

When the database is reset, if Prisma Migrate detects a seed script, it will trigger seeding.

Note: For a simple and integrated way to re-create data when the database is reset, check out our seeding guide.

Edit this page on GitHub