How to use iLike operator with Sequelize to make case insensitive queries

In Sequelize, iLike is a highly practical operator for performing case-insensitive fuzzy queries in PostgreSQL databases. This is particularly useful for implementing search functionality when case sensitivity is not required. Here is an example of how to use the iLike operator for querying in Sequelize:

Assume we have a model named Users that represents a user table, containing fields firstName and lastName.

Example: Using iLike to Find Users

We want to find all users whose first name contains 'john', case-insensitively. Here is the code to achieve this:

javascript
const { User } = require('./models');

async function findUsersByName(name) {
  try {
    const users = await User.findAll({
      where: {
        firstName: {
          [Sequelize.Op.iLike]: `%${name}%`
        }
      }
    });
    return users;
  } catch (error) {
    console.error('Query failed:', error);
  }
}

// Call the function to perform the query
findUsersByName('john').then(users => {
  console.log('Found users:', users);
});

In this example, Sequelize.Op.iLike specifies a case-insensitive fuzzy query. We apply %${name}% to the firstName field, which searches for all firstName values containing the specified string, where ${name} is the search keyword passed in.

Notes

• iLike is only valid in PostgreSQL, as it is a PostgreSQL-specific operator. If you are using other databases (such as MySQL or SQLite), you may need to use the like operator and handle case sensitivity at the application level.
• Using iLike significantly simplifies handling case-insensitive queries, resulting in more concise and maintainable code.

By adopting this approach, you can perform flexible case-insensitive queries within your Sequelize application, enhancing user experience and data retrieval efficiency.