Expand-and-contract migrations
When you change your database schema in production, you need to keep the data consistent and avoid downtime. This guide shows you how to use the expand and contract pattern to safely migrate data between columns. The example replaces a boolean field with an enum field while preserving existing data.
Before starting this guide, make sure you have:
- Node.js installed (version 20 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
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)
}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"),
},
});Create a new branch for your changes:
git checkout -b create-status-fieldUpdate 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
}Generate the migration:
bunx prisma migrate dev --name add-status-columnpnpm run data-migration:add-status-columnyarn data-migration:add-status-columnnpm run data-migration:add-status-columnThen generate Prisma Client:
bunx prisma generatepnpm prisma migrate dev --name drop-published-columnyarn prisma migrate dev --name drop-published-columnnpx prisma migrate dev --name drop-published-columnThen generate Prisma Client:
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());Add the migration script to your package.json:
{
"scripts": {
"data-migration:add-status-column": "tsx ./prisma/migrations/<migration-timestamp>/data-migration.ts"
}
}- Update your DATABASE_URL to point to the production database
- Run the migration script:
bun run data-migration:add-status-columnpnpm prisma generateyarn prisma generatenpx prisma generateCreate a new branch for removing the old column:
git checkout -b drop-published-columnUpdate your schema to remove the published field:
model Post {
id Int @id @default(autoincrement())
title String
content String?
status Status @default(Draft)
}
enum Status {
Draft
InProgress
InReview
Published
}Create and run the final migration:
bunx prisma migrate dev --name drop-published-columnpnpm prisma migrate deployyarn prisma migrate deploynpx prisma migrate deployThen generate Prisma Client:
bunx prisma generateAdd the following command to your CI/CD pipeline:
bunx prisma migrate deployWatch for any errors in your logs and monitor your application's behavior after deployment.
-
Migration fails due to missing default
- Ensure you've added a proper default value
- Check that all existing records can be migrated
-
Data loss prevention
- Always backup your database before running migrations
- Test migrations on a copy of production data first
-
Transaction rollback
- If the data migration fails, the transaction will automatically rollback
- Fix any errors and retry the migration
Now that you've completed your first expand and contract migration, you can:
- Learn more about Prisma Migrate
- Explore schema prototyping
- Understand customizing migrations
For more information: