prisma-migration-assistant by patricio0312rev/skills
npx skills add https://github.com/patricio0312rev/skills --skill prisma-migration-assistant自信地规划和执行安全的 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. 应用到生产环境
// 添加新的可选字段 - 安全!
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;
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 开发者,精准高效
// 之前:单个 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;
# 迁移顺序:添加用户角色
## 阶段 1:增量(第 1 周)
1. 添加可选的 `role` 字段
2. 部署处理 NULL 角色的应用程序代码
3. 使用默认角色回填现有用户
## 阶段 2:强制执行(第 2 周)
1. 使 `role` 字段成为必填项
2. 部署在创建时需要角色的代码
3. 添加数据库约束
## 阶段 3:清理(第 3 周)
1. 移除旧的权限检查代码
2. 验证所有用户都有角色
// 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();
// 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} 个用户`);
}
-- migrations/20240115_add_role/rollback.sql
-- 回滚步骤 1:恢复旧结构(如果需要)
ALTER TABLE "User" DROP COLUMN "role";
-- 回滚步骤 2:恢复旧逻辑
-- (部署之前的应用程序版本)
-- 注意:数据丢失考虑
-- 如果您回填了数据,请记录丢失的内容
// 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("");
});
});
- [ ] 备份数据库
- [ ] 在预发布环境测试迁移
- [ ] 验证数据转换逻辑
- [ ] 检查引用完整性问题
- [ ] 估算迁移时间
- [ ] 规划回滚策略
- [ ] 安排维护窗口(如果需要)
- [ ] 通知团队部署事宜
#!/bin/bash
# scripts/preview-migration.sh
echo "🔍 预览迁移..."
# 创建迁移但不应用
npx prisma migrate dev --name "$1" --create-only
# 显示 SQL
echo ""
echo "📄 生成的 SQL:"
echo "=================="
cat prisma/migrations/*_$1/migration.sql
# 分析影响
echo ""
echo "📊 影响分析:"
echo "=================="
echo "受影响的表:$(cat prisma/migrations/*_$1/migration.sql | grep -c 'ALTER TABLE')"
echo "要更新的行数:[手动运行 COUNT 查询]"
echo "预计时间:[根据表大小估算]"
--create-only 标志每周安装次数
77
代码仓库
GitHub 星标数
21
首次出现
Jan 24, 2026
安全审计
安装于
codex61
gemini-cli61
opencode60
github-copilot56
claude-code55
cursor55
Plan and execute safe Prisma migrations with confidence.
// 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
// 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;
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";
// 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";
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 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
// 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();
// 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`);
}
-- 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
// 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("");
});
});
- [ ] 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
#!/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]"
--create-only flagWeekly Installs
77
Repository
GitHub Stars
21
First Seen
Jan 24, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
codex61
gemini-cli61
opencode60
github-copilot56
claude-code55
cursor55
React 组合模式指南:Vercel 组件架构最佳实践,提升代码可维护性
122,000 周安装
OpenClaw 环境安全审计员:一键扫描密钥泄露,审计沙箱配置,保障AI技能运行安全
197 周安装
政治科学家分析师技能:应用现实主义、自由主义、建构主义理论框架进行深度政治分析
195 周安装
SEO结构架构专家:优化网站信息架构、标题层次与结构化数据
201 周安装
Elastic Observability SLO管理指南:创建、监控服务等级目标与SLI类型详解
204 周安装
CSS开发指南:Flexbox、Grid布局、响应式设计与性能优化最佳实践
199 周安装
OpenSpec (OPSX) 指南:基于工件的开发工作流系统,实现变更管理与自动化
202 周安装