query-to-sequelize

** All credits goes to query-to-mongo **

Node.js package to convert query parameters into a sequelize query criteria and options

For example, a query such as: name=john&age>21&fields=name,age&sort=name,-age&offset=10&limit=10 becomes the following hash:

{
  criteria: { name: 'john', age: { [Op.gt]: 21 } },
  options: {
    fields: [ 'name', 'age' ],
    sort: [ [ 'name', 'ASC' ], [ 'age', 'DESC' ] ],
    skip: 10,
    limit: 10
  }
}

The resulting query object can be used as parameters for a sequelize query:

const q2s = require('query-to-sequelize')
const { Sequelize, DataTypes } = require('sequelize')

const sequelize = new Sequelize(PG_DB, PG_USER, PG_PASSWORD, {host: PG_HOST, port: PG_PORT, dialect: "postgres"})
await sequelize.authenticate()

const Model = sequelize.define("model", { name: {type: DataTypes: STRING}, age: {type: DataTypes.INTEGER}})

const {criteria, options} = q2s('name=john&age>13&limit=20&skip=40&sort=-name')

await Model.findAll({where: criteria, limit: options.limit, offset: options.skip, order: options.sort})

Comparision operators that are encoded into the value are also considered. For example, a query sucha as: name=john&age=%3E21 becomes the following hash:

{
  criteria: {
    name: 'john',
    age: { [Op.gt]: 21 }
  }
}

API

queryToSequelize(query, options)

Convert the query portion of a url to a sequelize query.

const queryToSequelize = require("query-to-sequelize")
const query = queryToSequelize("name=john&age>21&limit=10")
console.log(query)

{ criteria: { name: 'john', age: { [Op.gt]: 21 } },
  options: { limit: 10 },
  links: [Function] }

options:

• maxLimit The maximum limit (default is none)
• ignore List of criteria to ignore in addition to keywords used for query options ("fields", "omit", "sort", "offset", "limit")
• parser Query parser to use instead of querystring. Must implement parse(string) and stringify(obj).
• keywords Override the keywords used for query options ("fields", "omit", "sort", "skip", "limit"). For example: {fields:'$fields', omit:'$omit', sort:'$sort', offset:'$skip', limit:'$limit'}

returns:

• criteria Sequelize query criteria.
• options Sequelize query options.
• links Function to calculate relative links.

links(url, totalCount)

Calculate relative links given the base url and totalCount. Can be used to populate the express response links.

const q2s = require("query-to-sequelize")
const query = q2s("name=john&age>21&offset=20&limit=10")
console.log(query.links("http://localhost/api/v1/users", 100))

{ prev: 'http://localhost/api/v1/users?name=john&age%3E21=&offset=10&limit=10',
  first: 'http://localhost/api/v1/users?name=john&age%3E21=&offset=0&limit=10',
  next: 'http://localhost/api/v1/users?name=john&age%3E21=&offset=30&limit=10',
  last: 'http://localhost/api/v1/users?name=john&age%3E21=&offset=90&limit=10' }

Use

The module is intended for use by express routes, and so takes a parsed query as input:

const querystring = require('querystring')
const q2s = require('query-to-sequelize')
const query = 'name=john&age>21&fields=name,age&sort=name,-age&offset=10&limit=10'
const q = q2s(querystring.parse(query))

This makes it easy to use in an express route:

router.get('/api/v1/mycollection', function(req, res, next) {
  const q = q2s(res.query);
  ...
}

The format for arguments was inspired by item #7 in this article about best practices for RESTful APIs.

Field selection

The fields argument is a comma separated list of field names to include in the results. For example fields=name,age results in a option.fields value of [ 'name', 'age' ]. If no fields are specified then option.fields is null, returning full documents as results.

The omit argument is a comma separated list of field names to exclude in the results. For example omit=name,age results in a option.fields value of { exclude: [ 'name', 'age' ] }. If no fields are specified then option.fields is null, returning full documents as results.

Note that either fields or omit can be used. If both are specified then omit takes precedence and the fields entry is ignored.

Sorting

The sort argument is a comma separated list of fields to sort the results by. For example sort=name,-age results in a option.sort value of [ [ 'name', 'ASC' ], [ 'age', 'DESC' ] ]. If no sort is specified then option.sort is null and the results are not sorted.

Paging

The offset and limit arguments indicate the subset of the full results to return. By default, the full results are returned. If limit is set and the total count is obtained for the query criteria, pagination links can be generated:

  const { count, rows } = await Model.findAndCountAll({limit, offset})
  const links = q.links('http://localhost/api/v1/mycollection', count)

For example, if offset was 20, limit was 10, and count was 95, the following links would be generated:

{
   'prev': 'http://localhost/api/v1/mycollection?offset=10&limit=10',
   'first': `http://localhost/api/v1/mycollection?offset=0&limit=10`,
   'next': 'http://localhost/api/v1/mycollection?offset=30&limit=10',
   'last': 'http://localhost/api/v1/mycollection?offset=90&limit=10'
}

These pagination links can be used to populate the express response links.

Filtering

Any query parameters other then the keywords fields, omit, sort, offset, and limit are interpreted as query criteria. For example name=john&age>21 results in a criteria value of:

{ name: 'john', age: { [Op.gt]: 21 } }

• Supports standard comparison operations (=, !=, >, <, >=, <=).
• Numeric values, where Number(value) != NaN, are compared as numbers (ie., field=10 yields {field:10}).
• Values of true and false are compared as booleans (ie., {field:true})
• Values that are dates are compared as dates (except for YYYY which matches the number rule).
• Multiple equals comparisons are merged into a [Op.in] operator. For example, id=a&id=b yields {id: { [Op.in]: [ 'a', 'b' ] }}.
• Multiple not-equals comparisons are merged into a [Op.notIn] operator. For example, id!=a&id!=b yields {id:{[Op.notIn]: ['a','b']}}.
• Comma separated values in equals or not-equals yield an [Op.in] or [Op.notIn] operator. For example, id=a,b yields {id:{[Op.in]: ['a','b']}}.
• Regex patterns. For example, name=/^john/i yields {id: {[Op.regexp]: /^john/i}}.
• Parameters without a value check if the field is not null. For example, foo&bar=10 yields {foo: {[Op.not]: null}, bar: 10}.
• Parameters prefixed with a not (!) and without a value check that the field is not present. For example, !foo&bar=10 yields {foo: {[Op.is]: null}, bar: 10}.
• Support for forced string comparison; value in single or double quotes (field='10' or field="10") would force a string compare. Allows for string with embedded comma (field="a,b") and quotes (field="that's all folks").

A note on overriding keywords

You can adjust the keywords (fields, omit, sort, offset, and limit) by providing an alternate set as an option. For example:

altKeywords = {fields:'$fields', omit:'$omit', sort:'$sort', offset:'$offset', limit:'$limit'}
const q = q2m(res.query, {keywords: altKeywords});

This will then interpret the standard keywords as query parameters instead of options. For example a query of age>21&omit=false&$omit=a results in a criteria value of:

{
  'age': { [Op.gt]: 21 },
  'omit': false
}

and an option value of:

q.option = {
  fields: { a: false }
}

Development

There's a test script listed in package.json that will execute the mocha tests:

npm install
npm test

Creating a Release

1. Ensure all unit tests pass with npm test
2. Use npm version major|minor|patch to increment the version in package.json and tag the release
3. Push the tags git push origin master --tags
4. Publish the release npm publish ./

Major Release

npm version major
git push origin master --tags
npm publish ./

Minor Release (backwards compatible changes)

npm version minor
git push origin master --tags
npm publish ./

Patch Release (bug fix)

npm version patch
git push origin master --tags
npm publish ./