Server Side Node.js Express Pagination MySQL Example
Tags: node, mysql, pagination
Published: 2021-01-01 17:02:00

Tutorial: https://loizenai.com/server-side-nodejs-mysql-pagination-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.

## 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:

![Overall Server Side Node.js Express Pagination MySQL Example with Sequelize CRUD queries](https://loizenai.com/wp-content/uploads/2020/07/Nodejs-Pagination-Filtering-Sorting-RestAPIs-Overall-Architecture.png)

![Server side Nodejs Express Pagination](https://loizenai.com/wp-content/uploads/2020/07/Testcase-1-Nodejs-Filtering-RestAPI.png "Server side Nodejs Express Pagination")

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

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”:

youtube: https://youtu.be/9asw2jSi4zE

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

- Filtering Request – a GET RestAPI /api/customers/filteringbyage is used to fetch all records from MySQL with a filtering by age
- Pagination Request – a GET RestAPI /api/customers/pagination is used to fetch data from MySQL with pagination purpose.
- 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 – Filtering Customers by age](https://loizenai.com/wp-content/uploads/2020/07/Testcase-1-Nodejs-Filtering-RestAPI.png)

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](https://loizenai.com/wp-content/uploads/2020/07/Testcase-1-Check-the-database-for-comparing-with-filtering-results.png)

## Testcase 2 – Do a pagination request

![Testcase 2 – Do a pagination request](https://loizenai.com/wp-content/uploads/2020/07/Testcase-2-Node.js-Pagination-RestAPI-Request.png)

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:

totalItems describes a total number of matching records in database for the pagination request
totalPages describes the total number of pages matching with requested limit
limit describes the number of items for each fetching page
currentPageNumber is the order number of requested page (currentPageNumber = page + 1)
currentPageSize is the size of the current page (currentPageSize <= limit)
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:

![Do the LIMIT MySQL query](https://loizenai.com/wp-content/uploads/2020/07/Check-the-pagination-request-by-excuting-a-LIMIT-statement-to-MYSQL-database.png)

## Testcase 3 - Do the Pagination Filtering and Sorting request

![Testcase 3 - Do the Pagination Filtering and Sorting request](https://loizenai.com/wp-content/uploads/2020/07/Testcase-3-Do-Paging-Filtering-and-Sorting-request.png)

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](https://loizenai.com/wp-content/uploads/2020/07/Check-the-total-items-in-MySQL-database-with-the-condition-age-23.png)

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](https://loizenai.com/wp-content/uploads/2020/07/Checking-the-pagination-and-filtering-with-WHERE-and-LIMIT-statement.png)

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

![Response Items](https://loizenai.com/wp-content/uploads/2020/07/Response-Items.png)

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](https://loizenai.com/wp-content/uploads/2020/07/Do-a-finally-version-Native-SQL-to-get-the-pagination-filtering-and-sorting-result-for-comparing-with-Response-items.png)