NodeJS MySQL pagination example for beginners with source code

February 7, 2017 JavaScript, MySQL, MySQL, NodeJS 12 Comments

Spread the love

Last updated:9th March, 2019

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 Node ORM 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;

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.

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

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.

Install NodeJS MySQL ORM module

Type npm install orm. This will install ORM for NodeJS Mysql . Using ORM gives advantages of creating Models, create, get, find, remove, count, aggregated functions.

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 orm         = require('orm');

var totalRec = 0,

pageSize  = 6,

pageCount = 0;

var start       = 0;

var currentPage = 1;

var title  = 'NodeJs MySQL pagination example';

router.use(orm.express("mysql://root:@localhost:/onlinestore", {

  define: function (db, models, next) {

    models.products = db.define("tbl_products", {

    id           : Number,

    product_name : String,

    price        : String,

    category     : String,

  });

  next();

  }

}));

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

  var result = req.models.products.count({

  }, function(error, productsCount){

      if(error) throw error;

       totalRec      = productsCount;

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

            currentPage = req.query.page;

   }
    
     if(currentPage >1){
     
       start = (currentPage - 1) * pageSize;
    }
    

    var result = req.models.products.find({},{limit: pageSize, offset: start}, function(error, products){ 

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

    });
    

  });


});

module.exports = router;

NodeJS MySQL pagination code explanation

Code below includes express and NodeJS ORM module to manipulate database.

var express    = require('express');

var router     = express.Router();

var orm        = require('orm');

NodeJS connection to database and products model creation

In order to make a connection to MySQL database using NodeJS, inside routes.use method, MySQL with database username and database name is passed. Note that there is no password for username root: If your database has a password for the user then add that. Next, a model for products database table is created.

In db.define table name is passed, An object containing table columns names with their data types are passed. Number data type for id, product_name, price and category columns are defined as String and are assigned to models.products.

router.use(orm.express("mysql://root:@localhost:/onlinestore", {

  define: function (db, models, next) {

    models.products = db.define("tbl_products", {

    id           : Number,

    product_name : String,

    price        : String,

    category     : String,

  });

  next();

  }

}));

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.

If we have 80 records in the database and want to display 10 records on each page then there will be total of 80/10 = 8 pages. So using req.models.products.count({} ..), count of records is fetched in productsCount variable and is assigned to totalRec. Note the empty { }, these indicates that there is no condition used (As Where clause in SQL).

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

req.models.products.count( … ) method of ORM driver finds all records, there is no condition passed in { }. Records limit and offset are passed. 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 result = req.models.products.count({


  }, function(error, productsCount){


      if(error) throw error;

       totalRec      = productsCount;

        pageCount     =  Math.ceil(totalRec /  pageSize);

  
      if (typeof req.query.page !== 'undefined') {


            currentPage = req.query.page;


   }
    
     if(currentPage >1){
     
       start = (currentPage - 1) * pageSize;

    }
    

    var result = req.models.products.find({},{limit: pageSize, offset: start}, function(error, products){ 

        if(error) throw error;

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


    });


  });

});

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 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')  &laquo;

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

Summary

Pagination is a very important feature in all dynamic web applications. In this article, you have learned NodeJS MySQL pagination example with Node ORM module. You can find example source code for NodeJS MySQL pagination example below in the link. Please leave your feedback or comments.

Click here to