Custom Pagination In Nodejs With Mysql in ejs view engine

In this tutorial we will learn how to set custom pagination in nodejs with mysql database. Without express-pagination we can create our own pagination. Here is the complete code for pagination in nodejs and mysql.
Firstly need to create table in mysql database which name is employee.

Table Employee:

--
-- Table structure for table ` employee`
--

CREATE TABLE ` employee` (
  `employee_id` int(11) NOT NULL,
  `employee_name` varchar(255) NOT NULL,
  `employee_designation` varchar(255) NOT NULL,
  `status` enum('A','IN','S') NOT NULL DEFAULT 'A' COMMENT '''A - Active'',''IN-Inactive'',''S-Suspend'''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE ` employee`
  ADD PRIMARY KEY (`employee_id`);

ALTER TABLE ` employee`
  MODIFY `employee_id` int(11) NOT NULL AUTO_INCREMENT;

For data insertion run below sql command for employee data in your sql console.

INSERT INTO ` employee` (`employee_id`, `employee_name`, `employee_designation`, `status`) VALUES
(1, 'Rakesh', 'Developer', 'A'),
(2, 'Ramesh', 'Project Manager', 'A'),
(3, 'Sandy', 'Sr Developer', 'A'),
(4, 'Mahi', 'Sr Architect', 'A'),
(5, 'Johnson', 'Sales Executive', 'A'),
(6, 'John', 'Sr Manager', 'A'),
(7, 'James', 'Sales Manager', 'A'),
(8, 'Jessy', 'HR', 'A'),
(9, 'Smith', 'Manager', 'A');

In nodejs project need to install nodejs mysql driver. Check below command for install Mysql driver using npm.
>npm install mysql
Create connection pool for connect mysql with nodejs for database operations. We will create db.js file on root directory and put below code snip in /db.js.

MYSQL Connection Pool

/db.js

var mysql = require('mysql');
var connection = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'tmr'  
});
module.exports = connection;

Now we will create models file for write database operations query to fetch all employee record and get count of total number of employee. In /models/Employee.js put below code in this created file.

In Model SQL Query For Count And Fetch Record

var db = require('../db');

var Employee = {
  getAllEmployee : function(Employee,callback) {
    db.query("select * from employee Limit ?,?",[Employee.offset,Employee.limit],callback);
  },
  employeeCount : function(callback) {
    db.query("select count(1) from employee", callback);
  }
}

module.exports = Employee;

In the above code we included db.js file and create object db for connect mysql and export module as Employee with function for count and get all employee list from database.
Now we will create route file inside routes directory which is inside /routes directory and insert some configuration for employee listing inside app.js configured file.
In /app.js

Server File Configuration (app.js)

var employee = require('./routes/employee');
var app = express();
app.use('/employee',employee);

Below I have write code for employee data listing with pagination. For listing employee data include Employee model for fetch record according to page wise. For pagination set all variables with its initial values.
routes/employee.js

Code for Pagination Setup in nodejs Route File

// routes/employee.js
var express = require('express');
var router = express.Router();
var Employee = require('../models/Employee');

//Difine variable for Pagination
var totalEmployee = 0;
var pageSize = 2;
var pageCount = 0;
var start = 0;
var currentPage = 1;

router.get('/',function(req,res,next){

  Employee.employeeCount(function(err,result) {
    if(err) throw err;
    totalEmployee = result[0].total_emp;
    pageCount = Math.ceil(totalEmployee/pageSize);

    if (typeof req.query.page !== 'undefined') {
      currentPage = req.query.page;
    }
    
    if (parseInt(currentPage)>1) {
      start = (currentPage - 1) * pageSize;
    }

    Employee.getAllEmployee({offset: start,limit : pageSize}, function(err,result){
      if(err){
        res.json(err);
      }else{
        console.log(result);
        res.render('employee/index',{title: 'Employee Listing', employeeData: result, pageCount: pageCount, pageSize: pageSize, currentPage: currentPage});
      }
    });
  });
});

module.exports = router;

In Above code I write logic for pagination and get data from database with its limits. For display employee record write code inside /views/semployee/index.ejs file. below is the complete view code with listing and pagination of employee record.

Code for Display Record with Pagination in view(Ejs File)

/views/semployee/index.ejs
<div class="rows">
  <table class="table">
    <thead>
      <tr>
        <th>S no.</th>
        <th>Employee Name</th>
        <th>Status</th>
      </tr>
    </thead>
    <tbody>
      <% for(var i=0; i < employeeData.length; i++) { %>
      <tr>
        <td><%=(currentPage*pageSize)-(pageSize)+(i+1) %></td>
        <td><%= employeeData[i].employee_name %></td>
        <td><%= employeeData[i].status %></td>
      </tr>
      <% } %>
  </table>
</div>
<!-- pagination start -->
  <div class="rows">
    <% if(pageCount > 1) { %>
      <ul class="pagination">
        <%
        var y = 1;
        if(currentPage>1) {
          y = currentPage-1;
          %>
          <li> <a href="/employee/?page=<%= y %> %>">&laquo;</a> </li>
        <% } %>
        <%  for (y; y <= pageCount; y++) {
          if(currentPage == y) { %>
              <li><span><%= y %></span></li>
          <% }else{ %>
              <li><a href="/employee/?page=<%= y %>"><%= y %></a></li>
          <% }
            if(parseInt(currentPage) + 4 == y) {
              break;
            }
        }  %>
        <% if(currentPage != pageCount) { %>
          <li> <a href="/employee/?page=<%= Math.ceil(pageCount) %>">&raquo;</a> </li>
        <% } %>
      </ul>
    <% } %>
  </div>