❤
npm
Sign UpSign In

sequelize-search-builder-custom

0.9.4 • Public • Published

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()
{
  [Sequelize.Op.or]: {
    [Sequelize.Op.or]: [{
      age: {
        [Sequelize.Op.gt]: 100,
      },
    }, {
      age: {
        [Sequelize.Op.lt]: 10,
      },
    }],
    name: {
      [Sequelize.Op.like]: '%john%',
    },
  },
}

If _condition parameter is absent - "and" will be use 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 Sequelize.Op.gt Greater than
gte Sequelize.Op.gte Greater than or equal
lt Sequelize.Op.lt Less than
lte Sequelize.Op.lte Less than or equal
ne Sequelize.Op.ne Not equal
between Sequelize.Op.between Between [value1, value2]
notBetween Sequelize.Op.notBetween Not Between [value1, value2]
in Sequelize.Op.in In value list [value1, value2, ...]
notIn Sequelize.Op.notIn Not in value list [value1, value2, ...]
like Sequelize.Op.like Like search (%value, value%, %value%)
notLike Sequelize.Op.notLike Not like search (%value, value%, %value%)
iLike Sequelize.Op.iLike case insensitive LIKE (PG only)
notILike Sequelize.Op.notILike case insensitive NOT LIKE (PG only)
regexp Sequelize.Op.regexp Regexp (MySQL and PG only)
notRegexp Sequelize.Op.notRegexp Not Regexp (MySQL and PG only)
iRegexp Sequelize.Op.iRegexp iRegexp (case insensitive) (PG only)
notIRegexp Sequelize.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,
    });

Contribute

You are Welcome =) Keep in mind:

npm run test

Readme

Keywords

Package Sidebar

Install

npm i sequelize-search-builder-custom

Repository

github.com/PokatilovArt/sequelize-search-builder

Homepage

github.com/PokatilovArt/sequelize-search-builder#readme

Weekly Downloads

15

Version

0.9.4

License

MIT

Unpacked Size

15.4 kB

Total Files

10

Last publish

Collaborators

  • pokatilov_art
Try on RunKit
Report malware