database-migration
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.
- License 许可
- MIT
- 最近更新
- 2026-11-01
- Stars
- ★ 3
- Forks
- 1
01 前言 Front Matter
- name
- database-migration
- description
- 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.
03 SKILL.md 正文
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
// 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
// migrations/1701234567-CreateUsers.ts
import { 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
// 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
// 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)
// 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
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
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
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
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;
}
}
};
Zero-Downtime Migrations
Blue-Green Deployment Strategy
// Phase 1: Make changes backward compatible
module.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 data
module.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 column
module.exports = {
up: async (queryInterface) => {
await queryInterface.removeColumn('users', 'email');
}
};
Cross-Database Migrations
PostgreSQL to MySQL
// Handle differences
module.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
}
});
}
}
};
Resources
- references/orm-switching.md: ORM migration guides
- references/schema-migration.md: Schema transformation patterns
- references/data-transformation.md: Data migration scripts
- references/rollback-strategies.md: Rollback procedures
- assets/schema-migration-template.sql: SQL migration templates
- assets/data-migration-script.py: Data migration utilities
- scripts/test-migration.sh: Migration testing script
Best Practices
- Always Provide Rollback: Every up() needs a down()
- Test Migrations: Test on staging first
- Use Transactions: Atomic migrations when possible
- Backup First: Always backup before migration
- Small Changes: Break into small, incremental steps
- Monitor: Watch for errors during deployment
- Document: Explain why and how
- Idempotent: Migrations should be rerunnable
Common Pitfalls
- Not testing rollback procedures
- Making breaking changes without downtime strategy
- Forgetting to handle NULL values
- Not considering index performance
- Ignoring foreign key constraints
- Migrating too much data at once
04 同仓库更多 Skills
seedance-prompt-en
Write effective prompts for Jimeng Seedance 2.0 multimodal AI video generation. Use when users want to create video prompts using text, images, videos, and audio inputs with the @ reference system. Covers camera movements, effects replication, video extension, editing, music beat-matching, e-commerce ads, short dramas, and educational content.
onepage-pdf
Convert an HTML page into a single continuous-page PDF (one tall page, no pagination breaks), preserving desktop layout, backgrounds and selectable text. Use when the user asks to turn an HTML file/report/proposal into a "single-page PDF", "long PDF", "不分页 PDF", "单页 PDF", "长图式 PDF", or complains that an HTML-to-PDF export breaks into pages or cuts content. Supports optional string redaction with leak verification.
native-feel-cross-platform-desktop
Use when the user is designing, prototyping, or rewriting a desktop app that must run on multiple OSes (macOS + Windows, optionally Linux) AND feel indistinguishable from a native app to its users — fast launch, native windowing, native input handling, native materials. Trigger words include "cross-platform desktop", "Electron alternative", "Tauri vs native", "WebView wrapper", "near-native performance", "Raycast architecture", "WebKit/WebView2 quirks", "WKWebView", "system tray app", "global hotkey app", "launcher app". Do NOT trigger this skill for pure web apps, pure mobile apps, or for greenfield projects that have no native-feel requirement.
gstack
Router for the gstack skill suite. (gstack)
fireworks-tech-graph
Use when the user wants to create any technical diagram - architecture, data flow, flowchart, sequence, agent/memory, or concept map - and export as SVG+PNG. Trigger on: "画图" "帮我画" "生成图" "做个图" "架构图" "流程图" "可视化一下" "出图" "generate diagram" "draw diagram" "visualize" or any system/flow description the user wants illustrated.
dbs-video-extract
接收抖音、小红书、微信视频号等短视频链接或分享文案,查询作品/账号数据并提取语音文字稿,按作者和标题归档为 Markdown。用户希望完整解析短视频、取得数据或文字稿时使用;运行前检查 TikHub 与轻抖使用凭证,缺少时用新手能理解的方式引导购买和安全配置。