sequelize-restful-helper
v1.0.4
Published
This library helps you to build [Sequelize ORM](http://sequelizejs.com/) queries using http query parameters. It is useful for building APIs.
Downloads
33
Keywords
Readme
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
).