Skip to content

010-data-migration.mdx

Latest commit

 

History

History
280 lines (207 loc) · 6.22 KB

010-data-migration.mdx

File metadata and controls

280 lines (207 loc) · 6.22 KB
title Migrate data using the expand and contract pattern
metaTitle How to migrate data with Prisma ORM using the expand and contract pattern
description Learn how to perform data migrations using the expand and contract pattern with Prisma ORM
sidebar_label Expand-and-contract migrations
completion_time 10 min
image /img/guides/data-migration-cover.png
tags
migration
data-migration
schema
database
prisma-migrate
production
postgresql
expand-and-contract
schema-evolution
best-practices
git
deployment
ci-cd
community_section true

Introduction

When making changes to your database schema in production, it's crucial to ensure data consistency and avoid downtime. This guide shows you how to use the expand and contract pattern to safely migrate data between columns. We'll walk through a practical example of replacing a boolean field with an enum field while preserving existing data.

Prerequisites

Before starting this guide, make sure you have:

  • Node.js installed (version 18 or higher)
  • A Prisma ORM project with an existing schema
  • A supported database (PostgreSQL, MySQL, SQLite, SQL Server, etc.)
  • Access to both development and production databases
  • Basic understanding of Git branching
  • Basic familiarity with TypeScript

1. Set up your environment

1.1. Review initial schema

Start with a basic schema containing a Post model:

generator client {
  provider = "prisma-client"
  output   = "./generated/prisma"
}

datasource db {
  provider = "postgresql"
}

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

1.2. Configure Prisma

Create a prisma.config.ts file in the root of your project with the following content:

import 'dotenv/config'
import { defineConfig, env } from 'prisma/config';

export default defineConfig({
  schema: 'prisma/schema.prisma',
  migrations: {
    path: 'prisma/migrations',
  },
  datasource: {
    url: env('DATABASE_URL'),
  },
});

:::note

You'll need to install the required packages. If you haven't already, install them using your package manager:

npm install prisma @types/pg --save-dev
npm install @prisma/client @prisma/adapter-pg pg dotenv

:::info

If you are using a different database provider (MySQL, SQL Server, SQLite), install the corresponding driver adapter package instead of @prisma/adapter-pg. For more information, see Database drivers.

:::

:::

1.3. Create a development branch

Create a new branch for your changes:

git checkout -b create-status-field

2. Expand the schema

2.1. Add new column

Update your schema to add the new Status enum and field:

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
}

2.2. Create migration

Generate the migration:

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

3. Migrate the data

3.1. Create migration script

Create a new TypeScript file for the data migration:

import { PrismaClient } from '../generated/prisma/client'
import { PrismaPg } from '@prisma/adapter-pg'
import 'dotenv/config'

const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL,
})

const prisma = new PrismaClient({
  adapter,
})

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

3.2. Set up migration script

Add the migration script to your package.json:

{
  "scripts": {
    "data-migration:add-status-column": "tsx ./prisma/migrations/<migration-timestamp>/data-migration.ts"
  }
}

3.3. Execute migration

  1. Update your DATABASE_URL to point to the production database
  2. Run the migration script:
npm run data-migration:add-status-column

4. Contract the schema

4.1. Create cleanup branch

Create a new branch for removing the old column:

git checkout -b drop-published-column

4.2. Remove old column

Update your schema to remove the published field:

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

enum Status {
  Draft
  InProgress
  InReview
  Published
}

4.3. Generate cleanup migration

Create and run the final migration:

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

5. Deploy to production

5.1. Set up deployment

Add the following command to your CI/CD pipeline:

npx prisma migrate deploy

5.2. Monitor deployment

Watch for any errors in your logs and monitor your application's behavior after deployment.

Troubleshooting

Common issues and solutions

  1. Migration fails due to missing default

    • Ensure you've added a proper default value
    • Check that all existing records can be migrated

  2. Data loss prevention

    • Always backup your database before running migrations
    • Test migrations on a copy of production data first

  3. Transaction rollback

    • If the data migration fails, the transaction will automatically rollback
    • Fix any errors and retry the migration

Next steps

Now that you've completed your first expand and contract migration, you can:

For more information: