Query Handler

A Node.js module for handling Sequelize query objects from Express request bodies or query parameters, with support for dynamic model resolution, nested includes, and Sequelize operators.

Features

• Dynamic Query Parsing: Parses query objects from Express request body or query parameters, supporting attributes, where clauses, ordering, and pagination.
• Sequelize Operators: Supports Sequelize operators (e.g., Op.like, Op.gte) in where clauses, both at the top level and within include clauses.
• Model Resolution: Resolves Sequelize model names to model objects using a factory function, eliminating the need to pass models repeatedly.
• Nested Includes: Supports arbitrary levels of nested include clauses for Sequelize associations, including where clauses with operators.
• Robust Error Handling: Validates inputs, handles JSON parsing errors, and validates Sequelize operators.
• Unit Tests: Includes comprehensive Jest tests for reliability, covering operator usage and nested includes.

Installation

1. Ensure you have Node.js installed (version 12 or higher recommended).
2. Install the required dependency:

   npm install lodash

3. Copy the queryHandler.js file into your project directory.
4. (Optional) For testing, install Jest:

   npm install --save-dev jest

Usage

1. Initialize the Module

The module exports a createHandleQuery function that creates a handleQuery function with your Sequelize models bound.

const { createHandleQuery } = require("sequelize-advanced-query");

// Define your Sequelize models
const models = [
  { key: "User", value: UserModel },
  { key: "Post", value: PostModel },
];

// Create handleQuery with models
const handleQuery = createHandleQuery(models);

2. Handle Queries

Use the handleQuery function in your Express routes to process query parameters or body data.

const express = require("express");
const app = express();

app.use(express.json()); // Enable JSON body parsing

app.get("/api/data", (req, res) => {
  try {
    const query = handleQuery(req);
    // Use query with Sequelize
    // e.g., Model.findAll(query)
    res.json(query);
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

app.listen(3000, () => console.log("Server running on port 3000"));

3. Query Format

The handleQuery function expects a query object in the request query._query (as a JSON string or object) or body._query. Example query:

{
  "attributes": ["id", "name"],
  "where": {
    "id": 1,
    "name": { "[Op.like]": "%John%" },
    "[Op.or]": [
      { "status": "active" },
      { "role": "admin" }
    ]
  },
  "order": [["createdAt", "DESC"]],
  "include": [
    {
      "model": "User",
      "where": { "email": { "[Op.like]": "%@example.com" } },
      "include": [
        {
          "model": "Post",
          "where": { "published": true }
        }
      ]
    }
  ]
}

• Pagination: Add page and pageSize as query parameters (e.g., /api/data?page=2&pageSize=5).
• Nested Includes: Supports arbitrary levels of nested include clauses, with model names resolved to Sequelize model objects.
• Sequelize Operators: Supports Sequelize operators in where clauses (both top-level and within include). Use the format [Op.<operator>] (e.g., [Op.like], [Op.gte], [Op.or]) to specify operators. For example:
  • name: { "[Op.like]": "%John%" } translates to name: { [Op.like]: "%John%" }.
  • [Op.or]: [{ status: "active" }, { role: "admin" }] translates to [Op.or]: [{ status: "active" }, { role: "admin" }]

Operator Examples

• Top-level Where Clause:

{
  "where": {
    "name": { "[Op.like]": "%John%" },
    "age": { "[Op.gte]": 18 },
    "[Op.and]": [
      { "status": "active" },
      { "role": { "[Op.eq]": "user" } }
    ]
  }
}

This translates to a Sequelize query with Op.like, Op.gte, and Op.and.

• Where Clause in Include:

{
  "include": [
    {
      "model": "Profile",
      "where": {
        "city": { "[Op.in]": ["New York", "London"] },
        "[Op.or]": [
          { "verified": true },
          { "score": { "[Op.gt]": 100 } }
        ]
      }
    }
  ]
}

This applies Op.in, Op.or, and Op.gt to the where clause of the Profile model include.

API

createHandleQuery(models)

• Parameters:
  • models: Array of objects with key (model name) and value (Sequelize model).
• Returns: A handleQuery function with models bound.
• Throws: Error if models is not an array.

handleQuery(req) (Returned by createHandleQuery)

• Parameters:
  • req: Express request object with query._query or body._query.
• Returns: A Sequelize-compatible query object with attributes, where, order, include, limit, and offset.
• Throws: Errors for invalid req, malformed JSON, or missing models.

Testing

The module includes a Jest test file (queryHandler.test.js) to verify functionality.

1. Ensure Jest is installed:

   npm install --save-dev jest

2. Add a test script to package.json:

   {
     "scripts": {
       "test": "jest"
     }
   }

3. Run tests:

   npm test

The test file covers:

• Query parsing, pagination, and nested includes.
• Error handling for invalid inputs, malformed JSON, and missing models.

Example

const { createHandleQuery } = require("sequelize-advanced-query");

// Initialize with models
const handleQuery = createHandleQuery([
  { key: "User", value: UserModel },
  { key: "Post", value: PostModel },
]);

// Example Express route
app.get("/api/users", async (req, res) => {
  try {
    const query = handleQuery(req);
    const users = await UserModel.findAll(query);
    res.json(users);
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

Send a request:

curl "http://localhost:3000/api/users?_query={\"attributes\":[\"id\",\"name\"],\"where\":{\"name\":{\"[Op.like]\":\"%John%\"}},\"include\":[{\"model\":\"Post\",\"where\":{\"published\":{\"[Op.eq]\":true}}}],\"page\":2,\"pageSize\":10}"

Dependencies

• Lodash for deep merging of query objects.
• Express (assumed for request handling).
• Sequelize (assumed for model definitions).
• Jest (optional, for running tests).

Notes

• Ensure models are initialized with createHandleQuery before calling handleQuery.
• The module uses a closure to bind models, making it thread-safe and testable.
• For TypeScript projects, consider adding type definitions for better IDE support.

License

MIT License