Build a Node.js Express CRUD REST API Using PostgreSQL Database in Browser Using Javascript

Welcome folks today in this blog post we will be talking about that how to create a basic CRUD rest API in node JS Express and postgreSQL database in JavaScript all the source code of this application will be given in the description.

Get Started

In order to get started guys first of all we need to initialise the node.js project using the below command

npm init -y

so the above command will create a package.json file for our node JS project now we need to install all the packages with which will be required for this project.

So first of all kinds we will be installing the express package to build the web server for this application so to install this the command will look like this

npm i express

and then guys we need to install the package which will be communicating with the postgreSQL database the command is shown below

npm i pg

and also there is one more dependency we need to install for this project which will automatically restart the server whenever we make any sort of changes the command is shown below

npm i –save-dev nodemon

The package.json file will look something like this as shown below

Directory Structure of the App

So now we will be showing the actual files and folders which will be needed for this project guys so in the below image you can see the screenshot of the directory structure

so now after that guys we need to create the starting point for application which would be the index for JS file

index.js

const express = require('express');
const bodyParser = require('body-parser');
const app = express();
const port = 3000;

app.use(bodyParser.json());
app.use(
  bodyParser.urlencoded({
    extended: true,
  })
);

app.get('/', (request, response) => {
  response.json({ info: 'Node.js, Express, and Postgres API' });
});

app.listen(port, () => {
  console.log(`App running on port ${port}.`);
});

const express = require('express');

const bodyParser = require('body-parser');

const app = express();

const port = 3000;

app.use(bodyParser.json());

app.use(

bodyParser.urlencoded({

extended: true,

})

);

app.get('/', (request, response) => {

response.json({ info: 'Node.js, Express, and Postgres API' });

});

app.listen(port, () => {

console.log(`App running on port ${port}.`);

});

So in the above block of code guys basically we have initialised simple Express application and we have started that it up at Port 3000 and inside that application we have a simple get request to the home route so whenever user opens the home page you will get a simple JSON response and then we are using the app.listen() method to start the application at that port number

Installing PostgreSQL Database

Now installing this post postgreSQL database we need to visit this link and simply download the software onsite your machine so this also offers a command line Utility tool as well alongside with this software which makes it very much easy to create database tables on the command line simply install the software on your machine as shown in the screenshot

So as you can see that guys after installing this software you will have a simple command line utility to inside that you can simply pick your password for your database and after that you can execute any sort of SQL command directly from the command line to create tables and data bases

CREATING DATABASE in PostgreSQL

CREATE DATABASE api;

1	CREATE DATABASE api;

Creating Tables in PostgreSQL

CREATE TABLE users (
  ID SERIAL PRIMARY KEY,
  name VARCHAR(30),
  email VARCHAR(30)
);

CREATE TABLE users (

ID SERIAL PRIMARY KEY,

name VARCHAR(30),

email VARCHAR(30)

);

Inserting Data into Tables in PostgreSQL

INSERT INTO users (name, email)
  VALUES ('Jerry', 'jerry@example.com'), ('George', 'george@example.com');

1 2	INSERT INTO users (name, email) VALUES ('Jerry', 'jerry@example.com'), ('George', 'george@example.com');

so now in the next step guys we need to write all the crud operations methods in a separate file called as queries.js so we need to create this file in the next step as shown below

queries.js

const Pool = require('pg').Pool;
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'users',
  password: '123456',
  port: 5432,
});

const Pool = require('pg').Pool;

const pool = new Pool({

user: 'postgres',

host: 'localhost',

database: 'users',

password: '123456',

port: 5432,

});

Connecting to PostgreSQL Database

As you can see the guys we are importing the pg library package that we are installed early on and with the help of this library we are instantiating a new constructor of this library inside this we are passing some options which are localhost username database name and the port number so generally the postgresql database runs on the port number which is 5432 and the default username is postgresql.

Making the CRUD Methods

Now guys will be making the different crud operations methods which we will be performing to the postgresql database so in which first of all we will be creating the records we will be reading all the records which are present inside the database we will be trying to update the record delete the record so all these operations will be performing in the next term.

const getUsers = (request, response) => {
  pool.query('SELECT * FROM users ORDER BY id ASC', (error, results) => {
    if (error) {
      throw error;
    }
    response.status(200).json(results.rows);
  });
};

const getUsers = (request, response) => {

pool.query('SELECT * FROM users ORDER BY id ASC', (error, results) => {

if (error) {

throw error;

}

response.status(200).json(results.rows);

});

};

As you can see that guys in the above code we are using the select statement and basically we have selecting all the data which is present inside the table Basically we are returning the data inside the ascending order

const getUserById = (request, response) => {
  const id = parseInt(request.params.id);

  pool.query('SELECT * FROM users WHERE id = $1', [id], (error, results) => {
    if (error) {
      throw error;
    }
    response.status(200).json(results.rows);
  });
};

const getUserById = (request, response) => {

const id = parseInt(request.params.id);

pool.query('SELECT * FROM users WHERE id = $1', [id], (error, results) => {

if (error) {

throw error;

}

response.status(200).json(results.rows);

});

};

As you can see that we are getting the specific information about a specific record using its ID that we are receiving it inside the parameter object so we are passing this ID in the select statement to get the desired information

const createUser = (request, response) => {
  const { name, email } = request.body;

  pool.query(
    'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
    [name, email],
    (error, results) => {
      if (error) {
        throw error;
      }
      response.status(201).send(`User added with ID: ${results.rows[0].id}`);
    }
  );
};

const createUser = (request, response) => {

const { name, email } = request.body;

pool.query(

'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',

[name, email],

(error, results) => {

if (error) {

throw error;

}

response.status(201).send(`User added with ID: ${results.rows[0].id}`);

}

);

};

As you can see that guys in the above code we are writing the function to create a new record for a new user inside the table and basically we are getting the name and the email from the raw JSON that we receive from the request object and then basically we are using the insert statement to insert the name and the email address to the table

const updateUser = (request, response) => {
  const id = parseInt(request.params.id);
  const { name, email } = request.body;

  pool.query(
    'UPDATE users SET name = $1, email = $2 WHERE id = $3',
    [name, email, id],
    (error, results) => {
      if (error) {
        throw error;
      }
      response.status(200).send(`User modified with ID: ${id}`);
    }
  );
};

const updateUser = (request, response) => {

const id = parseInt(request.params.id);

const { name, email } = request.body;

pool.query(

'UPDATE users SET name = $1, email = $2 WHERE id = $3',

[name, email, id],

(error, results) => {

if (error) {

throw error;

}

response.status(200).send(`User modified with ID: ${id}`);

}

);

};

So we can see that guys we are writing the update user function and inside this function we are taking two arguments first is the ID of the user that we need to update and the second argument we are taking is the actual information that we need to update which is the actual username and the email address and then basically we are using the update statement to actually update the records passing these two arguments in the statement

const deleteUser = (request, response) => {
  const id = parseInt(request.params.id);

  pool.query('DELETE FROM users WHERE id = $1', [id], (error, results) => {
    if (error) {
      throw error;
    }
    response.status(200).send(`User deleted with ID: ${id}`);
  });
};

const deleteUser = (request, response) => {

const id = parseInt(request.params.id);

pool.query('DELETE FROM users WHERE id = $1', [id], (error, results) => {

if (error) {

throw error;

}

response.status(200).send(`User deleted with ID: ${id}`);

});

};

As you can see in the above method we are taking the ID parameter in the delete user function so with the help of this ID parameter we are able to delete the user with the specified ID using the delete statement

module.exports = {
  getUsers,
  getUserById,
  createUser,
  updateUser,
  deleteUser,
};

module.exports = {

getUsers,

getUserById,

createUser,

updateUser,

deleteUser,

};

Lastly guys we are exporting all these methods that we defined early on using the module.exports statement so that we can import all these methods in different files of our project.

Include Methods in App.js

Now we need to include all the methods guys inside the app.js file so that we can perform the operations according to the routes that we define

const express = require('express');
const bodyParser = require('body-parser');
const app = express();
const port = 3000;

const db = require('./queries');

app.use(bodyParser.json());
app.use(
  bodyParser.urlencoded({
    extended: true,
  })
);

app.get('/', (request, response) => {
  response.json({ info: 'Node.js, Express, and Postgres API' });
});

app.get('/users', db.getUsers);
app.get('/users/:id', db.getUserById);
app.post('/users', db.createUser);
app.put('/users/:id', db.updateUser);
app.delete('/users/:id', db.deleteUser);

app.listen(port, () => {
  console.log(`App running on port ${port}.`);
});

const express = require('express');

const bodyParser = require('body-parser');

const app = express();

const port = 3000;

const db = require('./queries');

app.use(bodyParser.json());

app.use(

bodyParser.urlencoded({

extended: true,

})

);

app.get('/', (request, response) => {

response.json({ info: 'Node.js, Express, and Postgres API' });

});

app.get('/users', db.getUsers);

app.get('/users/:id', db.getUserById);

app.post('/users', db.createUser);

app.put('/users/:id', db.updateUser);

app.delete('/users/:id', db.deleteUser);

app.listen(port, () => {

console.log(`App running on port ${port}.`);

});

So we can see that guys we are including all these methods that we defined in that other file so we have different route for different methods out there

Testing the REST API Using Postman

As you can see that guys we are testing the rest crud API that we developed early on using The Postman software that we have to test out API without having to write any sort of front end so we can see that we are using the four methods which are get post put and delete to perform the crud operations