How to Add, Delete new Columns in Sequelize CLI

Question

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."

Refresh

Answer 1

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

Operation

Is there any documentation for this? – Refresh

Thanks. What if I want to add multiple columns? – Refresh

Also which docs should I prefer? docs.sequelizejs.com or sequelize.readthedocs.io ?? – Refresh

For 1. You just add multiple addColumn statements in the up function and then the corresponding removeColumns in the down function. – Operation

For 2. I'd say sequelize.readthedocs.io/en/v3. It comes straight from the source code repository. – Operation

Thanks. Note that you have to specify the name of the new migration with --name parameter. – Paramour

Should I manually add the new column to the already existing model file before or after the migration command? Or does sequelize add it automatically? – Facies

@Hans, sequelize doesn't touch the model file. So you should update the model class after the migration is done. – Operation

how to add default value ? like default must be true.. what option do we provide – Electrophone

Answer 2

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/

Tool

Also, it will be better to wrap a promise using a transaction. You can find an example here: docs.sequelizejs.com/manual/migrations.html#migration-skeleton – Beachhead

@Beachhead You can use transactions and column changes, but mysql 5.7 does not allow column changes as part of a transaction. – Stanton

Answer 3

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

Diffractometer

Upvoting for giving a direct link to the migrations part of the doc. – Sorry

@Gavin creating migration, adding new columns to this migration and then running migration will also add the columns to model as well? – Edina

@MKJ it will not add columns to the model. If you generate a new migration for a new table and give it attributes, it would then generate the model with the given attributes (columns) – Diffractometer

Answer 4

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

Ned

Answer 5

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

Mencius

that's awesome! – Deodorant

hi @NS23, what extension are you using? – Apiculate

@JohnReyFlores there is no need for any extension. You can read article in link to better understand type safety with jsdoc “Type Safe JavaScript with JSDoc” by TruckJS medium.com/@trukrs/type-safe-javascript-with-jsdoc-7a2a63209b76 – Mencius

GREAT I didn't know about this incredible usefull feature!! thank you – Gauntlett

Answer 6

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;
        }
    }
};

Attrition

why do you manually rollback? you can get ride of the try/catch, when an error comes it will automatically rollback – Deodorant

Answer 7

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

Merna