Shivam1910/crud-backend-postgres

Node.js PostgreSQL CRUD example with Express Rest APIs

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

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

• GET api/tutorials get all Tutorials
• GET api/tutorials/:id get Tutorial by id
• POST api/tutorials add new Tutorial
• PUT api/tutorials/:id update Tutorial by id
• DELETE api/tutorials/:id remove Tutorial by id
• DELETE api/tutorials remove all Tutorials
• GET api/tutorials/published find all published Tutorials
• GET api/tutorials?title=[kw] find all Tutorials which title contains 'kw'

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:

 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:

 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:

 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:

 id | title | description | published | createdAt | updatedAt
----+-------+-------------+-----------+-----------+-----------

Project setup

npm i

Run

npm start