Indexes & Uniques

Sequelize supports adding indexes to the model definition which will be created during model synchronization. You have multiple ways of defining indexes on a model.

Single-column index

The @Index decorator is the simplest solution to define an index. It is ideal for single-column indexes. To define an index, you use the @Index decorator on the attribute you want to index:

import { Model, DataTypes } from '@sequelize/core';
import { Index, Attribute, NotNull } from '@sequelize/core/decorators-legacy';

class User extends Model {
  @Attribute(DataTypes.STRING)
  @NotNull
  @Index
  declare firstName: string;
}

The @Index decorator accepts an optional options bag as a parameter. The list of available options is available in the API documentation.

Here is an example of a single-column gin index:

import { Model, DataTypes } from '@sequelize/core';
import { Index, Attribute, NotNull } from '@sequelize/core/decorators-legacy';

class User extends Model {
  @Attribute(DataTypes.JSONB)
  @NotNull
  @Index({ using: 'gin' })
  declare data: object;
}

Index names are automatically generated by Sequelize, but you can also specify a custom index name using the name option:

import { Model, DataTypes } from '@sequelize/core';
import { Index, Attribute, NotNull } from '@sequelize/core/decorators-legacy';

class User extends Model {
  @Attribute(DataTypes.STRING)
  @NotNull
  @Index({ name: 'user_first_name_index' })
  declare firstName: string;
}

Simple Multi-column indexes

In the above example, we gave our index a custom name using the name option. This can also be exploited to create multi-column indexes: You can use the same index name for multiple columns and Sequelize will create a single multi-column index for you:

import { Model, DataTypes } from '@sequelize/core';
import { Index, Attribute, NotNull } from '@sequelize/core/decorators-legacy';

class User extends Model {
  @Attribute(DataTypes.STRING)
  @NotNull
  @Index({ name: 'firstName-lastName' })
  declare firstName: string;

  @Attribute(DataTypes.STRING)
  @NotNull
  @Index({ name: 'firstName-lastName' })
  declare lastName: string;
}

The attribute option can be used to specify the options specific to that attribute in the multi-attribute index:

import { Model, DataTypes } from '@sequelize/core';
import { Index, Attribute, NotNull } from '@sequelize/core/decorators-legacy';

class User extends Model {
  @Attribute(DataTypes.STRING)
  @NotNull
  @Index({ name: 'firstName-lastName' })
  declare firstName: string;

  @Attribute(DataTypes.STRING)
  @NotNull
  @Index({
    name: 'firstName-lastName',
    // note: collations vary greatly between databases, this is just an example.
    // You should check your database's documentation on collations for more information.
    attribute: { collate: 'case_insensitive' },
  })
  declare lastName: string;
}

caution

While it's possible to use options other than name and attribute in multi-column indexes that use the @Index decorator, the practice is discouraged as you would have to repeat the same options on each attribute. Use createIndexDecorator for that use case instead.

Complex Multi-column indexes

The createIndexDecorator API is a convenient way to define complex multi-column indexes, without having to repeat the same options on each attribute.

Without createIndexDecorator, a complex multi-column index would look like this:

import { Model, DataTypes } from '@sequelize/core';
import { Index, Attribute, NotNull } from '@sequelize/core/decorators-legacy';

class User extends Model {
  @Attribute(DataTypes.STRING)
  @NotNull
  @Index({
    name: 'firstName-lastName',
    type: 'fulltext',
    concurrently: true,
  })
  declare firstName: string;

  @Attribute(DataTypes.STRING)
  @NotNull
  @Index({
    name: 'firstName-lastName',
    type: 'fulltext',
    concurrently: true,
    attribute: { collate: 'case_insensitive' },
  })
  declare lastName: string;
}

Notice how we had to repeat the same options on each attribute. This can become verbose very quickly. With createIndexDecorator, you only have to specify the options once:

const NameIndex = createIndexDecorator('NameIndex', {
  name: 'firstName-lastName',
  type: 'fulltext',
  concurrently: true,
});

class User extends Model {
  @Attribute(DataTypes.STRING)
  @NameIndex
  declare firstName: string;

  @Attribute(DataTypes.STRING)
  // Attribute-specific options are still specified on the attribute:
  @NameIndex({ collate: 'case_insensitive' })
  declare lastName: string;
}

Alternative Syntax (@Table)

Another way to define an index is to use the @Table decorator on your model. This decorator accepts an indexes option, which is an array of index definitions.

This option can define any index that can be defined using either the @Index decorator, or the createIndexDecorator API, but it can be more cumbersome to use. Here is an example:

import { Model, DataTypes } from '@sequelize/core';
import { Table, Attribute, NotNull } from '@sequelize/core/decorators-legacy';

@Table({
  indexes: [
    {
      name: 'firstName-lastName',
      type: 'fulltext',
      concurrently: true,
      // The "fields" option defines the list of attributes that make up the index, and their options:
      fields: ['firstName', { name: 'lastName', collate: 'case_insensitive' }],
    },
  ],
})
class User extends Model {
  @Attribute(DataTypes.STRING)
  @NotNull
  declare firstName: string;

  @Attribute(DataTypes.STRING)
  @NotNull
  declare lastName: string;
}

Unique Indexes

Unique constraints are basically the same as indexes, except that they also enforce uniqueness of the column(s) they are defined on.

Creating a unique index is as simple as adding the unique option to the index definition:

import { Model, DataTypes } from '@sequelize/core';
import { Index, Attribute, NotNull } from '@sequelize/core/decorators-legacy';

class User extends Model {
  @Attribute(DataTypes.STRING)
  @NotNull
  @Index({ unique: true })
  declare email: string;
}

This option is available in all ways of defining indexes.

Shorthand Syntax

If all you need is for the attribute(s) to be unique, and don't actually care about the other index options, you can use the @Unique decorator as a shorthand for the unique option:

import { Model, DataTypes } from '@sequelize/core';
import { Unique, Attribute, NotNull } from '@sequelize/core/decorators-legacy';

class User extends Model {
  @Attribute(DataTypes.STRING)
  @NotNull
  @Unique
  declare email: string;
}

You can also create composite uniques by giving the same name to multiple @Unique decorators:

import { Model, DataTypes } from '@sequelize/core';
import { Unique, Attribute, NotNull } from '@sequelize/core/decorators-legacy';

class User extends Model {
  @Attribute(DataTypes.STRING)
  @NotNull
  @Unique('firstName-lastName')
  declare firstName: string;

  @Attribute(DataTypes.STRING)
  @NotNull
  @Unique('firstName-lastName')
  declare lastName: string;
}

caution

The @Unique decorator does not give access to very useful index options. One such option is collate, which can be used to create case-insensitive indexes. If you need to use this option, you should use the @Index decorator instead.

Some examples

Did you know?

All index decorators (including @Unique) can be used more than once on the same attribute. This is useful if you need your column to be part of multiple, different, composite unique constraints.

This is a gin index that uses the jsonb_path_ops operator:

import { Model, DataTypes } from '@sequelize/core';
import { Index, Attribute, NotNull } from '@sequelize/core/decorators-legacy';

class User extends Model {
  @Attribute(DataTypes.JSONB)
  @NotNull
  @Index({ using: 'gin', operator: 'jsonb_path_ops' })
  declare data: Record<string, unknown>;
}

This is a btree index, with a few attribute options:

import { Model, DataTypes } from '@sequelize/core';
import { createIndexDecorator, Attribute, NotNull } from '@sequelize/core/decorators-legacy';

const MyIndex = createIndexDecorator('MyIndex', {
  using: 'btree',
});

class Book extends Model {
  @Attribute(DataTypes.STRING)
  @NotNull
  @MyIndex
  declare title: string;

  @Attribute(DataTypes.STRING)
  @NotNull
  @MyIndex({
    collate: 'en_US',
    order: 'DESC',
    length: 5,
  })
  declare title: string;
}