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

    ¥Node.js installed (version 20 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"
  output   = "./generated/prisma"
}

datasource db {
  provider = "postgresql"
}

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

1.2.配置 Prisma​

¥1.2. Configure Prisma

在项目根目录中创建 prisma.config.ts 文件，并添加以下内容：

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

prisma.config.ts
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'),
  },
});
注意

你需要安装所需的软件包。如果你尚未安装，请使用包管理器安装：

¥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
信息

如果你使用其他数据库提供商（MySQL、SQL Server、SQLite），请安装相应的驱动程序适配器包，而不是 @prisma/adapter-pg。欲了解更多信息，请参阅 数据库驱动程序。

¥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.创建一个开发分支​

¥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.添加新列​

¥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

然后生成 Prisma 客户端：

¥Then generate Prisma Client:

npx prisma generate

3. 迁移数据​

¥ Migrate the data

3.1.创建迁移脚本​

¥3.1. Create migration script

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

¥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.设置迁移脚本​

¥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

然后生成 Prisma 客户端：

¥Then generate Prisma Client:

npx prisma generate

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!