Skip to main content

使用扩展和收缩模式迁移数据

10 min

介绍​

¥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（版本 18 或更高版本）

    ¥Node.js installed (version 18 or higher)

  • 一个已设置好模式的 Prisma ORM 项目

    ¥A Prisma ORM project with an existing schema

  • 支持的数据库（PostgreSQL、MySQL、SQLite、SQL Server 等）

    ¥A supported database (PostgreSQL, MySQL, SQLite, SQL Server, etc.)

  • 访问开发和生产数据库

    ¥Access to both development and production databases

  • Git 分支基础知识

    ¥Basic understanding of Git branching

  • 熟悉 TypeScript 基本知识

    ¥Basic familiarity with TypeScript

1. 设置环境​

¥ Set up your environment

1.1.检查初始模式​

¥1.1. Review initial schema

从包含 Post 模型的基本架构开始：

¥Start with a basic schema containing a Post model:

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

1.2.创建一个开发分支​

¥1.2. Create a development branch

创建一个新分支以记录你的更改：

¥Create a new branch for your changes:

git checkout -b create-status-field

2. 扩展架构​

¥ Expand the schema

2.1.添加新列​

¥2.1. Add new column

更新你的架构以添加新的 Status 枚举和字段：

¥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.创建迁移​

¥2.2. Create migration

生成迁移：

¥Generate the migration:

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

3. 迁移数据​

¥ Migrate the data

3.1.创建迁移脚本​

¥3.1. Create migration script

为数据迁移创建一个新的 TypeScript 文件：

¥Create a new TypeScript file for the data migration:

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

3.2.设置迁移脚本​

¥3.2. Set up migration script

将迁移脚本添加到 package.json 中：

¥Add the migration script to your package.json:

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

3.3.执行迁移​

¥3.3. Execute migration

  1. 更新 DATABASE_URL 以指向生产数据库

    ¥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.创建清理分支​

¥4.1. Create cleanup branch

创建一个新分支以删除旧列：

¥Create a new branch for removing the old column:

git checkout -b drop-published-column

4.2.删除旧列​

¥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.生成清理迁移​

¥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.设置部署​

¥5.1. Set up deployment

将以下命令添加到你的 CI/CD 管道：

¥Add the following command to your CI/CD pipeline:

npx prisma migrate deploy

5.2.监控部署​

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


Stay connected with Prisma

Continue your Prisma journey by connecting with our active community. Stay informed, get involved, and collaborate with other developers:

We genuinely value your involvement and look forward to having you as part of our community!