How to define an index, within a Sequelize model?

Defining indexes in Sequelize can improve the efficiency of database queries, especially when handling large volumes of data and complex queries. Defining indexes in Sequelize models is primarily achieved through the indexes option of the model. The following outlines the steps and examples for defining indexes in Sequelize models.

Step 1: Define the Model

First, define a Sequelize model. Let's assume we have a User model with email and username fields.

javascript
const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('sqlite::memory:');

const User = sequelize.define('User', {
  email: {
    type: DataTypes.STRING,
    allowNull: false
  },
  username: {
    type: DataTypes.STRING,
    allowNull: false
  }
});

Step 2: Add Indexes

In the model definition, you can add indexes through the indexes property, which is an array containing one or more objects, each representing an index.

Single-column index

If you want to create an index on the email field, you can do the following:

javascript
const User = sequelize.define('User', {
  email: {
    type: DataTypes.STRING,
    allowNull: false
  },
  username: {
    type: DataTypes.STRING,
    allowNull: false
  }
}, {
  indexes: [
    {
      name: 'email_index',  // Index name, optional
      unique: true,         // Whether it is a unique index
      fields: ['email']     // Indexed fields
    }
  ]
});

Composite index

If you want to create a composite index on email and username, you can define it as follows:

javascript
const User = sequelize.define('User', {
  email: {
    type: DataTypes.STRING,
    allowNull: false
  },
  username: {
    type: DataTypes.STRING,
    allowNull: false
  }
}, {
  indexes: [
    {
      name: 'email_username_index',
      unique: true,          
      fields: ['email', 'username']  // Indexed fields for composite index
    }
  ]
});

Summary

By adding index objects to the indexes property in the model definition, you can conveniently define indexes in Sequelize. Each index object can specify the index name (name), whether it is unique (unique), and the indexed fields (fields). Adding indexes can significantly improve query performance, especially when dealing with large datasets. This approach makes database design more flexible and efficient, ensuring the performance and scalability of the application.