NodeJS MySQL pagination example for beginners with source code

NodeJS MySQL pagination example

NodeJS is a popular open source platform used for creating web applications. NodeJS is based on Google chrome’s V8 JavaScript engine. It uses JavaScript as server side language and has non blocking I/O, event driven architecture. Top companies like GoDaddy, Groupon, IBM, LinkedIn, Microsoft,  Netflix and PayPal etc use NodeJS. In this tutorial we are going to learn about NodeJS MySQL pagination.

 

 

1. Create a database table tbl_products and insert data.

2. Install NodeJS and generate a skeleton using express generator.

3. Install mysql module to access MySQL database and fetch records using NodeJS.

4. Create a NodeJS route file to fetch records and add pagination code.

5. Display records with pagination in a Jade based template file.

Create a database table and insert data

Open PhpMyAdmin and run queries in SQL tab.

CREATE DATABASE onlinestore;

CREATE TABLE `tbl_products` (

  `id` int(11) NOT NULL,
  `product_name` varchar(500) NOT NULL,
  `price` varchar(500) NOT NULL,
  `category` varchar(500) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tbl_products` (`id`, `product_name`, `price`, `category`) VALUES

(1, 'Samsung Galaxy S7 Edge', '$600', 'Mobile Phone'),
(2, 'Google nexus', '$450', 'Mobile Phone'),
(3, 'Apple IPhone 6', '$630', 'Mobile Phone'),
(4, 'Sony Vio', '$1200', 'Laptop'),
(5, 'Samsung T.V', '$900', 'T.V'),
(6, 'Apple IPAD', '$710', 'Tablet'),
(7, 'MacBook Pro', '$1000', 'Laptop'),
(8, 'Dell Laptop', '$950', 'Laptop'),
(9, 'Canon EOS 700D DSLR Camera', '$550', 'Camera'),
(10, 'Nikon D7100 DSLR Camera ', '$670', 'Camera'),
(11, 'Nokia Lumia 930', '349', 'Mobile Phone'),
(12, 'HTC Phone', '123', 'Mobile Phone'),
(13, 'LG Monitor', '210', 'Electronics'),
(14, 'Samsung Printer', '120', 'Electronics'),
(15, 'Samsung Gear Live Black - Made for Android', '125', 'Smart Watch'),
(16, 'Apple Watch', '250', 'Smart Watch');

ALTER TABLE `tbl_products`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `tbl_products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

nodejs mysql pagination exmaple table tecords

Install NodeJS

Please visit NodeJS Website and download NodeJS installer for Windows or Mac. After installing NodeJS open command line. Go to the location where you would like to create your project.

Install Express and generate application architecture

In command line type the command below npm install express-generator -g. express framework is installed. Now using express we are going to create and generate an application skeleton. Run the command below. $ express –view=jade paginationapp.

nodejs-mysql-pagination-express-generated-app-skeleton

This will create a NodeJS application structure in a directory paginationapp. Jade template engine is used in view files.

nodejs-mysql-pagination

Install dependencies using NPM

Go into the directory using command cd paginationapp. Now type npm install. This will install dependencies required in package.JSON file.

{
  "name": "paginationapp",
  "version": "0.0.0",
  "private": true,
  "scripts": {
    "start": "node ./bin/www"
  },
  "dependencies": {
    "body-parser": "~1.16.0",
    "cookie-parser": "~1.4.3",
    "debug": "~2.6.0",
    "express": "~4.14.1",
    "jade": "~1.11.0",
    "morgan": "~1.7.0",
    "mysql": "^2.13.0",
    "serve-favicon": "~2.3.2"
  }
}

Create NodeJS web server and run application

To run application type $ DEBUG=paginationapp:* npm start. This command starts  a node server on port 3000. Open browser and type http://localhost:3000/. You can see the website running.

Install NodeJS MySQL driver  to access database

Open command prompt, go to project folder and type following command npm install mysql. This command will install mysql driver for NodeJS.

Fetch records from database using NodeJS

Open index.js file in routes folder.  Add following code.

var express    = require('express');
var router     = express.Router();
var mysql      = require('mysql');

var connection = mysql.createConnection({

  host     : 'localhost',
  user     : 'root',
  password : 'root',
  database : 'test',
  socketPath: '/Applications/MAMP/tmp/mysql/mysql.sock'

});

var totalRec = 0,

pageSize  = 6,

pageCount = 0;

var start       = 0;

var currentPage = 1;

var title  = 'NodeJs MySQL pagination example';

connection.connect(function(err){ if(err){ 
  console.log(err);
  console.log("Error connecting database"); } });
/* GET home page. */

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

  var countSql = 'SELECT count(*) as numrows FROM tbl_products ';

  connection.query( countSql, function(err, countrows, fields) {
        if (err) throw err;
   
       totalRec      = countrows[0]['numrows'];

        pageCount     =  Math.ceil(totalRec /  pageSize);
  
      if (typeof req.query.page !== 'undefined') {

            currentPage = req.query.page;

   }
    
     if(currentPage >1){
     
       start = (currentPage - 1) * pageSize;
    }
    
var sql  = 'SELECT * from tbl_products LIMIT '+start+' ,'+pageSize+' ';
    connection.query( sql, function(err, data, fields) {

        if (err) throw err;

         res.render('index', { data: data, pageSize: pageSize, pageCount: pageCount,currentPage: currentPage});

      });

    });

  });
module.exports = router;

NodeJS MySQL pagination code explanation

Code below includes express and NodeJS MySQL driver.

var express    = require('express');
var router     = express.Router();
var mysql      = require('mysql');

NodeJS connection to MySQL database

In order to make a connection to MySQL database using NodeJS we use mysql library’s createConnection method.

var connection = mysql.createConnection({

  host     : 'localhost',
  user     : 'root',
  password : 'root',
  database : 'test',
  socketPath: '/Applications/MAMP/tmp/mysql/mysql.sock'

});
createConnection method accepts four parameters such as host, username, password, database name and optionally socket path.

NodeJS MySQL pagination basic parameters

Assign values to basic parameters  to be used in pagination.

var totalRec = 0,

pageSize  = 6,

pageCount = 0;

var start       = 0;

var currentPage = 1;

var title  = 'NodeJs MySQL pagination example';

NodeJS Router and Fetch records from database

Next you can see a route ‘/’ is defined and in anonymous function  request, response and next parameters are passed. To perform NodeJS MySQL pagination we get count of total records first. Page count is calculated by dividing total records by records to be displayed on one page. Like If we have 80 records in database and want to display 10 records on each page then there will be total of 80/10 = 8 pages.

When next link on page or any page is clicked that page is displayed and page number is also passed in browser as URL parameter. req.query.page is used to get page number from URL.

connection .query method of mysql driver executes the query. If some error occurs, an error is thrown otherwise index.jade page from views directory is displayed. Data and pagination parameters are passed to Jade template page.

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

  var countSql = 'SELECT count(*) as numrows FROM tbl_products ';

  connection.query( countSql, function(err, countrows, fields) {
        if (err) throw err;
   
       totalRec      = countrows[0]['numrows'];

        pageCount     =  Math.ceil(totalRec /  pageSize);
  
      if (typeof req.query.page !== 'undefined') {

            currentPage = req.query.page;

   }
    
     if(currentPage >1){
     
       start = (currentPage - 1) * pageSize;
    }
    
var sql  = 'SELECT * from tbl_products LIMIT '+start+' ,'+pageSize+' ';
    connection.query( sql, function(err, data, fields) {

        if (err) throw err;

         res.render('index', { data: data, pageSize: pageSize, pageCount: pageCount,currentPage: currentPage});

      });

    });

  });
module.exports = router;

Display records on index.JADE template page

To display all records in Jade template file, add following Jade code.

table
      tbody
        tr
          th #
          th Product Name
          th Price
          th Category

        each item in data
         tr
           td #{item['id']}
           td #{item['product_name']}
           td #{item['price']}
           td #{item['category']}

First a table is created with heading tags as product name, price and category. Next in an each loop id, product name, price and category values for all records are displayed.

NodeJS MySQL Pagination code in Jade file

The code below is used to display the pagination in the page.

if pageCount > 1

      ul.pagination

       if currentPage > 1
        li

          a(href='/?page=1')  «

       - var x = 1

       if currentPage > 5

         - x = x + (currentPage - 4)

       if (x !== 1)

           li.disabled
              a(href='#') ...
       - for (x; x <= pageCount; x++)

           if( currentPage == x)

            li.active
               span.sr_only
                   = currentPage

           else
             li
               a(href= "/?page=#{x}" )
                = x
           if x == (currentPage + 4)
               li.disabled
                a(href="#") ...
                - break

        if currentPage != pageCount
          li
            a(href= "/?page=#{Math.floor(pageCount)}" ) &raquo;

nodejs mysql pagination -display records

Summary

Pagination is very important feature in all dynamic web applications. In this article we have seen  a  NodeJS MySQL pagination example. You can find example source code for NodeJS MySQL pagination example below in the link. Please leave you feedback or comments.

Click Here to download example source code.

 

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

About Jason William