Tutorial Link : Nodejs PostgreSQL CRUD Example
In the tutorial, I will introduce step by step how to create a ‘Node.js PostgreSQL CRUD Example – Express RestAPIs + Sequelize + PostgreSQL tutorial’ with a full-stack technologies: Express RestAPI Framework (Cors + Body-Parse) + Sequelize ORM + PostgreSQL database.
To handling all POST/GET/PUT/DELETE RestAPI requests and do CRUD with PostgreSQL database, we create a backend web Node.js application with 4 main points:
- To handle CRUD RestAPI requests with Node.js, we use Express.js framework.
- To do CRUD operations with PostgreSQL database, we use Sequelize ORM queries.
- We define all RestAPI urls in router.js.
- We implement how to process each RestAPI requests in controller.js file.
After the tutorial, we will understand overall architecture and clearly picture how to create a full backend web restapis application with Node.js technology from abstract overview to specific helpful frameworks and details sourcecode for connecting all things in one application.
We will define 8 RestAPIs with POST/GET/PUT/DELETE methods for posting, fetching, updating, removing, pagination, filtering and sorting data from PostgreSQL database:
– For normally requests with POST/GET/PUT/DELETE methods, we create a first GROUP with 5 RestAPIs:
- POST RestAPI /api/customers/create will handle the submit data from client to save in PostgreSQL database
- GET RestAPI /api/customers/all will fetch all data from PostgreSQL database
- GET RestAPI /api/customers/onebyid/:id will get a single data by primary key id
- PUT RestAPI /api/customers/update/:id will update an existed record in PostgreSQL database
- DELETE RestAPI /api/customers/delete/:id will delete an existed record in PostgreSQL which is associated with a primary key id
– For advanced purpose such as Filtering, Pagination and Sorting, we create the second RestAPIs group:
1.Filtering Request – GET RestAPI /api/customers/filteringbyage is used to fetch all records from PostgreSQL with a filtering by age
2.Pagination Request – GET RestAPI /api/customers/pagination is used to fetch data from PostgreSQL with pagination purpose.
- Pagination Filtering and Sorting – GET RestAPI /api/customers/pagefiltersort is defined to fetch data from PostgreSQL with pagination, filtering by age and ordering by 2 fields firstname and lastname
Check PostgreSQL’s records:
Testcase 3 – Nodejs Express PostgreSQL GET Request: get one data record from PostgreSQL with a given id
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 the number of records in database
- totalPages describes the total number of pages with requested limit
- limit describes the number of items for a 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 dataset attached with the pagination request
Using Native PostgreSQL query with LIMIT statement to check the above result:
What does it mean? - The above request had done with 3 proccessing steps:
- Do the Filtering with age=23, and We just have 4 Customer items in database having age is 23 so returned totalItems is 4. The totalPages is 2 because of 2 reason:
- limit: 3
- and totalPages = Math.ceil(data.count / limit) = Math.ceil(4 / 3)
- Do the pagination with offset = 0 (limit*page) and row_counts = 3:
- Finally Do the Sorting by firstname with ascending order and lastname with descending order: