--- id: gh-database-migration name: "database-migration" url: https://skills.yangsir.net/skill/gh-database-migration author: wshobson domain: ai-data-management-analysis tags: ["database", "migration", "orm", "schema", "data-transformation"] install_count: 17500 rating: 4.60 (120 reviews) github: https://github.com/wshobson/agents/tree/main/plugins/framework-migration/skills/database-migration --- # database-migration > 该技能专注于跨 ORM 执行数据库迁移，包括零停机策略、数据转换和回滚程序。它能帮助开发者高效、安全地进行数据库版本升级、模式重构和数据迁移，确保业务连续性并降低风险。 **Stats**: 17,500 installs · 4.6/5 (120 reviews) ## Before / After 对比 ### 数据库迁移效率与安全性 **Before**: 传统数据库迁移过程复杂，手动操作易出错，常导致长时间的系统停机，影响业务连续性。数据转换和回滚机制不完善，一旦出现问题，恢复成本高昂且耗时。 **After**: 通过零停机策略和自动化回滚，显著缩短迁移停机时间，确保数据一致性。简化了跨ORM和平台的迁移流程，大幅降低了操作风险和恢复成本，提升了开发效率和系统稳定性。 | Metric | Before | After | Change | |---|---|---|---| | 平均停机时间 | 120分钟 | 5分钟 | -95.8% | | 数据不一致错误率 | 20% | 1% | -95% | ## Readme # Database Migration Master database schema and data migrations across ORMs (Sequelize, TypeORM, Prisma), including rollback strategies and zero-downtime deployments. ## When to Use This Skill - Migrating between different ORMs - Performing schema transformations - Moving data between databases - Implementing rollback procedures - Zero-downtime deployments - Database version upgrades - Data model refactoring ## ORM Migrations ### Sequelize Migrations ```javascript // migrations/20231201-create-users.js module.exports = { up: async (queryInterface, Sequelize) => { await queryInterface.createTable("users", { id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true, }, email: { type: Sequelize.STRING, unique: true, allowNull: false, }, createdAt: Sequelize.DATE, updatedAt: Sequelize.DATE, }); }, down: async (queryInterface, Sequelize) => { await queryInterface.dropTable("users"); }, }; // Run: npx sequelize-cli db:migrate // Rollback: npx sequelize-cli db:migrate:undo ``` ### TypeORM Migrations ```typescript // migrations/1701234567-CreateUsers.ts import { MigrationInterface, QueryRunner, Table } from "typeorm"; export class CreateUsers1701234567 implements MigrationInterface { public async up(queryRunner: QueryRunner): Promise { await queryRunner.createTable( new Table({ name: "users", columns: [ { name: "id", type: "int", isPrimary: true, isGenerated: true, generationStrategy: "increment", }, { name: "email", type: "varchar", isUnique: true, }, { name: "created_at", type: "timestamp", default: "CURRENT_TIMESTAMP", }, ], }), ); } public async down(queryRunner: QueryRunner): Promise { await queryRunner.dropTable("users"); } } // Run: npm run typeorm migration:run // Rollback: npm run typeorm migration:revert ``` ### Prisma Migrations ```prisma // schema.prisma model User { id Int @id @default(autoincrement()) email String @unique createdAt DateTime @default(now()) } // Generate migration: npx prisma migrate dev --name create_users // Apply: npx prisma migrate deploy ``` ## Schema Transformations ### Adding Columns with Defaults ```javascript // Safe migration: add column with default module.exports = { up: async (queryInterface, Sequelize) => { await queryInterface.addColumn("users", "status", { type: Sequelize.STRING, defaultValue: "active", allowNull: false, }); }, down: async (queryInterface) => { await queryInterface.removeColumn("users", "status"); }, }; ``` ### Renaming Columns (Zero Downtime) ```javascript // Step 1: Add new column module.exports = { up: async (queryInterface, Sequelize) => { await queryInterface.addColumn("users", "full_name", { type: Sequelize.STRING, }); // Copy data from old column await queryInterface.sequelize.query("UPDATE users SET full_name = name"); }, down: async (queryInterface) => { await queryInterface.removeColumn("users", "full_name"); }, }; // Step 2: Update application to use new column // Step 3: Remove old column module.exports = { up: async (queryInterface) => { await queryInterface.removeColumn("users", "name"); }, down: async (queryInterface, Sequelize) => { await queryInterface.addColumn("users", "name", { type: Sequelize.STRING, }); }, }; ``` ### Changing Column Types ```javascript module.exports = { up: async (queryInterface, Sequelize) => { // For large tables, use multi-step approach // 1. Add new column await queryInterface.addColumn("users", "age_new", { type: Sequelize.INTEGER, }); // 2. Copy and transform data await queryInterface.sequelize.query(` UPDATE users SET age_new = CAST(age AS INTEGER) WHERE age IS NOT NULL `); // 3. Drop old column await queryInterface.removeColumn("users", "age"); // 4. Rename new column await queryInterface.renameColumn("users", "age_new", "age"); }, down: async (queryInterface, Sequelize) => { await queryInterface.changeColumn("users", "age", { type: Sequelize.STRING, }); }, }; ``` ## Data Transformations ### Complex Data Migration ```javascript module.exports = { up: async (queryInterface, Sequelize) => { // Get all records const [users] = await queryInterface.sequelize.query( "SELECT id, address_string FROM users", ); // Transform each record for (const user of users) { const addressParts = user.address_string.split(","); await queryInterface.sequelize.query( `UPDATE users SET street = :street, city = :city, state = :state WHERE id = :id`, { replacements: { id: user.id, street: addressParts[0]?.trim(), city: addressParts[1]?.trim(), state: addressParts[2]?.trim(), }, }, ); } // Drop old column await queryInterface.removeColumn("users", "address_string"); }, down: async (queryInterface, Sequelize) => { // Reconstruct original column await queryInterface.addColumn("users", "address_string", { type: Sequelize.STRING, }); await queryInterface.sequelize.query(` UPDATE users SET address_string = CONCAT(street, ', ', city, ', ', state) `); await queryInterface.removeColumn("users", "street"); await queryInterface.removeColumn("users", "city"); await queryInterface.removeColumn("users", "state"); }, }; ``` ## Rollback Strategies ### Transaction-Based Migrations ```javascript module.exports = { up: async (queryInterface, Sequelize) => { const transaction = await queryInterface.sequelize.transaction(); try { await queryInterface.addColumn( "users", "verified", { type: Sequelize.BOOLEAN, defaultValue: false }, { transaction }, ); await queryInterface.sequelize.query( "UPDATE users SET verified = true WHERE email_verified_at IS NOT NULL", { transaction }, ); await transaction.commit(); } catch (error) { await transaction.rollback(); throw error; } }, down: async (queryInterface) => { await queryInterface.removeColumn("users", "verified"); }, }; ``` ### Checkpoint-Based Rollback ```javascript module.exports = { up: async (queryInterface, Sequelize) => { // Create backup table await queryInterface.sequelize.query( "CREATE TABLE users_backup AS SELECT * FROM users", ); try { // Perform migration await queryInterface.addColumn("users", "new_field", { type: Sequelize.STRING, }); // Verify migration const [result] = await queryInterface.sequelize.query( "SELECT COUNT(*) as count FROM users WHERE new_field IS NULL", ); if (result[0].count > 0) { throw new Error("Migration verification failed"); } // Drop backup await queryInterface.dropTable("users_backup"); } catch (error) { // Restore from backup await queryInterface.sequelize.query("DROP TABLE users"); await queryInterface.sequelize.query( "CREATE TABLE users AS SELECT * FROM users_backup", ); await queryInterface.dropTable("users_backup"); throw error; } }, }; ``` ## Additional patterns and templates More detailed templates and worked examples live in `references/details.md`. Read that file for the full pattern library. --- *Source: https://skills.yangsir.net/skill/gh-database-migration* *Markdown mirror: https://skills.yangsir.net/api/skill/gh-database-migration/markdown*