Database Migration

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