Sequelize.js: Select Rows Where Column is in List

Overview

Sequelize.js is a powerful Object-Relational Mapper (ORM) for Node.js, which is widely used for handling database interactions in an easy and efficient manner. This article provides a step-by-step guide on how to use Sequelize.js to retrieve data from the database where a specific column’s value is within a provided list. We will look at simple ‘IN’ queries followed by more complex, real-world examples to solidify the concept.

Setting Up Sequelize

Before we dive into querying with Sequelize, let’s ensure that you have Sequelize installed and set up:

npm install --save sequelize
npm install --save pg pg-hstore  // If using PostgreSQL

Next, let’s establish a connection to your database:

const Sequelize = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'postgres',
  host: 'localhost'
});

Basic IN Queries

Fetching rows where a column is in a list of values is straightforward in Sequelize:

const User = sequelize.define('user', {
  // ... define user attributes 
});

User.findAll({
  where: {
    id: [1, 2, 3]
  }
}).then(users => {
  console.log(users);
}).catch(error => {
  console.error(error);
});

This will select users where the ID is either 1, 2, or 3.

Advanced IN Queries

For more advanced scenarios, you might need to perform an ‘IN’ query with a subquery. Let’s assume we want to find all users who have made a purchase in a specific list of product IDs:

const User = sequelize.define('user', {
  // define user attributes
});
const Purchase = sequelize.define('purchase', {
  // define purchase attributes
});

User.hasMany(Purchase);
Purchase.belongsTo(User);

User.findAll({
  include: [{
    model: Purchase,
    where: {
      productId: {
        [Sequelize.Op.in]: [3, 7, 42]
      }
    }
  }]
}).then(users => {
  console.log(users);
}).catch(error => {
  console.error(error);
});

This query not only selects users based on the products they’ve purchased but also includes details from their purchase records.

Utilizing Sequelize Operators

Operators play a crucial role in creating dynamic queries in Sequelize:

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

User.findAll({
  where: {
    id: {
      [Op.in]: [1, 2, 3]
    }
  }
}).then(users => {
  console.log(users);
}).catch(error => {
  console.error(error);
});

The Op.in is the Sequelize operator that corresponds to SQL’s IN. It’s used to check whether a value is within a list of values.

Conclusion

In this article, we have covered how to construct ‘IN’ queries in Sequelize, starting from basic operations to more advanced scenarios involving subqueries and associations. These operations are vital for building complex database queries in a Node.js application with ease and efficiency.