Querying

Attributes

To select only some attributes, you can use the attributes option. Most often, you pass an array:

  1. Model.findAll({
  2.   attributes: ['foo', 'bar']
  3. });
  1. SELECT foo, bar ...

Attributes can be renamed using a nested array:

  1. Model.findAll({
  2.   attributes: ['foo', ['bar', 'baz']]
  3. });
  1. SELECT foo, bar AS baz ...

You can use sequelize.fn to do aggregations:

  1. Model.findAll({
  2.   attributes: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
  3. });
  1. SELECT COUNT(hats) AS no_hats ...

When using aggregation function, you must give it an alias to be able to access it from the model. In the example above you can get the number of hats with instance.get('no_hats').

Sometimes it may be tiresome to list all the attributes of the model if you only want to add an aggregation:

  1. // This is a tiresome way of getting the number of hats...
  2. Model.findAll({
  3.   attributes: ['id', 'foo', 'bar', 'baz', 'quz', [sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
  4. });
  6. // This is shorter, and less error prone because it still works if you add / remove attributes
  7. Model.findAll({
  8.   attributes: { include: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']] }
  9. });
  1. SELECT id, foo, bar, baz, quz, COUNT(hats) AS no_hats ...

Similarly, it's also possible to remove a selected few attributes:

  1. Model.findAll({
  2.   attributes: { exclude: ['baz'] }
  3. });
  1. SELECT id, foo, bar, quz ...

Where

Whether you are querying with findAll/find or doing bulk updates/destroys you can pass a where object to filter the query.

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.

Basics

  1. const Op = Sequelize.Op;
  3. Post.findAll({
  4.   where: {
  5.     authorId: 2
  6.   }
  7. });
  8. // SELECT * FROM post WHERE authorId = 2
  10. Post.findAll({
  11.   where: {
  12.     authorId: 12,
  13.     status: 'active'
  14.   }
  15. });
  16. // SELECT * FROM post WHERE authorId = 12 AND status = 'active';
  18. Post.findAll({
  19.   where: {
  20.     [Op.or]: [{authorId: 12}, {authorId: 13}]
  21.   }
  22. });
  23. // SELECT * FROM post WHERE authorId = 12 OR authorId = 13;
  25. Post.findAll({
  26.   where: {
  27.     authorId: {
  28.       [Op.or]: [12, 13]
  29.     }
  30.   }
  31. });
  32. // SELECT * FROM post WHERE authorId = 12 OR authorId = 13;
  34. Post.destroy({
  35.   where: {
  36.     status: 'inactive'
  37.   }
  38. });
  39. // DELETE FROM post WHERE status = 'inactive';
  41. Post.update({
  42.   updatedAt: null,
  43. }, {
  44.   where: {
  45.     deletedAt: {
  46.       [Op.ne]: null
  47.     }
  48.   }
  49. });
  50. // UPDATE post SET updatedAt = null WHERE deletedAt NOT NULL;
  52. Post.findAll({
  53.   where: sequelize.where(sequelize.fn('char_length', sequelize.col('status')), 6)
  54. });
  55. // SELECT * FROM post WHERE char_length(status) = 6;

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}           // AND (a = 5)
  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.not]: true,            // IS NOT TRUE
  12. [Op.between]: [6, 10],     // BETWEEN 6 AND 10
  13. [Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
  14. [Op.in]: [1, 2],           // IN [1, 2]
  15. [Op.notIn]: [1, 2],        // NOT IN [1, 2]
  16. [Op.like]: '%hat',         // LIKE '%hat'
  17. [Op.notLike]: '%hat'       // NOT LIKE '%hat'
  18. [Op.iLike]: '%hat'         // ILIKE '%hat' (case insensitive) (PG only)
  19. [Op.notILike]: '%hat'      // NOT ILIKE '%hat'  (PG only)
  20. [Op.regexp]: '^[h|a|t]'    // REGEXP/~ '^[h|a|t]' (MySQL/PG only)
  21. [Op.notRegexp]: '^[h|a|t]' // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)
  22. [Op.iRegexp]: '^[h|a|t]'    // ~* '^[h|a|t]' (PG only)
  23. [Op.notIRegexp]: '^[h|a|t]' // !~* '^[h|a|t]' (PG only)
  24. [Op.like]: { [Op.any]: ['cat', 'hat']}
  25.                        // LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
  26. [Op.overlap]: [1, 2]       // && [1, 2] (PG array overlap operator)
  27. [Op.contains]: [1, 2]      // @> [1, 2] (PG array contains operator)
  28. [Op.contained]: [1, 2]     // <@ [1, 2] (PG array contained by operator)
  29. [Op.any]: [2,3]            // ANY ARRAY[2, 3]::INTEGER (PG only)
  31. [Op.col]: 'user.organization_id' // = "user"."organization_id", with dialect specific column identifiers, PG in this example

Range Operators

Range types can be queried with all supported operators.

Keep in mind, the provided range value candefine the bound inclusion/exclusionas well.

  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.noExtendRight]: [1, 2] // &< [1, 2) (PG range does not extend to the right of operator)
  11. [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
  8.     }
  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 -

  1. const Op = Sequelize.Op;
  2. const operatorsAliases = {
  3.   $gt: Op.gt
  4. }
  5. const connection = new Sequelize(db, user, pass, { operatorsAliases })
  7. [Op.gt]: 6 // > 6
  8. $gt: 6 // same as using Op.gt (> 6)

Operators security

Using Sequelize without any aliases improves security.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.

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.

For backward compatibility reasons Sequelize sets the following aliases by default -$eq, $ne, $gte, $gt, $lte, $lt, $not, $in, $notIn, $is, $like, $notLike, $iLike, $notILike, $regexp, $notRegexp, $iRegexp, $notIRegexp, $between, $notBetween, $overlap, $contains, $contained, $adjacent, $strictLeft, $strictRight, $noExtendRight, $noExtendLeft, $and, $or, $any, $all, $values, $col

Currently the following legacy aliases are also set but are planned to be fully removed in the near future -ne, not, in, notIn, gte, gt, lte, lt, like, ilike, $ilike, nlike, $notlike, notilike, .., between, !.., notbetween, nbetween, overlap, &&, @>, <@

For better security it is highly advised to use Sequelize.Op and not depend on any string alias 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 } });

Sequelize will warn you if you're using the default aliases and not limiting themif you want to keep using all default aliases (excluding legacy ones) without the warning you can pass the following operatorsAliases option -

  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.   $notRegexp: Op.notRegexp,
  19.   $iRegexp: Op.iRegexp,
  20.   $notIRegexp: Op.notIRegexp,
  21.   $between: Op.between,
  22.   $notBetween: Op.notBetween,
  23.   $overlap: Op.overlap,
  24.   $contains: Op.contains,
  25.   $contained: Op.contained,
  26.   $adjacent: Op.adjacent,
  27.   $strictLeft: Op.strictLeft,
  28.   $strictRight: Op.strictRight,
  29.   $noExtendRight: Op.noExtendRight,
  30.   $noExtendLeft: Op.noExtendLeft,
  31.   $and: Op.and,
  32.   $or: Op.or,
  33.   $any: Op.any,
  34.   $all: Op.all,
  35.   $values: Op.values,
  36.   $col: Op.col
  37. };
  39. const connection = new Sequelize(db, user, pass, { operatorsAliases });

JSON

The JSON data type is supported by the PostgreSQL, SQLite and MySQL 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

JSONB can be queried in three different ways.

Nested object

  1. {
  2.   meta: {
  3.     video: {
  4.       url: {
  5.         [Op.ne]: null
  6.       }
  7.     }
  8.   }
  9. }

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. }

Relations / Associations

  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.     }]
  7. })

Pagination / Limiting

  1. // Fetch 10 instances/rows
  2. Project.findAll({ limit: 10 })
  4. // Skip 8 instances/rows
  5. Project.findAll({ offset: 8 })
  7. // Skip 5 instances and fetch the 5 after that
  8. Project.findAll({ offset: 5, limit: 5 })

Ordering

order takes an array of items to order the query by or a sequelize method. Generally you will want to use a tuple/array of either attribute, direction or just direction to ensure proper escaping.

  1. Subtask.findAll({
  2.   order: [
  3.     // Will escape title and validate DESC against a list of valid direction parameters
  4.     ['title', 'DESC'],
  6.     // Will order by max(age)
  7.     sequelize.fn('max', sequelize.col('age')),
  9.     // Will order by max(age) DESC
  10.     [sequelize.fn('max', sequelize.col('age')), 'DESC'],
  12.     // Will order by  otherfunction(`col1`, 12, 'lalala') DESC
  13.     [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
  15.     // Will order an associated model's created_at using the model name as the association's name.
  16.     [Task, 'createdAt', 'DESC'],
  18.     // Will order through an associated model's created_at using the model names as the associations' names.
  19.     [Task, Project, 'createdAt', 'DESC'],
  21.     // Will order by an associated model's created_at using the name of the association.
  22.     ['Task', 'createdAt', 'DESC'],
  24.     // Will order by a nested associated model's created_at using the names of the associations.
  25.     ['Task', 'Project', 'createdAt', 'DESC'],
  27.     // Will order by an associated model's created_at using an association object. (preferred method)
  28.     [Subtask.associations.Task, 'createdAt', 'DESC'],
  30.     // Will order by a nested associated model's created_at using association objects. (preferred method)
  31.     [Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],
  33.     // Will order by an associated model's created_at using a simple association object.
  34.     [{model: Task, as: 'Task'}, 'createdAt', 'DESC'],
  36.     // Will order by a nested associated model's created_at simple association objects.
  37.     [{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
  38.   ]
  40.   // Will order by max age descending
  41.   order: sequelize.literal('max(age) DESC')
  43.   // Will order by max age ascending assuming ascending is the default order when direction is omitted
  44.   order: sequelize.fn('max', sequelize.col('age'))
  46.   // Will order by age ascending assuming ascending is the default order when direction is omitted
  47.   order: sequelize.col('age')
  49.   // Will order randomly based on the dialect (instead of fn('RAND') or fn('RANDOM'))
  50.   order: sequelize.random()
  51. })

Table Hint

tableHint can be used to optionally pass a table hint when using mssql. The hint must be a value from Sequelize.TableHints and should only be used when absolutely necessary. Only a single table hint is currently supported per query.

Table hints override the default behavior of mssql query optimizer by specifing certain options. They only affect the table or view referenced in that clause.

  1. const TableHints = Sequelize.TableHints;
  3. Project.findAll({
  4.   // adding the table hint NOLOCK
  5.   tableHint: TableHints.NOLOCK
  6.   // this will generate the SQL 'WITH (NOLOCK)'
  7. })