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:
- Detecting changes in your Prisma schema, which is the source of truth for your data model
- Generating
.sql
migration files with changes (which you can optionally modify) - 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:
Create a Prisma schema:
schema.prisma1datasource db {2 provider = "postgresql"3 url = env("DATABASE_URL")4}56model User {7 id Int @id @default(autoincrement())8 name String9 posts Post[]10}1112model Post {13 id Int @id @default(autoincrement())14 title String15 published Boolean @default(true)16 authorId Int17 author User @relation(fields: [authorId], references: [id])18}Migrate your schema - you will be prompted to name the migration file (for example,
first_migration
):$prisma migrate dev --preview-featureShow CLI outputPrisma Migrate creates a
migration.sql
file with the following SQL and runs it against your database:-- CreateTableCREATE TABLE "User" ("id" SERIAL,"name" TEXT NOT NULL,PRIMARY KEY ("id"));-- CreateTableCREATE TABLE "Post" ("id" SERIAL,"title" TEXT NOT NULL,"published" BOOLEAN NOT NULL DEFAULT true,"authorId" INTEGER NOT NULL,PRIMARY KEY ("id"));-- AddForeignKeyALTER 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.
Change the schema by adding two new fields:
- One optional field (
bio
) - One mandatory field with a default value (
jobTitle
)
schema.prisma1datasource db {2 provider = "postgresql"3 url = env("DATABASE_URL")4}56model User {7 id Int @id @default(autoincrement())8 name String9 bio String?10 posts Post[]11 jobTitle String @default("Blogger")12}1314model Post {15 id Int @id @default(autoincrement())16 title String17 published Boolean @default(true)18 authorId Int19 author User @relation(fields: [authorId], references: [id])20}- One optional field (
Migrate your schema again:
$prisma migrate dev --preview-featureShow CLI outputPrisma 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 'Blogger';Your Prisma Schema and database are now in sync again.
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-featureShow 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
andprisma 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.
Database | Database user requirements |
---|---|
SQLite | No special requirements. |
MySQL | Database user must have CREATE, ALTER, DROP, REFERENCES ON *.* privileges |
PostgreSQL | The user must be a super user or have CREATEDB privilege. See CREATE ROLE (PostgreSQL official documentation) |
SQL Server | The user must be a site admin or have the SERVER securable. See the official documentation. |
If you use a cloud-hosted database for development, see: Cloud-hosted shadow databases
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 databaseDatabase 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 theprisma 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.
Cloud-hosted shadow databases
Cloud providers do not generally allow you to drop and create databases. If you develop in a cloud-hosted environment:
- Create a dedicated cloud-hosted shadow database
- Add the URL to the
shadowDatabaseUrl
field:
datasource db {provider = "postgresql"url = env("DATABASE_URL")shadowDatabaseUrl = env("SHADOW_DATABASE_URL")}
Native types attributes
In 2.17.0 and later, you can use Prisma Migrate and native database type attributes together. Native database type 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 Intauthor User @relation(fields: [authorId], references: [id])}
Prisma Migrate uses the specified types when it creates a migration:
-- CreateTableCREATE TABLE "User" ("id" SERIAL,"name" VARCHAR(200) NOT NULL,PRIMARY KEY ("id"));-- CreateTableCREATE TABLE "Post" ("id" SERIAL,"title" VARCHAR(150) NOT NULL,"published" BOOLEAN NOT NULL DEFAULT true,"authorId" INTEGER NOT NULL,PRIMARY KEY ("id"));-- AddForeignKeyALTER TABLE "Post" ADD FOREIGN KEY("authorId")REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
Type mappings
Mappings by Prisma type
For type mappings organized by Prisma type, refer to the Prisma schema reference documentation.
Mappings by database provider
For type mappings organized by database provider, see:
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
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: P3014The 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.