where generally takes an object from attribute:value pairs, where value can be primitives for equality matches or keyed objects for other operators.

    It's also possible to generate complex AND/OR conditions by nesting sets of or and and Operators.

    Sequelize exposes symbol operators that can be used for to create more complex comparisons -

    1. const Op = Sequelize.Op
    3. [Op.and]: [{a: 5}, {b: 6}] // (a = 5) AND (b = 6)
    4. [Op.or]: [{a: 5}, {a: 6}]  // (a = 5 OR a = 6)
    5. [Op.gt]: 6,                // > 6
    6. [Op.gte]: 6,               // >= 6
    7. [Op.lt]: 10,               // < 10
    8. [Op.lte]: 10,              // <= 10
    9. [Op.ne]: 20,               // != 20
    10. [Op.eq]: 3,                // = 3
    11. [Op.is]: null              // IS NULL
    12. [Op.not]: true,            // IS NOT TRUE
    13. [Op.between]: [6, 10],     // BETWEEN 6 AND 10
    14. [Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
    15. [Op.in]: [1, 2],           // IN [1, 2]
    16. [Op.notIn]: [1, 2],        // NOT IN [1, 2]
    17. [Op.like]: '%hat',         // LIKE '%hat'
    18. [Op.notLike]: '%hat'       // NOT LIKE '%hat'
    19. [Op.iLike]: '%hat'         // ILIKE '%hat' (case insensitive) (PG only)
    20. [Op.notILike]: '%hat'      // NOT ILIKE '%hat'  (PG only)
    21. [Op.startsWith]: 'hat'     // LIKE 'hat%'
    22. [Op.endsWith]: 'hat'       // LIKE '%hat'
    23. [Op.substring]: 'hat'      // LIKE '%hat%'
    24. [Op.regexp]: '^[h|a|t]'    // REGEXP/~ '^[h|a|t]' (MySQL/PG only)
    25. [Op.notRegexp]: '^[h|a|t]' // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)
    26. [Op.iRegexp]: '^[h|a|t]'    // ~* '^[h|a|t]' (PG only)
    27. [Op.notIRegexp]: '^[h|a|t]' // !~* '^[h|a|t]' (PG only)
    28. [Op.like]: { [Op.any]: ['cat', 'hat']}
    29.                            // LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
    30. [Op.overlap]: [1, 2]       // && [1, 2] (PG array overlap operator)
    31. [Op.contains]: [1, 2]      // @> [1, 2] (PG array contains operator)
    32. [Op.contained]: [1, 2]     // <@ [1, 2] (PG array contained by operator)
    33. [Op.any]: [2,3]            // ANY ARRAY[2, 3]::INTEGER (PG only)
    35. [Op.col]: 'user.organization_id' // = "user"."organization_id", with dialect specific column identifiers, PG in this example
    36. [Op.gt]: { [Op.all]: literal('SELECT 1') }
    37.                           // > ALL (SELECT 1)

    Range Operators

    Range types can be queried with all supported operators.

    1. // All the above equality and inequality operators plus the following:
    3. [Op.contains]: 2           // @> '2'::integer (PG range contains element operator)
    4. [Op.contains]: [1, 2]      // @> [1, 2) (PG range contains range operator)
    5. [Op.contained]: [1, 2]     // <@ [1, 2) (PG range is contained by operator)
    6. [Op.overlap]: [1, 2]       // && [1, 2) (PG range overlap (have points in common) operator)
    7. [Op.adjacent]: [1, 2]      // -|- [1, 2) (PG range is adjacent to operator)
    8. [Op.strictLeft]: [1, 2]    // << [1, 2) (PG range strictly left of operator)
    9. [Op.strictRight]: [1, 2]   // >> [1, 2) (PG range strictly right of operator)
    10. [Op.noExtendLeft]: [1, 2]  // &> [1, 2) (PG range does not extend to the left of operator)

    Combinations

    1. const Op = Sequelize.Op;
    3. {
    4.   rank: {
    5.     [Op.or]: {
    6.       [Op.lt]: 1000,
    7.       [Op.eq]: null
    9.   }
    10. }
    11. // rank < 1000 OR rank IS NULL
    13. {
    14.   createdAt: {
    15.     [Op.lt]: new Date(),
    16.     [Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000)
    17.   }
    18. }
    19. // createdAt < [timestamp] AND createdAt > [timestamp]
    21. {
    22.   [Op.or]: [
    23.     {
    24.       title: {
    25.         [Op.like]: 'Boat%'
    26.       }
    27.     },
    28.     {
    29.       description: {
    30.         [Op.like]: '%boat%'
    31.       }
    32.     }
    33.   ]
    34. }
    35. // title LIKE 'Boat%' OR description LIKE '%boat%'

    Operators Aliases

    Sequelize allows setting specific strings as aliases for operators. With v5 this will give you deprecation warning.

    Operators security

    By default Sequelize will use Symbol operators. Using Sequelize without any aliases improves security. Not having any string aliases will make it extremely unlikely that operators could be injected but you should always properly validate and sanitize user input.

    Some frameworks automatically parse user input into js objects and if you fail to sanitize your input it might be possible to inject an Object with string operators to Sequelize.

    For better security it is highly advised to use symbol operators from Sequelize.Op like Op.and / Op.or in your code and not depend on any string based operators like $and / $or at all. You can limit alias your application will need by setting operatorsAliases option, remember to sanitize user input especially when you are directly passing them to Sequelize methods.

    1. const Op = Sequelize.Op;
    3. //use sequelize without any operators aliases
    4. const connection = new Sequelize(db, user, pass, { operatorsAliases: false });
    6. //use sequelize with only alias for $and => Op.and
    7. const connection2 = new Sequelize(db, user, pass, { operatorsAliases: { $and: Op.and } });
    1. const Op = Sequelize.Op;
    2. const operatorsAliases = {
    3.   $eq: Op.eq,
    4.   $ne: Op.ne,
    5.   $gte: Op.gte,
    6.   $gt: Op.gt,
    7.   $lte: Op.lte,
    8.   $lt: Op.lt,
    9.   $not: Op.not,
    10.   $in: Op.in,
    11.   $notIn: Op.notIn,
    12.   $is: Op.is,
    13.   $like: Op.like,
    14.   $notLike: Op.notLike,
    15.   $iLike: Op.iLike,
    16.   $notILike: Op.notILike,
    17.   $regexp: Op.regexp,
    18.   $iRegexp: Op.iRegexp,
    19.   $notIRegexp: Op.notIRegexp,
    21.   $notBetween: Op.notBetween,
    22.   $overlap: Op.overlap,
    23.   $contains: Op.contains,
    24.   $contained: Op.contained,
    25.   $adjacent: Op.adjacent,
    26.   $strictLeft: Op.strictLeft,
    27.   $strictRight: Op.strictRight,
    28.   $noExtendRight: Op.noExtendRight,
    29.   $noExtendLeft: Op.noExtendLeft,
    30.   $and: Op.and,
    31.   $or: Op.or,
    32.   $any: Op.any,
    33.   $all: Op.all,
    34.   $values: Op.values,
    35.   $col: Op.col
    36. };
    38. const connection = new Sequelize(db, user, pass, { operatorsAliases });

    The JSON data type is supported by the PostgreSQL, SQLite, MySQL and MariaDB dialects only.

    PostgreSQL

    The JSON data type in PostgreSQL stores the value as plain text, as opposed to binary representation. If you simply want to store and retrieve a JSON representation, using JSON will take less disk space and less time to build from its input representation. However, if you want to do any operations on the JSON value, you should prefer the JSONB data type described below.

    MSSQL

    MSSQL does not have a JSON data type, however it does provide support for JSON stored as strings through certain functions since SQL Server 2016. Using these functions, you will be able to query the JSON stored in the string, but any returned values will need to be parsed seperately.

    1. // ISJSON - to test if a string contains valid JSON
    2. User.findAll({
    3.   where: sequelize.where(sequelize.fn('ISJSON', sequelize.col('userDetails')), 1)
    4. })
    6. // JSON_VALUE - extract a scalar value from a JSON string
    7. User.findAll({
    8.   attributes: [[ sequelize.fn('JSON_VALUE', sequelize.col('userDetails'), '$.address.Line1'), 'address line 1']]
    9. })
    11. // JSON_VALUE - query a scalar value from a JSON string
    12. User.findAll({
    13.   where: sequelize.where(sequelize.fn('JSON_VALUE', sequelize.col('userDetails'), '$.address.Line1'), '14, Foo Street')
    14. })
    16. // JSON_QUERY - extract an object or array
    17. User.findAll({
    18.   attributes: [[ sequelize.fn('JSON_QUERY', sequelize.col('userDetails'), '$.address'), 'full address']]
    19. })

    JSONB can be queried in three different ways.

    Nested object

    Nested key

    1. {
    2.   "meta.audio.length": {
    3.     [Op.gt]: 20
    4.   }
    5. }

    Containment

    1. {
    2.   "meta": {
    3.     [Op.contains]: {
    4.       site: {
    5.         url: 'http://google.com'
    6.       }
    7.     }
    8.   }
    9. }
    1. // Find all projects with a least one task where task.state === project.state
    2. Project.findAll({
    3.     include: [{
    4.         model: Task,
    5.         where: { state: Sequelize.col('project.state') }
    6.     }]