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. });
  5. // This is shorter, and less error prone because it still works if you add / remove attributes
  6. Model.findAll({
  7. attributes: { include: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']] }
  8. });
  1. SELECT id, foo, bar, baz, quz, COUNT(hats) AS no_hats ...

Similarly, its 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.

Basics

  1. Post.findAll({
  2. where: {
  3. authorId: 2
  4. }
  5. });
  6. // SELECT * FROM post WHERE authorId = 2
  7. Post.findAll({
  8. where: {
  9. authorId: 12,
  10. status: 'active'
  11. }
  12. });
  13. // SELECT * FROM post WHERE authorId = 12 AND status = 'active';
  14. Post.destroy({
  15. where: {
  16. status: 'inactive'
  17. }
  18. });
  19. // DELETE FROM post WHERE status = 'inactive';
  20. Post.update({
  21. updatedAt: null,
  22. }, {
  23. where: {
  24. deletedAt: {
  25. $ne: null
  26. }
  27. }
  28. });
  29. // UPDATE post SET updatedAt = null WHERE deletedAt NOT NULL;
  30. Post.findAll({
  31. where: sequelize.where(sequelize.fn('char_length', sequelize.col('status')), 6)
  32. });
  33. // SELECT * FROM post WHERE char_length(status) = 6;

Operators

  1. $and: {a: 5} // AND (a = 5)
  2. $or: [{a: 5}, {a: 6}] // (a = 5 OR a = 6)
  3. $gt: 6, // > 6
  4. $gte: 6, // >= 6
  5. $lt: 10, // < 10
  6. $lte: 10, // <= 10
  7. $ne: 20, // != 20
  8. $not: true, // IS NOT TRUE
  9. $between: [6, 10], // BETWEEN 6 AND 10
  10. $notBetween: [11, 15], // NOT BETWEEN 11 AND 15
  11. $in: [1, 2], // IN [1, 2]
  12. $notIn: [1, 2], // NOT IN [1, 2]
  13. $like: '%hat', // LIKE '%hat'
  14. $notLike: '%hat' // NOT LIKE '%hat'
  15. $iLike: '%hat' // ILIKE '%hat' (case insensitive) (PG only)
  16. $notILike: '%hat' // NOT ILIKE '%hat' (PG only)
  17. $like: { $any: ['cat', 'hat']}
  18. // LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
  19. $overlap: [1, 2] // && [1, 2] (PG array overlap operator)
  20. $contains: [1, 2] // @> [1, 2] (PG array contains operator)
  21. $contained: [1, 2] // <@ [1, 2] (PG array contained by operator)
  22. $any: [2,3] // ANY ARRAY[2, 3]::INTEGER (PG only)
  23. $col: 'user.organization_id' // = "user"."organization_id", with dialect specific column identifiers, PG in this example

Combinations

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

JSONB

JSONB can be queried in three different ways.

Nested object

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

Nested key

  1. {
  2. "meta.audio.length": {
  3. $gt: 20
  4. }
  5. }

Containment

  1. {
  2. "meta": {
  3. $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.task
  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 })
  3. // Skip 8 instances/rows
  4. Project.findAll({ offset: 8 })
  5. // Skip 5 instances and fetch the 5 after that
  6. Project.findAll({ offset: 5, limit: 5 })

Ordering

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

  1. something.findOne({
  2. order: [
  3. // Will escape username and validate DESC against a list of valid direction parameters
  4. ['username', 'DESC'],
  5. // Will order by max(age)
  6. sequelize.fn('max', sequelize.col('age')),
  7. // Will order by max(age) DESC
  8. [sequelize.fn('max', sequelize.col('age')), 'DESC'],
  9. // Will order by otherfunction(`col1`, 12, 'lalala') DESC
  10. [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
  11. // Will order by name on an associated User
  12. [User, 'name', 'DESC'],
  13. // Will order by name on an associated User aliased as Friend
  14. [{model: User, as: 'Friend'}, 'name', 'DESC'],
  15. // Will order by name on a nested associated Company of an associated User
  16. [User, Company, 'name', 'DESC'],
  17. ]
  18. // All the following statements will be treated literally so should be treated with care
  19. order: 'convert(user_name using gbk)'
  20. order: 'username DESC'
  21. order: sequelize.literal('convert(user_name using gbk)')
  22. })