How to use operators in your Sequelize queries

As you may know, SQL query syntax has many operators that you can use to influence the action done by your query.

For example, the LIKE operator can be used in the WHERE clause to filter the data, showing only matching rows:

SELECT * FROM Users
WHERE `firstName` LIKE 'nathan';

When you use the Sequelize ORM for your JavaScript project, the SQL operators are included in the library in the form of the Op object.

You need to import the object from Sequelize to use it in your code:

const { Sequelize, Op } = require("sequelize");

When you call the query methods from your model, you can use the Op object properties to create SQL queries with operators.

For example, here’s how you use the findAll() method with the LIKE operator as in the previous example:

User.findAll({
  where: {
    firstName: {
      [Op.like]: "Nathan",
    },
  },
});

The Sequelize code above will generate and execute the following SQL query:

SELECT
  `id`,
  `firstName`,
  `status`
FROM
  `Users` AS `User`
WHERE
  `User`.`firstName` LIKE 'Nathan';

And that’s how you can use SQL operators in Sequelize.

Here’s the full list of available operators under the Sequelize Op object, taken from Sequelize v6.2.15.

Except for the first six operators, the rest are self-described:

const Op: OpTypes = {
  eq: Symbol.for('eq'),  // equal
  ne: Symbol.for('ne'),  // not equal
  gte: Symbol.for('gte'), // greater than or equal
  gt: Symbol.for('gt'), // greater than
  lte: Symbol.for('lte'), // less than or equal
  lt: Symbol.for('lt'), // less than
  not: Symbol.for('not'),
  is: Symbol.for('is'),
  in: Symbol.for('in'),
  notIn: Symbol.for('notIn'),
  like: Symbol.for('like'),
  notLike: Symbol.for('notLike'),
  iLike: Symbol.for('iLike'),
  notILike: Symbol.for('notILike'),
  startsWith: Symbol.for('startsWith'),
  endsWith: Symbol.for('endsWith'),
  substring: Symbol.for('substring'),
  regexp: Symbol.for('regexp'),
  notRegexp: Symbol.for('notRegexp'),
  iRegexp: Symbol.for('iRegexp'),
  notIRegexp: Symbol.for('notIRegexp'),
  between: Symbol.for('between'),
  notBetween: Symbol.for('notBetween'),
  overlap: Symbol.for('overlap'),
  contains: Symbol.for('contains'),
  contained: Symbol.for('contained'),
  adjacent: Symbol.for('adjacent'),
  strictLeft: Symbol.for('strictLeft'),
  strictRight: Symbol.for('strictRight'),
  noExtendRight: Symbol.for('noExtendRight'),
  noExtendLeft: Symbol.for('noExtendLeft'),
  and: Symbol.for('and'),
  or: Symbol.for('or'),
  any: Symbol.for('any'),
  all: Symbol.for('all'),
  values: Symbol.for('values'),
  col: Symbol.for('col'),
  placeholder: Symbol.for('placeholder'),
  join: Symbol.for('join'),
  match: Symbol.for('match')
} as OpTypes;

For more examples of how to use these operators, you can refer to the official Sequelize operators documentation.

And that’s how you can add operators in Sequelize. Nice work! 👍

Take your skills to the next level ⚡️

I'm sending out an occasional email with the latest tutorials on programming, web development, and statistics. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.