Sequelize: How to Find All Records with Case Insensitive 'Contains' (Fixing the 'operator does not exist' Error)

Table of Contents#

1. Understanding the Problem: Case-Insensitive "Contains" Search
2. Why Databases Matter: Case Sensitivity by Default
3. The "operator does not exist" Error: Root Cause
4. Solutions to Fix the Error
   • Solution 1: Use Op.iLike (PostgreSQL-Specific)
   • Solution 2: Op.like with LOWER() (Database-Agnostic)
   • Solution 3: Case-Insensitive Collation (MySQL-Specific)
   • Solution 4: Raw Queries with Sequelize.literal
5. Step-by-Step Implementation with Examples
6. Handling Edge Cases
7. Conclusion
8. References

Understanding the Problem: Case-Insensitive "Contains" Search#

A "contains" search looks for partial matches (e.g., "john" in "Johnny" or "Sarah Johnson"). "Case-insensitive" means the search ignores uppercase/lowercase differences (e.g., "John" matches "john", "JOHN", or "jOhN").

Sequelize provides operators like Op.like and Op.contains for pattern matching, but their behavior varies by database. The confusion arises when developers assume Op.contains works for string "contains" logic across all databases—but it doesn’t.

Why Databases Matter: Case Sensitivity by Default#

Database engines handle string comparison and case sensitivity differently:

• PostgreSQL: Text comparisons are case-sensitive by default. LIKE is case-sensitive, while ILIKE is case-insensitive.
• MySQL: Behavior depends on the column’s collation. For example, utf8_general_ci (case-insensitive) vs. utf8_bin (case-sensitive).
• SQLite: LIKE is case-insensitive for ASCII characters by default but case-sensitive for Unicode unless using COLLATE NOCASE.

Sequelize abstracts database differences, but you must still account for these nuances to avoid errors.

The "operator does not exist" Error: Root Cause#

The "operator does not exist" error (e.g., operator does not exist: text @> unknown in PostgreSQL) typically occurs when using Op.contains incorrectly.

Why? Sequelize’s Op.contains is designed for array containment, not string "contains" logic. For example, in PostgreSQL, Op.contains generates the @> operator, which checks if one array contains another (e.g., ARRAY[1,2] @> ARRAY[1]). Using Op.contains on a text column (e.g., name: { [Op.contains]: 'john' }) will fail because the database doesn’t recognize @> as a valid operator for text.

Mistake Example:

// ❌ Incorrect: Op.contains is for arrays, not strings!
const users = await User.findAll({
  where: {
    name: { [Op.contains]: 'john' } // Throws "operator does not exist"
  }
});

Solutions to Fix the Error#

To perform a case-insensitive "contains" search, use one of these database-aware solutions instead of Op.contains.

Solution 1: Use Op.iLike (PostgreSQL-Specific)#

PostgreSQL supports ILIKE, a case-insensitive version of LIKE. Sequelize’s Op.iLike operator generates ILIKE queries, making it ideal for PostgreSQL.

How it works:
Op.iLike matches patterns case-insensitively. Use % as a wildcard (matches any sequence of characters).

Example:

const { Op } = require('sequelize');
 
// ✅ Find users where "name" contains "john" (case-insensitive)
const users = await User.findAll({
  where: {
    name: { [Op.iLike]: '%john%' } // Generates: "name" ILIKE '%john%'
  }
});

Solution 2: Op.like with LOWER() (Database-Agnostic)#

For cross-database compatibility (PostgreSQL, MySQL, SQLite), use Sequelize.fn('LOWER', ...) to normalize both the column and search term to lowercase, then use Op.like.

How it works:

• Convert the column value to lowercase with Sequelize.fn('LOWER', Sequelize.col('name')).
• Convert the search term to lowercase and wrap in % wildcards.
• Use Op.like to match the normalized values.

Example:

const { Op, fn, col } = require('sequelize');
 
// ✅ Database-agnostic case-insensitive contains
const searchTerm = 'john';
const users = await User.findAll({
  where: {
    [Op.and]: fn('LOWER', col('name')),
    [Op.like]: `%${searchTerm.toLowerCase()}%`
  }
});
 
// Equivalent shorthand:
const users = await User.findAll({
  where: fn('LOWER', col('name')), { [Op.like]: `%${searchTerm.toLowerCase()}%` }
});

Generated SQL (cross-database):

WHERE LOWER("name") LIKE '%john%'

Solution 3: Case-Insensitive Collation (MySQL-Specific)#

MySQL uses collations to define string comparison rules. If your column uses a case-insensitive collation (e.g., utf8mb4_general_ci), Op.like will work case-insensitively by default.

Check Collation:

-- Check collation of the "name" column
SHOW FULL COLUMNS FROM users LIKE 'name';

If collation is case-insensitive:

// ✅ MySQL with case-insensitive collation
const users = await User.findAll({
  where: {
    name: { [Op.like]: '%john%' } // Case-insensitive due to collation
  }
});

If collation is case-sensitive (e.g., utf8mb4_bin), use LOWER() as in Solution 2.

Solution 4: Raw Queries with Sequelize.literal#

For complex scenarios, use Sequelize.literal to write raw SQL. This gives you full control over the query.

Example (PostgreSQL):

const users = await User.findAll({
  where: Sequelize.literal(`name ILIKE '%john%'`)
});

Example (MySQL with LOWER):

const users = await User.findAll({
  where: Sequelize.literal(`LOWER(name) LIKE '%john%'`)
});

Step-by-Step Implementation with Examples#

Let’s walk through a complete example using a User model with a name field.

1. Define the Model#

First, define a User model with a name column:

// models/User.js
module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    name: {
      type: DataTypes.STRING,
      allowNull: false
    }
  });
  return User;
};

2. Example 1: PostgreSQL with Op.iLike#

const { Op } = require('sequelize');
const User = require('./models/User');
 
async function findUsersContainingJohn() {
  try {
    const users = await User.findAll({
      where: {
        name: { [Op.iLike]: '%john%' } // Case-insensitive contains
      }
    });
    console.log(users.map(u => u.name)); // Output: ["John Doe", "Johnny Appleseed", "jOhN Smith"]
  } catch (error) {
    console.error('Error:', error);
  }
}
 
findUsersContainingJohn();

3. Example 2: Database-Agnostic with LOWER()#

const { Op, fn, col } = require('sequelize');
const User = require('./models/User');
 
async function findUsersContainingTerm(searchTerm) {
  try {
    const normalizedTerm = `%${searchTerm.toLowerCase()}%`;
    const users = await User.findAll({
      where: fn('LOWER', col('name')), { [Op.like]: normalizedTerm }
    });
    return users;
  } catch (error) {
    console.error('Error:', error);
  }
}
 
// Usage: Find users with "alice" in name (case-insensitive)
findUsersContainingTerm('alice');

4. Example 3: MySQL with Case-Insensitive Collation#

If your name column uses utf8mb4_general_ci (case-insensitive collation):

const { Op } = require('sequelize');
const User = require('./models/User');
 
async function findUsersInMySQL() {
  const users = await User.findAll({
    where: {
      name: { [Op.like]: '%john%' } // Collation makes this case-insensitive
    }
  });
  console.log(users.map(u => u.name)); // Output: ["John", "JOHN", "john"]
}

Handling Edge Cases#

Escaping Special Characters#

The % and _ characters are wildcards in LIKE/ILIKE. If your search term contains them (e.g., "john%doe"), escape them to avoid unexpected matches:

function escapeLikeWildcards(term) {
  return term.replace(/[%_]/g, '\\$&'); // Escapes % and _ with backslash
}
 
// Usage:
const searchTerm = 'john%doe';
const normalizedTerm = `%${escapeLikeWildcards(searchTerm.toLowerCase())}%`;

Performance Considerations#

• Indexing: Use indexes to speed up searches. For LOWER(name) LIKE '%term%', create a functional index:

  -- PostgreSQL
  CREATE INDEX idx_name_lower ON "Users" (LOWER(name));
   
  -- MySQL
  CREATE INDEX idx_name_lower ON Users (LOWER(name));

• PostgreSQL citext: For frequent case-insensitive searches, use the citext (case-insensitive text) type for the column. This allows using ILIKE with standard indexes.

Empty Search Term#

Avoid returning all records if searchTerm is empty by adding a guard clause:

if (!searchTerm.trim()) {
  throw new Error('Search term cannot be empty');
}

Conclusion#

The "operator does not exist" error when using Op.contains in Sequelize is a common pitfall caused by mistaking array containment for string "contains" logic. To fix it:

• Use Op.iLike for PostgreSQL.
• Use LOWER() with Op.like for cross-database compatibility.
• Leverage MySQL’s case-insensitive collations or SQLite’s COLLATE NOCASE.

By choosing the right approach for your database and handling edge cases like escaping and indexing, you’ll implement robust, case-insensitive "contains" searches in Sequelize.

References#

• Sequelize Operators Documentation
• PostgreSQL ILIKE Operator
• MySQL Collation Documentation
• SQLite LIKE Behavior