Sequelize Migration Patterns

Migration Basics

File Structure

migrations/
├── 20240101000000-create-users.js
├── 20240101000001-create-orders.js
├── 20240102000000-add-status-to-users.js
└── 20240103000000-add-index-to-orders.js

Basic Migration Template

'use strict';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    // Forward migration
  },

  async down(queryInterface, Sequelize) {
    // Rollback migration
  },
};

Creating Tables

Basic Table Creation

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('users', {
      id: {
        type: Sequelize.BIGINT,
        primaryKey: true,
        autoIncrement: true,
      },
      email: {
        type: Sequelize.TEXT,
        allowNull: false,
        unique: true,
      },
      first_name: {
        type: Sequelize.TEXT,
        allowNull: false,
      },
      last_name: {
        type: Sequelize.TEXT,
        allowNull: false,
      },
      status: {
        type: Sequelize.TEXT,
        allowNull: false,
        defaultValue: 'active',
      },
      metadata: {
        type: Sequelize.JSONB,
        allowNull: false,
        defaultValue: {},
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('NOW()'),
      },
      updated_at: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('NOW()'),
      },
    });

    // Add indexes after table creation
    await queryInterface.addIndex('users', ['email'], { unique: true });
    await queryInterface.addIndex('users', ['status']);
    await queryInterface.addIndex('users', ['created_at']);
  },

  async down(queryInterface) {
    await queryInterface.dropTable('users');
  },
};

Table with Foreign Keys

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('orders', {
      id: {
        type: Sequelize.BIGINT,
        primaryKey: true,
        autoIncrement: true,
      },
      user_id: {
        type: Sequelize.BIGINT,
        allowNull: false,
        references: {
          model: 'users',
          key: 'id',
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE',
      },
      status: {
        type: Sequelize.TEXT,
        allowNull: false,
        defaultValue: 'pending',
      },
      total: {
        type: Sequelize.DECIMAL(10, 2),
        allowNull: false,
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('NOW()'),
      },
      updated_at: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('NOW()'),
      },
    });

    // CRITICAL: Index foreign key columns
    await queryInterface.addIndex('orders', ['user_id']);
    await queryInterface.addIndex('orders', ['status']);
    await queryInterface.addIndex('orders', ['created_at']);
  },

  async down(queryInterface) {
    await queryInterface.dropTable('orders');
  },
};

Adding Columns

Simple Column Addition

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addColumn('users', 'phone', {
      type: Sequelize.TEXT,
      allowNull: true,
    });
  },

  async down(queryInterface) {
    await queryInterface.removeColumn('users', 'phone');
  },
};

Column with Default Value

module.exports = {
  async up(queryInterface, Sequelize) {
    // For non-volatile defaults, this is fast (no table rewrite)
    await queryInterface.addColumn('users', 'is_verified', {
      type: Sequelize.BOOLEAN,
      allowNull: false,
      defaultValue: false,
    });
  },

  async down(queryInterface) {
    await queryInterface.removeColumn('users', 'is_verified');
  },
};

NOT NULL Column on Existing Table (Safe Pattern)

module.exports = {
  async up(queryInterface, Sequelize) {
    // Step 1: Add column as nullable
    await queryInterface.addColumn('users', 'role', {
      type: Sequelize.TEXT,
      allowNull: true,
    });

    // Step 2: Backfill existing rows
    await queryInterface.sequelize.query(`
      UPDATE users SET role = 'user' WHERE role IS NULL
    `);

    // Step 3: Add NOT NULL constraint
    await queryInterface.changeColumn('users', 'role', {
      type: Sequelize.TEXT,
      allowNull: false,
      defaultValue: 'user',
    });
  },

  async down(queryInterface) {
    await queryInterface.removeColumn('users', 'role');
  },
};

Modifying Columns

Change Column Type

module.exports = {
  async up(queryInterface, Sequelize) {
    // Note: May require USING clause for incompatible types
    await queryInterface.changeColumn('products', 'price', {
      type: Sequelize.DECIMAL(12, 2), // Expanding precision
      allowNull: false,
    });
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.changeColumn('products', 'price', {
      type: Sequelize.DECIMAL(10, 2),
      allowNull: false,
    });
  },
};

Change Column Type with USING

module.exports = {
  async up(queryInterface) {
    // When types aren't directly compatible
    await queryInterface.sequelize.query(`
      ALTER TABLE products
      ALTER COLUMN status TYPE INTEGER
      USING CASE
        WHEN status = 'active' THEN 1
        WHEN status = 'inactive' THEN 0
        ELSE -1
      END
    `);
  },

  async down(queryInterface) {
    await queryInterface.sequelize.query(`
      ALTER TABLE products
      ALTER COLUMN status TYPE TEXT
      USING CASE
        WHEN status = 1 THEN 'active'
        WHEN status = 0 THEN 'inactive'
        ELSE 'unknown'
      END
    `);
  },
};

Renaming

Rename Column

module.exports = {
  async up(queryInterface) {
    await queryInterface.renameColumn('users', 'name', 'full_name');
  },

  async down(queryInterface) {
    await queryInterface.renameColumn('users', 'full_name', 'name');
  },
};

Rename Table

module.exports = {
  async up(queryInterface) {
    await queryInterface.renameTable('users', 'accounts');
  },

  async down(queryInterface) {
    await queryInterface.renameTable('accounts', 'users');
  },
};

Index Management

Add Index

module.exports = {
  async up(queryInterface) {
    // Simple index
    await queryInterface.addIndex('orders', ['customer_id']);

    // Named index
    await queryInterface.addIndex('orders', ['status'], {
      name: 'orders_status_idx',
    });

    // Unique index
    await queryInterface.addIndex('users', ['email'], {
      unique: true,
      name: 'users_email_unique',
    });

    // Composite index
    await queryInterface.addIndex('orders', ['customer_id', 'created_at']);

    // Partial index
    await queryInterface.addIndex('orders', ['customer_id'], {
      where: { status: 'active' },
      name: 'orders_active_customer_idx',
    });
  },

  async down(queryInterface) {
    await queryInterface.removeIndex('orders', 'orders_status_idx');
    await queryInterface.removeIndex('users', 'users_email_unique');
    await queryInterface.removeIndex('orders', ['customer_id', 'created_at']);
    await queryInterface.removeIndex('orders', 'orders_active_customer_idx');
  },
};

Concurrent Index Creation (No Lock)

module.exports = {
  async up(queryInterface) {
    // CONCURRENTLY avoids locking the table during index creation
    // CRITICAL: Cannot run in a transaction
    await queryInterface.sequelize.query(`
      CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_idx
      ON orders (customer_id)
    `);
  },

  async down(queryInterface) {
    await queryInterface.sequelize.query(`
      DROP INDEX CONCURRENTLY IF EXISTS orders_customer_id_idx
    `);
  },
};

GIN Index for JSONB

module.exports = {
  async up(queryInterface) {
    await queryInterface.sequelize.query(`
      CREATE INDEX users_metadata_gin ON users USING GIN (metadata)
    `);
  },

  async down(queryInterface) {
    await queryInterface.sequelize.query(`
      DROP INDEX IF EXISTS users_metadata_gin
    `);
  },
};

Expression Index

module.exports = {
  async up(queryInterface) {
    await queryInterface.sequelize.query(`
      CREATE UNIQUE INDEX users_lower_email_idx ON users (LOWER(email))
    `);
  },

  async down(queryInterface) {
    await queryInterface.sequelize.query(`
      DROP INDEX IF EXISTS users_lower_email_idx
    `);
  },
};

Constraints

Add Foreign Key

module.exports = {
  async up(queryInterface) {
    await queryInterface.addConstraint('orders', {
      fields: ['customer_id'],
      type: 'foreign key',
      name: 'orders_customer_id_fkey',
      references: {
        table: 'customers',
        field: 'id',
      },
      onDelete: 'CASCADE',
      onUpdate: 'CASCADE',
    });

    // Don't forget to index the FK column!
    await queryInterface.addIndex('orders', ['customer_id']);
  },

  async down(queryInterface) {
    await queryInterface.removeConstraint('orders', 'orders_customer_id_fkey');
    await queryInterface.removeIndex('orders', ['customer_id']);
  },
};

Add Check Constraint

module.exports = {
  async up(queryInterface) {
    await queryInterface.addConstraint('products', {
      fields: ['price'],
      type: 'check',
      name: 'products_price_positive',
      where: {
        price: { [Sequelize.Op.gt]: 0 },
      },
    });

    // Or with raw SQL for complex constraints
    await queryInterface.sequelize.query(`
      ALTER TABLE orders
      ADD CONSTRAINT orders_status_valid
      CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled'))
    `);
  },

  async down(queryInterface) {
    await queryInterface.removeConstraint('products', 'products_price_positive');
    await queryInterface.removeConstraint('orders', 'orders_status_valid');
  },
};

Add Unique Constraint

module.exports = {
  async up(queryInterface) {
    await queryInterface.addConstraint('order_items', {
      fields: ['order_id', 'product_id'],
      type: 'unique',
      name: 'order_items_order_product_unique',
    });
  },

  async down(queryInterface) {
    await queryInterface.removeConstraint('order_items', 'order_items_order_product_unique');
  },
};

Data Migrations

Backfill Data

module.exports = {
  async up(queryInterface) {
    // Batch update to avoid locking
    const batchSize = 1000;
    let affected = batchSize;

    while (affected === batchSize) {
      const [, result] = await queryInterface.sequelize.query(`
        UPDATE users
        SET normalized_email = LOWER(email)
        WHERE id IN (
          SELECT id FROM users
          WHERE normalized_email IS NULL
          LIMIT ${batchSize}
        )
      `);
      affected = result.rowCount || 0;
    }
  },

  async down(queryInterface) {
    await queryInterface.sequelize.query(`
      UPDATE users SET normalized_email = NULL
    `);
  },
};

Copy Data Between Tables

module.exports = {
  async up(queryInterface) {
    await queryInterface.sequelize.query(`
      INSERT INTO user_profiles (user_id, bio, avatar_url, created_at)
      SELECT id, bio, avatar_url, NOW()
      FROM users
      WHERE bio IS NOT NULL OR avatar_url IS NOT NULL
    `);
  },

  async down(queryInterface) {
    await queryInterface.sequelize.query(`
      DELETE FROM user_profiles
    `);
  },
};

Enum Types

Create Enum Type

module.exports = {
  async up(queryInterface) {
    await queryInterface.sequelize.query(`
      CREATE TYPE order_status AS ENUM (
        'pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled'
      )
    `);

    await queryInterface.addColumn('orders', 'status_enum', {
      type: 'order_status',
      allowNull: false,
      defaultValue: 'pending',
    });
  },

  async down(queryInterface) {
    await queryInterface.removeColumn('orders', 'status_enum');
    await queryInterface.sequelize.query(`
      DROP TYPE IF EXISTS order_status
    `);
  },
};

Add Value to Existing Enum

module.exports = {
  async up(queryInterface) {
    // Add new value to enum (cannot be rolled back in PostgreSQL)
    await queryInterface.sequelize.query(`
      ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'refunded'
    `);
  },

  async down() {
    // Cannot remove enum values in PostgreSQL
    // Would need to recreate the type entirely
    console.log('Warning: Cannot remove enum value. Manual intervention required.');
  },
};

Transactions in Migrations

Automatic Transaction (Default)

Most Sequelize CLI migrations run in a transaction automatically.

Disable Transaction for CONCURRENTLY

module.exports = {
  async up(queryInterface) {
    // CREATE INDEX CONCURRENTLY cannot run in a transaction
    await queryInterface.sequelize.query(
      'CREATE INDEX CONCURRENTLY idx_users_email ON users (email)',
      { transaction: null } // Disable transaction
    );
  },

  async down(queryInterface) {
    await queryInterface.sequelize.query(
      'DROP INDEX CONCURRENTLY IF EXISTS idx_users_email',
      { transaction: null }
    );
  },
};

Safe Migration Patterns

Zero-Downtime Column Removal

// Step 1: Stop writing to the column (application change)
// Step 2: Migration to remove column

module.exports = {
  async up(queryInterface) {
    // First, drop any constraints
    await queryInterface.removeConstraint('users', 'users_legacy_id_fkey').catch(() => {});

    // Then drop the column
    await queryInterface.removeColumn('users', 'legacy_id');
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.addColumn('users', 'legacy_id', {
      type: Sequelize.INTEGER,
      allowNull: true,
    });
  },
};

Add Column with Default (PostgreSQL 11+)

// PostgreSQL 11+ handles default values without table rewrite
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addColumn('users', 'account_type', {
      type: Sequelize.TEXT,
      allowNull: false,
      defaultValue: 'standard',
    });
  },

  async down(queryInterface) {
    await queryInterface.removeColumn('users', 'account_type');
  },
};

Rename Column Safely (Two-Phase)

// Phase 1: Add new column, copy data
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addColumn('users', 'full_name', {
      type: Sequelize.TEXT,
      allowNull: true,
    });

    await queryInterface.sequelize.query(`
      UPDATE users SET full_name = name WHERE full_name IS NULL
    `);
  },

  async down(queryInterface) {
    await queryInterface.removeColumn('users', 'full_name');
  },
};

// Phase 2: (After application updated) Remove old column
module.exports = {
  async up(queryInterface) {
    await queryInterface.removeColumn('users', 'name');
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.addColumn('users', 'name', {
      type: Sequelize.TEXT,
      allowNull: true,
    });
    await queryInterface.sequelize.query(`
      UPDATE users SET name = full_name
    `);
  },
};

Sequelize CLI Commands

# Generate migration
npx sequelize-cli migration:generate --name add-phone-to-users

# Run pending migrations
npx sequelize-cli db:migrate

# Undo last migration
npx sequelize-cli db:migrate:undo

# Undo all migrations
npx sequelize-cli db:migrate:undo:all

# Undo to specific migration
npx sequelize-cli db:migrate:undo:all --to 20240101000000-create-users.js

# Check migration status
npx sequelize-cli db:migrate:status

Best Practices

Always write both up and down - Enable rollback capability
Test migrations both ways - Run up, then down, then up again
Use transactions - Sequelize does this by default
Batch large data updates - Avoid locking tables for extended periods
Index FK columns - PostgreSQL doesn't auto-create these
Use CONCURRENTLY for production indexes - Avoid blocking writes
Keep migrations small - One logical change per migration
Don't modify old migrations - Create new ones instead
Use raw SQL when needed - Sequelize's API doesn't cover everything
Name constraints explicitly - Makes them easier to remove later

sequelize-migrations

Sequelize Migration Patterns

Migration Basics

File Structure

Basic Migration Template

Creating Tables

Basic Table Creation

Table with Foreign Keys

Adding Columns

Simple Column Addition

Column with Default Value

NOT NULL Column on Existing Table (Safe Pattern)

Modifying Columns

Change Column Type

Change Column Type with USING

Renaming

Rename Column

Rename Table

Index Management

Add Index

Concurrent Index Creation (No Lock)

GIN Index for JSONB

Expression Index

Constraints

Add Foreign Key

Add Check Constraint

Add Unique Constraint

Data Migrations

Backfill Data

Copy Data Between Tables

Enum Types

Create Enum Type

Add Value to Existing Enum

Transactions in Migrations

Automatic Transaction (Default)

Disable Transaction for CONCURRENTLY

Safe Migration Patterns

Zero-Downtime Column Removal

Add Column with Default (PostgreSQL 11+)

Rename Column Safely (Two-Phase)

Sequelize CLI Commands

Best Practices