sequelize-smart-query

A powerful, chainable query builder for Sequelize that makes it incredibly simple to handle complex filtering, pagination, sorting, and nested model relationships dynamically.

Features

• Chainable API: Elegantly build complex Sequelize queries using a readable, fluent interface.
• Dynamic Frontend Query Parsing: Designed specifically to convert frontend HTTP query strings into powerful Sequelize queries effortlessly.
• Auto Nested Includes: Automatically joins (includes) related models when querying or sorting by nested attributes (e.g., User.profile.age).
• Keyword Searching: Seamlessly perform full-text searches across multiple fields with one method.
• Dynamic Sorting & Pagination: Easy pagination (page, limit) and sorting, including over nested relationships.
• Flexible & Type-Safe: Built with TypeScript to provide intelligent autocompletion for Sequelize models.

Installation

npm install sequelize-smart-query sequelize

(Note: sequelize is a peer dependency.)

How It Solves Backend Query Parsing from the Frontend

Building an API that must support complex frontend requirements (like data tables with multi-column filtering, global search, nested object filtering, and custom sorting) usually takes hundreds of lines of boilerplate code in your controllers.

sequelize-smart-query directly bridges this gap. It provides a standardized structure where frontend query strings map flawlessly into your database queries.

The Problem

A standard advanced data-table frontend might send an HTTP GET request to your API like this:

GET /api/orders?
  page=1&
  limit=15&
  keyword=INV&
  sort[createdAt]=DESC&
  filters[status][eq]=paid&
  filters[customer.country][like]=US%&
  filters[amount][gte]=100

Normally, manually parsing filters[customer.country][like]=US% and converting it into a deeply joined Sequelize include array with nested where clauses requires repetitive and error-prone association mapping.

Note on Query String Parsing: To consume nested queries like filters[status][eq]=paid, your incoming request query must be parsed into a deeply nested JavaScript object. If you are using Express.js, this works out-of-the-box because Express uses the qs library internally. If you are using standard Node.js or another framework that doesn't natively support deep query parsing, you should install and use qs to manually parse the raw URL query string.

The Solution

Using sequelize-smart-query, your backend controller reduces to this:

app.get('/api/orders', async (req, res) => {
  const { page, limit, keyword, filters, sort } = req.query;

  const { rows, count } = await new QueryBuilder(Order)
    .where(filters)       // Triggers auto-includes based on dot-notation!
    .search(keyword, ['invoiceNumber', 'notes'])
    .sort(sort)           // Triggers auto-includes for nested sorting!
    .paginate({ page, limit })
    .findAndCount();

  res.json({ data: rows, total: count });
});

The Builder automatically:

1. Recognizes customer.country and adds a join (include) to the customer alias.
2. Applies the Op.iLike operator for US%.
3. Handles Op.eq and Op.gte for the other attributes.
4. Correctly sets up LIMIT, OFFSET, and ORDER.

Supported Filter Operators

The .where(filters) method supports the following operator mappings in its object syntax. Pass these operators as the nested key in your endpoint query map:

| Query syntax | Meaning | Sequelize Operator | | --- | --- | --- | | eq | Equals | Op.eq | | ne | Not Equals | Op.ne | | gt | Greater Than | Op.gt | | gte | Greater Than or Equal To | Op.gte | | lt | Less Than | Op.lt | | lte | Less Than or Equal To | Op.lte | | like | Case-sensitive Like (LIKE) | Op.like | | iLike | Case-insensitive Like (ILIKE) | Op.iLike | | in | In Array | Op.in | | notIn| Not In Array | Op.notIn | | between | Between values | Op.between |

Usage Examples

Basic Example

import { QueryBuilder } from 'sequelize-smart-query';
import { Post } from './models';

const query = new QueryBuilder(Post)
  .select(['id', 'title', 'content'])
  .where({
    status: { eq: 'published' },
    'author.email': { like: '%@gmail.com' } // Automatically includes 'author'
  })
  .search('urgent update', ['title', 'content'])
  .sort({ createdAt: 'DESC' })
  .paginate({ page: 1, limit: 10 });

const { rows, count } = await query.findAndCount();

More Examples

Check out the examples/ directory in this repository to see more use-cases! The examples are isolated in their own environment to prevent cluttering the main project.

To run the examples locally:

# Navigate to the examples directory
cd examples

# Install the example-specific dependencies (express, sqlite3, etc.)
npm install

# Run the basic usage example
npm run basic

# Run the Express.js example server
npm run express

• examples/basic-example.ts: Simple usage, data seeding, and setup.
• examples/express-example.ts: How to integrate cleanly with Express.js to parse request queries directly.
• examples/models.ts: The mocked Sequelize instance and model definitions used in the examples.

API Reference

• select(attributes: string[]): Define specific fields to return.
• addSelect(field: any, alias?: string): Dynamically append a field (or sequelize.fn) to the selection.
• where(filters: Record<string, any>): Apply filters. Supports dot notation for nested models.
• search(keyword: string, fields: string[], operator?: symbol): Applies an OR search using Op.iLike (by default) across listed fields. The operator can be overridden to Op.like or others (e.g., injecting Op.like is especially useful when using SQLite, as it doesn't support Op.iLike).
• sort(sort: Record<string, "ASC" | "DESC">): Applies ordering. Supports dot notation for sorting by association field.
• paginate(options: { page?: number, limit?: number }): Automates calculations for offset and sets limit.
• distinct(): Adds distinct: true to the query options.
• groupBy(fields: string[]): Applies grouping.
• include(includes: Includeable[]): Manually append Sequelize includes if the automatic inclusion is not enough.
• withDeleted(): Disables paranoid mode (paranoid: false), returning soft-deleted records.
• rawWhere(sql: string): Apply a raw SQL conditional string to the WHERE clause via Op.and.
• build(): FindOptions: Returns the raw Sequelize options object instead of executing it. Useful for debugging or custom executions.
• findAll(): Executes Model.findAll() under the hood.
• findAndCount(): Executes Model.findAndCountAll() under the hood.

License

ISC