sequelize-restful-helper

RESTful API helper for Sequelize ORM

This library helps you to build Sequelize ORM queries using http query parameters. It is useful for building APIs.

About

When starting to use API and filtration over DB data is needed, you strike with millions different API and formats. This library aims at using simple query format for data filtration, pagination and sorting, and has simple, yet powerful response format without imposing it. It has a set of predefined pagination properties (page size, page #) that can be overridden for you current API.

Install

npm install --save sequelize-restful-helper

Usage cases

• Transform URI query parameters to Sequelize where
• Transform URI query parameters to Sequelize order
• Transform URI query parameters to Sequelize where with automatic include (Inner Joins) generation
• Transform URI query parameters to Sequelize where with predefined operators
• Manual use Model.findAndCountAll method: it allows you to inject extra sequelize options and transform sequelize result
• Generate standard API response from prepared data (page size, page number, count, model items ...)

Example

const express        = require('express');
const Sequelize      = require('sequelize');
const RestfulHelper  = require('sequelize-restful-helper');
const sequelize      = new Sequelize('database', 'username', 'password');
const app            = express();
 
const Product = sequelize.define('products', {/* ... */});
 
app.get('/products', function (req, res, next) {
    const restfulHelper = new RestfulHelper(
        req.query, // give the request query object
        {
            allowedFilters: ['id', 'name', 'is_active'], // white list.
            allowedOrder: ['id'], // white list
        }
    );

    return Product.findAndCountAll(restfulHelper.getSequelizeOptions())
        .then(products => {
            const formattedResponse = restfulHelper.getPaginator()
                .getRes(products.count, products.rows); 
            return res.json(formattedResponse);
        })
        .catch(next);
});

app.listen(3000, function () {
  console.log('Example app listening on port 3000!')
})

Usage recommendations

RESTful API helper for Sequelize ORM is designed to be agile and it can be tuned easily. Just usage of configuration parameters give you a power to change format, pagination details. Passing processed input (not a req.query but some processed or transformed object) allows you to use your own data input, which is not limited to query parameters.
Also you can transform your output (products.rows.map(product => product.render()) before returning it to the http response. But the recommended way is to use it like middleware. And you should always stay with the same API format (default one is quite pretty). Yous shouldn't trying to use the library everywhere.

For example, if you want to build a query with you own logic where one part of this logic is fully covered by restfulHelper and the other part is not (and actually can't be covered), then it is not recommended to mix these approaches.

Simple illustration of use case

| request | -> | restfulHelper | -> | sequelize | -> | response |

Complex illustration

| request | -> | query converter | -> | restfulHelper | -> | sequelize | -> | response converter | -> | response |

query converter - it should choose data input (req.body, or process legacy format for the BC), but shouldn't filter and convert parameters (renaming, datatypes, parsing)

response converter - is used to transform your data according to your API format.

Overcomplex illustration

| request | -> | query converter/validator/mapper | -> | restfulHelper | -> | sequelize query modification | -> | sequelize scope management | -> | sequelize | -> | response converter | -> | response |

You should avoid such situations, especially when you are trying to do the same as restfulHelper is used to: map query parameter when it is passed into where field or other use cases (see Usage cases section)

Details

Filter user input

Only known filters (allowedFilters or known by a operator) will be picked.

Map query to where - kind of renaming

    const restfulHelper = new RestfulHelper(req.query, {
        allowedFilters: ['id', 'name', 'is_active'],
        allowedOrder: ['id'],

        filterAliases: {
            is_active: 'is_visible_for_customer', // req.query.is_active -> where: {is_visible_for_customer: ...}
        },
    });

As the result, API parameter is_active will be mapped to the database field called is_visible_for_customer.

Map query to where inside include associated model (another sequelize entity)

    const Product = sequelize.define('products', {/* ... */});
    const Application = sequelize.define('application', {/* ... */});
    const Users = sequelize.define('users', {/* ... */});
    /*
    setup associations
    product - application - user
    */

    const restfulHelper = new RestfulHelper(req.query, {
        allowedFilters: ['id', 'name', 'is_active', 'user_id'],
        allowedOrder: ['id'],

        model: Product,
        filterAliases: {'user_id': 'application.users.id'},
    });

As the result, API parameter user_id will be mapped to the id field of users table for the associated application entity. You can build chains of any length you want (product - customer - ... - last entity in the chain).

Before starting with the implementation, you must setup model associations

Operators

About

Query object (input) is:

{"id": ">1"}

The result of the restfulHelper.getSequelizeOptions() will be the following:

{"where": {"id": {"$gt": 1}}}

So you can transform input and build where object. The $gt is a built-in operator.

How not to use operators

const SequelizeQueryBuilder = require('sequelize-restful-helper/src/SequelizeQueryBuilder');

const restfulHelper = new SequelizeQueryBuilder(req.query, {
    allowedFilters: ['id', 'name', 'is_active', 'user_id'],
    allowedOrder: ['id'],
    // model: Product,
    // ...
});

In this example, the restfulHelper will just use identity operator - map query parameter as is, without any transformation

{"id": ">1"}

The result of the restfulHelper.getSequelizeOptions() will be the following:

{"where": {"id": ">1"}}

How to use operators

The following example shows how to use only $gt and identity ($eq) operators:

const SequelizeQueryBuilder = require('sequelize-restful-helper/src/SequelizeQueryBuilder');

const restfulHelper = new SequelizeQueryBuilder(req.query, {
    allowedFilters: ['id', 'name', 'is_active', 'user_id'],
    allowedOrder: ['id'],

    operators: [
        SequelizeQueryBuilder.operators.operatorsBySequelizeName.$gt,
        SequelizeQueryBuilder.operators.operatorsBySequelizeName.$eq,
    ]
});

It is highly recommended to use only operators you need

How to search

Operators for the search

Search can be performed:

• by using the following two operators
  • ContainsOperator
  • SearchByFieldsOperator
• by using require('sequelize-restful-helper/src/SearchableQueryBuilder');
• without SequelizeQueryBuilder / RestfulHelper

SearchableQueryBuilder is just the preconfigured SequelizeQueryBuilder with all default operators and with ContainsOperator, SearchByFieldsOperator

ContainsOperator

It is an operator that wraps query parameter into percent signs ("%original value%") and builds where with $like. Also it doesn't support guessing by value passed in (like $gt operator - work only when a value that starts with special characters ">": ">1" is passed), so it just accepts an array of keys, that should be processed by the operator: onlyForFields. It can be called strict search - search by known field.

SearchByFieldsOperator

It is an operator that wraps query parameter into percent signs ("%some text%") and builds where that searches over static field set (without included entities).

The following is the example of where:

{
  "$or": {
    "id": {
      "$like": "%some text%"
    },
    "name": {
      "$like": "%some text%"
    },
    "other": {
      "$like": "%some text%"
    }
  }
}

It works for a single query parameter ("search" by default)

SearchableQueryBuilder

const SearchableQueryBuilder = require('sequelize-restful-helper/src/SearchableQueryBuilder');

const restfulHelper = new SearchableQueryBuilder(req.query, {
    allowedFilters: ['id', 'name', 'is_active', 'user_id'],
    allowedOrder: ['id'],

    searchFields: ['id', 'name', 'other'],
    strictSearchFields: ['user_id'],
    // model: Product,
    // ...
});

It performs a search (look at SearchByFieldsOperator) by 'id', 'name', 'other'. If 'user_id' query parameter is passed, it filters user_id field on the basis of its content (look at ContainsOperator).