This library helps you to build [Sequelize ORM](http://sequelizejs.com/) queries using http query parameters. It is useful for building APIs.
npm install sequelize-restful-helperThis library helps you to build Sequelize ORM queries using http query parameters.
It is useful for building APIs.
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.
``
npm install --save sequelize-restful-helper
- 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 ...)
`javascript
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!')
})
`
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 () 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)
Only known filters (allowedFilters or known by a operator) will be picked.
`js
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.
`js
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
Query object (input) is:
`json`
{"id": ">1"}
The result of the restfulHelper.getSequelizeOptions() will be the following:
`json`
{"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
`js
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
`json`
{"id": ">1"}
The result of the restfulHelper.getSequelizeOptions() will be the following:
`json`
{"where": {"id": ">1"}}
#### How to use operators
The following example shows how to use only $gt and identity ($eq) operators:
`js
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.">": ">1"
Also it doesn't support guessing by value passed in (like $gt operator - work only when a value that starts with special
characters 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:
`json`
{
"$or": {
"id": {
"$like": "%some text%"
},
"name": {
"$like": "%some text%"
},
"other": {
"$like": "%some text%"
}
}
}
It works for a single query parameter ("search" by default)
##### SearchableQueryBuilder
`js
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'.'user_id'
If query parameter is passed, it filters user_id field on the basis of its content (look at ContainsOperator`).