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

Question

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.

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

Answer 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

• https://sequelize.org/master/manual/migrations.html#migration-skeleton (search for "transaction()"