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.
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:
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
We define a set of 3 RestAPIs to do the pagination, filtering and sorting queries with MySQL databases as below list:
The request is used to filter all Customer records having age=23. It returns a JSON message with 3 fields:
Check the database again:
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 = limitpage = 71 = 7
row_count = limit
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:
Umh, It does NOT match correctly with the returned set of items inside the response:
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:
#nodejs #pagination #mysql #example #express #restapi