How to update migration files when change column or add new column using Sequelize

When using Sequelize for database migrations, if you need to modify existing columns or add new columns, you must follow specific steps to ensure the correct update of the database schema while avoiding data loss. Below are the detailed steps and examples:

1. Create a new migration file

First, you must create a new migration file to record and execute your database schema changes. You can use the Sequelize CLI's migration:generate command to create a migration file. For example, if you want to add a new column named birthdate to the Users table, you can run:

bash
npx sequelize-cli migration:generate --name add-birthdate-to-users

This will create a new migration file in the migrations folder, with a timestamp prefix, such as 20210310160000-add-birthdate-to-users.js.

2. Modify the migration file to define column changes

In the generated migration file, you need to use Sequelize's migration API to define the specific changes. This is typically implemented using the up and down methods, where up applies the migration and down rolls it back.

Here is an example migration script for adding a new column:

javascript
'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn('Users', 'birthdate', {
      type: Sequelize.DATE,
      allowNull: true
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.removeColumn('Users', 'birthdate');
  }
};

3. Execute the migration

After creating and modifying the migration file, you can use the Sequelize CLI to run the migration and apply the changes to the database:

bash
npx sequelize-cli db:migrate

This command executes all unexecuted migrations, including the one you created for adding the column.

4. Verify the changes

After the migration is executed, verify the database to confirm that the birthdate column has been added to the Users table and that other data remains unchanged.

5. Roll back the migration (if needed)

If issues are found with the migration or if you need to revert changes, use the following command to roll back the most recent migration:

bash
npx sequelize-cli db:migrate:undo

This executes the down method of the last migration file, reverting the database schema changes.

By following these steps, you can safely update your database schema when using Sequelize to modify or add columns. In practice, ensure you thoroughly test migration scripts in development or testing environments before applying them to production.