Back to Details

prisma-migration-assistant

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Prisma Migration Assistant

Prisma迁移助手

Plan and execute safe Prisma migrations with confidence.
放心地规划并执行安全的Prisma迁移。

Migration Planning Workflow

迁移规划流程

typescript
// 1. Update schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  // NEW: Split name into firstName and lastName
  firstName String?
  lastName  String?
  // OLD: name      String  // Will remove this
  createdAt DateTime @default(now())
}

// 2. Create migration
// npx prisma migrate dev --name split_user_name --create-only

// 3. Review generated SQL
// 4. Add data migration
// 5. Test migration
// 6. Apply to production
typescript
// 1. 更新schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  // 新增：将name拆分为firstName和lastName
  firstName String?
  lastName  String?
  // 旧字段：name      String  // 将会删除此字段
  createdAt DateTime @default(now())
}

// 2. 创建迁移
// npx prisma migrate dev --name split_user_name --create-only

// 3. 审阅生成的SQL
// 4. 添加数据迁移
// 5. 测试迁移
// 6. 应用到生产环境

Migration Types

迁移类型

1. Additive Migration (Safe)

1. 增量迁移（安全）

prisma
// Adding new optional field - safe!
model Product {
  id          Int     @id @default(autoincrement())
  name        String
  description String?
  price       Float
  newField    String? // NEW - optional, no backfill needed
}
bash
undefined
prisma
// 添加新的可选字段 - 安全！
model Product {
  id          Int     @id @default(autoincrement())
  name        String
  description String?
  price       Float
  newField    String? // 新增 - 可选，无需回填数据
}
bash
undefined

Generate migration

生成迁移

npx prisma migrate dev --name add_product_new_field
npx prisma migrate dev --name add_product_new_field

SQL generated:

生成的SQL:

ALTER TABLE "Product" ADD COLUMN "newField" TEXT;

ALTER TABLE "Product" ADD COLUMN "newField" TEXT;

undefined
undefined

2. Column Rename (Needs Data Copy)

2. 列重命名（需复制数据）

prisma
model User {
  id         Int    @id @default(autoincrement())
  emailAddr  String @unique // Renamed from 'email'
}
sql
-- migrations/20240115_rename_email/migration.sql

-- Step 1: Add new column
ALTER TABLE "User" ADD COLUMN "emailAddr" TEXT;

-- Step 2: Copy data
UPDATE "User" SET "emailAddr" = "email";

-- Step 3: Make new column required
ALTER TABLE "User" ALTER COLUMN "emailAddr" SET NOT NULL;

-- Step 4: Add unique constraint
CREATE UNIQUE INDEX "User_emailAddr_key" ON "User"("emailAddr");

-- Step 5: Drop old column
ALTER TABLE "User" DROP COLUMN "email";
prisma
model User {
  id         Int    @id @default(autoincrement())
  emailAddr  String @unique // 从'email'重命名而来
}
sql
-- migrations/20240115_rename_email/migration.sql

-- 步骤1：添加新列
ALTER TABLE "User" ADD COLUMN "emailAddr" TEXT;

-- 步骤2：复制数据
UPDATE "User" SET "emailAddr" = "email";

-- 步骤3：设置新列为必填
ALTER TABLE "User" ALTER COLUMN "emailAddr" SET NOT NULL;

-- 步骤4：添加唯一约束
CREATE UNIQUE INDEX "User_emailAddr_key" ON "User"("emailAddr");

-- 步骤5：删除旧列
ALTER TABLE "User" DROP COLUMN "email";

3. Data Transformation (Complex)

3. 数据转换（复杂）

prisma
// Before: Single name field
// After: First and last name
model User {
  id        Int     @id @default(autoincrement())
  firstName String
  lastName  String
  // name   String  // Removed
}
sql
-- migrations/20240115_split_name/migration.sql

-- Step 1: Add new columns
ALTER TABLE "User" ADD COLUMN "firstName" TEXT;
ALTER TABLE "User" ADD COLUMN "lastName" TEXT;

-- Step 2: Data migration (split name)
-- PostgreSQL
UPDATE "User"
SET
  "firstName" = SPLIT_PART("name", ' ', 1),
  "lastName" = CASE
    WHEN array_length(string_to_array("name", ' '), 1) > 1
    THEN array_to_string((string_to_array("name", ' '))[2:], ' ')
    ELSE ''
  END
WHERE "name" IS NOT NULL;

-- Step 3: Handle NULL values
UPDATE "User"
SET
  "firstName" = COALESCE("firstName", ''),
  "lastName" = COALESCE("lastName", '');

-- Step 4: Make columns required
ALTER TABLE "User" ALTER COLUMN "firstName" SET NOT NULL;
ALTER TABLE "User" ALTER COLUMN "lastName" SET NOT NULL;

-- Step 5: Drop old column
ALTER TABLE "User" DROP COLUMN "name";
prisma
// 之前：单个name字段
// 之后：名字和姓氏字段
model User {
  id        Int     @id @default(autoincrement())
  firstName String
  lastName  String
  // name   String  // 已删除
}
sql
-- migrations/20240115_split_name/migration.sql

-- 步骤1：添加新列
ALTER TABLE "User" ADD COLUMN "firstName" TEXT;
ALTER TABLE "User" ADD COLUMN "lastName" TEXT;

-- 步骤2：数据迁移（拆分name）
-- PostgreSQL
UPDATE "User"
SET
  "firstName" = SPLIT_PART("name", ' ', 1),
  "lastName" = CASE
    WHEN array_length(string_to_array("name", ' '), 1) > 1
    THEN array_to_string((string_to_array("name", ' '))[2:], ' ')
    ELSE ''
  END
WHERE "name" IS NOT NULL;

-- 步骤3：处理NULL值
UPDATE "User"
SET
  "firstName" = COALESCE("firstName", ''),
  "lastName" = COALESCE("lastName", '');

-- 步骤4：设置列为必填
ALTER TABLE "User" ALTER COLUMN "firstName" SET NOT NULL;
ALTER TABLE "User" ALTER COLUMN "lastName" SET NOT NULL;

-- 步骤5：删除旧列
ALTER TABLE "User" DROP COLUMN "name";

4. Type Change (Risky)

4. 类型变更（有风险）

prisma
model Product {
  id    Int    @id @default(autoincrement())
  price Decimal @db.Decimal(10, 2) // Changed from Float
}
sql
-- migrations/20240115_price_to_decimal/migration.sql

-- Step 1: Add new column with correct type
ALTER TABLE "Product" ADD COLUMN "price_new" DECIMAL(10,2);

-- Step 2: Copy and convert data
UPDATE "Product"
SET "price_new" = CAST("price" AS DECIMAL(10,2));

-- Step 3: Drop old column
ALTER TABLE "Product" DROP COLUMN "price";

-- Step 4: Rename new column
ALTER TABLE "Product" RENAME COLUMN "price_new" TO "price";

-- Step 5: Make NOT NULL if required
ALTER TABLE "Product" ALTER COLUMN "price" SET NOT NULL;
prisma
model Product {
  id    Int    @id @default(autoincrement())
  price Decimal @db.Decimal(10, 2) // 从Float类型修改而来
}
sql
-- migrations/20240115_price_to_decimal/migration.sql

-- 步骤1：添加类型正确的新列
ALTER TABLE "Product" ADD COLUMN "price_new" DECIMAL(10,2);

-- 步骤2：复制并转换数据
UPDATE "Product"
SET "price_new" = CAST("price" AS DECIMAL(10,2));

-- 步骤3：删除旧列
ALTER TABLE "Product" DROP COLUMN "price";

-- 步骤4：重命名新列
ALTER TABLE "Product" RENAME COLUMN "price_new" TO "price";

-- 步骤5：若需要则设置为必填
ALTER TABLE "Product" ALTER COLUMN "price" SET NOT NULL;

Migration Sequencing

迁移排序

markdown
undefined
markdown
undefined

Migration Sequence: Add User Roles

迁移序列：添加用户角色

Phase 1: Additive (Week 1)

阶段1：增量变更（第1周）

  1. Add optional 
    role
    field
  2. Deploy application code that handles NULL roles
  3. Backfill existing users with default role
  1. 添加可选的
    role
    字段
  2. 部署可处理NULL角色的应用代码
  3. 用默认角色回填现有用户数据

Phase 2: Enforcement (Week 2)

阶段2：强制约束（第2周）

  1. Make 
    role
    field required
  2. Deploy code that requires role on creation
  3. Add database constraint
  1. 将
    role
    字段设置为必填
  2. 部署创建用户时必须指定角色的代码
  3. 添加数据库约束

Phase 3: Cleanup (Week 3)

阶段3：清理（第3周）

  1. Remove old permission checking code
  2. Verify all users have roles
undefined
  1. 删除旧的权限检查代码
  2. 验证所有用户都已分配角色
undefined

Backfill Strategies

回填策略

Small Table (< 10k rows)

小表（<1万行）

typescript
// scripts/backfill-user-roles.ts
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function backfillUserRoles() {
  const usersWithoutRoles = await prisma.user.findMany({
    where: { role: null },
  });

  console.log(`Backfilling ${usersWithoutRoles.length} users...`);

  // Single transaction for small dataset
  await prisma.$transaction(
    usersWithoutRoles.map((user) =>
      prisma.user.update({
        where: { id: user.id },
        data: { role: "USER" }, // Default role
      })
    )
  );

  console.log("✅ Backfill complete");
}

backfillUserRoles();
typescript
// scripts/backfill-user-roles.ts
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function backfillUserRoles() {
  const usersWithoutRoles = await prisma.user.findMany({
    where: { role: null },
  });

  console.log(`正在回填 ${usersWithoutRoles.length} 个用户...`);

  // 针对小型数据集使用单事务
  await prisma.$transaction(
    usersWithoutRoles.map((user) =>
      prisma.user.update({
        where: { id: user.id },
        data: { role: "USER" }, // 默认角色
      })
    )
  );

  console.log("✅ 回填完成");
}

backfillUserRoles();

Large Table (> 10k rows)

大表（>1万行）

typescript
// scripts/backfill-large-table.ts
async function backfillBatched() {
  const batchSize = 1000;
  let processed = 0;
  let hasMore = true;

  while (hasMore) {
    const batch = await prisma.user.findMany({
      where: { role: null },
      take: batchSize,
      select: { id: true },
    });

    if (batch.length === 0) {
      hasMore = false;
      break;
    }

    // Process batch
    await prisma.$transaction(
      batch.map((user) =>
        prisma.user.update({
          where: { id: user.id },
          data: { role: "USER" },
        })
      )
    );

    processed += batch.length;
    console.log(`Processed ${processed} users...`);

    // Rate limiting
    await new Promise((resolve) => setTimeout(resolve, 100));
  }

  console.log(`✅ Backfilled ${processed} users`);
}
typescript
// scripts/backfill-large-table.ts
async function backfillBatched() {
  const batchSize = 1000;
  let processed = 0;
  let hasMore = true;

  while (hasMore) {
    const batch = await prisma.user.findMany({
      where: { role: null },
      take: batchSize,
      select: { id: true },
    });

    if (batch.length === 0) {
      hasMore = false;
      break;
    }

    // 处理批次
    await prisma.$transaction(
      batch.map((user) =>
        prisma.user.update({
          where: { id: user.id },
          data: { role: "USER" },
        })
      )
    );

    processed += batch.length;
    console.log(`已处理 ${processed} 个用户...`);

    // 速率限制
    await new Promise((resolve) => setTimeout(resolve, 100));
  }

  console.log(`✅ 已回填 ${processed} 个用户`);
}

Rollback Guidance

回滚指南

sql
-- migrations/20240115_add_role/rollback.sql

-- Rollback Step 1: Add back old structure (if needed)
ALTER TABLE "User" DROP COLUMN "role";

-- Rollback Step 2: Restore old logic
-- (Deploy previous application version)

-- Note: Data loss consideration
-- If you backfilled data, document what was lost
sql
-- migrations/20240115_add_role/rollback.sql

-- 回滚步骤1：恢复旧结构（若需要）
ALTER TABLE "User" DROP COLUMN "role";

-- 回滚步骤2：恢复旧逻辑
-- （部署之前的应用版本）

-- 注意：数据丢失风险
-- 若已执行数据回填，请记录可能丢失的数据

Migration Testing

迁移测试

typescript
// tests/migrations/split-name.test.ts
import { PrismaClient } from "@prisma/client";
import { execSync } from "child_process";

describe("Split name migration", () => {
  let prisma: PrismaClient;

  beforeAll(async () => {
    // Setup test database
    execSync("npx prisma migrate deploy", {
      env: { DATABASE_URL: process.env.TEST_DATABASE_URL },
    });
    prisma = new PrismaClient();
  });

  it("should split name correctly", async () => {
    // Create user with old schema
    await prisma.$executeRaw`
      INSERT INTO "User" (name) VALUES ('John Doe')
    `;

    // Run migration
    execSync("npx prisma migrate deploy");

    // Verify split
    const user = await prisma.user.findFirst();
    expect(user?.firstName).toBe("John");
    expect(user?.lastName).toBe("Doe");
  });

  it("should handle single name", async () => {
    await prisma.$executeRaw`
      INSERT INTO "User" (name) VALUES ('Madonna')
    `;

    execSync("npx prisma migrate deploy");

    const user = await prisma.user.findFirst({
      where: { firstName: "Madonna" },
    });
    expect(user?.lastName).toBe("");
  });
});
typescript
// tests/migrations/split-name.test.ts
import { PrismaClient } from "@prisma/client";
import { execSync } from "child_process";

describe("拆分name字段迁移", () => {
  let prisma: PrismaClient;

  beforeAll(async () => {
    // 搭建测试数据库
    execSync("npx prisma migrate deploy", {
      env: { DATABASE_URL: process.env.TEST_DATABASE_URL },
    });
    prisma = new PrismaClient();
  });

  it("应正确拆分name字段", async () => {
    // 用旧schema创建用户
    await prisma.$executeRaw`
      INSERT INTO "User" (name) VALUES ('John Doe')
    `;

    // 执行迁移
    execSync("npx prisma migrate deploy");

    // 验证拆分结果
    const user = await prisma.user.findFirst();
    expect(user?.firstName).toBe("John");
    expect(user?.lastName).toBe("Doe");
  });

  it("应处理单个名字的情况", async () => {
    await prisma.$executeRaw`
      INSERT INTO "User" (name) VALUES ('Madonna')
    `;

    execSync("npx prisma migrate deploy");

    const user = await prisma.user.findFirst({
      where: { firstName: "Madonna" },
    });
    expect(user?.lastName).toBe("");
  });
});

Pre-Migration Checklist

迁移前检查清单

markdown
- [ ] Backup database
- [ ] Test migration on staging
- [ ] Verify data transformation logic
- [ ] Check for referential integrity issues
- [ ] Estimate migration time
- [ ] Plan rollback strategy
- [ ] Schedule maintenance window (if needed)
- [ ] Notify team of deployment
markdown
- [ ] 备份数据库
- [ ] 在预发布环境测试迁移
- [ ] 验证数据转换逻辑
- [ ] 检查引用完整性问题
- [ ] 估算迁移时间
- [ ] 制定回滚策略
- [ ] 安排维护窗口（若需要）
- [ ] 通知团队部署计划

SQL Preview Script

SQL预览脚本

bash
#!/bin/bash
bash
#!/bin/bash

scripts/preview-migration.sh

scripts/preview-migration.sh

echo "🔍 Previewing migration..."
echo "🔍 正在预览迁移..."

Create migration without applying

创建迁移但不应用

npx prisma migrate dev --name "$1" --create-only
npx prisma migrate dev --name "$1" --create-only

Show SQL

展示SQL

echo "" echo "📄 Generated SQL:" echo "==================" cat prisma/migrations/*_$1/migration.sql
echo "" echo "📄 生成的SQL：" echo "==================" cat prisma/migrations/*_$1/migration.sql

Analyze impact

分析影响

echo "" echo "📊 Impact Analysis:" echo "==================" echo "Tables affected: $(cat prisma/migrations/*_$1/migration.sql | grep -c 'ALTER TABLE')" echo "Rows to update: [Run COUNT query manually]" echo "Estimated time: [Estimate based on table size]"
undefined
echo "" echo "📊 影响分析：" echo "==================" echo "受影响的表数量：$(cat prisma/migrations/*_$1/migration.sql | grep -c 'ALTER TABLE')" echo "需更新的行数：[手动运行COUNT查询]" echo "预估时间：[根据表大小估算]"
undefined

Best Practices

最佳实践

  1. Create migration, don't apply: Use 
    --create-only
    flag
  2. Review SQL carefully: Check generated migration
  3. Test on staging: Always test before production
  4. Batch large updates: Avoid locking tables
  5. Add before removing: Additive migrations first
  6. Version application code: Deploy code that handles both schemas
  7. Monitor performance: Watch query times during migration
  8. Have rollback plan: Document reversal steps
  1. 创建迁移但不立即应用：使用
    --create-only
    参数
  2. 仔细审阅SQL：检查生成的迁移脚本
  3. 在预发布环境测试：生产环境前务必测试
  4. 批量处理大型更新：避免锁定表
  5. 先添加再删除：优先使用增量迁移
  6. 版本化应用代码：部署可兼容新旧schema的代码
  7. 监控性能：迁移期间关注查询耗时
  8. 制定回滚计划：记录回滚步骤

Output Checklist

输出检查清单

  • Migration SQL generated and reviewed
  • Data backfill strategy planned
  • Rollback procedure documented
  • Migration sequencing defined
  • Testing plan created
  • Impact analysis completed
  • Staging deployment successful
  • Production deployment scheduled
  • 已生成并审阅迁移SQL
  • 已规划数据回填策略
  • 已记录回滚流程
  • 已定义迁移排序
  • 已创建测试计划
  • 已完成影响分析
  • 预发布环境部署成功
  • 已安排生产环境部署时间