Database Indexes With Sequelize

In this article we will look at how to create database indexes with Sequelize.

In posts Sorting REST AP I Results and Advanced Filtering in REST API we used allowedSortBy and allowFilterBy fields to make sure users don’t tank the database. However, if you have a lot of rows in your table, and we are talking hundreds of thousands or millions, you need to use indexes. Obviously, you don’t want to index all the fields in your table, because indexing slows down table writes. Usually, you should identify data access patterns in your application and then index database accordingly. When deciding on adding database indexes you should keep in mid that if you have a small dataset, the database engine may choose not to even use indexes because the database can just load everything in memory. It is a good idea to introduce indexes later on in the lifetime of the application when the application starts to slow down and you know how users access data

In this tutorial we will use allowedSortBy and allowedFilterBy fields as the guidance, assuming those fields are frequently used in sorting and filtering.

Database Indexes and Constraints

Before we start coding, let’s talk theory. There are a lot of types of indexes. The commonly used ones are:

Simple (single-column) – index created based on only one table column.
Composite (multi-column) – index created on two or more columns in the table. Composite index also covers single column where statement if this column is the first in the index sequence. Index on client_id and source_id will be used for both
- WHERE client_id = 3
- WHERE client_id = 3 AND source_id = 28374
Unique – used not only for performance, but also for data integrity. It can be a single column or multicolumn index.
Implicit index – automatically created by the database server. Indexes are automatically created for primary key constraints and unique constraints.

Now let’s take a look at the constraints so we have a clear distinction between indexes and constraints.

Constraints are used to ensure consistency, accuracy and reliability of the data in the table. You can specify constraints when creating a table with the CREATE TABLE statement, or after creating a table with the ALTER TABLE statement. Similar to indexes you can create constraints for one on more columns.

Commonly used constraints are:

PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Primary key uniquely identifies each row in a table. Often used with AUTOINCREMENT
FOREIGN KEY – Prevents actions that would destroy links between tables
UNIQUE – Ensures that all values in a column are different
NOT NULL – Ensures that a column cannot have a NULL value
CHECK – Ensures that the values in a column satisfies a specific condition
DEFAULT – Sets a default value for a column if no value is specified

The reason we are interested in constraints is that primary key and unique constraint have an implicitly index when created. So we don’t have to create an index for them. Foreign key constraint is not implicitly indexed, however, a rule of thumb is to create an index for them.

Create Database Indexes with Sequelize Migrations

In order to create database indexes with Sequelize first install sequelize-cli in your project. After you install and configure sequelize-cli , run

npx sequelize-cli migration:create --name add-indexes-to-travels-table

to create indexes for travels table.

In the generated migration file put the following code.

"use strict";

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  up: async (queryInterface) => {
    await queryInterface.addIndex("travels", ["created_at"]);

    await queryInterface.addIndex("travels", {
      fields: ["name"],
    });

    await queryInterface.addIndex("travels", {
      fields: ["number_of_days"],
      name: "travels_number_of_days_idx",
    });
  },
  down: async (queryInterface) => {
    await queryInterface.removeIndex("travels", ["created_at"]);
    await queryInterface.removeIndex("travels", ["name"]);
    await queryInterface.removeIndex("travels", "travels_number_of_days_idx");
  },
};

In the code above we added index to created_at column because we use it as default order by column. We aloso use fields attribute to specify columns. As a matter of fact Sequelize documentation suggests using it this way, rather than passing an array of columns as shorthand. You can create a custom name for the index. In the down migration, when removing index you can specify either the fields that the index uses or its name.

In the same manner lets create database indexes for tours table:

npx sequelize-cli migration:create --name add-indexes-to-travels-table

Add the following code to the generated file:

"use strict";

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  up: async (queryInterface) => {
    await queryInterface.addIndex("tours", {
      fields: ["name"],
      unique: true,
    });

    await queryInterface.addIndex("tours", {
      fields: ["price"],
    });

    await queryInterface.addIndex("tours", {
      fields: ["starting_date", "ending_date"],
    });
  },
  down: async (queryInterface) => {
    await queryInterface.removeIndex("tours", ["name"]);
    await queryInterface.removeIndex("tours", ["price"]);
    await queryInterface.removeIndex("tours", ["starting_date", "ending_date"]);
  },
};

You can also specify if index is unique by assigning true value to unique column. You also can index multiple columns by passing an array to fields property. According to MySQL 8.3 documentation, you can specify up to 16 columns in a composite index.

Apply Indexes to Database

To apply database indexes, you need to run migrations. Run the command:

npx sequelize-cli db:migrate

After running the migration, you can check if indexes are created correctly by running SQL queries:

SHOW INDEXES FROM travels

and

SHOW INDEXES FROM tours

Conclusion

In conclusion, indexes boost database performance. However, they should be created with data access patterns in mind. Over-indexing may slow down database writes. Certain constraints, such as primary key and unique are indexed automatically (implicit index). Other constraints, such as foreign keys, may require indexing, because they are frequently used in joints.

Sequelize helps indexing database by providing an API and using migration paradigm.

Share this article