tmukammel / sequelize-query-parser

Simple, powerful, minimalist package to turn your Node.js with Sequelize RESTful APIs into Graph APIs.

Sequelize Query Parser

Simple, powerful, minimalist package to turn your Node.js with Sequelize RESTful APIs into Graph APIs.

// Pass `db` connection object which has `Sequelize.Op`
const queryParser = require("sequelize-query")(db);

getUsers = async req => {
    // Parse your API query
    let query = await queryParser.parse(req);

    // Pass to Sequelize model find...() functions
    return User.findAndCountAll(query);
};

Features and Syntax with examples

• Request specific set of fields instead of fetching all the table columns

// fields=field01,field02...

// E.g: Get only id, name and email
?fields=id,name,email

• GET collection request APIs pagination support by default (without you writing code for that)

?offset=0&limit=10
// NOTE: Max page size limit is set to 200

• GET collection request APIs sorting and groupping by default.

Sorting

// Sort by id in ascending order
?sort_by=id.asc

// Sort by name in descending order
?sort_by=name.desc

Grouping

// Group by id
?group_by=id

• Simple filter query operations on the request objects

// Query format is
// filedName=unaryOperator:value

// Get all entries where firstName is like Reza or Rezaul, etc
?firstName=like:Reza%

// Get all entries where id is greater than 2 and lastName is like Reza%
?firstName=like:Reza%&id=gt:1

• Querying with sequelize JSON format

// Find where firstname is Twaha and id is 1 or firstName is Riajul and id is 2
?query={"or": [{"and": [{"firstName": "Twaha"}, {"id": 1}]}, {"and": [{"firstName": "Riajul"}, {"id": 2}]}]}
// NOTE: query maps your query object to `where=queryObj`

• Fetch and Query on relational objects (tables)

// Get only those whose role is 'admin'
?include={"model": "UserRole", "as": "userRole", "include" : {"model": "Role", "as" : "role", "required": 1, "where": {"name": "admin"} }}

Operators

Just use sequelize format query in the API requests
substituting Sequelize operators with below keys.

gt: Op.gt,                      // gt (greater than) for Op.gt
gte: Op.gte,
lt: Op.lt,
lte: Op.lte,
ne: Op.ne,
eq: Op.eq,
not: Op.not,
like: Op.like,                  // LIKE '%hat'
notLike: Op.notLike,            // NOT LIKE '%hat'
regexp: Op.regexp,              // REGEXP/~ '^[h|a|t]' (MySQL/PG only)
notRegexp: Op.notRegexp,        // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)
and: Op.and,                    // AND (a = 5)
or: Op.or,                      // (a = 5 OR a = 6)
between: Op.between,            // BETWEEN 6 AND 10
notBetween: Op.notBetween,      // NOT BETWEEN 11 AND 15
in: Op.in,                      // IN [1, 2]
notIn: Op.notIn,                // NOT IN [1, 2]

Installation

npm i sequelize-query

Contributing

I really appreciate your help in any case.
Fixes, Improvements, Enhancements, Documentation.

To contribute, please create a pull request to develop branch.

sequelize-query-parser's Issues

FEATURE REQUEST: Limit in include query

Is your feature request related to a problem? Please describe.
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

FEATURE REQUEST: Join Decomposition - query optimization

Is your feature request related to a problem? Please describe.
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

Escape chars in query - query parsing fails.

if we consider below query string component
query = {"or": [ {"firstName": {"like": "Twa%"} }, {"firstName": {"like": "Riaj%"} } ]}

this query string gets replaced with escape characters through the network request to below string

{%22or%22:%20[%20{%22firstName%22:%20%20{%22like%22:%20%22Twa%%22}%20},%20{%22firstName%22:%20%20{%22like%22:%20%22Riaj%%22}%20}%20]}

Need to unescape the escape characters.

FEATURE REQUEST: Auto add limit and offset

Is your feature request related to a problem? Please describe.
There is no max limit in a get request and it defaults to 10000000000000 which is very dangerous for any web server.

Describe the solution you'd like
There should be default max limit, that is added to the request even if it's absent in the original client request.