Data migrations

Prisma ORM does not yet natively support data migrations, but you can use the expand and contract pattern to migrate your data. For example from one column into another.

This guide covers how you can use Prisma ORM with the expand and contract pattern to:

• Expand your schema with a new column
• Create and run the data migration
• Contract your schema by dropping the old column

Overview of the steps

This tutorial will walk you through the following steps:

1. Expand your schema with a new column
2. Create and run the data migration file
3. Contract your schema by dropping the old column

It also makes the following assumptions:

• The production database is accessible from the development machine
• prisma migrate dev is only run against development database
• The expanding and contracting steps are handled in separate branches

For this guide, you will modify the following schema by replacing the published boolean field with a status enum:

prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

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

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
}

Expand your schema with a new column

Checkout to a new branch from your main branch:

git checkout -b create-status-field

Make the following updates to your Prisma schema:

• Create a Status enum with the following values: Unknown, Draft, InReview, and Published
• Add a status column to the Post model
• Mark the published field as optional

prisma/schema.prisma

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean? @default(false)
  status    Status
}

enum Status {
  Unknown
  Draft
  InProgress
  InReview
  Published
}

Create a new migration to sync the Prisma schema with the database schema:

npx prisma migrate dev --name add-status-column

Prisma Migrate will give you the following warning because the field being added to the database is non-nullable, and the database contains existing data which require a default value.

Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "data-migration", schema "public" at "localhost:5401"

Error:
⚠️ We found changes that cannot be executed:

  • Step 1 Added the required column `status` to the `Post` table without a default value. There are 4 rows in this table, it is not possible to execute this step.

You can use prisma migrate dev --create-only to create the migration file, and manually modify it to address the underlying issue(s).
Then run prisma migrate dev to apply it and verify it works.

Exit from the migration step and update the schema by adding a default value for the status field by adding the @default() attribute function.

prisma/schema.prisma

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean? @default(false)
  status    Status   @default(Unknown)
}

enum Status {
  Unknown
  Draft
  InProgress
  InReview
  Published
}

Generate and execute the migration using the following command:

npx prisma migrate dev --name add-default

Create and run the data migration file

Create a data migration file

Inside the generated migration folder from the previous step, create a file called data-migration.ts file. This file will contain a data migration which will be implemented using Prisma Client.

Add the following code to migrate the data from the published field to the status field in the file you just created:

prisma/migrations/20230417131956_add-status-column/data-migration.ts

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  await prisma.$transaction(async (tx) => {
    const posts = await tx.post.findMany()
    for (const post of posts) {
      await tx.post.update({
        where: { id: post.id },
        data: {
          status: post.published ? 'Published' : 'Unknown',
        },
      })
    }
  })
}

main()
  .catch(async (e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => await prisma.$disconnect())

The data migration is wrapped in a transaction to ensure that the query is rolled back, allowing you to iterate on your data migration file

Next steps:

1. Push your changes to a remote origin and create a new pull request.
2. Once you’re happy with the changes, merge the changes to your main branch.

To apply the changes to your production database, add prisma migrate deploy as part of your deployment/ build step in CI

Run the data migration

Update the package.json file with the script to execute the data-migration file. Be sure to update the 20230417131956_add-status-column with the name of your migration file.

package.json

"scripts": {
    "dev": "tsx ./script.ts",
    "data-migration:add-status-column": "tsx ./prisma/migrations/20230417131956_add-status-column/data-migration.ts"
  },

Next steps:

1. Push your changes to a remote origin and create a new pull request.
2. Once you’re happy with the changes, merge the changes to your “main” branch.

To apply the changes to your production database, add prisma migrate deploy as part of your deployment/ build step in CI.

Run the data migration

Update the DATABASE_URL environment variable with your production database's URL. Run the data migration script:

npm run data-migration:add-status-column

Contract your schema by dropping the old column

Checkout to a separate branch on your development machine:

git checkout -b drop-published-column

Delete the published field from your schema and generate a new migration:

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean? @default(false)
  status    Status   @default(Unknown)
}

enum Status {
  Draft
  InProgress
  InReview
  Published
}

Generate a new migration:

npx prisma migrate dev --name drop-published-column

Next steps:

1. Push your changes to a remote origin and create a new pull request.
2. Once you’re happy with the changes, merge the changes to your main branch.

To apply the changes to your production database, add prisma migrate deploy as part of your deployment/ build step in CI

npx prisma migrate deploy

You have successfully:

• Migrated data from the published to status column
• Dropped the published column from your schema