If you would enable Sequelize logging, you would see that during the "sync" phase and right after creating a table, Sequelize executes SHOW INDEX FROM table query. The question is - why?

---

To be more specific, here is the code I'm executing:

var Sequelize = require('sequelize');

var connection = new Sequelize('demo_schema', 'root', 'password');

var Article = connection.define('article', {
    slug: {
        type: Sequelize.STRING,
        primaryKey: true
    },
    title: {
        type: Sequelize.STRING,
        unique: true,
        allowNull: false
    },
    body:  {
        type: Sequelize.TEXT
    }
}, {
    timestamps: false
});

connection.sync({
    force: true,
    logging: console.log
}).then(function () {
   // TODO
});

Here is the output on the console:

Executing (default): DROP TABLE IF EXISTS `articles`;
Executing (default): DROP TABLE IF EXISTS `articles`;
Executing (default): CREATE TABLE IF NOT EXISTS `articles` (`slug` VARCHAR(255) , `title` VARCHAR(255) NOT NULL UNIQUE, `body` TEXT, UNIQUE `articles_title_unique` (`title`), PRIMARY KEY (`slug`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `articles`

If you would enable Sequelize logging, you would see that during the "sync" phase and right after creating a table, Sequelize executes SHOW INDEX FROM table query. The question is - why?

---

To be more specific, here is the code I'm executing:

var Sequelize = require('sequelize');

var connection = new Sequelize('demo_schema', 'root', 'password');

var Article = connection.define('article', {
    slug: {
        type: Sequelize.STRING,
        primaryKey: true
    },
    title: {
        type: Sequelize.STRING,
        unique: true,
        allowNull: false
    },
    body:  {
        type: Sequelize.TEXT
    }
}, {
    timestamps: false
});

connection.sync({
    force: true,
    logging: console.log
}).then(function () {
   // TODO
});

Here is the output on the console:

Executing (default): DROP TABLE IF EXISTS `articles`;
Executing (default): DROP TABLE IF EXISTS `articles`;
Executing (default): CREATE TABLE IF NOT EXISTS `articles` (`slug` VARCHAR(255) , `title` VARCHAR(255) NOT NULL UNIQUE, `body` TEXT, UNIQUE `articles_title_unique` (`title`), PRIMARY KEY (`slug`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `articles`

• javascript
• sql
• node.js
• orm
• sequelize.js

Share Improve this question Follow edited Jun 19, 2016 at 18:24 alecxe asked Jun 18, 2016 at 4:06 alecxealecxe 474k127127 gold badges1.1k1.1k silver badges1.2k1.2k bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 9 +50

The reason why Sequelize uses SHOW INDEX FROM is to avoid adding existing indexes to a model's table a second time. Adding an existing index a second time would result in an error.

Sequelize is currently not "smart" enough to understand that you have not defined any indexes. It checks the table anyway.

In your scenario, it does not make a difference since you force sync. That means the tables are always deleted and newly created. When adding new indexes, there won't be any existing indexes.

Imagine you would specify a model with indexes like so:

sequelize.define('user', {}, {
  indexes: [
    // Create a unique index on email
    {
      unique: true,
      fields: ['email']
    }
  ]
})

The extensive form of this example can be found at http://docs.sequelizejs./en/latest/docs/models-definition/#indexes.

Fictional Story

If you created the table with Sequelize.sync() yesterday, added a new index to your model today and would not use force sync (because then the table would be dropped and created --> no existing indexes), Sequelize will add only the new index.

How does Sequelize work here?

Sequelize determines which indexes are new by issuing SHOW INDEX FROM and paring the return value to the indexes of your model. If Sequelize finds that there are more (or different) indexes in your model than on your existing table, it will add these indexes to the underlying table.

The code doing this can be found on GitHub. In the following code snippet I added some ments to the respective code:

// this.QueryInterface.showIndex produces the statement `SHOW INDEX FROM [table of the model]`. It returns a promise containing the found indexes.
.then(() => this.QueryInterface.showIndex(this.getTableName(options), options))
// `indexes` will contain the indexes which are present on the existing table.
// If you force sync, this will always be empty since the table is new.
.then(indexes => {
  // Assign an auto-generated name to indexes which are not named by the user
  this.options.indexes = this.QueryInterface.nameIndexes(this.options.indexes, this.tableName);

  // Fill `indexes` with only the indexes from your model which are not present on the table.
  indexes = _.filter(this.options.indexes, item1 => !_.some(indexes, item2 => item1.name === item2.name));

  // .map iterates over `indexes` and adds an index for each entry.
  // Remeber, this is for indexes which are present in the model definition but not present on the table.
  return Promise.map(indexes, index => this.QueryInterface.addIndex(
    this.getTableName(options),
    _.assign({
      logging: options.logging,
      benchmark: options.benchmark,
      transaction: options.transaction
    }, index),
    this.tableName
  ));
})