database-migration by bobmatnyc/claude-mpm-skills
npx skills add https://github.com/bobmatnyc/claude-mpm-skills --skill database-migration生产环境中演进数据库模式的安全模式。
-- Add new column (nullable initially)
ALTER TABLE users ADD COLUMN full_name VARCHAR(255) NULL;
-- Deploy new code that writes to both old and new
UPDATE users SET full_name = CONCAT(first_name, ' ', last_name);
-- Backfill existing data
UPDATE users
SET full_name = CONCAT(first_name, ' ', last_name)
WHERE full_name IS NULL;
-- Make column required
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
-- Remove old columns
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;
-- Create index concurrently (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Phase 1: Add new column
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
-- Phase 2: Copy data
UPDATE users SET email_address = email;
-- Phase 3: Drop old column (after deploy)
ALTER TABLE users DROP COLUMN email;
-- Phase 1: Add new column with new type
ALTER TABLE products ADD COLUMN price_cents INTEGER;
-- Phase 2: Migrate data
UPDATE products SET price_cents = CAST(price * 100 AS INTEGER);
-- Phase 3: Drop old column
ALTER TABLE products DROP COLUMN price;
ALTER TABLE products RENAME COLUMN price_cents TO price;
-- Add column first
ALTER TABLE orders ADD COLUMN user_id INTEGER NULL;
-- Populate data
UPDATE orders SET user_id = (
SELECT id FROM users WHERE users.email = orders.user_email
);
-- Add foreign key
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id) REFERENCES users(id);
# Generate migration
alembic revision --autogenerate -m "add user full_name"
# Apply migration
alembic upgrade head
# Rollback
alembic downgrade -1
// Create migration
knex migrate:make add_full_name
// Apply migrations
knex migrate:latest
// Rollback
knex migrate:rollback
# Generate migration
rails generate migration AddFullNameToUsers full_name:string
# Run migrations
rails db:migrate
# Rollback
rails db:rollback
def test_migration_forward_backward():
# Apply migration
apply_migration("add_full_name")
# Verify schema
assert column_exists("users", "full_name")
# Rollback
rollback_migration()
# Verify rollback
assert not column_exists("users", "full_name")
-- Locks table for long time
ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL;
-- Can't rollback
DROP TABLE old_users;
-- Breaks existing code immediately
ALTER TABLE users DROP COLUMN email;
-- Add as nullable first
ALTER TABLE users ADD COLUMN email VARCHAR(255) NULL;
-- Rename instead of drop
ALTER TABLE old_users RENAME TO archived_users;
-- Keep old column until new code deployed
-- (multi-phase approach)
-- Every migration needs DOWN
-- UP
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
-- DOWN
ALTER TABLE users DROP COLUMN full_name;
需要进行模式变更吗?
需要零停机吗?
计划回滚吗?
选择迁移工具?
→ 查看references/decision-trees.md获取全面的决策框架
迁移中途失败 → 检查数据库状态,使用修复迁移向前修复
检测到模式漂移 → 使用自动生成功能创建协调迁移
无法回滚(无降级) → 创建反向迁移或向前修复
外键冲突 → 在添加约束前清理数据,或添加为 NOT VALID
迁移锁表时间过长 → 使用 CONCURRENTLY,分阶段添加列,批量更新
循环依赖 → 创建合并迁移或重新排序依赖关系
→ 查看references/troubleshooting.md获取带示例的详细解决方案
每周安装数
73
代码仓库
GitHub 星标数
18
首次出现
2026 年 1 月 23 日
安全审计
安装于
claude-code55
codex54
github-copilot52
gemini-cli52
opencode52
cursor51
Safe patterns for evolving database schemas in production.
-- Add new column (nullable initially)
ALTER TABLE users ADD COLUMN full_name VARCHAR(255) NULL;
-- Deploy new code that writes to both old and new
UPDATE users SET full_name = CONCAT(first_name, ' ', last_name);
-- Backfill existing data
UPDATE users
SET full_name = CONCAT(first_name, ' ', last_name)
WHERE full_name IS NULL;
-- Make column required
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;
-- Remove old columns
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;
-- Create index concurrently (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Phase 1: Add new column
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
-- Phase 2: Copy data
UPDATE users SET email_address = email;
-- Phase 3: Drop old column (after deploy)
ALTER TABLE users DROP COLUMN email;
-- Phase 1: Add new column with new type
ALTER TABLE products ADD COLUMN price_cents INTEGER;
-- Phase 2: Migrate data
UPDATE products SET price_cents = CAST(price * 100 AS INTEGER);
-- Phase 3: Drop old column
ALTER TABLE products DROP COLUMN price;
ALTER TABLE products RENAME COLUMN price_cents TO price;
-- Add column first
ALTER TABLE orders ADD COLUMN user_id INTEGER NULL;
-- Populate data
UPDATE orders SET user_id = (
SELECT id FROM users WHERE users.email = orders.user_email
);
-- Add foreign key
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id) REFERENCES users(id);
# Generate migration
alembic revision --autogenerate -m "add user full_name"
# Apply migration
alembic upgrade head
# Rollback
alembic downgrade -1
// Create migration
knex migrate:make add_full_name
// Apply migrations
knex migrate:latest
// Rollback
knex migrate:rollback
# Generate migration
rails generate migration AddFullNameToUsers full_name:string
# Run migrations
rails db:migrate
# Rollback
rails db:rollback
def test_migration_forward_backward():
# Apply migration
apply_migration("add_full_name")
# Verify schema
assert column_exists("users", "full_name")
# Rollback
rollback_migration()
# Verify rollback
assert not column_exists("users", "full_name")
-- Locks table for long time
ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL;
-- Can't rollback
DROP TABLE old_users;
-- Breaks existing code immediately
ALTER TABLE users DROP COLUMN email;
-- Add as nullable first
ALTER TABLE users ADD COLUMN email VARCHAR(255) NULL;
-- Rename instead of drop
ALTER TABLE old_users RENAME TO archived_users;
-- Keep old column until new code deployed
-- (multi-phase approach)
-- Every migration needs DOWN
-- UP
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
-- DOWN
ALTER TABLE users DROP COLUMN full_name;
Making a schema change?
Need zero downtime?
Planning rollback?
Choosing migration tool?
→ Seereferences/decision-trees.md for comprehensive decision frameworks
Migration failed halfway → Check database state, fix forward with repair migration
Schema drift detected → Use autogenerate to create reconciliation migration
Cannot rollback (no downgrade) → Create reverse migration or fix forward
Foreign key violation → Clean data before adding constraint, or add as NOT VALID
Migration locks table too long → Use CONCURRENTLY, add columns in phases, batch updates
Circular dependency → Create merge migration or reorder dependencies
→ Seereferences/troubleshooting.md for detailed solutions with examples
🌳 Decision Trees - Schema migration strategies, zero-downtime patterns, rollback strategies, migration tool selection, and data migration approaches. Load when planning migrations or choosing strategies.
🔧 Troubleshooting - Failed migration recovery, schema drift detection, migration conflicts, rollback failures, data integrity issues, and performance problems. Load when debugging migration issues.
Weekly Installs
73
Repository
GitHub Stars
18
First Seen
Jan 23, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
claude-code55
codex54
github-copilot52
gemini-cli52
opencode52
cursor51
Azure 升级评估与自动化工具 - 轻松迁移 Functions 计划、托管层级和 SKU
96,200 周安装
CLAUDE.md 架构师技能:为软件项目生成和优化 AI 项目指令文件,提升 Claude 代码效率
98 周安装
PlantUML 语法参考大全:15+图表类型快速上手,从UML到C4架构图
98 周安装
React 19、Next.js 16、Vue 3.5 前端开发专家 - 现代Web应用与组件架构模式
98 周安装
Supabase RPC函数安全审计指南:发现RLS绕过与SQL注入漏洞
98 周安装
ScrapeNinja:高性能网络爬虫API,绕过反爬虫,支持JS渲染与代理轮换
98 周安装
Coingecko API 开发工具 - 官方文档整合的加密货币数据开发辅助技能
99 周安装