How to paginate records in MySQL using Sequelize and Nodejs

Often at times, I find my self struggling with Sequelize to find a direct answer for my query. Recently, I have been working on a fullstack application in which there was a basic requirement of paginating results from backend (REST API) to the frontend. I struggled for two reasons. Firstly, coming from NoSQL background it’s hard to grasp SQL DBs. Second reason being is Sequelize documentation does not provide a clear and direct solution to this very basic abstraction. Lot of people assume things in the world of SQL databases.

Thus, in this post we will be talking about a basic paginating module using Sequelize, MySQL and Node.js. I am using you have some tables and records inside your MySQL database. To setup a new app and making database connection, read my post on Getting started with Sequelize.

I am directly jumping on user model definition:

Copy
1'use strict';
2module.exports = function (sequelize, DataTypes) {
3  var user = sequelize.define(
4    'user',
5    {
6      id: {
7        allowNull: false,
8        autoIncrement: true,
9        primaryKey: true,
10        type: DataTypes.INTEGER
11      },
12      username: DataTypes.INTEGER,
13      first_name: DataTypes.STRING,
14      last_name: DataTypes.STRING,
15      date_of_birth: DataTypes.STRING,
16      created: DataTypes.INTEGER,
17      updated: DataTypes.INTEGER
18    },
19    {
20      timestamps: false,
21      freezeTableName: true,
22      underscore: true
23    }
24  );
25  return user;
26};

I am using that we a table that contains hundred of user records that we want to display on an web application, say in the admin panel, and we want to show just 50 records at once.

In the api/user.js I am defining an endpoint /:page that will fetch number of results we need from the database.

Copy
1router.get('/:page', (req, res) => {
2  let limit = 50; // number of records per page
3  let offset = 0;
4  db.user
5    .findAndCountAll()
6    .then(data => {
7      let page = req.params.page; // page number
8      let pages = Math.ceil(data.count / limit);
9      offset = limit * (page - 1);
10      db.user
11        .findAll({
12          attributes: ['id', 'first_name', 'last_name', 'date_of_birth'],
13          limit: limit,
14          offset: offset,
15          $sort: { id: 1 }
16        })
17        .then(users => {
18          res
19            .status(200)
20            .json({ result: users, count: data.count, pages: pages });
21        });
22    })
23    .catch(function (error) {
24      res.status(500).send('Internal Server Error');
25    });
26});

findAndCountAll is the model for searching multiple records in the database and it returns both the data required and the count of elements in that table. The above query will get 50 user records at once until the next page is called to fetch the next 50 records. limit and offset are required in queries related to pagination in which limit fetches the number of rows based on the query whereas offset is used to skip the number of rows in the database table.