Managing Database Schema Changes with Sequelize CLI Migrations

Introduction to Sequelize CLI Migrations

During development, changes to database schema are frequent, especially when using ORMs like Sequelize in Node.js. Sequelize CLI provides a robust way to manage these changes through migrations, which are files that describe how to update the database and roll back changes, essential for version control and team collaboration.

Creating a Migration File

To add a new column, such as 'completed' to the 'Todo' model, first create a migration file using the CLI command. For example, run sequelize migration:create --name add_completed_to_todo. This generates a migration file with up and down functions to define schema changes and rollback logic.

Writing Migration Logic

In the migration file, define the up function to add the column and the down function to revert changes. For instance, to add a boolean column 'completed', the code should look like this:

module.exports = {
  up: function(queryInterface, Sequelize) {
    return queryInterface.addColumn('Todo', 'completed', Sequelize.BOOLEAN);
  },
  down: function(queryInterface, Sequelize) {
    return queryInterface.removeColumn('Todo', 'completed');
  }
};

Executing Migrations

After writing the migration, run sequelize db:migrate to apply the changes to the database. This ensures safe schema updates and avoids errors like "No migrations were executed, database schema was already up to date."

Advanced Usage: Adding Multiple Columns

For adding multiple columns at once, wrap operations in Promise.all. For example, to add two string columns to 'tableName':

module.exports = {
  up: (queryInterface, Sequelize) => {
    return Promise.all([
      queryInterface.addColumn('tableName', 'columnName1', { type: Sequelize.STRING }),
      queryInterface.addColumn('tableName', 'columnName2', { type: Sequelize.STRING }),
    ]);
  },
  down: (queryInterface, Sequelize) => {
    return Promise.all([
      queryInterface.removeColumn('tableName', 'columnName1'),
      queryInterface.removeColumn('tableName', 'columnName2'),
    ]);
  }
};

Conclusion

Using Sequelize CLI migrations allows for controlled and reversible schema changes, crucial for both development and production environments. Always commit migration files to the repository to ensure consistency and traceability of changes.