/sequelize-search-builder

Minimalist library for parsing search request to sequelize query

Primary LanguageJavaScriptMIT LicenseMIT

Sequelize Search Builder

About

This is a lightweight library to convert search request (e.g. HTTP) to Sequelize ORM query.

Installation

npm install --save sequelize-search-builder

Usage

Example based on Express framework.

Direct generation of where/order/limit/offset query

const router  = require('express').Router(),
    models = require('../models'),
    searchBuilder = require('sequelize-search-builder');

router.get('/search', async (req, res, next) => {
    // Set req.query param to Search Builder constructor
    const search = new searchBuilder(models.Sequelize, req.query),
        whereQuery  = search.getWhereQuery(),
        orderQuery  = search.getOrderQuery(),
        limitQuery  = search.getLimitQuery(),
        offsetQuery = search.getOffsetQuery();
    
    res.json({
        data: await models.product.findAll({
            include: [{ all: true, nested: true, duplicating: false }],
            where:  whereQuery,
            order:  orderQuery,
            limit:  limitQuery,
            offset: offsetQuery,
            logging: console.log,
        })
    });
});

Full query generation example (getFullQuery method)

res.json({
    data: await models.product.findAll(search.getFullQuery({
        include: [{ all: true, nested: true, duplicating: true }],
    }))
});

You can set HTTP query string as second parameter for Seach Builder constructor (it will parse by 'qs' library to object).

Request Examples

Equal:

// HTTP:
?filter[name]=John&filter[surname]=Smith
// req.query:
{ filter: { name: 'John', surname: 'Smith' } }
// getWhereQuery()
{ name: 'John', surname: 'Smith' }

Equal (OR):

// HTTP:
?filter[name]=John&filter[surname]=Smith&filter[_condition]=or
// req.query:
{ filter: { name: 'John', surname: 'Smith', _condition: 'or', } }
// getWhereQuery()
{ [Symbol(or)]: {name: 'John', surname: 'Smith'} }

Conditions:

// HTTP:
filter[age][gt]=100&filter[age][lt]=10&filter[age][_condition]=or&filter[name][iLike]=%john%&filter[_condition]=or
// req.query
{
  filter: {
    age: {
      gt: 100,
      lt: 10,
      _condition: 'or',
    },
    name: {
      iLike: '%john%',
    },
    _condition: 'or',
  },
}
// getWhereQuery()
{
  [Op.or]: {
    [Op.or]: [{
      age: {
        [Op.gt]: 100,
      },
    }, {
      age: {
        [Op.lt]: 10,
      },
    }],
    name: {
      [Op.like]: '%john%',
    },
  },
}

If _condition parameter is absent - "and" will be used by default

Order:

// HTTP:
?filter[name]=desc
// req.query:
{ order: { name: 'desc' } }
// getOrderQuery()
[ [ 'name', 'desc' ] ]

You can find more examples in the tests of the project (test/index.js)

Git repository with DB tests: https://github.com/segemun/sequelize-search-builder-db-tests

Allowed query conditions

Request Option Sequelize Symbol Description
eq (=) = (no Symbol) Equal
gt Op.gt Greater than
gte Op.gte Greater than or equal
lt Op.lt Less than
lte Op.lte Less than or equal
ne Op.ne Not equal
between Op.between Between [value1, value2]
notBetween Op.notBetween Not Between [value1, value2]
in Op.in In value list [value1, value2, ...]
notIn Op.notIn Not in value list [value1, value2, ...]
like Op.like Like search (%value, value%, %value%)
notLike Op.notLike Not like search (%value, value%, %value%)
iLike Op.iLike case insensitive LIKE (PG only)
notILike Op.notILike case insensitive NOT LIKE (PG only)
regexp Op.regexp Regexp (MySQL and PG only)
notRegexp Op.notRegexp Not Regexp (MySQL and PG only)
iRegexp Op.iRegexp iRegexp (case insensitive) (PG only)
notIRegexp Op.notIRegexp notIRegexp (case insensitive) (PG only)

Configuration

You can redefine configuration variables in rc file

Just create .sequelize-search-builderrc file in root folder of your project

Or use setter for 'config' parameter (setConfig)

RC file example:

{
  "logging": false,
  "fields": {
    "filter" : "filter",
    "order"  : "order",
    "limit"  : "limit",
    "offset" : "offset"
  },
  "default-limit": 10
}

Setter example:

new searchBuilder(models.Sequelize, req.query)
    .setConfig({
        logging: true,
    });

Front-End

You can use Sequelize Search Builder Client module for the generation request http search string on the client side.

Contribute

You are Welcome =) Keep in mind:

npm run test