Pagination in nodejs with mysql

Question

In my project I need to query the db with pagination and provide user the functionality to query based on current search result. Something like limit, I am not able to find anything to use with nodejs. My backend is mysql and I am writing a rest api.

Answer 1

You could try something like that (assuming you use Express 4.x).

Use GET parameters (here page is the number of page results you want, and npp is the number of results per page).

In this example, query results are set in the results field of the response payload, while pagination metadata is set in the pagination field.

As for the possibility to query based on current search result, you would have to expand a little, because your question is a bit unclear.

var express = require('express');
var mysql   = require('mysql');
var Promise = require('bluebird');
var bodyParser = require('body-parser');
var app = express();

var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'myuser',
  password : 'mypassword',
  database : 'wordpress_test'
});
var queryAsync = Promise.promisify(connection.query.bind(connection));
connection.connect();

// do something when app is closing
// see http://stackoverflow.com/questions/14031763/doing-a-cleanup-action-just-before-node-js-exits
process.stdin.resume()
process.on('exit', exitHandler.bind(null, { shutdownDb: true } ));

app.use(bodyParser.urlencoded({ extended: true }));

app.get('/', function (req, res) {
  var numRows;
  var queryPagination;
  var numPerPage = parseInt(req.query.npp, 10) || 1;
  var page = parseInt(req.query.page, 10) || 0;
  var numPages;
  var skip = page * numPerPage;
  // Here we compute the LIMIT parameter for MySQL query
  var limit = skip + ',' + numPerPage;
  queryAsync('SELECT count(*) as numRows FROM wp_posts')
  .then(function(results) {
    numRows = results[0].numRows;
    numPages = Math.ceil(numRows / numPerPage);
    console.log('number of pages:', numPages);
  })
  .then(() => queryAsync('SELECT * FROM wp_posts ORDER BY ID DESC LIMIT ' + limit))
  .then(function(results) {
    var responsePayload = {
      results: results
    };
    if (page < numPages) {
      responsePayload.pagination = {
        current: page,
        perPage: numPerPage,
        previous: page > 0 ? page - 1 : undefined,
        next: page < numPages - 1 ? page + 1 : undefined
      }
    }
    else responsePayload.pagination = {
      err: 'queried page ' + page + ' is >= to maximum page number ' + numPages
    }
    res.json(responsePayload);
  })
  .catch(function(err) {
    console.error(err);
    res.json({ err: err });
  });
});

app.listen(3000, function () {
  console.log('Example app listening on port 3000!');
});

function exitHandler(options, err) {
  if (options.shutdownDb) {
    console.log('shutdown mysql connection');
    connection.end();
  }
  if (err) console.log(err.stack);
  if (options.exit) process.exit();
}

Here is the package.json file for this example:

{
  "name": "stackoverflow-pagination",
  "dependencies": {
    "bluebird": "^3.3.3",
    "body-parser": "^1.15.0",
    "express": "^4.13.4",
    "mysql": "^2.10.2"
  }
}

Answer 2

I taked the solution of @Benito and I tried to make it more clear

var numPerPage = 20;
var skip = (page-1) * numPerPage; 
var limit = skip + ',' + numPerPage; // Here we compute the LIMIT parameter for MySQL query
sql.query('SELECT count(*) as numRows FROM users',function (err, rows, fields) {
    if(err) {
        console.log("error: ", err);
        result(err, null);
    }else{
        var numRows = rows[0].numRows;
        var numPages = Math.ceil(numRows / numPerPage);
        sql.query('SELECT * FROM users LIMIT ' + limit,function (err, rows, fields) {
            if(err) {
                console.log("error: ", err);
                result(err, null);
            }else{
                console.log(rows)
                result(null, rows,numPages);
            }
        });            
    }
});

Answer 3

Was looking for a quick solution. maybe would be useful for someone.

SELECT id  FROM complexCoding LIMIT ? OFFSET ?
",req.query.perpage,((req.query.page-1) * req.query.perpage)

Do not forget to paginate according to the total count of id divided by perpage

Answer 4

I wrote a pagination class in order to use it on different pages, I used bootstrap to style the links, you can change it if you're not using bootstrap.

Items route

router.get('/items/:page',(req,res) => {
const db = require('mysql'),
        Pagination = require('./pagination'),

        // Get current page from url (request parameter)
        page_id = parseInt(req.params.page),
        currentPage = page_id > 0 ? page_id : currentPage,

//Change pageUri to your page url without the 'page' query string 
        pageUri = '/items/';

        /*Get total items*/
        db.query('SELECT COUNT(id) as totalCount FROM items',(err,result)=>{

            // Display 10 items per page
            const perPage = 10,
                totalCount = result[0].totalCount;

            // Instantiate Pagination class
            const Paginate = new Pagination(totalCount,currentPage,pageUri,perPage);


            /*Query items*/
            db.query('SELECT * FROM items LIMIT '+Paginate.perPage+' OFFSET '+Paginate.start,(err,result)=>{

                data = {
                    items : result,
                    pages : Paginate.links()
                }

                // Send data to view
                res.render('items',data);
            });
        });

});

On items view, just print "pages" to generate pagination links

{{ pages }}

pagination.js >> Add this code to pagination.js and import it to any page you want to use pagination

class Pagination{

constructor(totalCount,currentPage,pageUri,perPage=2){
    this.perPage = perPage;
    this.totalCount =parseInt(totalCount);
    this.currentPage = parseInt(currentPage);
    this.previousPage = this.currentPage - 1;
    this.nextPage = this.currentPage + 1;
    this.pageCount = Math.ceil(this.totalCount / this.perPage);
    this.pageUri = pageUri;
    this.offset  = this.currentPage > 1 ? this.previousPage * this.perPage : 0;
    this.sidePages = 4;
    this.pages = false;
}



links(){
    this.pages='<ul class="pagination pagination-md">';

    if(this.previousPage > 0)
        this.pages+='<li class="page-item"><a class="page-link" href="'+this.pageUri + this.previousPage+'">Previous</a></li>';


        /*Add back links*/
        if(this.currentPage > 1){
            for (var x = this.currentPage - this.sidePages; x < this.currentPage; x++) {
                if(x > 0)
                    this.pages+='<li class="page-item"><a class="page-link" href="'+this.pageUri+x+'">'+x+'</a></li>';
            }
        }

        /*Show current page*/
        this.pages+='<li class="page-item active"><a class="page-link" href="'+this.pageUri+this.currentPage+'">'+this.currentPage+'</a></li>';

        /*Add more links*/
        for(x = this.nextPage; x <= this.pageCount; x++){

            this.pages+='<li class="page-item"><a class="page-link" href="'+this.pageUri+x+'">'+x+' </a></li>';

            if(x >= this.currentPage + this.sidePages)
                break;
        }


        /*Display next buttton navigation*/
        if(this.currentPage + 1 <= this.pageCount)
            this.pages+='<li class="page-item"><a class="page-link" href="'+this.pageUri+this.nextPage+'">Next</a></li>';

        this.pages+='</ul>';

    return this.pages;
}
}
module.exports = Pagination;