Sequelize Aggregations

Pre-requisites:

Basic knowledge of Sequelize and nodejs

What are database aggregations?

Let’s say that you have a database of students and you want to find the number of students in it, or the student with maximum/minimum marks. You will use aggregations to help you. Sequelize provides inbuilt functions to handle this.

Let’s set up the project.

1. Create an empty folder.

2. Run the command npm init.

3. Install express, mysql2, Sequelize, and sequelize-cli using the following command.

npm i express mysql2 sequelize sequelize-cli

In the config/config.js file, you have to change the value in the development key like this.

Make sure to enter the username, password, and database name according to your system. Let’s start with the model now.
Create a file called students.js in the models’ folder. And paste the following code into it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
module.exports = (sequelize, DataTypes) => {
  const Student = sequelize.define(
    'Student', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      name: {
        type: DataTypes.STRING,
      },
      marks: {
        type: DataTypes.INTEGER,
      },
    },
  );
  return Student;
};

We have a student with a name and marks. Now put the following code in your app.js file in the root folder.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
const express = require("express");
const {
  sequelize
} = require("./models");
const app = express();
const db = require("./models");
app.use(express.json());
app.use(express.urlencoded({
  extended: false
}));

db.sequelize
  .sync()
  .then((result) => {
    app.listen(3000, () => {
      console.log("Server started");
    });
  })
  .catch((err) => {
    console.log(err);
  });

This is code to set up a server and import the Sequelize models. If you run it now using nodemon app.js you should see this.

A student table has been created. Now let’s create an API to insert data into this table.

1
2
3
4
5
6
7
8
9
app.post('/', async (req, res) => {
  try {
    const data = req.body;
    const student = await db.Student.bulkCreate(data);
    res.send(student);
  } catch (err) {
    res.send(err);
  }
})

It will take an array of JSON objects in the body and add them to the students’ table, with the help of bulkCreate method.

Open up the postman and make a post request to http://localhost:3000/ with the following body.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[
  {
    "name": "Srajan Kishor Gupta",
    "marks": "89"
    },
  {
    "name": "Kamlendra Kumar Rathour",
    "marks": "93"
    },
  {
    "name": "Nikhil Kumar Singh",
    "marks": "92"
    },
  {
    "name": "Ubaid Ahmed",
    "marks": "92"
    }
]

The following has been stored in our MYSQL table.

Count/MAX/MIN/SUM functions:

Let’s say you now want to find the number of students in your table. This is where aggregation comes in. In Sequelize, it is done using the fn method. Here it is counting the number of occurrences of id, which is part of each student entity.

1
2
3
4
5
6
7
8
9
10
11
app.get("/", async (req, res) => {
  try {
    const student = await db.Student.findOne({
      attributes: [sequelize.fn("COUNT", sequelize.col("id"))],
      raw: true
    });
    res.send(student);
  } catch (err) {
    res.send(err);
  }
});

The response will look like this.

1
2
3
{
  "COUNT(`id`)": 4
}

Also, you can replace the COUNT with MAX to find the maximum id. Let’s replace id with marks to find the maximum marks, like this.

1
2
3
4
5
6
7
8
9
10
11
app.get("/", async (req, res) => {
  try {
    const student = await db.Student.findOne({
      attributes: [sequelize.fn("MAX", sequelize.col("marks"))],
      raw: true
    });
    res.send(student);
  } catch (err) {
    res.send(err);
  }
});

We get the following output:

1
2
3
{
  "MAX(`marks`)": 93
}

Similarly, to find the minimum, you can do as follows:

1
2
3
4
5
6
7
8
9
10
11
app.get("/", async (req, res) => {
  try {
    const student = await db.Student.findOne({
      attributes: [sequelize.fn("MIN", sequelize.col("marks"))],
      raw: true
    });
    res.send(student);
  } catch (err) {
    res.send(err);
  }
});

We get the following output:

1
2
3
{
  "MAX(`marks`)": 89
}

Let’s now get the sum of the marks of students. To do that replace min with SUM.

1
2
3
4
5
6
7
8
9
10
11
app.get("/", async (req, res) => {
  try {
    const student = await db.Student.findOne({
      attributes: [sequelize.fn("SUM", sequelize.col("marks"))],
      raw: true
    });
    res.send(student);
  } catch (err) {
    res.send(err);
  }
});

Here’s the output:

1
2
3
{
  "SUM(`marks`)": "366"
}

These are very basic aggregation functions. Let’s find the student who has these maximum marks. It can be done in the following way.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
app.get("/", async (req, res) => {
  try {
    const student = await db.Student.findOne({
      attributes: [sequelize.fn("MAX", sequelize.col("marks"))],
      raw: true
    });
    const s = await db.Student.findAll({
      where: {
        marks: Object.values(student)
      }
    })
    res.send(s);
  } catch (err) {
    res.send(err);
  }
});

We can also figure out the average of the marks, like this.

1
2
3
4
5
6
7
8
9
10
11
app.get("/", async (req, res) => {
  try {
    const student = await db.Student.findOne({
      attributes: [sequelize.fn("AVG", sequelize.col("marks"))],
      raw: true
    });
    res.send(student);
  } catch (err) {
    res.send(err);
  }
});

1
2
3
{
  "AVG(`marks`)": "91.5000"
}

More complicated functions:

Let’s try something more complicated, like finding everyone with a name longer than fifteen characters.

1
2
3
4
5
6
7
8
9
10
11
12
app.get("/", async (req, res) => {
  try {
    const student = await db.Student.findAll({
      where: sequelize.where(sequelize.fn('char_length', sequelize.col('name')), {
        [Op.gt]: 15
      }),
    });
    res.send(student);
  } catch (err) {
    res.send(err);
  }
});

Here, we are checking the character length for the column name. And we are checking whether this is greater than fifteen (Op.gt: 15).

Let’s say we want to count how many students scored more than ninety.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
app.get("/", async (req, res) => {
  try {
    const student = await db.Student.count({
      where: {
        marks: {
            [Op.gt]: 90
        }
      }
    });
    res.send(`{student} students have marks more than 90`)
  } catch (err) {
    res.send(err);
  }
});

Our output will be like this:

3 students have marks more than 90

These are some basic ways of aggregation functions. A lot more of these functions can be found on the Sequelize official docs, but all will work in the above-mentioned way.

Using limit and offset:

What if you want to see data in chunks of two? You can set a limit of two and an offset as well. Offset tells how many initial entries will be skipped.

1
2
3
4
5
6
7
8
9
10
11
app.get("/", async (req, res) => {
  try {
    const student = await db.Student.findAll({
      limit: 1,
      offset: 2
    })
    res.send(student);
  } catch (err) {
    res.send(err);
  }
});

The first two entries will be skipped because the offset is two and only one entry will be fetched, which is the third.