Build a REST API With Node.js and PostgreSQL (GET, POST, PUT,DELETE) – Step by Step

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.

Here’s what we would cover today

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

So let’s get started!

 

1. Setup PostgreSQL/Nodejs

Interestingly, both PostgreSQL and Node.js are free!

Download PostgreSQL from here

Download Node.js from here

Install both packages. Please watch the video for the step by step. But it’s quite easy and clear.

Run this command to install PostgreSQL

npm install pg --save

 

2. Create Database Connection

Create a file called connection.js. This file would hold the connection data as shown below:

const {Client} = require('pg')

const client = new Client({
    host: "localhost",
    user: "postgres",
    port: 5432,
    password: "rootUser",
    database: "postgres"
})

module.exports = client

 

3. Create the Server and Client

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

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.

const client = require('./connection.js')
const express = require('express');
const app = express();

app.listen(3300, ()=>{
    console.log("Sever is now listening at port 3000");
})

client.connect();

 

Add the BodyParser: This is used to handle conversion to and from json.

const bodyParser = require("body-parser");
app.use(bodyParser.json());

You also need to install body-parser using npm install (see the video)

 

4. Get All Users

For GET requests, we use app.get() function. This function takes two parameters: the route /users  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  then send the result back.

app.get('/users', (req, res)=>{
    client.query(`Select * from users`, (err, result)=>{
        if(!err){
            res.send(result.rows);
        }
    });
    client.end;
})
client.connect();

 

5. Get User By Id

The code below is used to get a single user by id. Take note of how the parameter is passed in the url.

app.get('/users/:id', (req, res)=>{
    client.query(`Select * from users where id=${req.params.id}`, (err, result)=>{
        if(!err){
            res.send(result.rows);
        }
    });
    client.end;
})
client.connect();

 

6. Add New User

You can post a new user using the code below:

app.post('/users', (req, res)=> {
    const user = req.body;
    let insertQuery = `insert into users(id, firstname, lastname, location) 
                       values(${user.id}, '${user.firstname}', '${user.lastname}', '${user.location}')`

    client.query(insertQuery, (err, result)=>{
        if(!err){
            res.send('Insertion was successful')
        }
        else{ console.log(err.message) }
    })
    client.end;
})

 

7. Update User Details

Basically, the the update code follows the same pattern:

app.put('/users/:id', (req, res)=> {
    let user = req.body;
    let updateQuery = `update users
                       set firstname = '${user.firstname}',
                       lastname = '${user.lastname}',
                       location = '${user.location}'
                       where id = ${user.id}`

    client.query(updateQuery, (err, result)=>{
        if(!err){
            res.send('Update was successful')
        }
        else{ console.log(err.message) }
    })
    client.end;
})

 

8. Delete a User

The delete code is given below:

app.delete('/users/:id', (req, res)=> {
    let insertQuery = `delete from users where id=${req.params.id}`

    client.query(insertQuery, (err, result)=>{
        if(!err){
            res.send('Deletion was successful')
        }
        else{ console.log(err.message) }
    })
    client.end;
})

 

9. Next Steps

Thumbs up to you if you’ve come this far!

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.

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.

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.

Thanks and happy learning! I remain Kindson The Genius!