How to Add, Delete new Columns in Sequelize CLI

I've just started using Sequelize and Sequelize CLI

Since it's a development time, there are a frequent addition and deletion of columns. What the best the method to add a new column to an existing model?

For example, I want to a new column 'completed' to Todo model. I'll add this column to models/todo.js. Whats the next step?

I tried sequelize db:migrate

not working: "No migrations were executed, database schema was already up to date."

If you are using sequelize-cli you need to create the migration first. This is just a file that tells the engine how to update the database and how to roll back the changes in case something goes wrong. You should always commit this file to your repository

$ sequelize migration:create --name name_of_your_migration

The migration file would look like this:

module.exports = {
  up: function(queryInterface, Sequelize) {
    // logic for transforming into the new state
    return queryInterface.addColumn(
      'Todo',
      'completed',
     Sequelize.BOOLEAN
    );
   
  },
 
  down: function(queryInterface, Sequelize) {
    // logic for reverting the changes
    return queryInterface.removeColumn(
      'Todo',
      'completed'
    );
  }
}

And then, run it:

$ sequelize db:migrate

If you want to add multiple columns to the same table, wrap everything in a Promise.all() and put the columns you'd like to add within an array:

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')
    ]);
  }
};

You can have any column type supported by sequelize https://sequelize.readthedocs.io/en/2.0/api/datatypes/

To add multiple columns in sequelize

Step 1: generate empty migration

sequelize migration:generate --name custom_name_describing_your_migration

Step 2: add columns to the empty migration

Use a transaction as per the docs https://sequelize.org/master/manual/migrations.html#migration-skeleton:

module.exports = {
    up: (queryInterface, Sequelize) => {
        return queryInterface.sequelize.transaction((t) => {
            return Promise.all([
                queryInterface.addColumn('table_name', 'field_one_name', {
                    type: Sequelize.STRING
                }, { transaction: t }),
                queryInterface.addColumn('table_name', 'field_two_name', {
                    type: Sequelize.STRING,
                }, { transaction: t })
            ])
        })
    },

    down: (queryInterface, Sequelize) => {
        return queryInterface.sequelize.transaction((t) => {
            return Promise.all([
                queryInterface.removeColumn('table_name', 'field_one_name', { transaction: t }),
                queryInterface.removeColumn('table_name', 'field_two_name', { transaction: t })
            ])
        })
    }
};

Step 3: run the migration

sequelize db:migrate

If you are working in vscode, you can add type definition in the migration file. which helps to identify all the methods QueryInterface and sequelize provide.

 module.exports = {
/**
   * @typedef {import('sequelize').Sequelize} Sequelize
   * @typedef {import('sequelize').QueryInterface} QueryInterface
   */

  /**
   * @param {QueryInterface} queryInterface
   * @param {Sequelize} Sequelize
   * @returns
   */
  up: function(queryInterface, Sequelize) {
    // logic for transforming into the new state
    return queryInterface.addColumn(
      'Todo',
      'completed',
     Sequelize.BOOLEAN
    );

  },

  down: function(queryInterface, Sequelize) {
    // logic for reverting the changes
    return queryInterface.removeColumn(
      'Todo',
      'completed'
    );
  }
}

Which will provide intellisense like below

You can still use the sync function which takes an object parameter with two options of course a default option where you don't add a value and an instance where you add a force or an alter attribute. So in this case you want to use UserModel.sync({ force: true }): This creates the table, dropping it first if it already existed

UserModel.sync({ alter: true })

This checks what is the current state of the table in the database (which columns it has, what are their data types, etc), and then performs the necessary changes in the table to make it match the mode... You can use this when you use model instances For more info on updating and tables and models check out the docs on more functionality here

I think if you check your column inside a particular table before adding or removing it, that would be great. This will remove error if the column already exists.

'use strict';

module.exports = {
  // result_description
  up: async (queryInterface, Sequelize) => {
    let tableName = 'yourTableName';
    let columnName1 = 'columnName1';
    let columnName2 = 'columnName1';
    return Promise.all([
      queryInterface.describeTable(tableName)
        .then(tableDefinition => {
          if (tableDefinition.columnName1) return Promise.resolve();

          return queryInterface.addColumn(
            tableName,
            columnName1,
            {
              type: Sequelize.INTEGER,
              allowNull: false
            }
          );
        }),
      queryInterface.describeTable(tableName)
        .then(tableDefinition => {
          if (tableDefinition.columnName2) return Promise.resolve();

          return queryInterface.addColumn(
            tableName,
            columnName2,
            {
              type: Sequelize.STRING,
              allowNull: false
            }
          );
        })
    ]);
  },

  down: (queryInterface, Sequelize) => {

    let tableName = 'TestList';
    let columnName1 = 'totalScore';
    let columnName2 = 'resultDescription';
    return Promise.all([
      queryInterface.describeTable(tableName)
        .then(tableDefinition => {
          if (tableDefinition.columnName1) return Promise.resolve();
          return queryInterface.removeColumn(tableName, columnName1)
        }),
      queryInterface.describeTable(tableName)
        .then(tableDefinition => {
          if (tableDefinition.columnName1) return Promise.resolve();
          return queryInterface.removeColumn(tableName, columnName2)
        }),
    ]);
  }
};

Per Pter suggestion to wrap Promise in a transaction, here's a sample using async/await and a transaction (from docs with bug fix when creating an index):

'use strict';

module.exports = {
	async up(queryInterface, Sequelize) {
		const transaction = await queryInterface.sequelize.transaction();
		try {
			await queryInterface.addColumn(
				'Todo',
				'completed',
				{
					type: Sequelize.STRING,
				},
				{ transaction }
			);
			
			await queryInterface.addIndex(
				'Todo',
				{
					fields: ['completed'],
					unique: true,
				},
				{ transaction }
			);
			
			await transaction.commit();
		} catch (err) {
			await transaction.rollback();
			throw err;
		}
	},

	async down(queryInterface, Sequelize) {
		const transaction = await queryInterface.sequelize.transaction();
		try {
			await queryInterface.removeColumn(
				'Todo',
				'completed',
				{ transaction }
			);
			
			await transaction.commit();
		} catch (err) {
			await transaction.rollback();
			throw err;
		}
	}
};