sequelize-helper

Useful features to facilitate the use of Sequelize.

Install

npm i sequelize-butler

Filter

Tool to write where conditions for sequelize more easily

Instance

const SequelizeButler = require('sequelize-butler')
const Sequelize = require('sequelize')

let sequelize = new Sequelize({ [...] })
let filter = new SequelizeButler.Filter(sequelize)

Methods

The following methods with the "add" prefix allow to add where-conditions to Filter instance, while methods with the "get" prefix transform the added where-conditions into a "where" object that can be used by sequelize

addLike

Adds a like condition to one or more columns

Arguments

• columns: array of columns to wich run the condition
• value: test to search

Example

Code:

filter.addLike(['column1', 'column2'], 'abc')

SQL output (PG in this example):

("table"."column1" ILIKE '%abc%' OR "table"."column2" ILIKE '%abc%')

addNotLike

Adds a not-like condition to one or more columns

Arguments

• columns: array of columns to wich run the condition
• value: test to search

Example

Code:

filter.addNotLike(['column1', 'column2'], 'abc')

SQL output (PG in this example):

("table"."column1" NOT ILIKE '%abc%' AND "table"."column2" NOT ILIKE '%abc%')

addEqual

Adds an equal condition to one column, for dates you must specify the type, if value is null or undefined an "IS NULL" condition is added

Arguments

• column: column to compare
• value: value to compare to column
• type (optional): Sequelize data type

Example

Code:

filter.addEqual('column1', '2017-01-01 18:00', Sequelize.DATE)

SQL output (PG in this example):

(CAST('column1' AS TIMESTAMP(0)) = '2017-01-01T18:00:00')

addNotEqual

Add a not-equal condition to one column, for dates you must specify the type, if value is null or undefined an "IS NOT NULL" condition is added

Arguments

• column: column to compare
• value: value to compare to column
• type (optional): Sequelize data type

Example

Code:

filter.addNotEqual('column1', '2017-01-01 18:00', Sequelize.DATEONLY)

SQL output (PG in this example):

(CAST('column1' AS DATE) != '2017-01-01T00:00:00')

addGreaterTo

Adds a greater-to condition to one column, for dates you must specify the type

Arguments

• column: column to compare
• value: value to compare to column
• type (optional): Sequelize data type

Example

Code:

filter.addGreaterTo('column1', 2)

SQL output (PG in this example):

("table"."column1" > 2)

addGreaterEqualTo

Adds a greater-equal-to condition to one column, for dates you must specify the type

Arguments

• column: column to compare
• value: value to compare to column
• type (optional): Sequelize data type

Example

Code:

filter.addGreaterEqualTo('column1', 2)

SQL output (PG in this example):

("table"."column1" >= 2)

addLessTo

Adds a less-to condition to one column, for dates you must specify the type

Arguments

• column: column to compare
• value: value to compare to column
• type (optional): Sequelize data type

Example

Code:

filter.addLessTo('column1', 2)

SQL output (PG in this example):

("table"."column1" < 2)

addLessEqualTo

Adds a less-equal-to condition to one column, for dates you must specify the type

Arguments

• column: column to compare
• value: value to compare to column
• type (optional): Sequelize data type

Example

Code:

filter.addLessEqualTo('column1', 2)

SQL output (PG in this example):

("table"."column1" <= 2)

addBetween

Adds a between condition to one column, for dates you must specify the type

Arguments

• column: column to compare
• from: left extremity of the range
• to: right extremity of the range
• type (optional): Sequelize data type

Example

Code:

filter.addBetween('column1', '2017-01-01 18:00', '2017-01-01 19:00', Sequelize.DATE)

SQL output (PG in this example):

("table"."column1" BETWEEN '2017-01-01T18:00:00.00000' AND '2017-01-01T19:00:00.99999')

addNotBetween

Adds a not-between condition to one column, for dates you must specify the type

Arguments

• column: column to compare
• from: left extremity of the range
• to: right extremity of the range
• type (optional): Sequelize data type

Example

Code:

filter.addNotBetween('column1', '2017-01-01 18:00', '2017-01-02 19:00', Sequelize.DATEONLY)

SQL output (PG in this example):

("table"."column1" NOT BETWEEN '2017-01-01T00:00:00.00000' AND '2017-01-02T23:59:59.99999')

addIn

Adds a in condition to one column, for dates you must specify the type

Arguments

• column: column to compare
• values: array of values to compare to column
• type (optional): Sequelize data type

Example

Code:

filter.addIn('column1', [1, 2])

SQL output (PG in this example):

("table"."column1" IN (1, 2))

addNotIn

Adds a not-in condition to one column, for dates you must specify the type

Arguments

• column: column to compare
• values: array of values to compare to column
• type (optional): Sequelize data type

Example

Code:

filter.addNotIn('column1', [1, 2])

SQL output (PG in this example):

("table"."column1" NOT IN (1, 2))

addSequelizeCondition

Adds a custom condition not included in the previous ones

Arguments

• condition: Sequelize condition

Example

Code:

filter.addSequelizeCondition({
  column1: { [Sequelize.Op.overlap]: [1, 2] }
})

SQL output (PG in this example):

("table"."column1" && ARRAY[1,2])

getWhere

Gets a where object to use in a sequelize query, with all added condition in AND

Example

Code:

filter.addEqual('column1', 1)
filter.addEqual('column2', 'test')

model.findaAll({
  ...
  where: filter.getWhere()
  ...
})

SQL output (PG in this example):

("table"."column1" = 1 AND "table"."column2" = 'test')

getWhereUsingOr

Gets a where object to use in a sequelize query, with all added condition in OR

Example

Code:

filter.addEqual('column1', 1)
filter.addEqual('column2', 'test')

model.findaAll({
  ...
  where: filter.getWhereUsingOr()
  ...
})

SQL output (PG in this example):

WHERE ("table"."column1" = 1 OR "table"."column2" = 'test')

Annidate Conditions

This example shows how to implement nested filters

Code:

let filter = new Filter(sequelize)
filter.addLike(['column1'], 'test')
let orBlock = new Filter(sequelize)
orBlock.addEqual('column2', 'abc')
orBlock.addEqual('column3', 5)
filter.addSequelizeCondition(orBlock.getWhereUsingOr())
let where = filter.getWhere()

SQL output (PG in this example):

WHERE (("table"."column1" ILIKE '%test%') 
AND ("table"."column2" = 'abc' OR "table"."column3" = 5))

Order

Tool to write order-by conditions for sequelize more easily

Static methods

getOrderBy

Gets an order object to use in a sequelize query when aliases are used

Code:

const SequelizeButler = require('sequelize-butler')

let aliases = {
  'user_id': 'id',
  'user_name': 'name',
  'user_email': 'email',
  'roles.role_name': 'role'
}

let order = [['name', 'ASC'], ['role', 'DESC']]

model.findaAll({
  ...
  order: SequelizeButler.Order.getOrderBy(order, aliases)
  ...
})

Error

Tool for parsing sequelize validation errors into a more usable object

Instance

const SequelizeButler = require('sequelize-butler')
const Sequelize = require('sequelize')

let sequelize = new Sequelize({ [...] })
model = sequelize.define('table', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true
  },
  name: {
    type: Sequelize.TEXT,
    allowNull: false
  },
  email: {
    type: Sequelize.TEXT,
    validate: {
      isEmail: {
        msg: 'Email is not valid'
      }
    }
  }
})

model.create({
      email: 'abcd'
}).then(() => {
    ...
}).catch((error) => {
  let errorParser = new SequelizeButler.Error(error)
  ....
})

Methods

isValidationError

Returns true if the error is generated by a validation, false otherwise.

Code:

errorParser.isValidationError()

getResults

Returns a json object with the validation errors

Code:

errorParser.getResults('There are some errors')

Otput example:

{
  success: false,
  message: 'There are some errors',
  subresults: [
    { success: false, message: 'table.name cannot be null' },
    { success: false, message: 'Email is not valid' }
  ]
}