Sequelize.js: How to use the IN and NOT IN operators

Introduction

Sequelize.js is one of the most popular Object-Relational Mapping (ORM) libraries for Node.js. It provides a robust framework to work with various SQL databases using a clean and developer-friendly API. One of its core features is the ease of building complex queries with operators like IN and NOT IN. This article will guide you through the use-cases and implementation strategies with Sequelize for these operators, utilizing practical examples.

What are the IN and NOT IN operators?

In SQL, the IN operator is used to specify multiple possible values for a column. It’s analogous to asking for values that are ‘inside’ a list. Conversely, NOT IN represents values that are ‘not inside’ the list. They are particularly handy in filtering queries where one needs to select records with fields matching a list of values.

Basic usage of IN operator

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

User.findAll({
  where: {
    id: {
      [Op.in]: [1, 2, 3]
    }
  }
});

In this example, we’re retrieving all users whose IDs are either 1, 2, or 3.

Basic usage of NOT IN operator

User.findAll({
  where: {
    id: {
      [Op.notIn]: [4, 5, 6]
    }
  }
});

Conversely, this query returns all users that do not have IDs 4, 5, or 6.

Advanced Queries

Both operators can be used not just with numeric values but also with strings, dates, and associated models in complex joined queries. Let’s explore some advanced use-cases.

Working with Strings

User.findAll({
  where: {
    email: {
      [Op.in]: ['[email protected]', '[email protected]']
    }
  }
});

This query fetches users with specific email addresses, demonstrating how the IN operator can also be applied to string values.

Using with Associations

// Assume User hasMany Posts
User.findAll({
  include: [{
    model: Post,
    where: {
      id: {
        [Op.notIn]: [10, 20, 30]
      }
    }
  }]
});

Here we are extracting users along with their posts, excluding posts with certain IDs.

Conclusion

The IN and NOT IN operators in Sequelize.js provide powerful capabilities for query construction, allowing for concise and readable code when working with sets of values. Whether you are filtering on basic field values or constructing advanced queries involving associations and subqueries, these operators are indispensable tools in the Sequelize toolkit.