Rest API with PostgreSQL and Node Js, Step-by-Step Tutorial

In this article we will be learning the following

1. Creating Node Js server with Express
2. Creating Simple CURD APIs
3. Integrating with PostgreSQL database
4. Sending, receiving data from the API to the database

So lets get started

Dead Simple Chat offers powerful Javascript Chat SDK and APIs that you can use to easily add Chat functionality to your React Applications.

Step 1 Pre-requisites

We need the following to follow along this with tutorial  

1. Node JS and Express Js
2. PostgreSQL
3. Knowledge of JavaScript

Step 2: Setting up the system and installing dependencies

create a new directory and name it rest-api-node-postgresql and then cd into it.

then in your terminal type the command npm init to initialize a new project

after you have initialized the project the package.json file will be created and it should look something like this.

While initializing the project you can select any settings you want except for the main entry file should be index.js

{
  "name": "rest-api-node-postgresql",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC"
}

Now, let us intall the expressjs web framework. type the below commmand on your terminal to install the express and and save it as a dependency

npm install express --save

now let us start the server. Paste the below code in your index .js file

const express = require('express')
const app = express()
const port = 8080

app.get('/', (req, res) => {
  res.send('Hello World!')
})

app.listen(port, () => {
  console.log(`Example app listening on port ${port}`)
})

we now have a server running on port 8080

Installing PG: Postgres client for Node Js

Next let us install pg the postgres client for node js. type the below command in your terminal

npm install pg --save

Step 3: Setting Up the database connection

we have started our server and install the pg client for node js, now let us set up out connection to the database

to set up the database connection first you need a database. I have installed postgresql database in a docker container using the docker desktop

In the next few steps I will be explaining how to set up the database in docker. If you have a database running or if you know how to set up database in docker, you can skip this step

Setting up local postgreSQL database in docker container

Install docker in your machine and then open the terminal

a. Pull the database image from docker hub

type the below code to install the postgreSQL database in a docker container

docker pull postgres

This will pull the latest postgres image from the docker hub

b. create a dedicated volume for database storage

then create a dedicate volume for database storage in your machine for data persistence

type the below command to create a dedicate volume

docker volume create postgresvol

c. start the database

run the below command to start the database

docker run --name database-postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 -v postgresvol:/var/lib/postgresql/data -d postgres

what are we doing here in this code

we have name our container database-postgres and we have set a password for the database as postgres and we have bind the containers port on which the database is running to our local machine port of 5432

lastly we have mount the postgresvol vol that we created to the containers '/var/lib/postgresql/data' data directory

d. accessing the database

type the below command to access the database

docker ps

the docker ps command lists tje number of conatainers and you can see there the database container running

to access the database type the below command

psql -h localhost -p 5432 -U postgres

Now that we have established the local database in a docker container let us move on to our application

Configuring the database from our Node Js server

Now that we have started our database server. Lets us now connect to the server from our nodejs server and create a database table named albums

write the below code in your index.js file

const express = require('express')
const { Pool } = require('pg');
const app = express()
const port = 8080

const pool = new Pool({
    user: 'postgres',
    host: 'localhost',
    database: 'postgres',
    password: 'postgres',
    port: 5432,
  });
  
  app.use(express.json());

  async function createAlbumsTable() {
    try {
      const query = `
        CREATE TABLE IF NOT EXISTS albums (
          id SERIAL PRIMARY KEY,
          title VARCHAR(255) NOT NULL,
          artist VARCHAR(255) NOT NULL,
          price NUMERIC(10, 2)
        );
      `;
  
      await pool.query(query);
      console.log('Albums table created');
    } catch (err) {
      console.error(err);
      console.error('Albums table creation failed');
    }
  }
  
  createAlbumsTable();

So, what are we doing here

we have required the pg as a dependency

and we are creating a new pool and connecting to our database

then we are using the express.json middleware to parse json

after that we have written a function  that checks if a table named album exists in our database and if not creates a table named albums

the variable in the table are, id, title, artist name and price

then we are quering the pool and console logging that the table has been created

If we are unable to create the table for some reason we are logging the error to the console

Dead Simple Chat offers powerful Javascript Chat SDK and APIs that you can use to easily add Chat functionality to your React Applications.

Step 4 Creating Routes

Now that we have connected to the database and created the table albums let us create some API routes to get, update, post and delete albums basically CURD APIs

POST API

Let us first create a POST API to receive and store the album data in our postgres database

Paste the below code in your index.js files

app.post('/albums', async (req, res) => {
    // Validate the incoming JSON data
    const { title, artist, price } = req.body;
    console.log(req.body);
    if (!title || !artist || !price) {
      return res.status(400).send('One of the title, or artist, or price is missing in the data');
    }
  
    try {
      // try to send data to the database
      const query = `
        INSERT INTO albums (title, artist, price)
        VALUES ($1, $2, $3)
        RETURNING id;
      `;
      const values = [title, artist, price];
  
      const result = await pool.query(query, values);
      res.status(201).send({ message: 'New Album created', albumId: result.rows[0].id });
    } catch (err) {
      console.error(err);
      res.status(500).send('some error has occured');
    }
  });

So, what does this do

this API endpoint receives JSON data data and transfers the data to const title, artist, price.

then check in the if statement that all are present or not. Even if one of the above const are null the API rejects the data and respond with a status 400 missing fields

after checking that the data is correct the api tries to insert the data into the database table albums

and if it is successful it returns and id related to the entry that is saved in the database

After that the API returns the status 201 album created successfully with album id or if some error occurs the api returns status 500 error occurred

Now that we have created a POST API, the complete code till now looks like

const express = require('express')
const { Pool } = require('pg');
const app = express()
const port = 8080

const pool = new Pool({
    user: 'postgres',
    host: 'localhost',
    database: 'postgres',
    password: 'postgres',
    port: 5432,
  });
  
  app.use(express.json());

  async function createAlbumsTable() {
    try {
      const query = `
        CREATE TABLE IF NOT EXISTS albums (
          id SERIAL PRIMARY KEY,
          title VARCHAR(255) NOT NULL,
          artist VARCHAR(255) NOT NULL,
          price NUMERIC(10, 2)
        );
      `;
  
      await pool.query(query);
      console.log('Albums table created');
    } catch (err) {
      console.error(err);
      console.error('Albums table creation failed');
    }
  }
  
  createAlbumsTable();


app.get('/', (req, res) => {
  res.send('Hello World!')
})

app.post('/albums', async (req, res) => {
    // Validate the incoming JSON data
    const { title, artist, price } = req.body;
    console.log(req.body);
    if (!title || !artist || !price) {
      return res.status(400).send('One of the title, or artist, or price is missing in the data');
    }
  
    try {
      // try to send data to the database
      const query = `
        INSERT INTO albums (title, artist, price)
        VALUES ($1, $2, $3)
        RETURNING id;
      `;
      const values = [title, artist, price];
  
      const result = await pool.query(query, values);
      res.status(201).send({ message: 'New Album created', albumId: result.rows[0].id });
    } catch (err) {
      console.error(err);
      res.status(500).send('some error has occured');
    }
  });
  
app.listen(port, () => {
  console.log(`Example app listening on port ${port}`)
})

Example

Let us now try to send some data to the API and see what is returned. I am using postman to send data but you can use postman or curl to send data to the api as well

Dead Simple Chat offers powerful Javascript Chat SDK and APIs that you can use to easily add Chat functionality to your React Applications.

GET API to get all albums from the database

Now that we have created a post API to send data to the server and save in the database

let us now create a get all albums API endpoint /albums

when a request is received at this end point the server will fetch all the rows from from the database table and return it to the user in JSON format

paste this code in your index.js file

app.get('/albums', async (req, res) => {
    try {
      const query = 'SELECT * FROM albums;';
      const { rows } = await pool.query(query);
      res.status(200).json(rows);
    } catch (err) {
      console.error(err);
      res.status(500).send('failed');
    }
  });

what are we doing in this code

we a request is recieved at the /albums endpoint we try to get all the data from the albums table from our database, convert that data to json and send the data to the front end with status code 200

if there is an error we retuned failed along with status code 500

Let us try to get all the albums. You can try this using your browser as well as using postman or curl

using browser fgo to localhost://8080/albums and you will get the json data returned like

"id":1,"title":"sdkfjkl","artist":"hello world","price":"19.99"},{"id":2,"title":"cool man","artist":"awesome man","price":"20.00"}]

Dead Simple Chat offers powerful Javascript Chat SDK and APIs that you can use to easily add Chat functionality to your React Applications.

API to get a specific Album from the database

We can create an end point where we can query an specific album by id

to do this write the below code

  app.get('/albums/:id', async (req, res) => {
    try {
      const { id } = req.params;
      const query = 'SELECT * FROM albums WHERE id = $1;';
      const { rows } = await pool.query(query, [id]);
  
      if (rows.length === 0) {
        return res.status(404).send('this album is not in the database');
      }
  
      res.status(200).json(rows[0]);
    } catch (err) {
      console.error(err);
      res.status(500).send('failed');
    }
  });

what are we doing here

we are receiving the request on /albums/:id like /albums/1 etc and then we are extracting the id from the request and querying the database for the specific album

Once we find the album we are sending the album to the front end with the 200 status

If we do not find the album in the database we send the status 404 along with the message that the album is not in the database

if there is an error we send the failed message along with the status 500

API to update the album

write this code to update an album using the id

  app.put('/albums/:id', async (req, res) => {
    try {
      const { id } = req.params;
      const { title, artist, price } = req.body;
  
      if (!title && !artist && !price) {
        return res.status(400).send('provide a field (title, artist, or price)');
      }
  
      const query = `
        UPDATE albums
        SET title = COALESCE($1, title),
            artist = COALESCE($2, artist),
            price = COALESCE($3, price)
        WHERE id = $4
        RETURNING *;
      `;
      const { rows } = await pool.query(query, [title, artist, price, id]);
  
      if (rows.length === 0) {
        return res.status(404).send('Cannot find anything');
      }
  
      res.status(200).json(rows[0]);
    } catch (err) {
      console.error(err);
      res.status(500).send('Some error has occured failed');
    }
  });

we receive a put request on the /albums/id and we receive the data to be updated in the body in JSO N format

we check if there is data otherwise we send that a field should be provided

then if there is data we query the database and update the database with the new data

if we cannot find the table we send 'cannot find anything'

if an error has occurred we send some error has occurred

let us try postman to update an album with id of 1

Now that we have built the api to update the data let us know built the api to delete the data

API to delete the album

Write the below code to delete the album using an id

  app.delete('/albums/:id', async (req, res) => {
    try {
      const { id } = req.params;
      const query = 'DELETE FROM albums WHERE id = $1 RETURNING *;';
      const { rows } = await pool.query(query, [id]);
  
      if (rows.length === 0) {
        return res.status(404).send('we have not found the album');
      }
  
      res.status(200).json(rows[0]);
    } catch (err) {
      console.error(err);
      res.status(500).send('some error has occured');
    }
  });

In the delete api we recieve the data on the delete endpoint along with the id of the album that needs to be deleted

we query the database and delete the album with the id  

if we cannot find the album we send the status code 404 along with the message that the album cannot be found

if successful in deleting the album we send the album data to the front end

if some error has occoured we send status code 500 along with the message that some error has occured

let us try and delete the album with the id of 1 using postman

You might be interested in some of our other articles

• Rest API with GO Lang and PostgreSQL
• API vs SDK: Explained with Examples
• Node.JS MongoDB: The Complete Guide
• Data Fetching with getServerSideProps and getStaticProps

Conclusion

In this article we have created an REST API server with nodejs and postgreSQL. We created api to create, update, delete the data. We also stored the data in the database and retrived the data from the database

I hope you have found the article to be useful

thank you for reading