Introduction
Sequelize is a powerful ORM (Object-Relational Mapper) for Node.js, which allows developers to work with relational databases like MySQL, PostgreSQL, SQLite, and MSSQL through an easy-to-use API. One of its core features is the ability to perform aggregate operations which are commonly used in database queries to calculate a single result from a set of values. In this article, we’ll explore how to use Sequelize to perform aggregation operations such as sum, average, minimum, and maximum on your datasets. We’ll also look at more advanced queries to truly leverage Sequelize’s capabilities.
Get Things Ready
To make sure we have the same starting point, define the User model as follows:
const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('// your database details here');
const User = sequelize.define('User', {
username: DataTypes.STRING,
email: DataTypes.STRING,
age: DataTypes.INTEGER
});Summing Values with Sequelize
Let’s start with one of the simplest aggregate operations: sum.
User.sum('age').then(totalAge => {
console.log(`The sum of all users' ages is ${totalAge}.`);
});The sum method will total up all the ages and return it, in this case logging it to the console.
Calculating the Average Value
User.average('age').then(avgAge => {
console.log(`The average age of users is ${avgAge}.`);
});The average method calculates the average of all the ages. Note that the correct method is actually findAll with attributes using avg as shown below.
User.findAll({
attributes: [[sequelize.fn('AVG', sequelize.col('age')), 'avgAge']]
}).then(result => {
const avgAge = result[0].getDataValue('avgAge');
console.log(`The average age of users is ${avgAge}.`);
});Finding the Minimum and Maximum Values
Similarly, we can use Sequelize to find the minimum or maximum of a given column.
User.min('age').then(minAge => {
console.log(`The youngest user is ${minAge} years old.`);
});
User.max('age').then(maxAge => {
console.log(`The oldest user is ${maxAge} years old.`);
});More Complex Queries with Grouping
When it’s necessary to categorize aggregate data, the group option comes into play.
User.findAll({
attributes: [
'gender',
[sequelize.fn('COUNT', sequelize.col('gender')), 'count'],
[sequelize.fn('AVG', sequelize.col('age')), 'average_age'],
],
group: ['gender']
}).then(results => {
results.forEach(result => {
console.log(
`${result.gender}: Average age is ${result.average_age}. Count: ${result.count}`
);
});
});Here, users are grouped by gender, and the count and average age for each gender are calculated.
Working with Having Clauses
HAVING clauses in SQL are analogous to WHERE clauses, but for grouped data. Sequelize also supports this.
User.findAll({
attributes: [
'country',
[sequelize.fn('SUM', sequelize.col('age')), 'total_age'],
],
group: 'country',
having: sequelize.where(sequelize.fn('SUM', sequelize.col('age')), { [Sequelize.Op.gt]: 100 })
}).then(countries => {
countries.forEach(country => {
console.log(`${country.country}: Total age is ${country.total_age}.`);
});
});Combining Aggregate Functions
It is often useful to combine multiple aggregate operations in a single query. Sequelize can do this easily:
User.findAll({
attributes: [
[sequelize.fn('MIN', sequelize.col('age')), 'min_age'],
[sequelize.fn('MAX', sequelize.col('age')), 'max_age'],
[sequelize.fn('AVG', sequelize.col('age')), 'average_age'],
]
}).then(stats => {
const statsValues = stats[0].get();
console.log(
`Minimum age: ${statsValues.min_age}, `
+ `Maximum age: ${statsValues.max_age}, `
+ `Average age: ${statsValues.average_age}`
);
});Conclusion
In this article, we have looked at how to perform various aggregate operations in Sequelize.js including sum, average, minimum, and maximum. We’ve moved through several examples from basic summation to more complex queries involving grouping and having clauses. With apt knowledge of aggregations, Sequelize allows you to create highly performant and often complex queries with ease, making it a powerful tool for any Node.js developer working with SQL databases.
