CODEWITHSUNDEEP
node.jsdatabasemigrationsequelize.js
Slava Fomin II
Slava Fomin II

Reputation: 28611

Define partial index in Sequelize migration?

I'm using the following index in my model definition right now:

{
  name: 'unique_partner_id',
  unique: true,
  fields: ['partnerId'],
  where: {
    email: {
      $ne: null
    }
  }
}

However, I want to use migrations instead of sync, so I'm trying to move this definition from model to the initial migration file.

There is a queryInterface.addIndex() method, however, I can't find any documentation for it.

SO, how do I define a partial index using queryInterface?

Upvotes: 11

Views: 2412

Answers (2)

PRS
PRS

Reputation: 702

I was looking for how to do this in a model and your question answered that for me. So now I'm going to answer this for you! Here's the up function that worked for me:

up: function(queryInterface, Sequelize) {
  return queryInterface.addIndex(
    'tablename',
    ['column1', 'column2'],
    {
      name: 'indexname',
      where: {column3: {[Sequelize.Op.ne]: null}}
    }
  );
}

Upvotes: 8

villasv
villasv

Reputation: 6831

Here's an example using the where querying syntax with operators:

  up: (queryInterface, Sequelize) => queryInterface.addIndex(
    'column_name',
    ['new_id'],
    {
      name: 'index_name',
      where: { new_id: { [Sequelize.Op.ne]: null } },
    },
  )

Upvotes: 0

Related Questions