sequelize-easy-query

An easy and robust way of making filtering, searching and ordering using querystring in sequelize.

Installation

npm install sequelize-easy-query --save

Quick Start

Let's say we have a "User" table, we want to implement filtering, ordering and searching using querystring, with the native sequelize "where" and "order" clause.

// user-model.js
// For demonstration purpose, some codes are omitted

const Sequelize = require('sequelize')

module.exports.User = new Sequelize(configs).define('user', {
  gender: Sequelize.BOOLEAN,
  active: Sequelize.BOOLEAN,
  age: Sequelize.TINYINT,
  motto: Sequelize.STRING,
  bio: Sequelize.STRING,
  updated_at: Sequelize.Date,
})

// user-router.js

const seq = require('sequelize-easy-query')

const users = await User.findAll({
  where: seq('raw query string', {
    filterBy: ['gender', 'active'],
    searchBy: ['bio', 'motto'],
  }),
  order: seq('raw query string', {
    orderBy: ['age', 'updated_at'],
  }),
})

Now we can make query using querystring individually or in combination with safety:

example.com/api/users?gender=0&active=1&search=programmer&search=confident&cost=DESC

Passing incomplete querystring or nonexistent column names won't cause any error, in below cases, the whole table without any filtering will be returned:

example.com/api/users?&foo=1

example.com/api/users?gender

example.com/api/users?search&&

Table of API

Basic query

• filterBy
• searchBy
• orderBy

Query with alias

• filterByAlias
• orderByAlias

Pre-query

• filter
• search
• order

Basic Query

filterBy: string[ ]

Filter users by "gender" and "active" column:

const users = await User.findAll({
  where: seq('raw query string', {
    filterBy: ['gender', 'active'],
  }),
})

Making query in combination, this will return users with gender=0 AND active=1

example.com/api/users?gender=0&active=1

Multiple selection, this will return users with gender=0 OR users with gender=1

example.com/api/users?gender=0&gender=1

searchBy: string[ ]

Search users if they have certain content in their "bio" OR "motto" column:

const users = await User.findAll({
  where: seq('raw query string', {
    searchBy: ['bio', 'motto'],
  }),
})

Use key "search" to trigger a search:

example.com/api/users?search=some_values

Multiple search, this will return users that have "value_1" OR "value_2":

example.com/api/users?search=value_1&search=value_2

orderBy: string[ ]

Order users by their "age" OR "updated_at" value:

const users = await User.findAll({
  order: seq('raw query string', {
    orderBy: ['age', 'updated_at'],
  }),
})

Only two options are usable: DESC or ASC:

example.com/api/users?age=DESC

example.com/api/users?updated_at=ASC

Multiple ordering is meaningless, only the first one will work:

example.com/api/users?age=DESC&updated_at=ASC

Query With Alias

filterByAlias: {}

Sometimes we want the key used for query not to be the same as its corresponding column name:

const users = await User.findAll({
  where: seq('raw query string', {
    filterByAlias: {
      gender: 'isMale',
      active: 'isAvailale',
    },
  }),
})

Now we can filter users by using the new keys and the original ones can no longer be used:

example.com/api/users?isMale=0&isAvailable=1

This feature is especially useful when we have included other associated models, we want to filter the main model based on columns from those associated models but not to affect the main model:

const users = await User.findAll({
  include: [{
    model: Puppy,
    where: seq('raw query string', {
      filterByAlias: {
        gender: 'puppy_gender'
      }
    })
  }],
  where: seq('raw query string', {
    filterBy: ['gender']
  }),
})

Now "puppy_gender" is used to filter users based on their puppies' gender, but not they themselves' gender:

example.com/api/users?puppy_gender=1

While "gender" is still used to filter users by users' gender:

example.com/api/users?gender=1

Alias can also be given the same value as the original column name, it's totally fine:

const users = await User.findAll({
  where: seq('raw query string', {
    filterByAlias: {
      gender: 'gender',
      active: 'active',
    },
  }),
})

// is same as
const users = await User.findAll({
  where: seq('raw query string', {
    filterBy: ['gender', 'active'],
  }),
})

orderByAlias: {}

Please refer to filterByAlias which is for the same purpose and with the same behaviour.

Pre-query

Sometimes we want to directly send pre-filtered data to client, this can be done with options "filter", "search" and "order":

filter: {}

Pre-filter without any querystring from client:

const users = await User.findAll({
  where: seq('raw query string', {
    filter: {
      gender: 1,
      active: 0,
    }
  }),
})

Pre-filter with multiple selection on one column:

const users = await User.findAll({
  where: seq('raw query string', {
    filter: {
      gender: [0, 1],
      active: 0,
    }
  }),
})

search: string[ ]

Pre-search without any querystring from client, "searchBy" is still needed to be declared as it tells database on which columns to perform the search:

const users = await User.findAll({
  where: seq('raw query string', {
    search: ['some content', 'some other content'],
    searchBy: ['bio', 'motto'],
  }),
})

order: {}

Pre-order without any querystring from client, it can only take one key-value pairs at a time:

const users = await User.findAll({
  order: seq('raw query string', {
    order: {
      age: 'DESC',
    }
  }),
})

Something about pre-query to be noticed that:

• Even with pre-query, further custom querystring can still be given from client:

example.com/api/users?gender=0&search=programmer

• Once "filterByAlias" or "orderByAlias" is set, it also requires to use the alias in the pre-query fields:

const users = await User.findAll({
  where: seq('raw query string', {
    filterByAlias: {
      gender: 'isMale',
      active: 'isAvailable',
    },
    filter: {
      isMale: 1,
      isAvailable: 0,
    },
    orderByAlias: {
      age: 'years',
    },
    order: {
      years: 'DESC',
    }
  }),
})