Node.js PostgreSQL CRUD Example Express RestAPIs + Sequelize + PostgreSQL tutorial

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.

Overview Architecture – Node.js Express Sequelize PostgreSQL CRUD RestAPIs Example

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.

Project Goal

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:

1. POST RestAPI /api/customers/create will handle the submit data from client to save in PostgreSQL database
2. GET RestAPI /api/customers/all will fetch all data from PostgreSQL database
3. GET RestAPI /api/customers/onebyid/:id will get a single data by primary key id
4. PUT RestAPI /api/customers/update/:id will update an existed record in PostgreSQL database
5. 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.

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

Testcase 1 – Nodejs Express PostgreSQL POST Request

Check PostgreSQL’s records:

Testcase 2 – Nodejs Express PostgreSQL GET Request: get all data from PostgreSQL

Testcase 3 – Nodejs Express PostgreSQL GET Request: get one data record from PostgreSQL with a given id

Testcase 4 – Nodejs Express PostgreSQL UPDATE request

Testcase 5 – Nodejs Express PostgreSQL DELETE request: delete a record with a given id

Testcase 6 – Nodejs Express PostgreSQL Filtering request by a field

Testcase 7 – Nodejs Express PostgreSQL Pagination request

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:

1. totalItems describes the number of records in database
2. totalPages describes the total number of pages with requested limit
3. limit describes the number of items for a fetching page
4. currentPageNumber is the order number of requested page (currentPageNumber = page + 1)
5. currentPageSize is the size of the current page (currentPageSize <= limit)
6. customers is a dataset attached with the pagination request

Using Native PostgreSQL query with LIMIT statement to check the above result:

Testcase 8 - Nodejs Express PostgreSQL Pagination Filtering and Sorting request

What does it mean? - The above request had done with 3 proccessing steps:

1. 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:
2. limit: 3
3. and totalPages = Math.ceil(data.count / limit) = Math.ceil(4 / 3)

1. Do the pagination with offset = 0 (limit*page) and row_counts = 3:

1. Finally Do the Sorting by firstname with ascending order and lastname with descending order:

Related post

• Angular 10 Node.js MySQL CRUD Example
• Token Based Authentication in Node.js using JWT (JSON Web Tokens) + MySQL Example
• Nodejs RestAPIs Upload Download Multiple Excel Files