Migrations for schema changes in Express

Introduction

Migrations help you change your database structure safely over time. They keep track of changes so your app and database stay in sync.

When you add a new table or column to your database.

When you rename or remove a column without losing data.

When you update the data type of a column.

When you want to share database changes with your team.

When deploying updates to production without breaking the app.

Syntax

Express

npx sequelize-cli migration:generate --name migration_name

// Inside the generated migration file:
module.exports = {
  up: async (queryInterface, Sequelize) => {
    // commands to apply changes
  },
  down: async (queryInterface, Sequelize) => {
    // commands to undo changes
  }
};

The up function applies the changes.

The down function reverts them if needed.

Examples

Adds an optional age column to the Users table and removes it on rollback.

Express

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn('Users', 'age', {
      type: Sequelize.INTEGER,
      allowNull: true
    });
  },
  down: async (queryInterface) => {
    await queryInterface.removeColumn('Users', 'age');
  }
};

Creates a new Posts table with id and title columns, and drops it on rollback.

Express

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Posts', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      title: {
        type: Sequelize.STRING,
        allowNull: false
      }
    });
  },
  down: async (queryInterface) => {
    await queryInterface.dropTable('Posts');
  }
};

Sample Program

This migration adds a new email column to the Users table. The column is required and unique. If you undo the migration, the column is removed.

Express

/* migration file: 20240601-add-email-to-users.js */
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn('Users', 'email', {
      type: Sequelize.STRING,
      allowNull: false,
      unique: true
    });
  },
  down: async (queryInterface) => {
    await queryInterface.removeColumn('Users', 'email');
  }
};

Important Notes

Always write the down method to safely undo changes.

Test migrations on a development database before applying to production.

Use descriptive names for migration files to remember their purpose.

Summary

Migrations help manage database changes step-by-step.

They have up and down functions to apply and undo changes.

Using migrations keeps your app and database structure in sync safely.