ssb

sequelize-search-builder

Minimalist library for parsing search request to sequelize query

Showing:

Popularity

Downloads/wk

231

GitHub Stars

28

Maintenance

Last Commit

8mos ago

Contributors

2

Package

Dependencies

4

Size (min+gzip)

289.2KB

License

MIT

Type Definitions

Tree-Shakeable

No?

Categories

Readme

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 OptionSequelize SymbolDescription
eq (=)= (no Symbol)Equal
gtOp.gtGreater than
gteOp.gteGreater than or equal
ltOp.ltLess than
lteOp.lteLess than or equal
neOp.neNot equal
betweenOp.betweenBetween [value1, value2]
notBetweenOp.notBetweenNot Between [value1, value2]
inOp.inIn value list [value1, value2, ...]
notInOp.notInNot in value list [value1, value2, ...]
likeOp.likeLike search (%value, value%, %value%)
notLikeOp.notLikeNot like search (%value, value%, %value%)
iLikeOp.iLikecase insensitive LIKE (PG only)
notILikeOp.notILikecase insensitive NOT LIKE (PG only)
regexpOp.regexpRegexp (MySQL and PG only)
notRegexpOp.notRegexpNot Regexp (MySQL and PG only)
iRegexpOp.iRegexpiRegexp (case insensitive) (PG only)
notIRegexpOp.notIRegexpnotIRegexp (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

Rate & Review

Great Documentation0
Easy to Use0
Performant0
Highly Customizable0
Bleeding Edge0
Responsive Maintainers0
Poor Documentation0
Hard to Use0
Slow0
Buggy0
Abandoned0
Unwelcoming Community0
100
No reviews found
Be the first to rate

Alternatives

No alternatives found

Tutorials

No tutorials found
Add a tutorial