Skip to content

Small library to convert express query params (or objects in general) into a syntax recognised by the Sequelize library.

License

Notifications You must be signed in to change notification settings

stuartaharrison/sequelize-query-parser

Repository files navigation

Sequelize Qquery Parser

Accept query parameters in express or similar libraries and convert them into syntax easily recognised by the Sequelize Library. Useful for when building quick and simple API's for accepting varied user queries and searches.

Features

  • Handles basic operations to build queries from query parameters.
  • Basic pagination support to tag on offset and limit to your queries.
  • Basic & Multiple-Column Sorting support. Query and sort your results by 1-or-more columns.
  • Parses string input to integers, floats, booleans and dates.
  • Accepts non-string values (though from express it will likely come through as a string type anyway).
  • Allows specified DATETIME fields to be compared on the DATE-part only.
  • Blacklisting.
  • Aliasing.
  • Custom handlers for specific column/query-key names.

Operations

operation query string query object
equal ?foo=bar { foo: "bar" }
unequal ?foo=!bar { foo: { [Op.ne]: "bar" } }
between ?age=|20|30 { age: { [Op.gte]: 20, [Op.lte]: 30 }
is null ?foo= { foo: { [Op.is]: null } }
is not null ?foo=! { foo: { [Op.not]: null } }
greater than ?foo=>10 { foo: { [Op.gt]: 10 } }
greater than or equal to ?foo=>=10 { foo: { [Op.gte]: 10 } }
less than ?foo=<10 { foo: { [Op.lt]: 10 } }
less than or equal to ?foo=<=10 { foo: { [Op.lte]: 10 } }
starts with ?foo=^bar { foo: { [Op.startsWith]: "bar" } }
ends with ?foo=$bar { foo: { [Op.endsWith]: "bar" } }
contains ?foo=~bar { foo: { [Op.like]: '%bar%' } }
in array ?foo=$inbar|baz { foo: { [Op.in]: ['bar', 'baz'] }}
not in array ?foo=$nin!bar|baz { foo: { [Op.notIn]: ['bar', 'baz'] }}

You can see the configuration section to see how the operations can be configured

Note: I use [Op.gte] & [Op.lte] instead of [Op.between] to handle dates and other types better. I will investigate if the latter is a better option.

Aliasing & Blacklisting

By setting a column/property value into the Blacklist, you are telling the parser to simply ignore the key-value pair and not include it in the final output. With Aliasing, you can convert a specific incoming key-value pair to match the name of a known column. For example, converting incoming personsAge=12 to age=12. Both of these can be configured in the initialise options.

const sequelizeDateQS = new SequelizeQS({
    blacklist: ['createdAt'],
    alias: {
        'personsAge': 'age'
    }
});

Date Only

You can now easily (as of v1.1.0) have specific Date-type columns converted to compare on the Date only instead of including the Timestamp. DATEONLY fields in Sqlite are still working as they did before though!

To prevent breaking changes from prior versions and to also not rely on guess working with the code, you will need to specify what columns in your Sequelize model are actual Date columns you want to be handled in this way. You must also enable this feature by setting the dateOnlyCompare property in the options to true.

const sequelizeDateQS = new SequelizeQS({
    dateOnlyCompare: true,
    dateFields: ['createdAt', 'updatedAt', 'lastLogin']
});

Now when we compare on the lastLogin column, our query can come out like;

SELECT count(*) AS `count` FROM `customers` AS `customers` WHERE date(`lastLogin`) LIKE '2021-%';

By default, dateOnlyCompare will be false and the initial array value for dateFields will contain the original timestamp columns that sequelize typically adds to your models by default (createdAt & updatedAt).

Custom Handlers

A big improvement over the initial version is the ability to have the parser handle specific columns/query string properties in a specific way. This is configured through the initial options when setting up the parser.

Please note, that the value your custom function must return is one that would be interpreted by the Sequelize library. You can see an example below for more details;

const { Op } = require('sequelize');

const minimumAgeHandler = (column, value, options) => {
    return {
        'age': {
            [Op.gte]: value
        }
    }
};

const sequelizeDateQS = new SequelizeQS({
    customHandlers: {
        minAge: minimumAgeHandler
    }
});

Pagination

This library contains some basics for parsing pagination and setting default values. There is checks in place to ensure no negative pages are set and also that not too many records are pulled down. The default maximum page size is 100 but this can be configured in the options. You can also omit page and limit from the parameters and no paging will take place. Though, setting 1 or both will have an effect or enabling pagination.

Param name Default Value
page 1
limit 25

You can see the configuration section to see how to adjust defaults for parameters

Sorting

You can also configure your options to sort the results of your query by 1 or more columns.

operation query string query object
single column in asc ?sort=name order: [ [ 'name', 'ASC' ] ]
single column in desc ?sort=!name order: [ [ 'name', 'DESC' ] ]
multiple columns ?sort=age|name order: [ [ 'age', 'ASC' ], [ 'name', 'ASC' ] ]
multiple columns with desc ?sort=age|!name order: [ [ 'age', 'ASC' ], [ 'name', 'DESC' ] ]

Install

Please not that you will need Sequelize for this to work.

npm i --save sequelize
npm i --save sequelizeqp

How to Use

const SequelizeQS = require('sequelizeqp');
const sequelizeParser = SequelizeQS();

Configuration Options

You can pass additional configuration options into the constructor for the parser. This will change how the parser operations. As of Version 1.1, this have been a little more fleshed out. However, This is currently pretty experimental and does not have all the options available. You should be cautious with the order of operations otherwise you might get some unintended results!

property decription default
ops The available operations. ['$in', '$nin', '$', '!', '|', '^', '~', '>=', '>', '<=', '<']
alias The alias matching for properties and db columns. { }
blacklist The list of columns that should be ignored and not compared on [ ]
customHandlers List of available custom handlers for specific columns { }
dateOnlyCompare Tells the parser to convert recognised datetime fields to date-only for comparisons false
dateFields List of date columns in your model that are datetime type fields ['createdAt', 'updatedAt']
defaultPaginationLimit Limit to default too when no limit is set or maximum size has been exceeded. 25
maximumPageSize The maximum page size allowed. 100

Parse

fetch me the first page of 25 where the age of the customer is between 20 & 30 and the order total is more or equal to £100

var parser = SequelizeQS();
var query = parser.parse({
    page: 1,
    limit: 25,
    age: '|20|30',
    orderTotal: '>=100'
});

// if using express:
// var query = parser.parse(req.query);

await models.customers.findAll(query);

Why build this Library?

There is a lot of questions out there that point to a libary like this being desired. However, equally there is calls that a library like this should not be required and to "put in the work" for each of your queries. Recently, I've found myself building small API's in express with MongoDB that included a react front-end in a CRM style front-end/web-application. There is a great small library for processing parameters into a mongoose query here that I drew a lot of inspiration from. I found myself working with MySQL and Sequelize and could not find a library to do the queries I needed, so to save time now and in the future, I built this library for myself. Sharing is caring, so I want to make this library publicly available for anyone else who is seeking a solution like this.

Future of the Library (Roadmap)

This library was primarily built for myself when building multiple small API's and finding myself duplicating the code for querying & pagination. I do have plans to extend some of the features to include more complex AND & OR operations, though this might take time for me to complete. Feel free to fork and submit a PR if you want to contribute to these features.

Some features that I have thought about but not necessarily implemented include;

  • complex queries with AND & OR operators.
  • white & blacklist for specific parameter names.
  • custom functions that execute when a specific parameter is included.
  • advanced sorting by functions (e.g. ordering by max(age))
  • DATE-only comparison.

Collaborators

Collaborating in a significant way will get you added to the collaborators list with higher access to make commits/changes.

Licence

This project is licenced under the GNU GPLv3 licence. Primarily to enable full-oper source and contribution but to keep closed sources from being generated.

About

Small library to convert express query params (or objects in general) into a syntax recognised by the Sequelize library.

Topics

Resources

License

Stars

Watchers

Forks

Packages

No packages published