Narcoleptic Pasta Manufacturer
    Wondering what’s next for npm?Check out our public roadmap! »

    sequelize-butler
    TypeScript icon, indicating that this package has built-in type declarations

    2.0.11 • Public • Published

    sequelize-butler

    npm npm npm Build Status

    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' }
      ]
    }
    

    Install

    npm i sequelize-butler

    DownloadsWeekly Downloads

    4

    Version

    2.0.11

    License

    MIT

    Unpacked Size

    115 kB

    Total Files

    29

    Last publish

    Collaborators

    • avatar