Using the where option in your Sequelize methods with examples

You can add the where option to any of the Sequelize query methods that you use in your JavaScript project.

Sequelize where option accepts an object describing the WHERE clause to add to your generated SQL query.

For example, suppose you have a table named Users with the following details:

+----+-----------+
| id | firstName |
+----+-----------+
|  1 | Nathan    |
+----+-----------+

For a WHERE clause with a simple condition, you can add a single property to the where object.

The property name will be the column name and the property value will be the value you use to filter the query.

await User.findAll({
  where: { firstName: "Nathan" },
});

The findAll() method will generate an SQL query with the following WHERE clause:

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

The above where option is the simplest WHERE condition you can define in your Sequelize query methods.

You can also create more complex conditions by adding operators from Sequelize Op object.

For example, you can create a WHERE ... IN condition using the Op.in operator as follows:

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

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

The generated SQL query will be as shown below:

SELECT
  `id`,
  `firstName`
FROM
  `Users` AS `User`
WHERE
  `User`.`firstName` IN ('Nathan', 'Jane');

Alternatively, you can also omit the Op.in operator for a WHERE ... IN clause and rewrite it as follows:

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

When you need to add the AND operator into the WHERE clause, you can add multiple properties to the where option.

The example below adds a condition for both the id AND firstName columns:

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

await User.findAll({
  where: {
    id: { [Op.lte]: 2 },
    firstName: { [Op.notIn]: ["Nathan", "Jane"] },
  },
});

The generated SQL query is as follows:

SELECT
  `id`,
  `firstName`
FROM
  `Users` AS `User`
WHERE
  `User`.`id` <= 2
  AND `User`.`firstName` NOT IN ('Nathan', 'Jane');

Lastly, for a WHERE with both OR and AND conditions:

await User.findAll({
  where: {
    id: {
      [Op.or]: {
        [Op.lt]: 5,
        [Op.eq]: 7,
      },
    },
    firstName: {
      [Op.like]: "Nathan",
    },
  },
});

The above method call will generate the following SQL query:

SELECT
  `id`,
  `firstName`
FROM
  `Users` AS `User`
WHERE (`User`.`id` < 5
  OR `User`.`id` != 7)
AND `User`.`firstName` LIKE 'Nathan';

And those are some examples of WHERE clauses you can make using the Sequelize where option.

You can use the where option with Sequelize findAll() or findOne(), destroy(), and update() methods.

Great work on learning about Sequelize where option. 😉