How to paginate records in MySQL using Sequelize and Nodejs

Jun 20, 2017

3 min read

#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.

Defining a Model

🔗

I am directly jumping on user model definition:

JavaScript icon

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.

JavaScript icon

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.

Originally Published at Hackernoon.com


Enjoyed this post? Sign up to my newsletter!

A periodic update about my recent blog posts and tutorials. Join 1300+ devs!

No spam, unsubscribe at any time!

Subscribe on Revue

HomeBlogNewsletterAbout meSpeakingEmail

©  2019-2022 Aman Mittal · All Rights Reserved