Skip to main content
Version: v7 - alpha

Operators

Sequelize provides a large number of operators to help you build complex queries. They are available in the Op object, which can be imported from @sequelize/core.

They can be used in two ways; either using sql.where, or as a key in where POJOs:

import { Op } from '@sequelize/core';

Post.findAll({
  where: {
    commentCount: {
      [Op.gt]: 2,
    },
  },
});

// or

Post.findAll({
  where: sql.where(sql.attribute('commentCount'), Op.gt, 2),
});

Basic Operators​

Implicit Operator​

When using a POJO, you can omit the operator and Sequelize will infer it. Depending on the value, Sequelize will use either the Op.eq, Op.is or Op.in operators.

Using null as the value will make Sequelize use the Op.is operator:

Post.findAll({
  where: {
    authorId: null,
  },
});
SELECT * FROM "posts" WHERE "authorId" IS NULL;

Using an array as the value will make Sequelize use the Op.in operator:

Post.findAll({
  where: {
    authorId: [2, 3],
  },
});
SELECT * FROM "posts" WHERE "authorId" IN (2, 3);

Finally, using any other value will make Sequelize use the Op.eq operator:

Post.findAll({
  where: {
    authorId: 2,
  },
});
SELECT * FROM "posts" WHERE "authorId" = 2;

Equality Operator​

Op.eq and Op.ne are the simple "equals" and "not equals" operators:

Post.findAll({
  where: {
    authorId: { [Op.eq]: 12 },
  },
});
SELECT * FROM "posts" WHERE "authorId" = 12;
info

As mentioned in the section about Implicit Operators, Sequelize can infer the Op.eq operator if you omit it, but there are cases where you need to explicitly use it, for instance when you want to do an equality check against an array, or a JSON object:

Post.findAll({
  where: {
    tags: {
      [Op.eq]: ['cooking', 'food'],
    },
    jsonMetadata: {
      [Op.eq]: { key: 'value' },
    },
  },
});

Produces:

SELECT * FROM "posts" WHERE "tags" = ARRAY['cooking', 'food'] AND "jsonMetadata" = '{"key": "value"}';

Whereas omitting the Op.eq operator would produce the following:

SELECT * FROM "posts" WHERE "tags" IN ('cooking', 'food') AND "jsonMetadata"->'key' = 'value';

IS Operator​

PostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
IS NULL
IS true, IS false[docs]

The Op.is and Op.isNot operators are used to check for NULL and boolean values:

Post.findAll({
  where: {
    authorId: { [Op.is]: null },
  },
});
SELECT * FROM "posts" WHERE "authorId" IS NULL;

Comparison Operators​

Op.gt, Op.gte, Op.lt, Op.lte are the comparison operators. They respectively mean:

  • Op.gt: Greater than
  • Op.gte: Greater than or equal to
  • Op.lt: Less than
  • Op.lte: Less than or equal to
Post.findAll({
  where: {
    commentCount: { [Op.gt]: 10 },
  },
});
SELECT * FROM "posts" WHERE "commentCount" > 10;
note

Sequelize does not include SQL Server's "not less than" (!<) and "not greater than" operators (!>).

See Custom Operators to learn how you can use them in your queries.

Between Operator​

The Op.between and Op.notBetween operators are used to check if a value is between two values. This operator takes an array of exactly two values (the lower and upper bounds):

Post.findAll({
  where: {
    commentCount: { [Op.between]: [1, 10] },
  },
});
SELECT * FROM "posts" WHERE "commentCount" BETWEEN 1 AND 10;

IN Operator​

The Op.in and Op.notIn operators are used to check if a value is in a list of values:

Post.findAll({
  where: {
    authorId: { [Op.in]: [2, 3] },
  },
});
SELECT * FROM "posts" WHERE "authorId" IN (2, 3);

String Operators​

LIKE Operator​

The Op.like and Op.notLike operators are used to check if a value matches a pattern. In supported dialects, you can also use Op.iLike and Op.notILike to perform case-insensitive matches.

Post.findAll({
  where: {
    title: { [Op.like]: '%The Fox & The Hound%' },
  },
});
SELECT * FROM "posts" WHERE "title" LIKE '%The Fox & The Hound%';

Sequelize does not provide a way to escape characters in LIKE patterns yet. You will need to use a custom operator to do so:

import { Literal, sql, Expression } from '@sequelize/core';

function like(value: Expression, pattern: string, escape: string): Literal {
  return sql`${value} LIKE ${pattern} ESCAPE ${escape}`;
}

Post.findAll({
  where: like(sql.attribute('title'), 'Inflation is above 10\\%', '\\'),
});
SELECT * FROM "posts" WHERE "title" LIKE 'Inflation is above 10\\%' ESCAPE '\\';

Regexp Operator​

The Op.regexp and Op.notRegexp operators are used to check if a value matches a regular expression. In supported dialects, you can also use Op.iRegexp and Op.notIRegexp to perform case-insensitive matches.

Post.findAll({
  where: {
    title: { [Op.regexp]: '^The Fox' },
  },
});
SELECT * FROM "posts" WHERE "title" ~ '^The Fox';

Starts & Ends With Operator​

The Op.startsWith and Op.endsWith operators are used to check if a value starts or ends with a string. Their negated versions are Op.notStartsWith and Op.notEndsWith.

Unlike the LIKE and REGEXP operators, these operators are case-sensitive, and will do an exact match against the string, not a pattern match.

These operators do not exist natively in most dialects, but are still available through Sequelize as Sequelize will generate the appropriate replacement.

caution

Currently, Op.startsWith and Op.endsWith both use LIKE under the hood and do not escape LIKE pattern characters. This is considered to be a bug and will be fixed in a future release.

Contains String Operator​

The Op.substring and Op.notSubstring operators are used to check if a value contains a string.

Unlike the LIKE and REGEXP operators, these operators are case-sensitive, and will do an exact match against the string, not a pattern match.

Just like Op.startsWith and Op.endsWith, these operators do not exist natively in most dialects, but are still available through Sequelize as Sequelize.

caution

Currently, Op.substring uses LIKE under the hood and does not escape LIKE pattern characters. This is considered to be a bug and will be fixed in a future release.

Array Operators​

PostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
[docs]

Array Contains Operator​

Op.contains and Op.contained are used to check whether an array contains or is contained by another array.

Post.findAll({
  where: {
    tags: { [Op.contains]: ['popular', 'trending'] },
  },
});
SELECT * FROM "posts" WHERE "tags" @> ARRAY['popular', 'trending'];
info

Both operands of the Op.contains and Op.contained operators must be arrays.

  • If you need to check whether an array contains a single value, you need to wrap the value in an array.
  • If you want to check if a single value is contained in an array, you can use the Op.any or Op.in operators instead.

Array Overlap Operator​

The Op.overlap operator can be used to check whether two arrays have at least one value in common:

Post.findAll({
  where: {
    tags: { [Op.overlap]: ['popular', 'trending'] },
  },
});
SELECT * FROM "posts" WHERE "tags" && ARRAY['popular', 'trending'];

Range Operators​

PostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
[docs]

Range Contains Operator​

These operators check for the containment relationship between two ranges.

The Op.contains operator returns true if the left range completely contains the other, while the Op.contained operator returns true if the left range is completely contained within the other.

The contained value can be a single element or a range.

Post.findAll({
  where: {
    // publishedDuring is a range of dates
    // This checks if a single date is present in the range
    publishedDuring: { [Op.contains]: new Date() },
  },
});
SELECT * FROM "posts" WHERE "publishedDuring" @> '2020-01-01';
Post.findAll({
  where: {
    // publishedDuring is a range of dates
    // This checks if a date range is fully contained within the publishedDuring range
    publishedDuring: {
      [Op.contains]: [new Date('2019-01-01'), new Date('2023-01-01')],
    },
  },
});
SELECT * FROM "posts" WHERE "publishedDuring" @> '[2019-01-01, 2023-01-01)';

Range Overlap Operator​

The Op.overlap operator determines whether two ranges have at least one value in common.

Post.findAll({
  where: {
    publishedDuring: {
      [Op.overlap]: [new Date('2019-01-01'), new Date('2023-01-01')],
    },
  },
});
SELECT * FROM "posts" WHERE "publishedDuring" && '[2019-01-01, 2023-01-01)';

Adjacent Ranges Operator​

The Op.adjacent operator checks whether two ranges are consecutive without gaps between them, nor overlaps.

Event.findAll({
  where: {
    occursDuring: {
      [Op.adjacent]: [new Date('2019-01-01'), new Date('2023-01-01')],
    },
  },
});
SELECT * FROM "events" WHERE "occursDuring" -|- '[2019-01-01, 2023-01-01)';

Range Left/Right Operators​

These operators can be used to check where the values of a range are located relative to the values of another range.

The Op.strictLeft operator check whether all values of the left range are less than any value of the other range.

Event.findAll({
  where: {
    occursDuring: {
      [Op.strictLeft]: [new Date('2019-01-01'), new Date('2023-01-01')],
    },
  },
});
SELECT * FROM "events" WHERE "occursDuring" << '[2019-01-01, 2023-01-01)';

JSONB Operators​

PostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
[docs]
Querying JSON

Sequelize offers a convenient syntax to access nested JSON properties. That syntax works with JSONB operators as well. See Querying JSON for more information.

JSON Contains Operator​

The Op.contains and Op.contained operators can be used to check whether a JSONB value contains another JSONB value.

Post.findAll({
  where: {
    meta: { [Op.contains]: { keywords: 'orm, javascript, sequelize' } },
  },
});
SELECT * FROM "posts" WHERE "meta" @> '{"keywords": "orm, javascript, sequelize"}';

To lean more, read about JSONB containment.

info

Sequelize stringifies JSON values for you, so you can use JavaScript values instead of using JSON strings.

JSON Key Existence Operators​

The Op.anyKeyExists and Op.allKeysExist check whether a JSONB value contains any or all of the given keys, respectively.

Post.findAll({
  where: {
    meta: { [Op.anyKeyExists]: ['keywords', 'description'] },
  },
});
SELECT * FROM "posts" WHERE "meta" ?| ARRAY['keywords', 'description'];

Misc Operators​

TSQuery Matching Operator​

PostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
[docs]

The Op.match operator is a postgres-specific operator that allows you to match a tsvector against a tsquery. It is equal to the @@ postgres operator.

It is one of the only operators that do not accept JavaScript values. Instead you must provide a built tsquery object, which you can get using the sql tag or the sql.fn function:

import { sql } from '@sequelize/core';

Document.findAll({
  where: {
    // in this example, it is assumed that this attribute has been previously populated using
    // postgres' to_tsvector function.
    searchTsVector: {
      [Op.match]: sql`to_tsquery('english', 'cat & rat')`,
    },
  },
});
SELECT * FROM "documents" WHERE "searchTsVector" @@ to_tsquery('english', 'cat & rat');

To learn more, see PostgreSQL's documentation on Full Text Search.

ALL, ANY, & VALUES​

PostgreSQLMariaDBMySQLMSSQLSQLiteSnowflakedb2ibmi
[docs]

The Op.all and Op.any operators can be used to compare a single value to an array of values.

What makes these operators especially useful is that it can be combined with other operators:

import { Op } from '@sequelize/core';

Post.findAll({
  where: {
    title: {
      // this will check that the title contains both the word "cat" and "dog"
      [Op.iLike]: {
        [Op.all]: ['%cat%', '%dog%'],
      },
    },
  },
});
SELECT * FROM "posts" WHERE "title" ILIKE ALL (ARRAY['%cat%', '%dog%']::TEXT[]);

If no comparison operator is specified, this operator will use the equality operator by default:

import { Op } from '@sequelize/core';

Post.findAll({
  where: {
    authorId: {
      // this will check that the authorId is equal to either 12 or 13
      [Op.any]: [12, 13],
    },
  },
});
SELECT * FROM "posts" WHERE "authorId" = ANY (ARRAY[12, 13]::INTEGER[]);

One limitation of ARRAYS is that you cannot make one of the values dynamic. The entire array is the value being compared. The Op.values operator can be used to circumvent this limitation. It allows you to specify a list of values that include computed values:

import { Op } from '@sequelize/core';

Post.findAll({
  where: {
    authorId: {
      [Op.any]: {
        // Op.values can be used to specify a list of values that include computed values,
        // like a column name.
        [Op.values]: [12, sql`12 + 45`],
      },
    },
  },
});
SELECT * FROM "posts" WHERE "authorId" = ANY (VALUES (12), (12 + 45));

Logical combinations​

The Op.and, Op.or, and Op.not operators can be used to combine multiple conditions.

AND​

A single object can specify multiple properties to check. If you do not specify any combination operator, Sequelize will default to joining them with Op.and:

Post.findAll({
  where: {
    authorId: 12,
    status: 'active',
  },
});
SELECT * FROM "posts" WHERE "authorId" = 12 AND "status" = 'active';

The same is true for arrays of objects:

Post.findAll({
  where: [{ authorId: 12 }, { status: 'active' }],
});
SELECT * FROM "posts" WHERE "authorId" = 12 AND "status" = 'active';

OR​

To replace that implicit AND with an OR, you can use the Op.or operator:

import { Op } from '@sequelize/core';

Post.findAll({
  where: {
    [Op.or]: {
      authorId: 12,
      status: 'active',
    },
  },
});
SELECT * FROM "posts" WHERE "authorId" = 12 OR "status" = 'active';

Of course, Op.or also accepts arrays:

import { Op } from '@sequelize/core';

Post.findAll({
  where: {
    [Op.or]: [{ authorId: 12 }, { status: 'active' }],
  },
});
SELECT * FROM "posts" WHERE "authorId" = 12 OR "status" = 'active';
note

When nesting objects inside of an Op.or operator, the OR operator will only be applied to one level of nesting at a time.

import { Op } from '@sequelize/core';

Post.findAll({
  where: {
    [Op.or]: [
      // These properties will be joined with `AND`
      {
        authorId: 12,
        status: 'active',
      },
      {
        commentCount: 12,
      },
    ],
  },
});
SELECT * FROM "posts" WHERE ("authorId" = 12 AND "status" = 'active') OR "commentCount" = 12;

NOT​

The Op.not operator can be used to negate a condition:

import { Op } from '@sequelize/core';

Post.findAll({
  where: {
    [Op.not]: {
      authorId: 12,
      status: 'active',
    },
  },
});
SELECT * FROM "posts" WHERE NOT ("authorId" = 12 AND "status" = 'active');

Where to use logical operators​

Logical operators can be used both before and after the name of the attribute being compared. For instance, the following two queries are equivalent:

import { Op } from '@sequelize/core';

Post.findAll({
  where: {
    [Op.or]: [{ authorId: 12 }, { authorId: 24 }],
  },
});

Post.findAll({
  where: {
    authorId: {
      [Op.or]: [12, 24],
    },
  },
});
SELECT * FROM "posts" WHERE "authorId" = 12 OR "authorId" = 24;

However, is it forbidden to use logical operators inside of another non-logical operator. The following would result in an error:

import { Op } from '@sequelize/core';

Post.findAll({
  where: {
    authorId: {
      // This is not allowed: OR must contain GT, not the other way around
      [Op.gt]: {
        [Op.or]: [12, 24],
      },
    },
  },
});

Custom Operators​

Thanks to the sql tag, it is very easy to create custom operators. We recommend reading the chapter on raw queries to learn more.

Here is an example of a LIKE that supports escaping special LIKE characters:

import { Literal, sql, Expression } from '@sequelize/core';

function like(value: Expression, pattern: string, escape: string): Literal {
  return sql`${value} LIKE ${pattern} ESCAPE ${escape}`;
}

Post.findAll({
  where: [{ authorId: 12 }, like(sql.attribute('title'), 'Inflation is above 10\\%', '\\')],
});
SELECT * FROM "posts" WHERE "authorId" = 12 AND "title" LIKE 'Inflation is above 10\%' ESCAPE '\';