Server Side Node.js Express Pagination MySQL Example – Express RestAPIs + Sequelize ORM (Filtering + Sorting)

Tutorial Nodejs MySQL Pagination Filtering Sorting - Express RestAPIs + Sequelize ORM + MySQL example

Facing with a large dataset is one of the most user-case in software development, and we need to present it to the user in smaller chunks. To resolve the problem, pagination and sorting is a way to handle it. So in the tutorial, I guide step by step with clearly code to implement a “Server Side Node.js Express Pagination MySQL Example” with Express RestAPIs, Sequelize CRUD queries with MySQL database to provide a set of RestAPIs for pagination filtering and sorting.

Related posts:


Overall Server Side Node.js Express Pagination MySQL Example with Sequelize CRUD queries

To handling Pagination RestAPI requests and do Paging Filtering and Sorting queries with MySQL database, we create a backend web Node.js application with 4 main points:

Nodejs Pagination Filtering Sorting RestAPIs - Overall Architecture
Nodejs Pagination Filtering Sorting RestAPIs – Overall Architecture
  • To handle pagination RestAPI requests with Node.js, We use Express framework.
  • To do pagination filtering and sorting queries with MySQL database, We use Sequelize ORM.
  • We define all RestAPI URLs in router.js.
  • We implement code how to process each paging filtering and sorting RestAPI request in controller.js file.

Video Guide

Below is a youtube video to guide step by step how to debug a running sourcecode of the tutorial “Server Side Node.js Express Pagination MySQL Example”:

Goal

We define a set of 3 RestAPIs to do the pagination, filtering and sorting queries with MySQL databases as below list:

  1. Filtering Request – a GET RestAPI /api/customers/filteringbyage is used to fetch all records from MySQL with a filtering by age
  2. Pagination Request – a GET RestAPI /api/customers/pagination is used to fetch data from MySQL with pagination purpose.
  3. Pagination Filtering and Sorting – a GET RestAPI /api/customers/pagefiltersort is defined to fetch data from MySQL with pagination, filtering by age and ordering by 2 fields firstname and lastname

Testcase 1 – Filtering Customers by age

Testcase 1 - Nodejs Filtering RestAPI
Testcase 1 – Nodejs Filtering RestAPI

The request is used to filter all Customer records having age=23. It returns a JSON message with 3 fields:

  • message – an overall descriptive message for filtering request
  • totalItems – total number of returned Customer items after filtering
  • customers – an array of all filtering Customer items

Check the database again:

Testcase 1 - Check the database for comparing with filtering results
Testcase 1 – Check the database for comparing with filtering results

Testcase 2 – Do a pagination request

Testcase 2 - Node.js Pagination RestAPI Request
Testcase 2 – Node.js Pagination RestAPI Request

What does it mean? We had done a pagination request to fetch a second page page = 1 with a size of page is 7 (limit=7)

The RestAPI returns a Json result with useful informantion as below:

  1. totalItems describes a total number of matching records in database for the pagination request
  2. totalPages describes the total number of pages matching with requested limit
  3. limit describes the number of items for each fetching page
  4. currentPageNumber is the order number of requested page (currentPageNumber = page + 1)
  5. currentPageSize is the size of the current page (currentPageSize <= limit)
  6. customers is a set of returned items attached with the pagination response

Using a native MySQL query with LIMIT statement to check above result:

SELECT 
    select_list
FROM
    table_name
LIMIT [offset,] row_count;

offset = limit*page = 7*1 = 7
row_count = limit

Do the LIMIT MySQL query:

Check the pagination request by excuting a LIMIT statement to MYSQL database
Check the pagination request by excuting a LIMIT statement to MYSQL database

Testcase 3 - Do the Pagination Filtering and Sorting request

Testcase 3 - Do Paging Filtering and Sorting request
Testcase 3 - Do Paging Filtering and Sorting request

What does it mean? - Now we double check the returned items inside response's result of paging-filtering and sorting request with 3 steps by using Native SQL execution:

1. Do the Filtering with age=23, and we just have 12 Customer items in database having age is 23 so the returned totalItems is 12. The totalPages is 3 because of two reasons:
- limit: 5
- and totalPages = Math.ceil(totalItems / limit) = Math.ceil(12 / 5)

Check the total items in MySQL database with the condition age = 23
Check the total items in MySQL database with the condition age = 23

2. Do the pagination with offset = 5 (limit*page = 5*1) and row_counts = 5:

Checking the pagination and filtering with WHERE and LIMIT statement
Checking the pagination and filtering with WHERE and LIMIT statement

Umh, It does NOT match correctly with the returned set of items inside the response:

Response Items
Response Items

Because of we do NOT execute the sorting by firstname and lastname in Native SQL query. Let's do it in next step right now!
3. Finally do the Sorting by firstname with ascending order and lastname with descending order:

Do a finally version Native SQL to get the pagination filtering and sorting result - for comparing with Response' items
Do a finally version Native SQL to get the pagination filtering and sorting result - for comparing with Response' items

That's good! The same as above response results ! Big Like!!!

Create Nodejs Project

Before creating a Nodejs project, we need to confirm that the Nodejs and npm had been installed in your computer development by cmd: node -v and npm -v

If these commandlines are not recognized by command prompt, it means you need to install them by visit the https://nodejs.org/en/ site and download installed package and do the nodejs setup for development later.

Now starting development! Create a folder and named it as Nodejs-Pagination-Filtering-Sorting, go inside the folder, open a cmd and initiate a Nodejs project by cmd npm init. After all, a package.json file is created as below content:

{
  "name": "nodejs-pagination-filtering-sorting",
  "version": "1.0.0",
  "description": "Nodejs pagination filtering sorting",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "repository": {
    "type": "git",
    "url": "git+https://github.com/github.com/loizenjava.git"
  },
  "keywords": [
    "nodejs",
    "filtering",
    "sorting",
    "pagination"
  ],
  "author": "https://loizenjava.com",
  "license": "ISC",
  "bugs": {
    "url": "https://github.com/github.com/loizenjava/issues"
  },
  "homepage": "https://github.com/github.com/loizenjava#readme",
  "dependencies": {
    "body-parse": "^0.1.0",
    "cors": "^2.8.5",
    "express": "^4.17.1",
    "mysql2": "^2.1.0",
    "sequelize": "^6.3.3"
  }
}

For coding Editor, we use friendly Visual Studio Code to write code and debug Nodejs project. For the tutorial, We will create a project with below structure:

Nodejs Project Structure
Nodejs Project Structure
  1. file db.config.js is used to define MySQL database configuration with Sequelize ORM
  2. file customer.model.js is used to define a Sequelize model mapped with corresponding MySQL database table schema.
  3. file router.js is used to define all Express RestAPI URLs
  4. file controller.js is used to implement detail logic code to process each incoming request
  5. file server.js is used to implement a Nodejs Web server

Install Express, Cors, Body Parse, Sequelize, MySQL

To development a Nodejs RestAPIs with MySQL database, we need a set of packages to handle the full stack of the web backend proccessing, they includes Express framework, Cors, Body Parse, MySQL packages and Sequelize ORM.

We can install all the packages by one cmd:

$npm install --save express cors body-parser mysql2 sequelize

For more details about "Express framework, Cors, Body Parse, MySQL packages and Sequelize ORM", you can refer here.

Create Sequelize Model

We define a Sequelize Model to represent a table in the database:

Customer Table Schema
Customer Table Schema

So here is the customer.model.js code:

module.exports = (sequelize, Sequelize) => {
	const Customer = sequelize.define('customer', {	
	  id: {
            type: Sequelize.INTEGER,
            autoIncrement: true,
            primaryKey: true
      },
	  firstname: {
			type: Sequelize.STRING
	  },
	  lastname: {
                        type: Sequelize.STRING
	  },	  address: {
			type: Sequelize.STRING
	  },
	  age: {
			type: Sequelize.INTEGER
    },
    copyrightby: {
      type: Sequelize.STRING,
      defaultValue: 'https://loizenjava.com'
    }
	});
	
	return Customer;
}

We create a Sequelize Customer model with 6 attributes for mapping with all corresponding customers table's columns:

  1. id attribute is a primary key with Int type (in database)
  2. firstname attribute has Sequelize.STRING type mapping with the firstname column in customers table with varchar type
  3. lastname attribute has Sequelize.STRING type mapping with the lastname column in customers table with varchar type
  4. address attribute has Sequelize.STRING type mapping with the lastname column in customers table with varchar type
  5. age attribute has Sequelize.INTEGER type mapping with the age column in customers table with int type
  6. copyrightby attribute has Sequelize.STRING type mapping with the copyrightby column in customers table with varchar type and having default value is https://loizenjava.com

Sequelize Pagination Filtering Sorting Queries

To do the pagination with database, Sequelize ORM provides 2 model methods for supporting the purpose with limit and offset parameters:

  1. .findAll() - Search for multiple elements in the database
  2. .findAndCountAll() - Search for multiple elements in the database, returns both data and total count

How about limit & offset for nodejs pagination?

  • limit is the maximum number of records to fetch
  • offset is the quantity of records to skip

For example, if we have total 12 items:

  • { offset: 5 }: skip first 5 items, fetch 7 remaining items.
  • { limit: 5 }: fetch first 5 items.
  • { offset: 5, limit: 5 }: skip first 5 items, fetch 6th and 10th items.

Sequelize .findAll({offset, limit}) method

Here is an query example with the Sequelize .findAll({offset, limit}) method:

Debug with .findAll() method of Sequelize Model
Debug with .findAll() method of Sequelize Model

The .findAll() method return an array of 7 items from id=8 to id=14 (the first 7 items are skips because the offset = page*limit = 7).

Test Sequelize findAll() method - return 7 items to Client Side
Test Sequelize findAll() method - return 7 items to Client Side

Using a Native SQL query to double check the above results:

Using Native SQL Query to Double Check the Returned Results of Squelize findAll() Query
Using Native SQL Query to Double Check the Returned Results of Squelize findAll() Query

- We do NOT know how many next items we can fetch more when doing the pagination with .findAll() method.

-> Let's go with next session: how to work with Sequelize .findAndCountAll() method for finding the new solution!

Sequelize .findAndCountAll({offset, limit}) method

Here is a query example with the Sequelize .findAndCountAll({offset, limit}) method:

Debug with Sequelize .findAndCountAll() method
Debug with Sequelize .findAndCountAll() method

This is a convenience method that combines .findAll() and count, this is useful when dealing with queries related to pagination where you want to retrieve data with a limit and offset but also need to know the total number of records that match the query:

The success handler will always receive an object type with two properties:

  • count is the total number records matching the where clause and other filters due to associations
  • rows - an array of objects, the records matching the where clause and other filters due to associations, within the limit and offset range

So the Sequelize method .findAndCountAll({...}) is the best solution for building Node.js RestAPIs with pagination filtering and sorting functions.

How to build Pagination Express RestAPIs

For building the Nodejs-MySQL-Pagination-RestAPIs project, we follow 4 steps for development:

  1. Create an Express WEB Application Server
  2. Define All RestAPI URLs in router.js
  3. Configure MySQL Database with Sequelize ORM
  4. Implement All RESTAPIs in controllers.js file

Create Express Application Server

To implement an Express RestAPIs Application, firstly we need create a server with express app:

- server.js:

const express = require('express');
const app = express();
...
const server = app.listen(8080, function () {
 
  let host = server.address().address
  let port = server.address().port
 
  console.log("App listening at http://%s:%s", host, port); 
})

For parsing body of requests, we need use body-parser dependency, add more code on server.js file:

...
var bodyParser = require('body-parser');
...
app.use(bodyParser.json());
...
const server = app.listen(8080, function () {
...

We define all RESTAPI URLs in a file router.js and then need attach it with the Express Application:

...
let router = require('./app/routers/router.js');
...
app.use('/', router);
...
const server = app.listen(8080, function () {
...

To enable CORS function, we need attach it with Express app:

const cors = require('cors')
const corsOptions = {
  origin: 'http://localhost:4200',
  optionsSuccessStatus: 200
}
app.use(cors(corsOptions));

Here is all coding in the server.js file:

const express = require('express');
const app = express();

var bodyParser = require('body-parser');
 
const db = require('./app/config/db.config.js');
  
// force: true will drop the table if it already exists
db.sequelize.sync({force: true}).then(() => {
  console.log('Drop and Resync with { force: true }');
}); 

let router = require('./app/routers/router.js');

const cors = require('cors')
const corsOptions = {
  origin: 'http://localhost:4200',
  optionsSuccessStatus: 200
}
app.use(cors(corsOptions));

app.use(bodyParser.json());
app.use('/', router);

// Create a Server
const server = app.listen(8080, function () {
 
  let host = server.address().address
  let port = server.address().port
 
  console.log("App listening at http://%s:%s", host, port); 
})

Define All RestAPI URLs in router.js

For making a router with Express framework, firstly we need get the router object from Express app. Then using it to define the GET/POST/DELETE/PUT requests:

let express = require('express');
let router = express.Router();
 
const customers = require('../controllers/controller.js');

router.get('/api/customers/pagination', customers.pagination);
router.get('/api/customers/filteringbyage', customers.filteringByAge);
router.get('/api/customers/pagefiltersort', customers.pagingfilteringsorting);

module.exports = router;

Use the express.Router class to create modular, mountable route handlers. A Router instance is a complete middleware and routing system.

Configure MySQL Database with Sequelize ORM

Firstly, we create a file env.js with an const Object to include all configured parameters for MySQL database setup.

const env = {
  database: 'loizenjavadb',
  username: 'root',
  password: '12345',
  host: 'localhost',
  dialect: 'mysql',
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

module.exports = env;

Now for the main setting between MySQL database and Sequelize ORM, we define a file db.config.js as below code:

const env = require('./env.js');
 
const Sequelize = require('sequelize');
const sequelize = new Sequelize(env.database, env.username, env.password, {
  host: env.host,
  dialect: env.dialect,
  operatorsAliases: false,
 
  pool: {
    max: env.max,
    min: env.pool.min,
    acquire: env.pool.acquire,
    idle: env.pool.idle
  }
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;
 
db.Customer = require('../models/customer.model.js')(sequelize, Sequelize);
 
module.exports = db;

Implement All RESTAPIs business logic in controllers.js file

Implement Controller.js File
Implement Controller.js File

For handling Nodejs RestAPIs' processing that been defined in router.js file, we implement all 3 working functions in controller.js file:

  1. filteringByAge = (req, res) function is used to handle a GET request at the endpoint /api/customers/filteringbyage to filter all records in MySQL by a given age value and return back all results to client in a Json message format
  2. pagination = (req, res) function is used to handle a GET request at the endpoint /api/customers/pagination with 2 query parameters limit and page to get a batch of records from MySQL database like as a pagination query
  3. pagingfilteringsorting = (req, res) function is used to handle a GET request at the endpoint /api/customers/pagefiltersort to do an association quering operation: pagination, plus filtering by age and sorting by firstname and lastname from MYSQL database with 3 request query parameters limit, page, age

Node.js Sequelize Filtering RestAPI

filteringByAge = (req, res) function is used to handle a GET request at the endpoint /api/customers/filteringbyage to filter all records in MySQL by a given age value and return back all results to client in a Json message format.

How to implement it? Just follow simple steps:

  • Get an age value from request query for using to do the filter later
  • Using the .findAll() method of Sequelize Model to retrieve all records from MySQL database with a filter by age condition in where clause: where: {age: age}
  • Build a Json message with informative fields and return it back to client side.
  • Do not forget to handle any exception when processing by using try-catch statement!

Coding:

exports.filteringByAge = (req, res) => {
  let age = req.query.age;

    Customer.findAll({
                      attributes: ['id', 'firstname', 'lastname', 'age', 'address', 'copyrightby'],
                      where: {age: age}
                    })
          .then(results => {
            res.status(200).json({
                message: "Get all Customers with age = " + age,
                customers: results,
            });
          })
          . catch(error => {
              console.log(error);
              res.status(500).json({
                message: "Error!",
                error: error
              });
            });
}

pagination = (req, res) function is used to handle a GET request at the endpoint /api/customers/pagination with 2 query parameters limit and page to get a batch of records from MySQL database for the pagination query.

To do the pagination with MySQL database by Sequelize ORM, we use the .findAndCountAll() method.

Signature of findAndCountAll() method:

public static async findAndCountAll(options: object): Promise<{count: number, rows: Model[]}>

.findAndCountAll(...) find all the rows matching your query, within a specified offset / limit, and get the total number of rows matching your query.

const offset = page ? page * limit : 0;

Simple steps to implement the pagination request:

  • Get the page and limit parameters from the request query
  • Calulate an offset from page and limit parameters
    const offset = page ? page * limit : 0;
  • Do the pagination query to MySQL database using the findAndCountAll() method of Sequelize model
  • Construct a JSON message with a set of informative fields and return back to client side
  • Remember to handle the un-expected error by using try-catch statement

Detail Coding:

exports.pagination = (req, res) => {
  try{
    let page = parseInt(req.query.page);
    let limit = parseInt(req.query.limit);
  
    const offset = page ? page * limit : 0;
  
    Customer.findAndCountAll({ limit: limit, offset:offset })
      .then(data => {
        const totalPages = Math.ceil(data.count / limit);
        const response = {
          message: "Paginating is completed! Query parameters: page = " + page + ", limit = " + limit,
          data: {
              "copyrightby": "https://loizenjava.com",
              "totalItems": data.count,
              "totalPages": totalPages,
              "limit": limit,
              "currentPageNumber": page + 1,
              "currentPageSize": data.rows.length,
              "customers": data.rows
          }
        };
        res.send(response);
      });  
  }catch(error) {
    res.status(500).send({
      message: "Error -> Can NOT complete a paging request!",
      error: error.message,
    });
  }    
}

Nodejs Sequelize Pagination RestAPI

pagination = (req, res) function is used to handle a GET request at the endpoint /api/customers/pagination with 2 query parameters limit and page to get a batch of records from MySQL database for the pagination query.

To do the pagination with MySQL database by Sequelize ORM, we use the .findAndCountAll() method.

Signature of findAndCountAll() method:

public static async findAndCountAll(options: object): Promise<{count: number, rows: Model[]}>

.findAndCountAll(...) find all the rows matching your query, within a specified offset / limit, and get the total number of rows matching your query.

const offset = page ? page * limit : 0;

Simple steps to implement the pagination request:

  • Get the page and limit parameters from the request query
  • Calulate an offset from page and limit parameters
    const offset = page ? page * limit : 0;
  • Do the pagination query to MySQL database using the findAndCountAll() method of Sequelize model
  • Construct a JSON message with a set of informative fields and return back to client side
  • Remember to handle the un-expected error by using try-catch statement

Detail Coding:

exports.pagination = (req, res) => {
  try{
    let page = parseInt(req.query.page);
    let limit = parseInt(req.query.limit);
  
    const offset = page ? page * limit : 0;
  
    Customer.findAndCountAll({ limit: limit, offset:offset })
      .then(data => {
        const totalPages = Math.ceil(data.count / limit);
        const response = {
          message: "Paginating is completed! Query parameters: page = " + page + ", limit = " + limit,
          data: {
              "copyrightby": "https://loizenjava.com",
              "totalItems": data.count,
              "totalPages": totalPages,
              "limit": limit,
              "currentPageNumber": page + 1,
              "currentPageSize": data.rows.length,
              "customers": data.rows
          }
        };
        res.send(response);
      });  
  }catch(error) {
    res.status(500).send({
      message: "Error -> Can NOT complete a paging request!",
      error: error.message,
    });
  }    
}

Nodejs Sequelize Pagination Filtering and Sorting RestAPI

pagingfilteringsorting = (req, res) function is used to handle a GET request at the endpoint /api/customers/pagefiltersort to do an association quering operation: pagination, plus filtering by age and sorting by firstname and lastname from MYSQL database with 3 request query parameters limit, page, age.

We continue to use the method .findAndCountAll(...) to do the associated operation: pagination plus filtering and sorting query. So we add more 2 clauses inside .findAndCountAll(...) method:

  • Where clause - using to filter by age field: where: {age: age}
  • Order clause - using to sort rows by 2 fileds firstname with ascending direction and lastname with descending direction:
    
    order: [
      ['firstname', 'ASC'],
      ['lastname', 'DESC']
    ],
    

Straightforward steps to implement the Pagination + Filtering and Sorting function:

  • Retrieve 3 parameters from incoming request query: limit, page for pagination and age for filtering.
  • Calulate an offset for Sequelize pagining query later:
    const offset = page ? page * limit : 0;
  • Do the Sequelize pagination filtering and sorting with .findAndCountAll() method:
    Customer.findAndCountAll({
                                attributes: ['id', 'firstname', 'lastname', 'age', 'address'],
                                where: {age: age}, 
                                order: [
                                  ['firstname', 'ASC'],
                                  ['lastname', 'DESC']
                                ],
                                limit: limit, 
                                offset:offset 
                              })
  • Construct a Json message with informative fields and return back to client side:
    
    .then(data => {
      const totalPages = Math.ceil(data.count / limit);
      const response = {
        message: "Pagination Filtering Sorting request is completed! Query parameters: page = " + page + ", limit = " + limit + ", age = " + age,
        data: {
            "copyrightby": "https://loizenjava.com",
            "totalItems": data.count,
            "totalPages": totalPages,
            "limit": limit,
            "age-filtering": age,
            "currentPageNumber": page + 1,
            "currentPageSize": data.rows.length,
            "customers": data.rows
        }
      };
      res.send(response);
    
  • Do NOT forget to handle an un-expected errors with try-catch statement.
    try{
        //...
    }catch(error) {
        res.status(500).send({
          message: "Error -> Can NOT complete a paging request!",
          error: error.message,
        });
    }   

- Coding here:

exports.pagingfilteringsorting = (req, res) => {
  try{
    let page = parseInt(req.query.page);
    let limit = parseInt(req.query.limit);
    let age = parseInt(req.query.age);
  
    const offset = page ? page * limit : 0;

    console.log("offset = " + offset);
  
    Customer.findAndCountAll({
                                attributes: ['id', 'firstname', 'lastname', 'age', 'address'],
                                where: {age: age}, 
                                order: [
                                  ['firstname', 'ASC'],
                                  ['lastname', 'DESC']
                                ],
                                limit: limit, 
                                offset:offset 
                              })
      .then(data => {
        const totalPages = Math.ceil(data.count / limit);
        const response = {
          message: "Pagination Filtering Sorting request is completed! Query parameters: page = " + page + ", limit = " + limit + ", age = " + age,
          data: {
              "copyrightby": "https://loizenjava.com",
              "totalItems": data.count,
              "totalPages": totalPages,
              "limit": limit,
              "age-filtering": age,
              "currentPageNumber": page + 1,
              "currentPageSize": data.rows.length,
              "customers": data.rows
          }
        };
        res.send(response);
      });  
  }catch(error) {
    res.status(500).send({
      message: "Error -> Can NOT complete a paging request!",
      error: error.message,
    });
  }      
}

Further Reading

Here is a list of articles related with the tutorial: "Server Side Node.js Express Pagination MySQL Example (filtering + sorting)"

  1. Sequelize Model Usage - Data retrieval / Finders
  2. Express Router

Related posts:

Tutorial Nodejs Express RestAPIs MySQL with Sequelize Queries Examples

Sourcecode

Clearly and running sourcecode for the tutorial - "Server Side Node.js Express Pagination MySQL Example using Sequelize CRUD queries":

Nodejs-Pagination-Filtering-Sorting

- GitHub Sourcecode for "Server Side Node.js Express Pagination MySQL Example":

Nodejs RestAPIs Express Sequelize MySQL - GitHub Sourcecode

One thought on “Server Side Node.js Express Pagination MySQL Example – Express RestAPIs + Sequelize ORM (Filtering + Sorting)”

Leave a Reply

Your email address will not be published. Required fields are marked *