1# database-migration — additional patterns and templates
2 
3## Zero-Downtime Migrations
4 
5### Blue-Green Deployment Strategy
6 
7```javascript
8// Phase 1: Make changes backward compatible
9module.exports = {
10  up: async (queryInterface, Sequelize) => {
11    // Add new column (both old and new code can work)
12    await queryInterface.addColumn("users", "email_new", {
13      type: Sequelize.STRING,
14    });
15  },
16};
17 
18// Phase 2: Deploy code that writes to both columns
19 
20// Phase 3: Backfill data
21module.exports = {
22  up: async (queryInterface) => {
23    await queryInterface.sequelize.query(`
24      UPDATE users
25      SET email_new = email
26      WHERE email_new IS NULL
27    `);
28  },
29};
30 
31// Phase 4: Deploy code that reads from new column
32 
33// Phase 5: Remove old column
34module.exports = {
35  up: async (queryInterface) => {
36    await queryInterface.removeColumn("users", "email");
37  },
38};
39```
40 
41## Cross-Database Migrations
42 
43### PostgreSQL to MySQL
44 
45```javascript
46// Handle differences
47module.exports = {
48  up: async (queryInterface, Sequelize) => {
49    const dialectName = queryInterface.sequelize.getDialect();
50 
51    if (dialectName === "mysql") {
52      await queryInterface.createTable("users", {
53        id: {
54          type: Sequelize.INTEGER,
55          primaryKey: true,
56          autoIncrement: true,
57        },
58        data: {
59          type: Sequelize.JSON, // MySQL JSON type
60        },
61      });
62    } else if (dialectName === "postgres") {
63      await queryInterface.createTable("users", {
64        id: {
65          type: Sequelize.INTEGER,
66          primaryKey: true,
67          autoIncrement: true,
68        },
69        data: {
70          type: Sequelize.JSONB, // PostgreSQL JSONB type
71        },
72      });
73    }
74  },
75};
76```
77