Model usage

Data retrieval / Finders

Finder methods are intended to query data from the database. They do not return plain objects but instead return model instances. Because finder methods return model instances you can call any model instance member on the result as described in the documentation for instances.

In this document we'll explore what finder methods can do:

find - Search for one specific element in the database

  1. // search for known ids
  2. Project.findById(123).then(project => {
  3.   // project will be an instance of Project and stores the content of the table entry
  4.   // with id 123. if such an entry is not defined you will get null
  5. })
  7. // search for attributes
  8. Project.findOne({ where: {title: 'aProject'} }).then(project => {
  9.   // project will be the first entry of the Projects table with the title 'aProject' || null
  10. })
  13. Project.findOne({
  14.   where: {title: 'aProject'},
  15.   attributes: ['id', ['name', 'title']]
  16. }).then(project => {
  17.   // project will be the first entry of the Projects table with the title 'aProject' || null
  18.   // project.title will contain the name of the project
  19. })

findOrCreate - Search for a specific element or create it if not available

The method findOrCreate can be used to check if a certain element already exists in the database. If that is the case the method will result in a respective instance. If the element does not yet exist, it will be created.

Let's assume we have an empty database with a User model which has a username and a job.

  1. User
  2.   .findOrCreate({where: {username: 'sdepold'}, defaults: {job: 'Technical Lead JavaScript'}})
  3.   .spread((user, created) => {
  4.     console.log(user.get({
  5.       plain: true
  6.     }))
  7.     console.log(created)
  9.     /*
  10.      findOrCreate returns an array containing the object that was found or created and a boolean that will be true if a new object was created and false if not, like so:
  12.     [ {
  13.         username: 'sdepold',
  14.         job: 'Technical Lead JavaScript',
  15.         id: 1,
  16.         createdAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET),
  17.         updatedAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET)
  18.       },
  19.       true ]
  21.  In the example above, the "spread" on line 39 divides the array into its 2 parts and passes them as arguments to the callback function defined beginning at line 39, which treats them as "user" and "created" in this case. (So "user" will be the object from index 0 of the returned array and "created" will equal "true".)
  22.     */
  23.   })

The code created a new instance. So when we already have an instance …

  1. User.create({ username: 'fnord', job: 'omnomnom' })
  2.   .then(() => User.findOrCreate({where: {username: 'fnord'}, defaults: {job: 'something else'}}))
  3.   .spread((user, created) => {
  4.     console.log(user.get({
  5.       plain: true
  6.     }))
  7.     console.log(created)
  9.     /*
  10.     In this example, findOrCreate returns an array like this:
  11.     [ {
  12.         username: 'fnord',
  13.         job: 'omnomnom',
  14.         id: 2,
  15.         createdAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET),
  16.         updatedAt: Fri Mar 22 2013 21: 28: 34 GMT + 0100(CET)
  17.       },
  18.       false
  19.     ]
  20.     The array returned by findOrCreate gets spread into its 2 parts by the "spread" on line 69, and the parts will be passed as 2 arguments to the callback function beginning on line 69, which will then treat them as "user" and "created" in this case. (So "user" will be the object from index 0 of the returned array and "created" will equal "false".)
  21.     */
  22.   })

… the existing entry will not be changed. See the job of the second user, and the fact that created was false.

findAndCountAll - Search for multiple elements in the database, returns both data and total count

This is a convenience method that combinesfindAll and count (see below) this is useful when dealing with queries related to pagination where you want to retrieve data with a limit and offset but also need to know the total number of records that match the query:

The success handler will always receive an object with two properties:

  • count - an integer, total number records matching the where clause and other filters due to associations
  • rows - an array of objects, the records matching the where clause and other filters due to associations, within the limit and offset range
  1. Project
  2.   .findAndCountAll({
  3.      where: {
  4.         title: {
  5.           [Op.like]: 'foo%'
  6.         }
  7.      },
  8.      offset: 10,
  9.      limit: 2
  10.   })
  11.   .then(result => {
  12.     console.log(result.count);
  13.     console.log(result.rows);
  14.   });

It support includes. Only the includes that are marked as required will be added to the count part:

Suppose you want to find all users who have a profile attached:

  1. User.findAndCountAll({
  2.   include: [
  3.      { model: Profile, required: true}
  4.   ],
  5.   limit: 3
  6. });

Because the include for Profile has required set it will result in an inner join, and only the users who have a profile will be counted. If we remove required from the include, both users with and without profiles will be counted. Adding a where clause to the include automatically makes it required:

  1. User.findAndCountAll({
  2.   include: [
  3.      { model: Profile, where: { active: true }}
  4.   ],
  5.   limit: 3
  6. });

The query above will only count users who have an active profile, because required is implicitly set to true when you add a where clause to the include.

The options object that you pass to findAndCountAll is the same as for findAll (described below).

findAll - Search for multiple elements in the database

  1. // find multiple entries
  2. Project.findAll().then(projects => {
  3.   // projects will be an array of all Project instances
  4. })
  6. // also possible:
  7. Project.all().then(projects => {
  8.   // projects will be an array of all Project instances
  9. })
  11. // search for specific attributes - hash usage
  12. Project.findAll({ where: { name: 'A Project' } }).then(projects => {
  13.   // projects will be an array of Project instances with the specified name
  14. })
  16. // search within a specific range
  17. Project.findAll({ where: { id: [1,2,3] } }).then(projects => {
  18.   // projects will be an array of Projects having the id 1, 2 or 3
  19.   // this is actually doing an IN query
  20. })
  22. Project.findAll({
  23.   where: {
  24.     id: {
  25.       [Op.and]: {a: 5},           // AND (a = 5)
  26.       [Op.or]: [{a: 5}, {a: 6}],  // (a = 5 OR a = 6)
  27.       [Op.gt]: 6,                // id > 6
  28.       [Op.gte]: 6,               // id >= 6
  29.       [Op.lt]: 10,               // id < 10
  30.       [Op.lte]: 10,              // id <= 10
  31.       [Op.ne]: 20,               // id != 20
  32.       [Op.between]: [6, 10],     // BETWEEN 6 AND 10
  33.       [Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
  34.       [Op.in]: [1, 2],           // IN [1, 2]
  35.       [Op.notIn]: [1, 2],        // NOT IN [1, 2]
  36.       [Op.like]: '%hat',         // LIKE '%hat'
  37.       [Op.notLike]: '%hat',       // NOT LIKE '%hat'
  38.       [Op.iLike]: '%hat',         // ILIKE '%hat' (case insensitive)  (PG only)
  39.       [Op.notILike]: '%hat',      // NOT ILIKE '%hat'  (PG only)
  40.       [Op.overlap]: [1, 2],       // && [1, 2] (PG array overlap operator)
  41.       [Op.contains]: [1, 2],      // @> [1, 2] (PG array contains operator)
  42.       [Op.contained]: [1, 2],     // <@ [1, 2] (PG array contained by operator)
  43.       [Op.any]: [2,3]            // ANY ARRAY[2, 3]::INTEGER (PG only)
  44.     },
  45.     status: {
  46.       [Op.not]: false           // status NOT FALSE
  47.     }
  48.   }
  49. })

Complex filtering / OR / NOT queries

It's possible to do complex where queries with multiple levels of nested AND, OR and NOT conditions. In order to do that you can use or, and or not Operators:

  1. Project.findOne({
  2.   where: {
  3.     name: 'a project',
  4.     [Op.or]: [
  5.       { id: [1,2,3] },
  6.       { id: { [Op.gt]: 10 } }
  7.     ]
  8.   }
  9. })
  11. Project.findOne({
  12.   where: {
  13.     name: 'a project',
  14.     id: {
  15.       [Op.or]: [
  16.         [1,2,3],
  17.         { [Op.gt]: 10 }
  18.       ]
  19.     }
  20.   }
  21. })

Both pieces of code will generate the following:

  1. SELECT *
  2. FROM `Projects`
  3. WHERE (
  4.   `Projects`.`name` = 'a project'
  5.    AND (`Projects`.`id` IN (1,2,3) OR `Projects`.`id` > 10)
  6. )
  7. LIMIT 1;

not example:

  1. Project.findOne({
  2.   where: {
  3.     name: 'a project',
  4.     [Op.not]: [
  5.       { id: [1,2,3] },
  6.       { array: { [Op.contains]: [3,4,5] } }
  7.     ]
  8.   }
  9. });

Will generate:

  1. SELECT *
  2. FROM `Projects`
  3. WHERE (
  4.   `Projects`.`name` = 'a project'
  5.    AND NOT (`Projects`.`id` IN (1,2,3) OR `Projects`.`array` @> ARRAY[3,4,5]::INTEGER[])
  6. )
  7. LIMIT 1;

Manipulating the dataset with limit, offset, order and group

To get more relevant data, you can use limit, offset, order and grouping:

  1. // limit the results of the query
  2. Project.findAll({ limit: 10 })
  4. // step over the first 10 elements
  5. Project.findAll({ offset: 10 })
  7. // step over the first 10 elements, and take 2
  8. Project.findAll({ offset: 10, limit: 2 })

The syntax for grouping and ordering are equal, so below it is only explained with a single example for group, and the rest for order. Everything you see below can also be done for group

  1. Project.findAll({order: 'title DESC'})
  2. // yields ORDER BY title DESC
  4. Project.findAll({group: 'name'})
  5. // yields GROUP BY name

Notice how in the two examples above, the string provided is inserted verbatim into the query, i.e. column names are not escaped. When you provide a string to order/group, this will always be the case. If you want to escape column names, you should provide an array of arguments, even though you only want to order/group by a single column

  1. something.findOne({
  2.   order: [
  3.     // will return `name`
  4.     ['name'],
  5.     // will return `username` DESC
  6.     ['username', 'DESC'],
  7.     // will return max(`age`)
  8.     sequelize.fn('max', sequelize.col('age')),
  9.     // will return max(`age`) DESC
  10.     [sequelize.fn('max', sequelize.col('age')), 'DESC'],
  11.     // will return otherfunction(`col1`, 12, 'lalala') DESC
  12.     [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],
  13.     // will return otherfunction(awesomefunction(`col`)) DESC, This nesting is potentially infinite!
  14.     [sequelize.fn('otherfunction', sequelize.fn('awesomefunction', sequelize.col('col'))), 'DESC']
  15.   ]
  16. })

To recap, the elements of the order/group array can be the following:

  • String - will be quoted
  • Array - first element will be quoted, second will be appended verbatim
  • Object -
    • Raw will be added verbatim without quoting
    • Everything else is ignored, and if raw is not set, the query will fail
  • Sequelize.fn and Sequelize.col returns functions and quoted column names

Raw queries

Sometimes you might be expecting a massive dataset that you just want to display, without manipulation. For each row you select, Sequelize creates an instance with functions for update, delete, get associations etc. If you have thousands of rows, this might take some time. If you only need the raw data and don't want to update anything, you can do like this to get the raw data.

  1. // Are you expecting a massive dataset from the DB,
  2. // and don't want to spend the time building DAOs for each entry?
  3. // You can pass an extra query option to get the raw data instead:
  4. Project.findAll({ where: { ... }, raw: true })

count - Count the occurrences of elements in the database

There is also a method for counting database objects:

  1. Project.count().then(c => {
  2.   console.log("There are " + c + " projects!")
  3. })
  5. Project.count({ where: {'id': {[Op.gt]: 25}} }).then(c => {
  6.   console.log("There are " + c + " projects with an id greater than 25.")
  7. })

max - Get the greatest value of a specific attribute within a specific table

And here is a method for getting the max value of an attribute:f

  1. /*
  2.   Let's assume 3 person objects with an attribute age.
  3.   The first one is 10 years old,
  4.   the second one is 5 years old,
  5.   the third one is 40 years old.
  6. */
  7. Project.max('age').then(max => {
  8.   // this will return 40
  9. })
  11. Project.max('age', { where: { age: { [Op.lt]: 20 } } }).then(max => {
  12.   // will be 10
  13. })

min - Get the least value of a specific attribute within a specific table

And here is a method for getting the min value of an attribute:

  1. /*
  2.   Let's assume 3 person objects with an attribute age.
  3.   The first one is 10 years old,
  4.   the second one is 5 years old,
  5.   the third one is 40 years old.
  6. */
  7. Project.min('age').then(min => {
  8.   // this will return 5
  9. })
  11. Project.min('age', { where: { age: { [Op.gt]: 5 } } }).then(min => {
  12.   // will be 10
  13. })

sum - Sum the value of specific attributes

In order to calculate the sum over a specific column of a table, you canuse the sum method.

  1. /*
  2.   Let's assume 3 person objects with an attribute age.
  3.   The first one is 10 years old,
  4.   the second one is 5 years old,
  5.   the third one is 40 years old.
  6. */
  7. Project.sum('age').then(sum => {
  8.   // this will return 55
  9. })
  11. Project.sum('age', { where: { age: { [Op.gt]: 5 } } }).then(sum => {
  12.   // will be 50
  13. })

Eager loading

When you are retrieving data from the database there is a fair chance that you also want to get associations with the same query - this is called eager loading. The basic idea behind that, is the use of the attribute include when you are calling find or findAll. Lets assume the following setup:

  1. const User = sequelize.define('user', { name: Sequelize.STRING })
  2. const Task = sequelize.define('task', { name: Sequelize.STRING })
  3. const Tool = sequelize.define('tool', { name: Sequelize.STRING })
  5. Task.belongsTo(User)
  6. User.hasMany(Task)
  7. User.hasMany(Tool, { as: 'Instruments' })
  9. sequelize.sync().then(() => {
  10.   // this is where we continue ...
  11. })

OK. So, first of all, let's load all tasks with their associated user.

  1. Task.findAll({ include: [ User ] }).then(tasks => {
  2.   console.log(JSON.stringify(tasks))
  4.   /*
  5.     [{
  6.       "name": "A Task",
  7.       "id": 1,
  8.       "createdAt": "2013-03-20T20:31:40.000Z",
  9.       "updatedAt": "2013-03-20T20:31:40.000Z",
  10.       "userId": 1,
  11.       "user": {
  12.         "name": "John Doe",
  13.         "id": 1,
  14.         "createdAt": "2013-03-20T20:31:45.000Z",
  15.         "updatedAt": "2013-03-20T20:31:45.000Z"
  16.       }
  17.     }]
  18.   */
  19. })

Notice that the accessor (the User property in the resulting instance) is singular because the association is one-to-something.

Next thing: Loading of data with many-to-something associations!

  1. User.findAll({ include: [ Task ] }).then(users => {
  2.   console.log(JSON.stringify(users))
  4.   /*
  5.     [{
  6.       "name": "John Doe",
  7.       "id": 1,
  8.       "createdAt": "2013-03-20T20:31:45.000Z",
  9.       "updatedAt": "2013-03-20T20:31:45.000Z",
  10.       "tasks": [{
  11.         "name": "A Task",
  12.         "id": 1,
  13.         "createdAt": "2013-03-20T20:31:40.000Z",
  14.         "updatedAt": "2013-03-20T20:31:40.000Z",
  15.         "userId": 1
  16.       }]
  17.     }]
  18.   */
  19. })

Notice that the accessor (the Tasks property in the resulting instance) is plural because the association is many-to-something.

If an association is aliased (using the as option), you must specify this alias when including the model. Notice how the user's Tools are aliased as Instruments above. In order to get that right you have to specify the model you want to load, as well as the alias:

  1. User.findAll({ include: [{ model: Tool, as: 'Instruments' }] }).then(users => {
  2.   console.log(JSON.stringify(users))
  4.   /*
  5.     [{
  6.       "name": "John Doe",
  7.       "id": 1,
  8.       "createdAt": "2013-03-20T20:31:45.000Z",
  9.       "updatedAt": "2013-03-20T20:31:45.000Z",
  10.       "Instruments": [{
  11.         "name": "Toothpick",
  12.         "id": 1,
  13.         "createdAt": null,
  14.         "updatedAt": null,
  15.         "userId": 1
  16.       }]
  17.     }]
  18.   */
  19. })

You can also include by alias name by specifying a string that matches the association alias:

  1. User.findAll({ include: ['Instruments'] }).then(users => {
  2.   console.log(JSON.stringify(users))
  4.   /*
  5.     [{
  6.       "name": "John Doe",
  7.       "id": 1,
  8.       "createdAt": "2013-03-20T20:31:45.000Z",
  9.       "updatedAt": "2013-03-20T20:31:45.000Z",
  10.       "Instruments": [{
  11.         "name": "Toothpick",
  12.         "id": 1,
  13.         "createdAt": null,
  14.         "updatedAt": null,
  15.         "userId": 1
  16.       }]
  17.     }]
  18.   */
  19. })
  21. User.findAll({ include: [{ association: 'Instruments' }] }).then(users => {
  22.   console.log(JSON.stringify(users))
  24.   /*
  25.     [{
  26.       "name": "John Doe",
  27.       "id": 1,
  28.       "createdAt": "2013-03-20T20:31:45.000Z",
  29.       "updatedAt": "2013-03-20T20:31:45.000Z",
  30.       "Instruments": [{
  31.         "name": "Toothpick",
  32.         "id": 1,
  33.         "createdAt": null,
  34.         "updatedAt": null,
  35.         "userId": 1
  36.       }]
  37.     }]
  38.   */
  39. })

When eager loading we can also filter the associated model using where. This will return all Users in which the where clause of Tool model matches rows.

  1. User.findAll({
  2.     include: [{
  3.         model: Tool,
  4.         as: 'Instruments',
  5.         where: { name: { [Op.like]: '%ooth%' } }
  6.     }]
  7. }).then(users => {
  8.     console.log(JSON.stringify(users))
  10.     /*
  11.       [{
  12.         "name": "John Doe",
  13.         "id": 1,
  14.         "createdAt": "2013-03-20T20:31:45.000Z",
  15.         "updatedAt": "2013-03-20T20:31:45.000Z",
  16.         "Instruments": [{
  17.           "name": "Toothpick",
  18.           "id": 1,
  19.           "createdAt": null,
  20.           "updatedAt": null,
  21.           "userId": 1
  22.         }]
  23.       }],
  25.       [{
  26.         "name": "John Smith",
  27.         "id": 2,
  28.         "createdAt": "2013-03-20T20:31:45.000Z",
  29.         "updatedAt": "2013-03-20T20:31:45.000Z",
  30.         "Instruments": [{
  31.           "name": "Toothpick",
  32.           "id": 1,
  33.           "createdAt": null,
  34.           "updatedAt": null,
  35.           "userId": 1
  36.         }]
  37.       }],
  38.     */
  39.   })

When an eager loaded model is filtered using include.where then include.required is implicitly set totrue. This means that an inner join is done returning parent models with any matching children.

Top level where with eagerly loaded models

To move the where conditions from an included model from the ON condition to the top level WHERE you can use the '$nested.column$' syntax:

  1. User.findAll({
  2.     where: {
  3.         '$Instruments.name$': { [Op.iLike]: '%ooth%' }
  4.     },
  5.     include: [{
  6.         model: Tool,
  7.         as: 'Instruments'
  8.     }]
  9. }).then(users => {
  10.     console.log(JSON.stringify(users));
  12.     /*
  13.       [{
  14.         "name": "John Doe",
  15.         "id": 1,
  16.         "createdAt": "2013-03-20T20:31:45.000Z",
  17.         "updatedAt": "2013-03-20T20:31:45.000Z",
  18.         "Instruments": [{
  19.           "name": "Toothpick",
  20.           "id": 1,
  21.           "createdAt": null,
  22.           "updatedAt": null,
  23.           "userId": 1
  24.         }]
  25.       }],
  27.       [{
  28.         "name": "John Smith",
  29.         "id": 2,
  30.         "createdAt": "2013-03-20T20:31:45.000Z",
  31.         "updatedAt": "2013-03-20T20:31:45.000Z",
  32.         "Instruments": [{
  33.           "name": "Toothpick",
  34.           "id": 1,
  35.           "createdAt": null,
  36.           "updatedAt": null,
  37.           "userId": 1
  38.         }]
  39.       }],
  40.     */

Including everything

To include all attributes, you can pass a single object with all: true:

  1. User.findAll({ include: [{ all: true }]});

Including soft deleted records

In case you want to eager load soft deleted records you can do that by setting include.paranoid to false

  1. User.findAll({
  2.     include: [{
  3.         model: Tool,
  4.         where: { name: { [Op.like]: '%ooth%' } },
  5.         paranoid: false // query and loads the soft deleted records
  6.     }]
  7. });

Ordering Eager Loaded Associations

In the case of a one-to-many relationship.

  1. Company.findAll({ include: [ Division ], order: [ [ Division, 'name' ] ] });
  2. Company.findAll({ include: [ Division ], order: [ [ Division, 'name', 'DESC' ] ] });
  3. Company.findAll({
  4.   include: [ { model: Division, as: 'Div' } ],
  5.   order: [ [ { model: Division, as: 'Div' }, 'name' ] ]
  6. });
  7. Company.findAll({
  8.   include: [ { model: Division, as: 'Div' } ],
  9.   order: [ [ { model: Division, as: 'Div' }, 'name', 'DESC' ] ]
  10. });
  11. Company.findAll({
  12.   include: [ { model: Division, include: [ Department ] } ],
  13.   order: [ [ Division, Department, 'name' ] ]
  14. });

In the case of many-to-many joins, you are also able to sort by attributes in the through table.

  1. Company.findAll({
  2.   include: [ { model: Division, include: [ Department ] } ],
  3.   order: [ [ Division, DepartmentDivision, 'name' ] ]
  4. });

Nested eager loading

You can use nested eager loading to load all related models of a related model:

  1. User.findAll({
  2.   include: [
  3.     {model: Tool, as: 'Instruments', include: [
  4.       {model: Teacher, include: [ /* etc */]}
  5.     ]}
  6.   ]
  7. }).then(users => {
  8.   console.log(JSON.stringify(users))
  10.   /*
  11.     [{
  12.       "name": "John Doe",
  13.       "id": 1,
  14.       "createdAt": "2013-03-20T20:31:45.000Z",
  15.       "updatedAt": "2013-03-20T20:31:45.000Z",
  16.       "Instruments": [{ // 1:M and N:M association
  17.         "name": "Toothpick",
  18.         "id": 1,
  19.         "createdAt": null,
  20.         "updatedAt": null,
  21.         "userId": 1,
  22.         "Teacher": { // 1:1 association
  23.           "name": "Jimi Hendrix"
  24.         }
  25.       }]
  26.     }]
  27.   */
  28. })

This will produce an outer join. However, a where clause on a related model will create an inner join and return only the instances that have matching sub-models. To return all parent instances, you should add required: false.

  1. User.findAll({
  2.   include: [{
  3.     model: Tool,
  4.     as: 'Instruments',
  5.     include: [{
  6.       model: Teacher,
  7.       where: {
  8.         school: "Woodstock Music School"
  9.       },
  10.       required: false
  11.     }]
  12.   }]
  13. }).then(users => {
  14.   /* ... */
  15. })

The query above will return all users, and all their instruments, but only those teachers associated with Woodstock Music School.

Include all also supports nested loading:

  1. User.findAll({ include: [{ all: true, nested: true }]});