Node.js PostgreSQL CRUD Example — Express RestAPIs + Sequelize + PostgreSQL tutorial
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
Below is the architecture of the tutorial ‘Node.js PostgreSQL CRUD 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:
- 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 keyid
- 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 keyid
– For advanced purpose such as Filtering, Pagination and Sorting, we create the second RestAPIs group:
- Filtering Request — GET RestAPI
/api/customers/filteringbyage
is used to fetch all records from PostgreSQL with a filtering byage
- 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 fieldsfirstname
andlastname
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:
totalItems
describes the number of records in databasetotalPages
describes the total number of pages with requested limitlimit
describes the number of items for a fetching pagecurrentPageNumber
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:
Testcase 8 — Nodejs Express PostgreSQL Pagination Filtering and Sorting request
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. ThetotalPages
is 2 because of 2 reason:
-limit
: 3
- andtotalPages
=Math.ceil(data.count / limit)
=Math.ceil(4 / 3)
2. Do the pagination with offset = 0
(limit*page
) and row_counts = 3
:
3. Finally Do the Sorting by firstname
with ascending order and lastname
with descending order:
Tutorial Link:
Sourcecode