Optimizing Complex Sequelize Queries: Search, Sorting & Pagination Done Right

Source: Dev.to

The problem

• Searching across related tables (e.g., customer + product)
• Sorting on nested relationships
• Pagination breaking due to JOIN duplication
• Incorrect counts from findAndCountAll

Traditional Approach (What Most People Do)

Typical fixes include:

• subQuery: false
• separate: true
• Raw SQL via sequelize.literal

Why this is problematic

• SQL injection risk (string interpolation)
• Performance issues (multiple queries with separate)
• Hard to maintain
• Not database‑agnostic

Improved Implementation

// modern-sequelize-query
const { offset = 0, limit = 10 } = req;

// Base query
// 🔍 SAFE SEARCH
const safeSearch = `%${search.trim()}%`;
queryOptions.where[Op.or] = [
  // add your search conditions here, using Op.like, fn, col, etc.
];

// 📊 SORTING (no raw injection)
// example:
switch (sortField) {
  case 'category':
    // add sorting logic
    break;
  default:
    // default sorting
    break;
}

// 🚀 Execute the query
const result = await Model.findAndCountAll({
  ...queryOptions,
  offset,
  limit,
  distinct: true, // ensures correct counts
});

Key improvements

• Scoped includes with required joins
• Safe replacements (no string interpolation)
• distinct with proper grouping
• Subqueries only where necessary
• Optional cursor‑based pagination for large datasets

Why This Is Better

✅ No SQL Injection Risk

Replaced raw sequelize.literal(\… ${searchValue}`)with Sequelize’s native operators (Op.like,fn,col`).

✅ Accurate Counts

Using distinct: true (or appropriate column) prevents duplication from joins.

✅ Better Performance

• Avoids separate: true unless truly needed.
• Executes optimized joins in a single query.

✅ Maintainable & Database‑Agnostic

All logic stays within Sequelize’s API, making the code easier to debug, extend, and portable across DB engines.

When to use separate: true

• You need nested pagination for very large child datasets.
• You want to lazy‑load associations.

Bonus: Even Better (Cursor Pagination)

Offset pagination can degrade performance on large tables. Consider switching to:

• Cursor‑based pagination (e.g., using createdAt or id as the cursor)
• Indexed pagination (leveraging indexed columns for fast look‑ups)

Best practices

• Avoid raw SQL whenever possible.
• Use fn, col, and where for safe queries.
• Apply distinct correctly for pagination.
• Use separate: true only when necessary.

Sequelize is powerful—but only when used correctly.

Have you faced Sequelize performance issues in production? Share your experience below!