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:
- 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”:
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
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:
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:
LIMIT [offset,] row_count;
offset = limit*page = 7*1 = 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:
- 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)
- Do the pagination with offset = 5 (limit*page = 5*1) and row_counts = 5:
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!
- Finally do the Sorting by firstname with ascending order and lastname with descending order: