In this article, we are going to learn about one of the most used databases with Node Js,PostgreSQL.We will learn how to integrate Node JS and PostgreSQL,make basic APIs for CRUD operations and the pros and cons of using PostgreSQL as a Database.Now we assume that you have learnt about the basics of Node JS and having it in your systems, if not then go through this article.

This article is split into 2 parts:

What is PostgreSQL

PostgreSQL, commonly referred to as Postgres, is a free and open source relational database management system. You might be familiar with a few other similar database systems, such as MySQL, Microsoft SQL Server, or MariaDB, which compete with PostgreSQL.

PostgreSQL is a robust but stable relational database that has been around since 1997 and is available on all major operating systems — Linux, Windows, and macOS. Since PostgreSQL is known for stability, extensibility, and standards compliance, it’s a popular choice for developers and companies to use for their database needs.We’ll begin this tutorial by installing PostgreSQL, creating a new user, creating a database, and initializing a table with schema and some data.

Installation

Check if your system is already having PostgreSQL installed or not by typing this command in your terminal:

$ postgres -V

You will see the version of postgres as output with some more details.If PostgreSQL is not installed on your machine, you should head to PostgreSQL Download Center, download and install the version compatible with your operating system before you continue and setup your user as root.

Let’s Dive In

Create a new folder( “newProject” or whatever you want) wherever you want and open that folder in your favourite text editor and open the integrated terminal if available else open the default terminal and head over to the directory you just created.Enter the following command and answer the questions to create the initial structure for your new project:

$  npm init

Now the next step would be to install driver dependencies.The three basic packages we are going to install are:

  • 1- ExpressJS: It’s a flexible Node.JS web application that has many features for web and mobile applications
  • 2- node-postgres(pg): to be able to connect to PostgreSQL.
  • 3- body-parser: package that can be used to handle JSON requests.

These packages can be installed by following command in the terminal:

$  npm install –save express body-parser pg

Now create a file server.js in the same directory and require all the dependencies and assigning a port as stated below(you can copy paste this exact code):

const express = require(‘express’);
const bodyParser = require(‘body-parser’);
const app = express();
// initializing our app
let port = 3000;
app.listen(port, () => {
console.log(‘Server is up and running on port number ‘ + port);
});

And after this step we need to use bodyparser.Body Parser is an npm package that is used to parse the incoming request bodies in a middleware.Paste the following code just below the one you just pasted.

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

Save this and head over to terminal to test this by typing node server.js command.

By doing so you will see this in your terminal Server is up and running on port number 3000, and this shows that we have successfully created our app.

Create Database And Table

For creating new database first open a new terminal and type the command sudo su – postgres . This will then ask for the password that you setup while creating the user.We can create a database with the SQL command.

postgres=# CREATE DATABASE test;

After this use the \list command to see the available databases.You will see the name of database you just created.Now lets connect to this database by using the following command which will display that we are connected to this newly created database.

postgres=# \c test;

The last thing we’ll do in the psql command prompt is create a table called users with three fields – two VARCHAR types and an auto-incrementing PRIMARY KEY id. For this enter the following command in the same terminal.

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

Now we will add a few entries to users to have some data to work with.You can do so easily by following command.

test-#   Insert into users VALUES (1,’Yash’, ‘Yash@temp.com’), (2,’Kesarwani’, ‘Kesarwani@temp.com’);

Now we have a database, table, and some data. We can begin building our Node.js CRUD APIs to connect to this data stored in a PostgreSQL database.

Connecting with PostgreSQL-DB

We’ll be using the node-postgres module to create a pool of connections. This way we don’t have to open a client and close it every time we make a query.Copy the following code in your app.js file just below where we are requiring files.

const Pool = require(‘pg’).Pool
const pool = new Pool({
user: ‘root’,
host: ‘localhost’,
database: ‘test’,
password: ‘password’,
port: 5432,
})

And after this step we need to use bodyparser.Body Parser is an npm package that is used to parse the incoming request bodies in a middleware.Paste the following code just below the one you just pasted.

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

Your app.js will now look something like this.

const express = require(“express”);
const bodyParser = require(“body-parser”);
const Pool = require(‘pg’).Pool
const app = express();

// initializing our app
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended: false}));
const pool = new Pool({
user: ‘root’,
host: ‘localhost’,
database: ‘test’,
password: ‘password’,
port: 5432,
})
app.get(“/”, (req, res) => {
res.json({ “message”:”Working Fine” });
});
let port = 3000;
app.listen(port, () => {
console.log(“Server is up and running on port number ” + port);
});

You can check the working of this till now by starting the app.js file same as earlier and then heading over to your browser and hitting this URL http://localhost:3000

You will see something like this as response.

If you also see the same as above, then we are ready to move on to the next step which is creating CRUD APIs.(Note- Before creating APIs download postman in your system from here,it is a very useful tool in testing your APIs. Open the app, make sure it’s a GET request and type the following url ‘localhost:3000/’. Just make sure that your server is still running on the port number 3000.).

That’s all I have and thanks a lot for reading. Please let me know if any corrections/suggestions. Please do share and comments if you like the post. Thanks in advance… 


Thanks Yash Kesarwani for helping us to grow day by day. He is expert in Node Js and loves to solve competitive programming.

Categories: Miscellaneous

0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Insert math as
Block
Inline
Additional settings
Formula color
Text color
#333333
Type math using LaTeX
Preview
\({}\)
Nothing to preview
Insert