Cli: Index columns during createTable?

[LoneWolfPR]

I'm trying to create a table with the indexes defined at the same time. Is this possible? If so, I'm assuming it would be the options you specify for each column, but I can't find anything in the docs. If not, does this mean I need to create a separate migration for each index I want to define per table since running multiple migration commands in a single migration file isn't really advisable?

[ezze]

I was able to specify unique index for a single column only by setting unique property to true:

import Sequelize from 'sequelize';

const schema = 'geobase';

const tableSensor = {
    tableName: 'sensor',
    schema
};

const tableSatellite = {
    tableName: 'satellite',
    schema
};

const tableSatelliteSensor = {
    tableName: 'satellite_sensor',
    schema
};

export function up(queryInterface) {
    const {sequelize} = queryInterface;
    return sequelize.transaction(transaction => {
        const options = { transaction }; 
        return queryInterface.createTable(tableSatelliteSensor, {
            id: {
                type: Sequelize.INTEGER,
                primaryKey: true,
                autoIncrement: true
            },
            satelliteId: {
                field: 'satellite_id',
                type: Sequelize.INTEGER,
                references: {
                    model: tableSatellite,
                    key: 'id'
                },
                unique: true,
                onUpdate: 'cascade',
                onDelete: 'cascade'
            },
            ...
        }, options);
    });
}

Unfortunatelly, if I want to create a composite unique index composite_index similar to the one described in models definition it doesn't work:

satelliteId: {
    field: 'satellite_id',
    type: Sequelize.INTEGER,
    references: {
        model: tableSatellite,
        key: 'id'
    },
    unique: 'composite_index',
    onUpdate: 'cascade',
    onDelete: 'cascade'
},
sensorId: {
    field: 'sensor_id',
    type: Sequelize.INTEGER,
    references: {
        model: tableSensor,
        key: 'id'
    },
    unique: 'composite_index',
    onUpdate: 'cascade',
    onDelete: 'cascade'
},
deviceNumber: {
    field: 'device_number',
    type: Sequelize.INTEGER,
    unique: 'composite_index'
}

Passing indexes to options also doesn't work:

Object.assign({}, options, {
    indexes: [{
        unique: true,
        fields: ['satelliteId', 'sensorId', 'deviceNumber']
    }]
})

The only thing that works for me is using addIndex:

return queryInterface.createTable(...).then(() => {
    return queryInterface.addIndex(
        tableSatelliteSensor,
        ['satellite_id', 'sensor_id', 'device_number'],
        Object.assign({
            indexName: 'composite_index',
            indicesType: 'UNIQUE'
        }, options)
    );
});

It would be very useful if specifying indexes for createTable compatible with models definition could be supported.

[zavan]

For a less cluttered code, one could use async/await:

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable(...)
    return queryInterface.addIndex(...)
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable(...)
  }
}

[vishwasraj]

For a less cluttered code, one could use async/await:

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable(...)
    return queryInterface.addIndex(...)
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable(...)
  }
}

This needs to be in the documentation.

[advaavramento]

You can use it during createTable. This way:

return queryInterface.createTable(tableSatelliteSensor, attributes, {
 uniqueKeys: {
        satellite_sensor: {
          customIndex: true,
          fields: ['sensorId', 'satelliteId'],
        },
      },
});

Check it out https://github.com/sequelize/sequelize/pull/9946/files

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.