Create a table and add indexes in a single migration with Sequelize

What is the correct way to create a table and add indices on some of its columns in a single migration?

Example Migration: 2012341234-create-todo.js

How would I create an index on the "author_id" and "title" column?

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Todos', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      author_id: {
        type: Sequelize.INTEGER,
        onDelete: 'CASCADE',
        references: {
          model: 'Authors',
          key: 'id',
          as: 'authorId'
        }
      },
      title: {
        type: Sequelize.STRING
      },

      content: {
        type: Sequelize.TEXT
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Todos');
  }
};

The Sequelize docs indicate that an index would be added like this:

queryInterface.addIndex('Todos', ['author_id', 'title']);

Can these methods just be chained? Do "up" and "down" just need to return a promise? I'm not seeing anything in the docs about it.

Solution 1:

Yes, the methods can be chained. In your case you just perform the addIndex after createTable method

return queryInterface.createTable('Todos', {
    // columns...
}).then(() => queryInterface.addIndex('Todos', ['author_id', 'title']))
.then(() => {
    // perform further operations if needed
});

Solution 2:

The accepted solution is problematic if the second step fails. Transactions in each step should be used to allow a roll back to ensure all of the migration steps that are inserts or updates are undone if a problem is encountered at any step. For example:

module.exports = {
 up: async (queryIntereface) => {
    const transaction = await queryInterface.sequelize.transaction();

    try {
      await queryInterface.createTable('Todos', {
        // columns...
      }, { transaction });
      await queryInterface.addIndex('Todos', ['author_id', 'title'], { transaction }));


      await transaction.commit();
    } catch (err) {
      await transaction.rollback();
      throw err;
    }
  },

  down: async (queryInterface) {

    etc...

Reference