- 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.
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:undoTypeORM 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:revertPrisma 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 deploySchema 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
},
});
}
},
};