Prisma迁移助手 - 安全规划与执行数据库迁移工作流指南

prisma-migration-assistant by patricio0312rev/skills

77 周安装量

21 GitHub Stars

GitHub

安装命令

npx skills add https://github.com/patricio0312rev/skills --skill prisma-migration-assistant

Node.js 开发数据库

🇨🇳中文介绍

Prisma 迁移助手

自信地规划和执行安全的 Prisma 迁移。

迁移规划工作流

// 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. 应用到生产环境

迁移类型

1. 增量迁移（安全）

// 添加新的可选字段 - 安全！
model Product {
  id          Int     @id @default(autoincrement())
  name        String
  description String?
  price       Float
  newField    String? // 新增 - 可选，无需回填
}



# 生成迁移
npx prisma migrate dev --name add_product_new_field

# 生成的 SQL：
# ALTER TABLE "Product" ADD COLUMN "newField" TEXT;

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

model User {
  id         Int    @id @default(autoincrement())
  emailAddr  String @unique // 从 'email' 重命名而来
}



-- 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";

广告位招租

在这里展示您的产品或服务

触达数万 AI 开发者，精准高效

联系我们

相关 Skills

find-skills 技能搜索工具 - Vercel Labs 开源智能体技能包管理器

917,400 周安装

Vercel React 最佳实践指南 | 58条Next.js性能优化规则与代码重构

294,600 周安装

Vercel Web界面规范检查工具 - 自动检测代码是否符合Web设计指南

237,000 周安装

agent-browser 浏览器自动化工具 - Vercel Labs 命令行网页操作与测试

166,500 周安装

// 之前：单个 name 字段
// 之后：first 和 last name
model User {
  id        Int     @id @default(autoincrement())
  firstName String
  lastName  String
  // name   String  // 已移除
}



-- 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";

model Product {
  id    Int    @id @default(autoincrement())
  price Decimal @db.Decimal(10, 2) // 从 Float 变更而来
}



-- 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：如果需要，设为 NOT NULL
ALTER TABLE "Product" ALTER COLUMN "price" SET NOT NULL;

// 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} 个用户`);
}

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

describe("拆分名称迁移", () => {
  let prisma: PrismaClient;

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

  it("应正确拆分名称", async () => {
    // 使用旧模式创建用户
    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("");
  });
});

🇺🇸English

Prisma Migration Assistant

Plan and execute safe Prisma migrations with confidence.

Migration Planning Workflow

// 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

Migration Types

1. Additive Migration (Safe)

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



# Generate migration
npx prisma migrate dev --name add_product_new_field

# SQL generated:
# ALTER TABLE "Product" ADD COLUMN "newField" TEXT;

2. Column Rename (Needs Data Copy)

model User {
  id         Int    @id @default(autoincrement())
  emailAddr  String @unique // Renamed from 'email'
}



-- 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";

3. Data Transformation (Complex)

// Before: Single name field
// After: First and last name
model User {
  id        Int     @id @default(autoincrement())
  firstName String
  lastName  String
  // name   String  // Removed
}



-- 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";

4. Type Change (Risky)

model Product {
  id    Int    @id @default(autoincrement())
  price Decimal @db.Decimal(10, 2) // Changed from Float
}



-- 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;

Migration Sequencing

# Migration Sequence: Add User Roles

## Phase 1: Additive (Week 1)

1. Add optional `role` field
2. Deploy application code that handles NULL roles
3. Backfill existing users with default role

## Phase 2: Enforcement (Week 2)

1. Make `role` field required
2. Deploy code that requires role on creation
3. Add database constraint

## Phase 3: Cleanup (Week 3)

1. Remove old permission checking code
2. Verify all users have roles

Backfill Strategies

Small Table (< 10k rows)

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

Large Table (> 10k rows)

// 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`);
}

Rollback Guidance

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

Migration Testing

// 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("");
  });
});

Pre-Migration Checklist

- [ ] 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

SQL Preview Script

#!/bin/bash
# scripts/preview-migration.sh

echo "🔍 Previewing migration..."

# Create migration without applying
npx prisma migrate dev --name "$1" --create-only

# Show SQL
echo ""
echo "📄 Generated 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]"

Best Practices

Create migration, don't apply : Use --create-only flag
Review SQL carefully : Check generated migration
Test on staging : Always test before production
Batch large updates : Avoid locking tables
Add before removing : Additive migrations first
Version application code : Deploy code that handles both schemas
Monitor performance : Watch query times during migration
Have rollback plan : Document reversal steps

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

Weekly Installs

Repository

patricio0312rev/skills

GitHub Stars

First Seen

Jan 24, 2026

Security Audits

Gen Agent Trust HubPass SocketPass SnykPass

Installed on

codex61

gemini-cli61

opencode60

github-copilot56

claude-code55

cursor55

Prisma迁移助手 - 安全规划与执行数据库迁移工作流指南

🇨🇳中文介绍

Prisma 迁移助手

迁移规划工作流

迁移类型

1. 增量迁移（安全）

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

相关 Skills

3. 数据转换（复杂）

4. 类型变更（有风险）

迁移顺序

回填策略

小表（< 10k 行）

大表（> 10k 行）

回滚指南

迁移测试

迁移前检查清单

SQL 预览脚本

最佳实践

输出检查清单