Node.js + PostgreSQL: CRUD example

In this tutorial, I will show you step by step to build Node.js + PostgreSQL CRUD example using Express and Sequelize ORM.

Full Article: https://bezkoder.com/node-express-sequelize-postgresql/

Node.js PostgreSQL CRUD example overview

We will build Rest Apis that can create, retrieve, update, delete and find Tutorials by title.

First, we start with an Express web server. Next, we add configuration for PostgreSQL database, create Tutorial model with Sequelize, write the controller. Then we define routes for handling all CRUD operations (including custom finder).

The following table shows overview of the Rest APIs that will be exported:

Finally, we're gonna test the Rest Apis using Postman.

This is our project structure:

Demo Video

This is our Node.js PostgreSQL CRUD example using Express & Sequelize application demo, test Rest Apis with Postman.

Test the APIs

Run our Node.js application with command: node server.js.

Using Postman, we're gonna test all the Apis above.

• Create a new Tutorial using POST /tutorials Api

After creating some new Tutorials, you can check PostgreSQL table:

testdb=# select * from tutorials;
 id |    title    |    description    | published |         createdAt          |         updatedAt
----+-------------+-------------------+-----------+----------------------------+----------------------------
  1 | Node Tut #1 | Tut#1 Description | f         | 2020-01-29 10:42:57.121+07 | 2020-01-29 10:42:57.121+07
  2 | Node Tut #2 | Tut#2 Description | f         | 2020-01-29 10:43:05.131+07 | 2020-01-29 10:43:05.131+07
  3 | Node Tut #3 | Tut#3 Description | f         | 2020-01-29 10:43:48.028+07 | 2020-01-29 10:43:48.028+07
  4 | Js Tut #4   | Tut#4 Desc        | f         | 2020-01-29 10:45:40.016+07 | 2020-01-29 10:45:40.016+07
  5 | Js Tut #5   | Tut#5 Desc        | f         | 2020-01-29 10:45:44.289+07 | 2020-01-29 10:45:44.289+07

• Retrieve all Tutorials using GET /tutorials Api

• Retrieve a single Tutorial by id using GET /tutorials/:id Api

• Update a Tutorial using PUT /tutorials/:id Api

Check tutorials table after some rows were updated:

testdb=# select * from tutorials;
 id |     title      |    description    | published |         createdAt          |         updatedAt
----+----------------+-------------------+-----------+----------------------------+----------------------------
  1 | Node Tut #1    | Tut#1 Description | f         | 2020-01-29 10:42:57.121+07 | 2020-01-29 10:42:57.121+07
  3 | Node Tut #3    | Tut#3 Description | f         | 2020-01-29 10:43:48.028+07 | 2020-01-29 10:43:48.028+07
  2 | Node Js Tut #2 | Tut#2 Description | t         | 2020-01-29 10:43:05.131+07 | 2020-01-29 10:51:55.235+07
  4 | Js Tut #4      | Tut#4 Desc        | t         | 2020-01-29 10:45:40.016+07 | 2020-01-29 10:54:17.468+07
  5 | Js Tut #5      | Tut#5 Desc        | t         | 2020-01-29 10:45:44.289+07 | 2020-01-29 10:54:20.544+07

• Find all Tutorials which title contains 'js': GET /tutorials?title=js

• Find all published Tutorials using GET /tutorials/published Api

• Delete a Tutorial using DELETE /tutorials/:id Api

Tutorial with id=4 was removed from tutorials table:

testdb=# select * from tutorials;
 id |     title      |    description    | published |         createdAt          |         updatedAt
----+----------------+-------------------+-----------+----------------------------+----------------------------
  1 | Node Tut #1    | Tut#1 Description | f         | 2020-01-29 10:42:57.121+07 | 2020-01-29 10:42:57.121+07
  3 | Node Tut #3    | Tut#3 Description | f         | 2020-01-29 10:43:48.028+07 | 2020-01-29 10:43:48.028+07
  2 | Node Js Tut #2 | Tut#2 Description | t         | 2020-01-29 10:43:05.131+07 | 2020-01-29 10:51:55.235+07
  5 | Js Tut #5      | Tut#5 Desc        | t         | 2020-01-29 10:45:44.289+07 | 2020-01-29 10:54:20.544+07

• Delete all Tutorials using DELETE /tutorials Api

Now there are no rows in tutorials table:

testdb=# select * from tutorials;
 id | title | description | published | createdAt | updatedAt
----+-------+-------------+-----------+-----------+-----------

For more details, implementation and Github, please visit:
https://bezkoder.com/node-express-sequelize-postgresql/

Further Reading

Server side Pagination:
Node.js Express Pagination with PostgreSQL example

Fullstack:

• Vue.js + Node.js + Express + PostgreSQL example
• Angular 8 + Node.js Express + PostgreSQL example
• Angular 10 + Node.js Express + PostgreSQL example
• Angular 11 + Node.js Express + PostgreSQL example
• Angular 12 + Node.js Express + PostgreSQL example
• Angular 13 + Node.js Express + PostgreSQL example
• React + Node.js + Express + PostgreSQL example

Security:
Node.js JWT Authentication & Authorization with PostgreSQL example