{"id":1971,"date":"2021-05-01T12:00:00","date_gmt":"2021-05-01T10:00:00","guid":{"rendered":"https:\/\/kindsonthegenius.com\/blog\/build-a-rest-api-with-node-js-and-postgresql-get-post-putdelete-step-by-step\/"},"modified":"2026-07-05T03:25:25","modified_gmt":"2026-07-05T01:25:25","slug":"build-a-rest-api-with-node-js-and-postgresql-get-post-putdelete-step-by-step","status":"publish","type":"post","link":"https:\/\/kindsonthegenius.com\/blog\/build-a-rest-api-with-node-js-and-postgresql-get-post-putdelete-step-by-step\/","title":{"rendered":"Build a REST API With Node.js and PostgreSQL (GET, POST, PUT,DELETE) \u2013 Step by Step"},"content":{"rendered":"

In this lesson, you will learn in few minutes how to build a simple REST API with Node.js and PostgreSQL. This REST API would serve a list of users. So we would setup PostgreSQL and create a database and a users table. Then we would also setup Node.js and create database connection.<\/p>\n

Here’s what we would cover today<\/p>\n

    \n
  1. Setup PostgreSQL\/Nodejs<\/a><\/li>\n
  2. Create Database Connection<\/a><\/li>\n
  3. Create the Server and Client<\/a><\/li>\n
  4. Get All Users<\/a><\/li>\n
  5. Get User By Id<\/a><\/li>\n
  6. Add New User<\/a><\/li>\n
  7. Update User<\/a><\/li>\n
  8. Delete User<\/a><\/li>\n
  9. Next Steps<\/a><\/li>\n<\/ol>\n

    So let’s get started!<\/p>\n

     <\/p>\n

    1. Setup PostgreSQL\/Nodejs<\/strong><\/h4>\n

    Interestingly, both PostgreSQL and Node.js are free!<\/p>\n

    Download PostgreSQL from here<\/a><\/p>\n

    Download Node.js from here<\/a><\/p>\n

    Install both packages. Please watch the video for the step by step. But it’s quite easy and clear.<\/p>\n

    Run this command to install PostgreSQL<\/p>\n

    npm install pg --save\r\n<\/pre>\n
     <\/p>\n
    2. Create Database Connection<\/strong><\/h4>\n
    Create a file called connection.js. This file would hold the connection data as shown below:<\/p>\n
    const<\/span> {Client} =<\/span> require('pg'<\/span>)\r\n\r\nconst<\/span> client =<\/span> new<\/span> Client({\r\n    host:<\/span> \"localhost\"<\/span>,\r\n    user:<\/span> \"postgres\"<\/span>,\r\n    port:<\/span> 5432<\/span>,\r\n    password:<\/span> \"rootUser\"<\/span>,\r\n    database:<\/span> \"postgres\"<\/span>\r\n})\r\n\r\nmodule.exports =<\/span> client\r\n<\/pre>\n
     <\/p>\n
    3. Create the Server and Client<\/strong><\/h4>\n
    Node.js allows us to create a server. Now you need to create a second file. I call it api.js (but you can give it any name).<\/p>\n
    Write the following code inside. This code creates a server listening at port 3300. Then a client is create as well that connects to the server.<\/p>\n
    const<\/span> client =<\/span> require('.\/connection.js'<\/span>)\r\nconst<\/span> express =<\/span> require('express'<\/span>);\r\nconst<\/span> app =<\/span> express();\r\n\r\napp.listen(3300<\/span>, ()=><\/span>{\r\n    console.log(\"Sever is now listening at port 3000\"<\/span>);\r\n})\r\n\r\nclient.connect();\r\n<\/pre>\n
     <\/p>\n
    Add the BodyParser:<\/strong> This is used to handle conversion to and from json.<\/p>\n
    const<\/span> bodyParser =<\/span> require(\"body-parser\"<\/span>);\r\napp.use(bodyParser.json());\r\n<\/pre>\n
    You also need to install body-parser using npm install (see the video)<\/p>\n
     <\/p>\n
    4. Get All Users<\/strong><\/h4>\n
    For GET requests, we use app.get() function. This function takes two parameters: the route \/users\u00a0 and a callback. The callback is an arrow function that executes when a request is received. The callback take two parameter: request and response. Inside the callback, we use the client to query the database and\u00a0 then send the result back.<\/p>\n
    app.get('\/users'<\/span>, (req, res)=><\/span>{\r\n    client.query(`<\/span>Select *<\/span> from users`<\/span>, (err, result)=><\/span>{\r\n        if<\/span>(!<\/span>err){\r\n            res.send(result.rows);\r\n        }\r\n    });\r\n    client.end;\r\n})\r\nclient.connect();\r\n<\/pre>\n
     <\/p>\n
    5. Get User By Id<\/strong><\/h4>\n
    The code below is used to get a single user by id. Take note of how the parameter is passed in the url.<\/p>\n
    app.get('\/users\/:id'<\/span>, (req, res)=><\/span>{\r\n    client.query(`<\/span>Select *<\/span> from users where id=<\/span>${req.params.id}`<\/span>, (err, result)=><\/span>{\r\n        if<\/span>(!<\/span>err){\r\n            res.send(result.rows);\r\n        }\r\n    });\r\n    client.end;\r\n})\r\nclient.connect();\r\n<\/pre>\n
     <\/p>\n
    6. Add New User<\/strong><\/h4>\n
    You can post a new user using the code below:
    \n<\/p>\n
    app.post('\/users'<\/span>, (req, res)=><\/span> {\r\n    const<\/span> user =<\/span> req.body;\r\n    let<\/span> insertQuery =<\/span> `<\/span>insert into users(id, firstname, lastname, location) \r\n                       values(${user.id}, '${user.firstname}'<\/span>, '${user.lastname}'<\/span>, '${user.location}'<\/span>)`<\/span>\r\n\r\n    client.query(insertQuery, (err, result)=><\/span>{\r\n        if<\/span>(!<\/span>err){\r\n            res.send('Insertion was successful'<\/span>)\r\n        }\r\n        else<\/span>{ console.log(err.message) }\r\n    })\r\n    client.end;\r\n})\r\n<\/pre>\n
     <\/p>\n
    7. Update User Details<\/strong><\/h4>\n
    Basically, the the update code follows the same pattern:<\/p>\n
    app.put('\/users\/:id'<\/span>, (req, res)=><\/span> {\r\n    let<\/span> user =<\/span> req.body;\r\n    let<\/span> updateQuery =<\/span> `<\/span>update users\r\n                       set firstname =<\/span> '${user.firstname}'<\/span>,\r\n                       lastname =<\/span> '${user.lastname}'<\/span>,\r\n                       location =<\/span> '${user.location}'<\/span>\r\n                       where id =<\/span> ${user.id}`<\/span>\r\n\r\n    client.query(updateQuery, (err, result)=><\/span>{\r\n        if<\/span>(!<\/span>err){\r\n            res.send('Update was successful'<\/span>)\r\n        }\r\n        else<\/span>{ console.log(err.message) }\r\n    })\r\n    client.end;\r\n})\r\n<\/pre>\n
     <\/p>\n
    8. Delete a User<\/strong><\/h4>\n
    The delete code is given below:<\/p>\n
    app.delete<\/span>('\/users\/:id'<\/span>, (req, res)=><\/span> {\r\n    let<\/span> insertQuery =<\/span> `<\/span>delete<\/span> from users where id=<\/span>${req.params.id}`<\/span>\r\n\r\n    client.query(insertQuery, (err, result)=><\/span>{\r\n        if<\/span>(!<\/span>err){\r\n            res.send('Deletion was successful'<\/span>)\r\n        }\r\n        else<\/span>{ console.log(err.message) }\r\n    })\r\n    client.end;\r\n})\r\n<\/pre>\n
     <\/p>\n
    9. Next Steps<\/strong><\/h4>\n
    Thumbs up to you if you’ve come this far!<\/p>\n
    Now I recommend we do the same using MySQL. The procedure is similar with just minor difference relating to the connection. I’ll leave this up to you as a home.<\/p>\n
    So we succeeded in building the API and creating database connection. Once you have an API (Application Programming Interface), then you also need a UI(User Interface). In the next lesson, we would build the UI using Angular 11, Angular Materials and Bootstrap.<\/p>\n
    It’s better you subscribe to my YouTube Channel so that when the next lesson come out in few days, you’ll get notified. Also find the complete code for this lesson in my GitHub Repository.<\/p>\n
    Thanks and happy learning! I remain Kindson The Genius!<\/p>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    In this lesson, you will learn in few minutes how to build a simple REST API with Node.js and PostgreSQL. This REST API would serve … <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"pagelayer_contact_templates":[],"_pagelayer_content":"","footnotes":""},"categories":[289],"tags":[],"class_list":["post-1971","post","type-post","status-publish","format-standard","hentry","category-rest-web-services"],"_links":{"self":[{"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/posts\/1971","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/comments?post=1971"}],"version-history":[{"count":1,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/posts\/1971\/revisions"}],"predecessor-version":[{"id":2139,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/posts\/1971\/revisions\/2139"}],"wp:attachment":[{"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/media?parent=1971"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/categories?post=1971"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/tags?post=1971"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}