Prisma Migrate

Basic example

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

1. Create a Prisma schema:

   schema.prisma
   1datasource db {
   2  provider = "postgresql"
   3  url      = env("DATABASE_URL")
   4}
   5
   6model User {
   7  id    Int      @id @default(autoincrement())
   8  name  String
   9  posts Post[]
   10}
   11
   12model Post {
   13  id        Int     @id @default(autoincrement())
   14  title     String
   15  published Boolean @default(true)
   16  authorId  Int
   17  author    User    @relation(fields: [authorId], references: [id])
   18}

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)

   schema.prisma
   1datasource db {
   2  provider = "postgresql"
   3  url      = env("DATABASE_URL")
   4}
   5
   6model User {
   7  id    Int      @id @default(autoincrement())
   8  name  String
   9  bio   String?
   10  posts Post[]
   11  jobTitle  String  @default("Blogger")
   12}
   13
   14model Post {
   15  id        Int     @id @default(autoincrement())
   16  title     String
   17  published Boolean @default(true)
   18  authorId  Int
   19  author    User    @relation(fields: [authorId], references: [id])
   20}

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

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

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

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

• Guide: Add Prisma Migrate to an existing project

Limitations and known issues

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