npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

query-to-sequelize

v0.1.1

Published

Convert query parameters into Sequelize query criteria and options.

Downloads

5

Readme

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 ./