Database Migrations

Managing database schema changes using Sequelize CLI migrations.

Overview

Sequelize CLI for migration management
Timestamp-based migration files
Reversible migrations with up/down methods
Version control for schema changes
Team collaboration support

Sequelize CLI Setup

Configuration

// adapters/shared/sql/config/config.json
{
  "development": {
    "use_env_variable": "SQL_DATABASE_URL",
    "dialect": "postgres",
    "migrationStorageTableName": "_sequelize_meta",
    "seederStorage": "sequelize",
    "seederStorageTableName": "_sequelize_data"
  },
  "production": {
    "use_env_variable": "SQL_DATABASE_URL",
    "dialect": "postgres",
    "migrationStorageTableName": "_sequelize_meta",
    "logging": false
  }
}

NPM Scripts

// package.json
{
  "scripts": {
    "dev:migrate": "sequelize-cli db:migrate --config adapters/shared/sql/config/config.json --url $SQL_DATABASE_URL --migrations-path adapters/shared/sql/migrations",
    "dev:migrate:undo": "sequelize-cli db:migrate:undo --config adapters/shared/sql/config/config.json --url $SQL_DATABASE_URL --migrations-path adapters/shared/sql/migrations",
    "dev:seed": "sequelize-cli db:seed:all --config adapters/shared/sql/config/config.json --url $SQL_DATABASE_URL --seeders-path adapters/shared/sql/seeders"
  }
}

Migration Commands

Creating Migrations

# Create a new migration
sequelize-cli migration:generate --name add-users-table \
  --config adapters/shared/sql/config/config.json \
  --migrations-path adapters/shared/sql/migrations

# Naming convention: YYYYMMDDHHMMSS-description.js
# Example: 20250520195511-add_service_model.js

Running Migrations

# Run all pending migrations
pnpm dev:migrate

# Run specific migration
sequelize-cli db:migrate --to 20250520195511-add_service_model.js \
  --config adapters/shared/sql/config/config.json \
  --url $SQL_DATABASE_URL \
  --migrations-path adapters/shared/sql/migrations

# Check migration status
sequelize-cli db:migrate:status \
  --config adapters/shared/sql/config/config.json \
  --url $SQL_DATABASE_URL \
  --migrations-path adapters/shared/sql/migrations

Reverting Migrations

# Undo last migration
pnpm dev:migrate:undo

# Undo all migrations
sequelize-cli db:migrate:undo:all \
  --config adapters/shared/sql/config/config.json \
  --url $SQL_DATABASE_URL \
  --migrations-path adapters/shared/sql/migrations

# Undo to specific migration
sequelize-cli db:migrate:undo:all --to 20250520195511-add_service_model.js \
  --config adapters/shared/sql/config/config.json \
  --url $SQL_DATABASE_URL \
  --migrations-path adapters/shared/sql/migrations

Migration Structure

Basic Template

// adapters/shared/sql/migrations/YYYYMMDDHHMMSS-description.js
"use strict";

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

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

Creating Tables

// adapters/shared/sql/migrations/20250520195511-add_service_model.js
"use strict";

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable("services", {
      id: {
        type: Sequelize.STRING(24),
        primaryKey: true,
      },
      title: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      slug: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true,
      },
      description: {
        type: Sequelize.TEXT,
        allowNull: false,
        defaultValue: "",
      },
      status: {
        type: Sequelize.ENUM(
          "pending",
          "rejected",
          "suspended",
          "active",
          "paused",
        ),
        allowNull: false,
        defaultValue: "pending",
      },
      basic_price: {
        type: Sequelize.DECIMAL(10, 2),
        allowNull: false,
      },
      basic_delivery_time: {
        type: Sequelize.INTEGER,
        allowNull: false,
      },
      category_id: {
        type: Sequelize.STRING(24),
        references: {
          model: "categories",
          key: "id",
        },
        onDelete: "CASCADE",
        onUpdate: "CASCADE",
      },
      owner_id: {
        type: Sequelize.STRING(24),
        references: {
          model: "users",
          key: "id",
        },
        onDelete: "CASCADE",
        onUpdate: "CASCADE",
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal("CURRENT_TIMESTAMP"),
      },
      updated_at: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal("CURRENT_TIMESTAMP"),
      },
    });

    // Add indexes
    await queryInterface.addIndex("services", ["category_id"], {
      name: "services_category_id_index",
      using: "btree",
    });

    // Add unique constraint
    await queryInterface.addConstraint("services", {
      type: "unique",
      fields: ["title", "owner_id"],
      name: "services_title_owner_unique",
    });
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable("services");
  },
};

Adding Columns

// adapters/shared/sql/migrations/20250601120000-add-service-rating.js
"use strict";

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addColumn("services", "average_rating", {
      type: Sequelize.FLOAT,
      allowNull: false,
      defaultValue: 0.0,
    });

    await queryInterface.addColumn("services", "reviews_count", {
      type: Sequelize.INTEGER,
      allowNull: false,
      defaultValue: 0,
    });
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.removeColumn("services", "average_rating");
    await queryInterface.removeColumn("services", "reviews_count");
  },
};

Modifying Columns

// adapters/shared/sql/migrations/20250602140000-modify-price-precision.js
"use strict";

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.changeColumn("services", "basic_price", {
      type: Sequelize.DECIMAL(12, 2), // Increased from 10,2 to 12,2
      allowNull: false,
    });
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.changeColumn("services", "basic_price", {
      type: Sequelize.DECIMAL(10, 2),
      allowNull: false,
    });
  },
};

Adding Indexes

// adapters/shared/sql/migrations/20250603100000-add-service-indexes.js
"use strict";

module.exports = {
  async up(queryInterface, Sequelize) {
    // Add B-tree index for status
    await queryInterface.addIndex("services", ["status"], {
      name: "services_status_index",
      using: "btree",
    });

    // Add composite index
    await queryInterface.addIndex("services", ["owner_id", "status"], {
      name: "services_owner_status_index",
      using: "btree",
    });

    // Add full-text search support
    await queryInterface.sequelize.query(`
      CREATE INDEX services_title_search_idx 
      ON services 
      USING gin(to_tsvector('english', title));
    `);
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.removeIndex("services", "services_status_index");
    await queryInterface.removeIndex("services", "services_owner_status_index");
    await queryInterface.sequelize.query(
      "DROP INDEX IF EXISTS services_title_search_idx;"
    );
  },
};

Foreign Keys

// adapters/shared/sql/migrations/20250604150000-add-order-references.js
"use strict";

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addConstraint("orders", {
      fields: ["seller_id"],
      type: "foreign key",
      name: "orders_seller_fk",
      references: {
        table: "users",
        field: "id",
      },
      onDelete: "CASCADE",
      onUpdate: "CASCADE",
    });

    await queryInterface.addConstraint("orders", {
      fields: ["customer_id"],
      type: "foreign key",
      name: "orders_customer_fk",
      references: {
        table: "users",
        field: "id",
      },
      onDelete: "CASCADE",
      onUpdate: "CASCADE",
    });
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.removeConstraint("orders", "orders_seller_fk");
    await queryInterface.removeConstraint("orders", "orders_customer_fk");
  },
};

Data Migrations

// adapters/shared/sql/migrations/20250605120000-update-service-status.js
"use strict";

module.exports = {
  async up(queryInterface, Sequelize) {
    // Update all pending services to active
    await queryInterface.sequelize.query(`
      UPDATE services 
      SET status = 'active' 
      WHERE status = 'pending' 
        AND created_at < NOW() - INTERVAL '7 days';
    `);
  },

  async down(queryInterface, Sequelize) {
    // Revert is not possible for data migrations
    // Consider logging or creating backup
  },
};

JSONB Columns

// adapters/shared/sql/migrations/20250714165830-orders-model.js
"use strict";

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable("orders", {
      id: {
        type: Sequelize.STRING,
        allowNull: false,
        primaryKey: true,
      },
      selected_options: {
        type: Sequelize.JSONB,
        allowNull: false,
        defaultValue: [],
      },
      events: {
        type: Sequelize.JSONB,
        allowNull: false,
        defaultValue: [],
      },
      // ... other columns
    });

    // Add JSONB index for faster queries
    await queryInterface.sequelize.query(`
      CREATE INDEX orders_selected_options_idx 
      ON orders 
      USING gin(selected_options);
    `);
  },

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

Migration Naming Conventions

Pattern

YYYYMMDDHHMMSS-action-table-description.js

Examples

20250520195511-add_service_model.js
20250601120000-add-service-rating.js
20250602140000-modify-price-precision.js
20250603100000-add-service-indexes.js
20250604150000-add-order-references.js
20250605120000-update-service-status.js

Naming Guidelines

add – Creating new tables
modify/change – Altering existing columns
add-column – Adding new columns
remove-column – Removing columns
add-index – Adding indexes
add-constraint – Adding constraints
update – Data migrations

Best Practices

✅ Do

Always make migrations reversible:

async up(queryInterface, Sequelize) {
  await queryInterface.addColumn("services", "new_field", {
    type: Sequelize.STRING,
  });
}

async down(queryInterface, Sequelize) {
  await queryInterface.removeColumn("services", "new_field");
}

Use transactions for complex migrations:

async up(queryInterface, Sequelize) {
  const transaction = await queryInterface.sequelize.transaction();

  try {
    await queryInterface.addColumn("services", "field1", {...}, { transaction });
    await queryInterface.addColumn("services", "field2", {...}, { transaction });

    await transaction.commit();
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
}

Add indexes for foreign keys:

async up(queryInterface, Sequelize) {
  await queryInterface.addColumn("orders", "service_id", {
    type: Sequelize.STRING(24),
    references: { model: "services", key: "id" },
  });

  // Always index foreign keys
  await queryInterface.addIndex("orders", ["service_id"]);
}

Use raw SQL for complex operations:

async up(queryInterface, Sequelize) {
  await queryInterface.sequelize.query(`
    CREATE INDEX services_title_search_idx 
    ON services 
    USING gin(to_tsvector('english', title));
  `);
}

❌ Don't

Don't modify existing migrations:

// ❌ Wrong - Editing already-run migration
// File: 20250520195511-add_service_model.js
// Changing this after it's been run by team

// ✅ Correct - Create new migration
// File: 20250606120000-add-service-field.js

Don't skip the down method:

// ❌ Wrong
async down(queryInterface, Sequelize) {
  // TODO: implement rollback
}

// ✅ Correct
async down(queryInterface, Sequelize) {
  await queryInterface.dropTable("services");
}

Don't hardcode values:

// ❌ Wrong
await queryInterface.bulkInsert("categories", [
  { id: "cat-1", title: "Design" },
]);

// ✅ Correct - Use seeders for data

Don't ignore dependencies:

// ❌ Wrong - Creating child before parent
await queryInterface.createTable("orders", {
  service_id: {
    references: { model: "services", key: "id" }, // services doesn't exist yet!
  },
});

// ✅ Correct - Ensure parent table exists first

Migration Workflow

Development

Create migration:

sequelize-cli migration:generate --name add-new-feature

Write up/down methods:

async up(queryInterface, Sequelize) {
  // Add your changes
}

async down(queryInterface, Sequelize) {
  // Reverse your changes
}

Test migration:

pnpm dev:migrate
pnpm dev:migrate:undo
pnpm dev:migrate

Commit migration file:

git add adapters/shared/sql/migrations/
git commit -m "Add migration for new feature"

Production

Review migrations:

sequelize-cli db:migrate:status --env production

Backup database:

pg_dump $DATABASE_URL > backup-$(date +%Y%m%d).sql

Run migrations:
```
NODE_ENV=production pnpm dev:migrate
```

Verify success:

sequelize-cli db:migrate:status --env production

Rollback if needed:

NODE_ENV=production pnpm dev:migrate:undo

Common Patterns

Adding Timestamps

created_at: {
  type: Sequelize.DATE,
  allowNull: false,
  defaultValue: Sequelize.literal("CURRENT_TIMESTAMP"),
},
updated_at: {
  type: Sequelize.DATE,
  allowNull: false,
  defaultValue: Sequelize.literal("CURRENT_TIMESTAMP"),
}

ENUM Columns

status: {
  type: Sequelize.ENUM("pending", "active", "suspended"),
  allowNull: false,
  defaultValue: "pending",
}

Decimal for Money

price: {
  type: Sequelize.DECIMAL(10, 2), // Max 99,999,999.99
  allowNull: false,
}

Unique Constraints

// Single column
email: {
  type: Sequelize.STRING,
  unique: true,
}

// Multiple columns
await queryInterface.addConstraint("services", {
  type: "unique",
  fields: ["title", "owner_id"],
  name: "services_title_owner_unique",
});

Troubleshooting

Migration Failed

# Check status
sequelize-cli db:migrate:status

# Manually fix database
psql $SQL_DATABASE_URL

# Mark migration as executed (if fixed manually)
INSERT INTO _sequelize_meta (name) 
VALUES ('20250520195511-add_service_model.js');

Locked Migration

-- Check for locks
SELECT * FROM pg_locks WHERE relation = '_sequelize_meta'::regclass;

-- Kill blocking process
SELECT pg_terminate_backend(pid) FROM pg_stat_activity 
WHERE state = 'active' AND query LIKE '%_sequelize_meta%';

Summary

Database migrations provide:

Version control for schema changes
Reversibility with up/down methods
Team collaboration via shared migration files
Production safety with tested migrations
Audit trail of all schema changes

Follow best practices for reliable database evolution across all environments.