Server Side Pagination In Node js MySQL Express RestAPIs + Sequelize ORM Pagination Filtering Sort

Server Side Pagination In Node js MySQL- Express RestAPIs + Sequelize ORM Pagination Filtering Sort

2. To do pagination filtering and sorting queries with MySQL database, We use Sequelize ORM.

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

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

IV. Structure of Video

0:06​ - Introduce Project Overview and Architecture of Nodejs Project Structure.
1:00​ - Download Sourcecode: Nodejs Pagination And Sorting with MySQL
1:24​ - Install needed dependencies for Nodejs project
1:36​ - Import Nodejs Project to Visual Studio Code
1:47​ - REview project structure of Sourcecode
2:40​ - Start debuging with nodejs project
2:55​ - Check created customers table in MYSQL database
3:13​ - prepare data for Customers table

---DEBUG TIME---
3:29​ - Do the first testcase - DEGBUG with filtering RESTAPI age=23
5:13​ - Do the DEBUG mode for the filtering restapi with age=18
5:52​ - DO the DEBUG with Nodejs pagination RestAPI page=1 & limit=5
7:05​ DO the DEBUG with Nodejs pagination RestAPI page=4 & limit=5
7:42​ - Do the Debug with Nodejs Pagination RestAPI page=5 & limit=5
8:02​ - Do the Debug with Pagination Filtering and Sorting with page=1 & limit=5 & age=23
9:51​ - Do the Debug with Pagination Filtering and Sorting with page=2 & limit=5 & age=23

Related posts:
1. Nodejs JWT Authentication Example – Express RestAPIs + JSON Web Token + BCryptjs + Sequelize + MySQL/PostgreSQL

2. Angular Nodejs Fullstack CRUD Application with MySQL/PostgreSQL – Angular 10-9-8 HttpClient + Nodejs Express, Sequelize ORM

3. Build Nodejs CRUD Application with MySQL/PostgreSQL – Express RestAPIs + Ajax : Post/Get/Put/Delete Request

4. Tutorial Nodejs Express RestAPIs MySQL with Sequelize Queries Examples

Sourcecode at:

Youtube: loizenai