PaperclipOrg
Claude Agent Skill · by Wshobson

Database Migration

A comprehensive migration toolkit that handles schema changes, data transformations, and rollbacks across Sequelize, TypeORM, and Prisma. Shows you how to safel

Install
Terminal · npx
$npx skills add https://github.com/wshobson/agents --skill database-migration
Works with Paperclip

How Database Migration fits into a Paperclip company.

Database Migration drops into any Paperclip agent that handles this kind of work. Assign it to a specialist inside a pre-configured PaperclipOrg company and the skill becomes available on every heartbeat — no prompt engineering, no tool wiring.

S
SaaS FactoryPaired

Pre-configured AI company — 18 agents, 18 skills, one-time purchase.

$27$59
Explore pack
Source file
SKILL.md403 lines
Expand
---name: database-migrationdescription: Execute database migrations across ORMs and platforms with zero-downtime strategies, data transformation, and rollback procedures. Use when migrating databases, changing schemas, performing data transformations, or implementing zero-downtime deployment strategies.--- # 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.jsmodule.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.tsimport { MigrationInterface, QueryRunner, Table } from "typeorm"; export class CreateUsers1701234567 implements MigrationInterface {  public async up(queryRunner: QueryRunner): Promise<void> {    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<void> {    await queryRunner.dropTable("users");  }} // Run: npm run typeorm migration:run// Rollback: npm run typeorm migration:revert``` ### Prisma Migrations ```prisma// schema.prismamodel 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 defaultmodule.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 columnmodule.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 columnmodule.exports = {  up: async (queryInterface) => {    await queryInterface.removeColumn("users", "name");  },   down: async (queryInterface, Sequelize) => {    await queryInterface.addColumn("users", "name", {      type: Sequelize.STRING,    });  },};``` ### Changing Column Types ```javascriptmodule.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 ```javascriptmodule.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 ```javascriptmodule.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 ```javascriptmodule.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;    }  },};``` ## Zero-Downtime Migrations ### Blue-Green Deployment Strategy ```javascript// Phase 1: Make changes backward compatiblemodule.exports = {  up: async (queryInterface, Sequelize) => {    // Add new column (both old and new code can work)    await queryInterface.addColumn("users", "email_new", {      type: Sequelize.STRING,    });  },}; // Phase 2: Deploy code that writes to both columns // Phase 3: Backfill datamodule.exports = {  up: async (queryInterface) => {    await queryInterface.sequelize.query(`      UPDATE users      SET email_new = email      WHERE email_new IS NULL    `);  },}; // Phase 4: Deploy code that reads from new column // Phase 5: Remove old columnmodule.exports = {  up: async (queryInterface) => {    await queryInterface.removeColumn("users", "email");  },};``` ## Cross-Database Migrations ### PostgreSQL to MySQL ```javascript// Handle differencesmodule.exports = {  up: async (queryInterface, Sequelize) => {    const dialectName = queryInterface.sequelize.getDialect();     if (dialectName === "mysql") {      await queryInterface.createTable("users", {        id: {          type: Sequelize.INTEGER,          primaryKey: true,          autoIncrement: true,        },        data: {          type: Sequelize.JSON, // MySQL JSON type        },      });    } else if (dialectName === "postgres") {      await queryInterface.createTable("users", {        id: {          type: Sequelize.INTEGER,          primaryKey: true,          autoIncrement: true,        },        data: {          type: Sequelize.JSONB, // PostgreSQL JSONB type        },      });    }  },};```