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

Handling complex queries in Sequelize can get messy — especially when you're dealing with:

• Multiple relationships


• One-to-many joins


• Dynamic search & sorting


• Pagination with accurate counts

If you’ve ever seen:

❌ Duplicate records

❌ Broken pagination

❌ Wrong counts in findAndCountAll

You're not alone.

Let’s break down the problem — and then fix it using modern, production-grade techniques.

🧠 The Problem

Imagine this structure:

• 
  Orders (main entity)


• 
  OrderItems (one-to-many)


• 
  Products (linked to OrderItems)


• 
  Customers (belongs to Orders)

⚠️ Common Challenges

• 🔍 Searching across related tables (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
  

👉 While this works, it has issues:

• ⚠️ SQL injection risk (string interpolation)


• 🐌 Performance issues (multiple queries with separate)


• 😵 Hard to maintain


• ❌ Not database-agnostic

✅ Modern Approach (Recommended)

Instead of relying heavily on literals and separate, we use:

🔑 Key Improvements

• ✅ Scoped includes with required joins
  


• ✅ Safe replacements (no string interpolation)
  


• ✅ distinct with proper grouping
  


• ✅ Subqueries ONLY where necessary
  


• ✅ Cursor-based pagination (optional upgrade)
  

🛠️ Improved Implementation

```js id="modern-sequelize-query"

const { Op, fn, col, where, literal } = require('sequelize');

const { offset = 0, limit = 10 } = req;

let { search, sortBy, sortOrder = 'ASC' } = req.query;

// Base query

const queryOptions = {

limit,

offset,

distinct: true,

col: 'Orders.id', // ensures correct count

include: [

{

model: Customers,

as: 'customer',

attributes: ['id', 'email', 'firstName', 'lastName'],

required: true,

},

{

model: OrderItems,

as: 'orderItems',

attributes: [],

required: false,

include: [

{

model: Products,

as: 'product',

attributes: [],

required: false,

},

],

},

],

where: {},

};

// 🔍 SAFE SEARCH

if (search) {

const searchValue = %${search.trim()}%;

queryOptions.where[Op.or] = [

where(

fn('concat', col('customer.firstName'), ' ', col('customer.lastName')),

{ [Op.like]: searchValue }

),

where(col('orderItems.product.category'), {

[Op.like]: searchValue,

}),

];

}

// 📊 SORTING (No raw injection)

switch (sortBy) {

case 'customer':

queryOptions.order = [

[{ model: Customers, as: 'customer' }, 'firstName', sortOrder],

[{ model: Customers, as: 'customer' }, 'lastName', sortOrder],

];

break;

case 'category':

queryOptions.order = [

[{ model: OrderItems, as: 'orderItems' },

{ model: Products, as: 'product' },

'category',

sortOrder],

];

break;

default:

queryOptions.order = [['id', 'ASC']];

}

// 🚀 Execute

const result = await Orders.findAndCountAll(queryOptions);

---



## 🔥 Why This Is Better



### ✅ No SQL Injection Risk



We replaced:







```js

sequelize.literal(`... ${searchValue}`)

with:

Op.like / fn / col

✅ Better Performance

• Avoids separate: true (which runs multiple queries)


• Uses optimized joins instead

✅ Accurate Pagination

• 
  distinct: true + col ensures correct counts


• No duplication from joins

✅ Cleaner & Maintainable

• Uses Sequelize-native APIs


• Easier to debug and extend

⚡ When Should You STILL Use separate: true?

Use it ONLY when:

• You need nested pagination
  


• You have very large child datasets
  


• You want to lazy-load associations
  

🚀 Bonus: Even Better (Cursor Pagination)

Offset pagination can slow down on large datasets.

👉 Consider switching to:

• Cursor-based pagination (createdAt, id)


• OR indexed pagination

📌 Key Takeaways

✅ Avoid raw SQL when possible

✅ Use fn, col, and where for safe queries

✅ Use distinct properly for pagination

✅ Avoid separate: true unless necessary

✅ Prefer structured joins over subqueries

🎯 Final Thoughts

Sequelize is powerful — but only if you use it the right way.

The old approach works…

👉 The modern approach scales.

💬 Have you faced Sequelize performance issues in production?

Let’s discuss your approach below!