1---
2name: database-migration
3description: 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.
4---
5 
6# Database Migration
7 
8Master database schema and data migrations across ORMs (Sequelize, TypeORM, Prisma), including rollback strategies and zero-downtime deployments.
9 
10## When to Use This Skill
11 
12- Migrating between different ORMs
13- Performing schema transformations
14- Moving data between databases
15- Implementing rollback procedures
16- Zero-downtime deployments
17- Database version upgrades
18- Data model refactoring
19 
20## ORM Migrations
21 
22### Sequelize Migrations
23 
24```javascript
25// migrations/20231201-create-users.js
26module.exports = {
27  up: async (queryInterface, Sequelize) => {
28    await queryInterface.createTable("users", {
29      id: {
30        type: Sequelize.INTEGER,
31        primaryKey: true,
32        autoIncrement: true,
33      },
34      email: {
35        type: Sequelize.STRING,
36        unique: true,
37        allowNull: false,
38      },
39      createdAt: Sequelize.DATE,
40      updatedAt: Sequelize.DATE,
41    });
42  },
43 
44  down: async (queryInterface, Sequelize) => {
45    await queryInterface.dropTable("users");
46  },
47};
48 
49// Run: npx sequelize-cli db:migrate
50// Rollback: npx sequelize-cli db:migrate:undo
51```
52 
53### TypeORM Migrations
54 
55```typescript
56// migrations/1701234567-CreateUsers.ts
57import { MigrationInterface, QueryRunner, Table } from "typeorm";
58 
59export class CreateUsers1701234567 implements MigrationInterface {
60  public async up(queryRunner: QueryRunner): Promise<void> {
61    await queryRunner.createTable(
62      new Table({
63        name: "users",
64        columns: [
65          {
66            name: "id",
67            type: "int",
68            isPrimary: true,
69            isGenerated: true,
70            generationStrategy: "increment",
71          },
72          {
73            name: "email",
74            type: "varchar",
75            isUnique: true,
76          },
77          {
78            name: "created_at",
79            type: "timestamp",
80            default: "CURRENT_TIMESTAMP",
81          },
82        ],
83      }),
84    );
85  }
86 
87  public async down(queryRunner: QueryRunner): Promise<void> {
88    await queryRunner.dropTable("users");
89  }
90}
91 
92// Run: npm run typeorm migration:run
93// Rollback: npm run typeorm migration:revert
94```
95 
96### Prisma Migrations
97 
98```prisma
99// schema.prisma
100model User {
101  id        Int      @id @default(autoincrement())
102  email     String   @unique
103  createdAt DateTime @default(now())
104}
105 
106// Generate migration: npx prisma migrate dev --name create_users
107// Apply: npx prisma migrate deploy
108```
109 
110## Schema Transformations
111 
112### Adding Columns with Defaults
113 
114```javascript
115// Safe migration: add column with default
116module.exports = {
117  up: async (queryInterface, Sequelize) => {
118    await queryInterface.addColumn("users", "status", {
119      type: Sequelize.STRING,
120      defaultValue: "active",
121      allowNull: false,
122    });
123  },
124 
125  down: async (queryInterface) => {
126    await queryInterface.removeColumn("users", "status");
127  },
128};
129```
130 
131### Renaming Columns (Zero Downtime)
132 
133```javascript
134// Step 1: Add new column
135module.exports = {
136  up: async (queryInterface, Sequelize) => {
137    await queryInterface.addColumn("users", "full_name", {
138      type: Sequelize.STRING,
139    });
140 
141    // Copy data from old column
142    await queryInterface.sequelize.query("UPDATE users SET full_name = name");
143  },
144 
145  down: async (queryInterface) => {
146    await queryInterface.removeColumn("users", "full_name");
147  },
148};
149 
150// Step 2: Update application to use new column
151 
152// Step 3: Remove old column
153module.exports = {
154  up: async (queryInterface) => {
155    await queryInterface.removeColumn("users", "name");
156  },
157 
158  down: async (queryInterface, Sequelize) => {
159    await queryInterface.addColumn("users", "name", {
160      type: Sequelize.STRING,
161    });
162  },
163};
164```
165 
166### Changing Column Types
167 
168```javascript
169module.exports = {
170  up: async (queryInterface, Sequelize) => {
171    // For large tables, use multi-step approach
172 
173    // 1. Add new column
174    await queryInterface.addColumn("users", "age_new", {
175      type: Sequelize.INTEGER,
176    });
177 
178    // 2. Copy and transform data
179    await queryInterface.sequelize.query(`
180      UPDATE users
181      SET age_new = CAST(age AS INTEGER)
182      WHERE age IS NOT NULL
183    `);
184 
185    // 3. Drop old column
186    await queryInterface.removeColumn("users", "age");
187 
188    // 4. Rename new column
189    await queryInterface.renameColumn("users", "age_new", "age");
190  },
191 
192  down: async (queryInterface, Sequelize) => {
193    await queryInterface.changeColumn("users", "age", {
194      type: Sequelize.STRING,
195    });
196  },
197};
198```
199 
200## Data Transformations
201 
202### Complex Data Migration
203 
204```javascript
205module.exports = {
206  up: async (queryInterface, Sequelize) => {
207    // Get all records
208    const [users] = await queryInterface.sequelize.query(
209      "SELECT id, address_string FROM users",
210    );
211 
212    // Transform each record
213    for (const user of users) {
214      const addressParts = user.address_string.split(",");
215 
216      await queryInterface.sequelize.query(
217        `UPDATE users
218         SET street = :street,
219             city = :city,
220             state = :state
221         WHERE id = :id`,
222        {
223          replacements: {
224            id: user.id,
225            street: addressParts[0]?.trim(),
226            city: addressParts[1]?.trim(),
227            state: addressParts[2]?.trim(),
228          },
229        },
230      );
231    }
232 
233    // Drop old column
234    await queryInterface.removeColumn("users", "address_string");
235  },
236 
237  down: async (queryInterface, Sequelize) => {
238    // Reconstruct original column
239    await queryInterface.addColumn("users", "address_string", {
240      type: Sequelize.STRING,
241    });
242 
243    await queryInterface.sequelize.query(`
244      UPDATE users
245      SET address_string = CONCAT(street, ', ', city, ', ', state)
246    `);
247 
248    await queryInterface.removeColumn("users", "street");
249    await queryInterface.removeColumn("users", "city");
250    await queryInterface.removeColumn("users", "state");
251  },
252};
253```
254 
255## Rollback Strategies
256 
257### Transaction-Based Migrations
258 
259```javascript
260module.exports = {
261  up: async (queryInterface, Sequelize) => {
262    const transaction = await queryInterface.sequelize.transaction();
263 
264    try {
265      await queryInterface.addColumn(
266        "users",
267        "verified",
268        { type: Sequelize.BOOLEAN, defaultValue: false },
269        { transaction },
270      );
271 
272      await queryInterface.sequelize.query(
273        "UPDATE users SET verified = true WHERE email_verified_at IS NOT NULL",
274        { transaction },
275      );
276 
277      await transaction.commit();
278    } catch (error) {
279      await transaction.rollback();
280      throw error;
281    }
282  },
283 
284  down: async (queryInterface) => {
285    await queryInterface.removeColumn("users", "verified");
286  },
287};
288```
289 
290### Checkpoint-Based Rollback
291 
292```javascript
293module.exports = {
294  up: async (queryInterface, Sequelize) => {
295    // Create backup table
296    await queryInterface.sequelize.query(
297      "CREATE TABLE users_backup AS SELECT * FROM users",
298    );
299 
300    try {
301      // Perform migration
302      await queryInterface.addColumn("users", "new_field", {
303        type: Sequelize.STRING,
304      });
305 
306      // Verify migration
307      const [result] = await queryInterface.sequelize.query(
308        "SELECT COUNT(*) as count FROM users WHERE new_field IS NULL",
309      );
310 
311      if (result[0].count > 0) {
312        throw new Error("Migration verification failed");
313      }
314 
315      // Drop backup
316      await queryInterface.dropTable("users_backup");
317    } catch (error) {
318      // Restore from backup
319      await queryInterface.sequelize.query("DROP TABLE users");
320      await queryInterface.sequelize.query(
321        "CREATE TABLE users AS SELECT * FROM users_backup",
322      );
323      await queryInterface.dropTable("users_backup");
324      throw error;
325    }
326  },
327};
328```
329 
330## Zero-Downtime Migrations
331 
332### Blue-Green Deployment Strategy
333 
334```javascript
335// Phase 1: Make changes backward compatible
336module.exports = {
337  up: async (queryInterface, Sequelize) => {
338    // Add new column (both old and new code can work)
339    await queryInterface.addColumn("users", "email_new", {
340      type: Sequelize.STRING,
341    });
342  },
343};
344 
345// Phase 2: Deploy code that writes to both columns
346 
347// Phase 3: Backfill data
348module.exports = {
349  up: async (queryInterface) => {
350    await queryInterface.sequelize.query(`
351      UPDATE users
352      SET email_new = email
353      WHERE email_new IS NULL
354    `);
355  },
356};
357 
358// Phase 4: Deploy code that reads from new column
359 
360// Phase 5: Remove old column
361module.exports = {
362  up: async (queryInterface) => {
363    await queryInterface.removeColumn("users", "email");
364  },
365};
366```
367 
368## Cross-Database Migrations
369 
370### PostgreSQL to MySQL
371 
372```javascript
373// Handle differences
374module.exports = {
375  up: async (queryInterface, Sequelize) => {
376    const dialectName = queryInterface.sequelize.getDialect();
377 
378    if (dialectName === "mysql") {
379      await queryInterface.createTable("users", {
380        id: {
381          type: Sequelize.INTEGER,
382          primaryKey: true,
383          autoIncrement: true,
384        },
385        data: {
386          type: Sequelize.JSON, // MySQL JSON type
387        },
388      });
389    } else if (dialectName === "postgres") {
390      await queryInterface.createTable("users", {
391        id: {
392          type: Sequelize.INTEGER,
393          primaryKey: true,
394          autoIncrement: true,
395        },
396        data: {
397          type: Sequelize.JSONB, // PostgreSQL JSONB type
398        },
399      });
400    }
401  },
402};
403```
404