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!