Node.js is a runtime environment like Chrome’s V8 JavaScript engine. Node.js is an open-source, cross-platform, and backend runtime environment that executes outside a web browser.
MySQL is an open-source relational database management system that is fast, reliable, flexible, and robust. Both MySQL and Node.js are trendy choices when building web applications. Another advantage of MySQL is its built-in support for utilities like pagination.
What is pagination, and why is it important?
Pagination is nothing but dividing data into discrete chunks or pages. A web page displaying thousands of records divided as pages feels more inviting and interactive and better for the application performance. Pagination thus helps better display, performance, and user experience by not overwhelming the user with data and avoiding long scrolling.
We can do both Client-side Pagination as well as Server-side Pagination. In this article, we will see an example of a Server-side Pagination.
Server-Side Pagination: As per IBM, Server-side Pagination is for:
- Large data set.
- Faster initial page load.
- Accessibility for those not running JavaScript.
- Complex view business logic.
- Resilience to concurrent changes.
Server-side pagination is usually done in the middleware (business logic) code or the database layer. Server-side pagination is usually more challenging than client-side, but it scales better.
Client-side Pagination: Client-side pagination is proper when:
- The Data Set is small.
- Faster subsequent page loads
- Sort & filter requirements are supported fully (unless results greater than max size).
Client-side pagination is faster to implement but is not very scalable.
Steps to do Server-side Pagination?
We are going to look at an example of Server-Side Pagination. We will handle pagination at the database level itself. For pagination using MySQL, we need to use the LIMIT Clause with the Offset value. The Limit Clause retrieves only a part of the records. The basic Syntax of the Limit clause is as follows:
Select <column-names>
From <table-names>
Where <conditions>
LIMIT <offset>, <number_of_records>;
Offset is optional with a default value of 0 but can get any positive value less than the number of records in the dataset.
Example Application: We will build a simple application with Node.js, Express.js, MySQL, and the Sequelize ORM. Ensure that you have Node.js and MySQL on your system. We will be using Visual Studio Code for developing the app.
Create a project folder and change the folder
mkdir PaginationExample
cd PaginationExample
Initialize the app
npm init
To generate the package.json file.
Next, we install express.js, sequelize, and pug for the templates.
npm install -g express sequelize pug dotenv express-paginate
We also require installing the dotenv and the express-paginate package. The Express-paginate package exposes various methods like href and middleware. The details of the functions are given in the documentation.
Our Folder structure is as follows.

Project folder structure
To run the node application, we need to run the following command from the Terminal in Visual Studio Code.
node server.js
If everything runs correctly, you should see an output similar to this in the terminal:

Running application
Once you see this message, you can open a browser and go to the link: localhost:8000
We have structured the calls in the application, and we should directly see a table with records and an option to Paginate.
Application Code: We will see the Application code files layer by layer.
Server.js File: The server.js is the main file that contains all your express-related configurations and our only route for fetching the records and calling the service file. The server file has the following code.
JavaScript
// Required External modules
const express = require("express");
const path = require("path");
require("dotenv").config();
const paginate = require("express-paginate");
// Required code files
const services = require("./service/services.js");
// Application Variables
const app = express();
const port = 8000;
// Server
app.listen(port, () => {
console.log(`App running on port ${port}.`);
});
// Configuration
app.set("views", path.join(__dirname, "./views"));
app.set("view engine", "pug");
app.use("/static", express.static(
path.join(__dirname, "public")));
app.use(paginate.middleware(10, 50));
// Routes
app.get("/", (req, res) => {
const limit = req.query.limit || 10;
const offset = req.offset;
services.findRecords({
offset: offset,
limit: limit
}).then((results) => {
const pageCount = Math.ceil(results.count / limit);
res.render("paginatedTable", {
data: results.rows,
pageCount,
pages: paginate.getArrayPages(req)
(3, pageCount, req.query.page),
});
});
});
Sequelize files: We have separated the database configuration, the Sequelize model, and the call into three separate files to make it easier to maintain as the application scales.
- The Services file holds all our Sequelize calls.
- The models.js holds the table structure we use for our query. We are using the nicer_but_slower_film_list table from the SAKILA database for our example.
- The dbconfig.js file holds the Sequelize object. The entire code related to files is available in this repository.
- Sequelize provides an in-built method: findAndCountAll, which is ideal for pagination. The findAndCountAll method takes the arguments offset and limit and returns the total records available and the actual records based on the limit and offset value. The code is as follows:
dbconfig.js: The dbconfig holds the Sequelize object. The properties for creating the Sequelize object come from the .env file based on your database setup. Here we have created a simple database object.
JavaScript
const Sequelize = require("sequelize");
module.exports = new Sequelize({
dialect: "mysql",
username: process.env.DB_USER,
password: process.env.DB_PASS,
host: process.env.DB_HOST,
port: process.env.DB_PORT,
database: process.env.DB_DATABASE,
logging: (log) => console.log("logging:", log),
});
models.js: The models.js file holds the description of the tables we are using in our queries. It is a Sequelize representation of the relational database tables.
JavaScript
var Sequelize = require("sequelize");
db = require("../config/dbconfig.js");
const nicer_but_slower_film_list = db.define(
"nicer_but_slower_film_list", {
FID: {
type: Sequelize.SMALLINT,
// To ensure that Sequelize
// does not use id by default
primaryKey: true,
},
title: Sequelize.STRING,
description: Sequelize.STRING,
category: Sequelize.STRING,
price: Sequelize.DECIMAL,
length: Sequelize.SMALLINT,
rating: Sequelize.ENUM("G", "PG", "PG-13", "R", "NC-17"),
actors: Sequelize.STRING,
},
{
// This is to ensure that Sequelize
// does not pluralize table names
freezeTableName: true,
// This is to ensure that Sequelize
// does not add its own timestamp
// variables in the query.
timestamps: false,
createdAt: false,
updatedAt: false,
}
);
module.exports = nicer_but_slower_film_list;
services.js: The services file holds the Sequelize calls we perform. This file would hold the calls for search, create, update, delete calls, etc. The file depends on the Sequelize object (dbconfig.js) and the Sequelize model (models.js).
JavaScript
const Sequelize = require("sequelize");
// Model file
var model = require("../models/models.js");
// db Configuration
db = require("../config/dbconfig.js");
let findRecords = async (req, res) => {
return model.findAndCountAll({
offset: req.offset,
limit: req.limit
});
};
module.exports = { findRecords: findRecords };
So if the limit we set is ten and the offset is 20 (that is page 3 records), then the query formed by the findAndCountAll and triggered in the database is:
SELECT `FID`, `title`, `description`, `category`, `price`, `length`, `rating`, `actors` FROM `nicer_but_slower_film_list` AS `nicer_but_slower_film_list` LIMIT 20, 10;
The query gives the following results from the database:
The output shown on the screen is as follows:
Application UI: Besides the files mentioned above, the project structure also has the node_modules folder, the node and express installation files, and the .env file. The .env file contains the database-related information like the Username, Password, MySQL Port number, etc., which we use in the dbconfig.js file to build the Sequelize connection object.
User Interface: To handle the user interface, we are using the PUG template. We use the express-paginate methods to handle pagination controls in the PUG template. The following code handles whether the Previous and the Next button links are shown or not.
paginatedTable.pug: This is the user interface with paginated results.
HTML
html
head
link(rel='stylesheet' href='https://getbootstrap.com/docs/4.4/dist/css/bootstrap.min.css')
style
include ../public/style.css
body
h1 Movies
table
thead
tr
th Title
th Description
th Category
th Length
th Rating
th Actors
tbody
each dat in data
tr
td #{dat.title}
td #{dat.description}
td #{dat.category}
td #{dat.length}
td #{dat.rating}
td #{dat.actors}
if paginate.hasPreviousPages || paginate.hasNextPages(pageCount)
.navigation.well-sm#pagination
ul.pager
if paginate.hasPreviousPages
a(href=paginate.href(true)).prev
i.fa.fa-arrow-circle-left
| Previous
if pages
each page in pages
a.btn.btn-default(href=page.url)= page.number
if paginate.hasNextPages(pageCount)
a(href=paginate.href()).next
| Next
i.fa.fa-arrow-circle-right
script(src='https://code.jquery.com/jquery-3.4.1.slim.min.js')
script(src='https://getbootstrap.com/docs/4.4/dist/js/bootstrap.bundle.min.js')
hasPrevious and hasNext are two methods exposed by the express-paginate package, which return Boolean values. Depending on the value of these Booleans, The UI shows the Next and Previous buttons.

Next and Previous buttons
style.css
The style sheet for the page is as follows:
CSS
table {
width: 100%;
border: 1px solid #fff;
border-collapse: collapse;
border-radius: 8px;
}
th,
td {
text-align: left;
text-transform: capitalize;
border: 1px solid darkgrey;
color: black;
}
th {
padding: 8px 10px;
height: 48px;
background-color: #808e9b;
}
td {
padding: 6px 8px;
height: 40px;
}
a:hover {
background-color: #555;
}
a:active {
background-color: black;
}
a:visited {
background-color: #ccc;
}
How does the application work exactly?
- The first time we hit the URL localhost:8000, because of the express-paginate middleware, the Limit value is set to 10 by default, and the offset is set to 0. Hence the first ten records are retrieved from the database and displayed.
- When the user hits the Next button or page number, i.e., 1,2, or 3, the paginate middleware calculates the offset. The formula to calculate offset is simple:
pageNumber(we see in the URL on the UI) -1 * limit
Where pageNumber starts at 1.
- We can also increase the limit up to 50 records. We cannot increase the limit further than 50 records since we have specified that as the maximum limit in the middleware function. The limit has been set in the server.js file.
app.use(paginate.middleware(10, 50));
Triggering a query like this:: http://localhost:8000/?page=1&limit=500 will not cause an error, but the number of records displayed will still be 50. We can also enhance the functionality to show some messages that only 50 records can be seen at one time.
Summary: This article showed how pagination works with Node.js and MySQL using a sample database from MySQL. We also saw how we could limit the user to see only a set number of records on the page not to cause UI disruptions. The entire code is available on the Github link.
Similar Reads
How to Paginate with Mongoose in Node.js?
When working with large datasets in Node.js applications using Mongoose, fetching and displaying all records at once can lead to performance issues. Pagination is a technique that allows us to break down data into smaller, more manageable manner. In this article, we'll explore how to implement pagin
5 min read
How to do Pagination in Node.js using Sorting Ids ?
Implementing pagination in a Node.js application involves fetching and displaying data in chunks or pages, typically from a database. One common approach to pagination is using sorting IDs, where each record in the dataset is assigned a unique identifier (ID) that determines its position in the sort
4 min read
How to Use Transactions in MySQL with NodeJS?
Transactions in MySQL are used to execute a series of operations as a single unit of work, ensuring that all operations either succeed or fail together. This is crucial in maintaining data integrity, especially when dealing with complex operations that involve multiple database queries. In Node.js,
2 min read
How To Do Pagination In Python
In this article, we'll walk you through the steps of setting up pagination in Python. We'll explain each step clearly and straightforwardly. To help you understand the idea of pagination in Python, we'll show you how to create a pagination system using the Tkinter library. We'll start by explaining
5 min read
How to Add Pagination in HTML Table ?
Pagination is a common feature in web applications, especially when dealing with large datasets. It helps in dividing the data into manageable chunks, improving the user experience by reducing the amount of data displayed at once and speeding up page loading times. In this article, we will discuss t
3 min read
How to Create Pagination in Node.js using Skip and Limit ?
Creating pagination in Node.js using the skip and limit methods. This approach efficiently retrieves specific data subsets from a database, improving performance and user experience by loading content in manageable segments rather than all at once. What is Pagination?Pagination is a very helpful met
4 min read
Node.js Connect Mysql with Node app
Node.js is a powerful platform for building server-side applications, and MySQL is a widely used relational database. Connecting these two can enable developers to build robust, data-driven applications. In this article, we'll explore how to connect a Node.js application with a MySQL database, cover
2 min read
How to Customize Pagination in Next.js ?
In this article, we will learn How we can add customized pagination in the NextJS project using Algolia. NextJS is a React-based framework. It has the power to Develop beautiful Web applications for different platforms like Windows, Linux, and mac. The linking of dynamic paths helps in rendering you
3 min read
Node.js MySQL POSITION() Function
POSITION() Function is a Builtin Function in MySQL which is used to get position of first occurrence of a pattern in a text when searched from specific position. Note: It is not Case Sensitive. Syntax: POSITION(pattern IN text) Parameters: POSITION() function accepts two parameters as mentioned abov
2 min read
How to make a Pagination using HTML and CSS ?
Creating pagination is quite simple, you can easily do that by using Bootstrap, and JavaScript. However, in this article, we will use HTML and CSS to create pagination. Pagination is helpful when the website contains lots of content on a single page, and a single page will not look good with all th
3 min read