How to Build a Simple REST API with Node.js and PostgreSQL
In this tutorial , we will learn how to create a simple RESTful API using 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. Whether you're an experienced developer or new to Node.js and PostgreSQL, this tutorial video is a valuable resource for anyone looking to build robust and scalable web applications.
Table of Contents
- Setup PostgreSQL/Nodejs
- Create Database Connection
- Create the Server and Client
- Get All Users
- Get User By Id
- Add New User
- Update User
- Delete User
- Next Steps
Let's get started
1. Setup PostgreSQL/Nodejs
Interestingly, both PostgreSQL and Node.js are free!
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
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
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.
Originally published by kindsonthegenius at kindsonthegenius
Happy Coding!!!
