How to Search Multiple Columns in Sequelize: Title and Description Query Example

In modern web applications, implementing a robust search feature is often critical for user experience. Whether you’re building a blog, e-commerce platform, or content management system, users expect to search across multiple fields (e.g., title, description, tags) to find relevant content.

Sequelize, the popular Node.js ORM (Object-Relational Mapper), simplifies database interactions by abstracting SQL into JavaScript. However, searching across multiple columns (e.g., title and description) requires a bit of setup. In this guide, we’ll walk through step-by-step how to implement multi-column search in Sequelize, with a focus on querying title and description fields. We’ll cover basic searches, advanced scenarios like case insensitivity, and edge cases to ensure your search works reliably.

Table of Contents#

  1. Prerequisites
  2. Setting Up the Project
  3. Defining the Model
  4. Basic Single Column Search
  5. Searching Multiple Columns (Title + Description)
  6. Advanced Scenarios
  7. Handling Edge Cases
  8. Conclusion
  9. References

Prerequisites#

Before diving in, ensure you have the following:

  • Node.js (v14+ recommended) and npm installed.
  • A basic understanding of Sequelize (e.g., model definition, querying).
  • A database (PostgreSQL, MySQL, SQLite, or MSSQL) set up. We’ll use PostgreSQL for examples, but the concepts apply to other databases with minor adjustments.

Setting Up the Project#

If you’re starting from scratch, follow these steps to set up a Sequelize project:

Step 1: Initialize a Node.js Project#

mkdir sequelize-multi-column-search
cd sequelize-multi-column-search
npm init -y

Step 2: Install Dependencies#

Install Sequelize and a database driver (we’ll use pg and pg-hstore for PostgreSQL):

npm install sequelize pg pg-hstore

Step 3: Initialize Sequelize#

Run Sequelize’s CLI to generate configuration files (if you don’t have the CLI, install it first with npm install -g sequelize-cli):

sequelize-cli init

This creates a config, models, migrations, and seeders folder. Update config/config.json with your database credentials.

Defining the Model#

We’ll use a Post model with title (string) and description (text) fields, as these are common columns to search across.

Step 1: Generate the Model#

Run the following to generate a Post model with title and description:

sequelize-cli model:generate --name Post --attributes title:string,description:text

Step 2: Review the Model File#

Sequelize will create models/post.js. Open it and ensure it looks like this:

'use strict';
const { Model } = require('sequelize');
 
module.exports = (sequelize, DataTypes) => {
  class Post extends Model {
    static associate(models) {
      // Define associations here (if needed)
    }
  }
  Post.init({
    title: {
      type: DataTypes.STRING,
      allowNull: false
    },
    description: {
      type: DataTypes.TEXT,
      allowNull: true
    }
  }, {
    sequelize,
    modelName: 'Post',
  });
  return Post;
};

Step 3: Run the Migration#

Execute the migration to create the Posts table in your database:

sequelize-cli db:migrate

Before searching multiple columns, let’s recap how to search a single column (e.g., title). This will help contextualize the multi-column approach.

Example: Search title for a Term#

To find posts where the title contains the search term (e.g., "guide"), use Sequelize’s Op.like operator with wildcards (%) for partial matches:

const { Op } = require('sequelize');
const Post = require('../models/post');
 
async function searchPostsByTitle(searchTerm) {
  const posts = await Post.findAll({
    where: {
      title: {
        [Op.like]: `%${searchTerm}%` // Matches any title containing "searchTerm"
      }
    }
  });
  return posts;
}
 
// Usage: searchPostsByTitle("guide")
  • Op.like performs a case-sensitive search (depends on the database).
  • % is a wildcard: %term matches "term" at the end, term% at the start, and %term% anywhere in the string.

Searching Multiple Columns (Title + Description)#

To search either title or description for a term, use Op.or to combine conditions. This tells Sequelize to return rows where at least one of the conditions is true.

Here’s a query that searches both title and description for the search term:

async function searchPostsMultiColumn(searchTerm) {
  const posts = await Post.findAll({
    where: {
      [Op.or]: [ // Match EITHER title OR description
        {
          title: {
            [Op.like]: `%${searchTerm}%` // Title contains searchTerm
          }
        },
        {
          description: {
            [Op.like]: `%${searchTerm}%` // Description contains searchTerm
          }
        }
      ]
    }
  });
  return posts;
}
 
// Usage: searchPostsMultiColumn("tutorial")

How It Works:#

  • [Op.or] is an array of conditions. Sequelize returns rows where at least one condition is satisfied.
  • Each condition uses Op.like with %${searchTerm}% to allow partial matches.

Case Insensitivity#

Most users expect searches to be case-insensitive (e.g., "Guide" and "guide" should return the same results). Database support for case-insensitive search varies:

  • PostgreSQL: Use Op.iLike (case-insensitive LIKE).
  • MySQL: Op.like is case-insensitive for non-binary strings (depends on the collation), but Op.iLike is not supported.
  • SQLite: Op.like is case-insensitive by default for ASCII characters.

Cross-Database Case Insensitivity#

To ensure case insensitivity across all databases, use Sequelize.fn('LOWER', ...) to convert columns and the search term to lowercase:

async function searchPostsCaseInsensitive(searchTerm) {
  const lowerSearchTerm = searchTerm.toLowerCase(); // Convert term to lowercase
  const posts = await Post.findAll({
    where: {
      [Op.or]: [
        Sequelize.where(
          Sequelize.fn('LOWER', Sequelize.col('title')), // Lowercase title
          Op.like, 
          `%${lowerSearchTerm}%` // Lowercase term
        ),
        Sequelize.where(
          Sequelize.fn('LOWER', Sequelize.col('description')), // Lowercase description
          Op.like, 
          `%${lowerSearchTerm}%`
        )
      ]
    }
  });
  return posts;
}
  • Sequelize.fn('LOWER', Sequelize.col('title')) converts the title column to lowercase.
  • The search term is also lowercased, ensuring "Guide" and "guide" are treated as equal.

Advanced Scenarios#

1. Partial Matches with Wildcards#

We’ve already used % wildcards, but let’s clarify their behavior:

  • %term: Matches "term" at the end of the string (e.g., "learn term").
  • term%: Matches "term" at the start (e.g., "term guide").
  • %term%: Matches "term" anywhere (e.g., "my term here").

To enforce exact matches (no wildcards), omit %:

title: { [Op.like]: searchTerm } // Exact match (case-sensitive)

2. Extending to More Columns#

You can easily add more columns to the search by adding conditions to Op.or. For example, to include a tags column:

[Op.or]: [
  { title: { [Op.iLike]: `%${searchTerm}%` } },
  { description: { [Op.iLike]: `%${searchTerm}%` } },
  { tags: { [Op.iLike]: `%${searchTerm}%` } } // Add more columns here
]

3. Weighting Results#

If you want to prioritize results where the term appears in the title over the description, you’ll need to handle this in application code (Sequelize doesn’t support ranking natively). For example:

async function searchPostsWithWeight(searchTerm) {
  const posts = await searchPostsMultiColumn(searchTerm); // Use the multi-column function
  // Prioritize posts where title matches
  return posts.sort((a, b) => {
    const aTitleMatch = a.title.toLowerCase().includes(searchTerm.toLowerCase()) ? 1 : 0;
    const bTitleMatch = b.title.toLowerCase().includes(searchTerm.toLowerCase()) ? 1 : 0;
    return bTitleMatch - aTitleMatch; // Title matches first
  });
}

Handling Edge Cases#

1. Empty Search Term#

If searchTerm is empty, the query will return all posts (since % matches any string). To avoid this, add a guard clause:

async function safeSearchPosts(searchTerm) {
  if (!searchTerm || searchTerm.trim() === '') {
    return []; // Return empty array if term is empty
  }
  // Proceed with multi-column search
  return searchPostsMultiColumn(searchTerm.trim());
}

2. Input Sanitization#

Always sanitize user input to prevent unexpected behavior (e.g., SQL injection). Sequelize automatically escapes values when using operators like Op.like, but avoid concatenating raw user input into the query string.

Conclusion#

Searching multiple columns in Sequelize is straightforward with Op.or to combine conditions for title, description, or other fields. Key takeaways:

  • Use Op.or to search across multiple columns (e.g., title OR description).
  • Use Op.like (or Op.iLike for PostgreSQL) with % wildcards for partial matches.
  • For cross-database case insensitivity, use Sequelize.fn('LOWER', ...).
  • Handle edge cases like empty search terms and sanitize input.

This approach scales to any number of columns—simply add more conditions to the Op.or array!

References#

2026-06