How to do pagination Node.js with MySQL ?

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.

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:

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.

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

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.

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

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

style.css

The style sheet for the page is as follows:

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.