Sequelize Filter Middleware

A helper function for dynamically building Sequelize find options (including where, include, order, and group) from a JSON request body. This package allows you to easily create flexible API endpoints that enable clients to filter and shape the data they receive.

Installation

npm install sequelize-filter-middleware

Make sure you have sequelize installed in your project as a peer dependency:

npm install sequelize

Usage

1. Import the Helper: In your route handler or service file, import the buildFindOptions function:

const buildFindOptions = require("sequelize-filter-middleware");
const { sequelize } = require("./models"); // Your Sequelize instance
const { Product, Category, User, UserProfile } = require("./models"); // Your Sequelize models

2. Use in Your Route Handler: In your Express (or similar framework) route, call buildFindOptions with your Sequelize instance and the req.body. Pass the result directly to your Sequelize model's findAll() method:

const express = require("express");
const app = express();
app.use(express.json());

app.get("/products/filter", async (req, res) => {
  try {
    const findOptions = buildFindOptions(sequelize, req.body);
    const products = await Product.findAll(findOptions);
    res.json(products);
  } catch (error) {
    console.error("Error fetching products:", error);
    res.status(500).json({ error: "Failed to fetch products" });
  }
});

// Example for another model
app.get("/users/filter", async (req, res) => {
  try {
    const findOptions = buildFindOptions(sequelize, req.body);
    const users = await User.findAll(findOptions);
    res.json(users);
  } catch (error) {
    console.error("Error fetching users:", error);
    res.status(500).json({ error: "Failed to fetch users" });
  }
});

// ... your server setup

Request Body Structure

The req.body should be a JSON object that can contain the following properties:

• where: where: An object defining the filtering conditions.
• include: An array specifying the associated models to include.
• order: An array defining the sorting order.
• group: An array of attributes to group the results by.

where Clause

The where property is an object where keys correspond to the attributes of your Sequelize model. The values can be:

• Direct Values: For simple equality checks (e.g., { category: "Electronics" }).
• Operator Objects: To use Sequelize operators (e.g., { price: { "$gt": 50 } }).

Sequelize Operators

To use Sequelize operators, the value of an attribute in the where clause should be an object where the keys are Sequelize operator symbols (prefixed with $). The sequelize library exports these operators under Sequelize.Op (or you can import { Op } from 'sequelize';).

Common Operators:

• $ne: Not equal to (e.g., { category: { "$ne": "Books" } })
• $eq: Equal to (e.g., { price: { "$eq": 100 } })
• $gt: Greater than (e.g., { quantity: { "$gt": 5 } })
• $gte: Greater than or equal to (e.g., { rating: { "$gte": 4 } })
• $lt: Less than (e.g., { discount: { "$lt": 0.1 } })
• $lte: Less than or equal to (e.g., { stock: { "$lte": 10 } })
• $like: Case-sensitive like (e.g., { name: { "$like": "Smart%" } })
• $iLike: Case-insensitive like (e.g., { email: { "$iLike": "%@example.com" } })
• $notLike: Case-sensitive not like (e.g., { description: { "$notLike": "%out of stock%" } })
• $notILike: Case-insensitive not like (e.g., { city: { "$notILike": "paris" } })
• $startsWith: Starts with (e.g., { model: { "$startsWith": "ABC" } })
• $endsWith: Ends with (e.g., { filename: { "$endsWith": ".jpg" } })
• $substring: Contains (e.g., { address: { "$substring": "Main St" } })
• $in: In an array of values (e.g., { color: { "$in": ["red", "blue", "green"] } })
• $notIn: Not in an array of values (e.g., { status: { "$notIn": ["pending", "cancelled"] } })
• $isNull: Is null (e.g., { deletedAt: { "$isNull": true } })
• $notNull: Is not null (e.g., { updatedAt: { "$notNull": true } })
• $between: Between two values (inclusive) (e.g., { createdAt: { "$between": ["2024-01-01", "2024-01-31"] } })
• $notBetween: Not between two values (e.g., { price: { "$notBetween": [10, 100] } })
• $or: Logical OR (takes an array of where clauses) (e.g., { "$or": [{ category: "Electronics" }, { brand: "XYZ" }] })
• $and: Logical AND (takes an array of where clauses) (e.g., { "$and": [{ price: { "$gt": 50 } }, { rating: { "$gte": 4 } }] })

Example where Clause in Request Body:

{
  "where": {
    "category": "Electronics",
    "price": {
      "$gte": 50,
      "$lte": 200
    },
    "name": {
      "$like": "%smart%"
    },
    "$or": [{ "brand": "Samsung" }, { "brand": "Apple" }]
  }
}

include Clause

The include property is an array that specifies the associated models to be included in the query result. Each element in the array can be:

• A string: The name (alias) of the associated model (assuming your associations are set up correctly and the alias matches the model name).
• An object: To specify more options for the inclusion, such as:
  • model: The Sequelize model to include (obtained from your sequelize.models).
  • as: The alias for the association.
  • where: An object to filter the included model's records.
  • attributes: An array of attributes to select from the included model.
  • include: A nested array of include options for deeply nested associations.

Example include Clause in Request Body:

{
  "include": [
    "Category", // String alias
    {
      "model": "User",
      "as": "seller",
      "attributes": ["id", "username"]
    },
    {
      "model": "UserProfile",
      "as": "profile",
      "where": {
        "isVerified": true
      },
      "include": [
        {
          "model": "Address",
          "as": "addressInfo",
          "attributes": ["city", "country"]
        }
      ]
    }
  ]
}

order Clause

The order property is an array that specifies how the results should be sorted. Each element can be:

• A string: The attribute to order by (defaults to ascending order).
• An array: With two elements: the attribute to order by and the direction ("ASC" or "DESC").

Example order Clause in Request Body:

{
  "order": [
    ["price", "DESC"],
    "name" // Defaults to ASC
  ]
}

group Clause

The group property is an array of attribute names to group the results by (useful for aggregate functions).

Example group Clause in Request Body:

{
  "group": ["category"]
}

Security Considerations

Be cautious when directly using user-provided input in your database queries. While Sequelize helps prevent SQL injection through its parameterized queries, it's crucial to:

• Validate and sanitize user input: Ensure the data types and values in the req.body are as expected. Whitelist allowed filterable fields and operators: Consider only allowing filtering on specific attributes and a predefined set of operators to prevent unexpected or malicious queries. Be mindful of the models and attributes you allow in the include clause: Avoid exposing sensitive or internal data structures.

Contributing

Contributions are welcome! Please feel free to submit issues and pull requests.

License