Postgres with Nodejs - 7 Minute Guide for Installation and Setup on Linux

In this tutorial, I am going to walk you through installing and setting up Postgres with Nodejs on Linux operating systems.

If you are using a Windows machine, please read this tutorial
If you are using a macOS machine, please read this tutorial.

If you wish for the simplest and easiest way to integrate the PostgreSQL database management system in your awesome Nodejs application, then you are in the right place!

Most Nodejs development tutorials on the web only teach you to integrate a NoSQL type when it comes to databases, especially MongoDB, because of its globally-achieved popularity from the MERN stack (MongoDB, Express, React, Nodejs) or the MEAN stack (MongoDB, Express, Angular, Nodejs) of web development.

However, there are some fans to the SQL types of databases especially PostgreSQL. So here I am to help you integrate Postgres with Nodejs applications!

By the end of this hopefully beginner-level tutorial, you should have understood the steps to install Postgres with Nodejs on your Linux operating system.

I have divided this into two parts. The first part will explain the installation of PostgreSQL on your machine and the second part will help you set up Postgres with Nodejs.

So, before we get started on installing Postgres with Nodejs, let’s get to know Postgres a bit.

What is the PostgreSQL Database Management System?

PostgreSQL is the world’s most advanced open-source database management system, or at least that’s what they say about themselves. PostgreSQL, as the name suggests, is an SQL-compliant relational database management system is and is free and extensible.

The PostgreSQL DBMS features a relational i.e., SQL and non-relational i.e., JSON querying support.

Being around for almost 30 years now, this database is highly stable and reputed. This means developers do not have to worry about its learning curve, reliability, accuracy, and assurance of performance.

Also, as if all that wasn’t enough, Postgres can be used as a primary database for web and mobile applications data storage.

Let us now move forward to installing Postgres on our Linux machine.

Installing & Setting Postgres with Nodejs on Linux (Ubuntu)

Pick your Linux Operating System PostgreSQL Installer

Postgres With Nodejs Download Page Linux

Select your Linux distribution. this will take you to the scripts section that would allow you to install Postgres. This tutorial will walk you through Postgres installation on Ubuntu 20.04.

You will need to execute the scripts one by one in your terminal. Let us get started. All code snippet credits in this section go to PostgreSQL Tutorial.

Step 1: Creating the file repository configuration

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Step 2: Importing the repository signing key

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –

Step 3: Updating the package list

$ sudo apt-get update

Step 4: Installing the latest version of PostgreSQL

$ sudo apt-get install postgresql

If you wish to install a specific version of PostgreSQL, pass this command

$ sudo apt-get install postgresql-12

Let’s say we want to install PostgreSQL 12; we can do it the way shown above. Likewise, you may install any preferred version passing the command in this format, postgresql-version.

Wait until the download and install are finished, it might take some time.

Connecting to the PostgreSQL Database Server Using psql

A user account in PostgreSQL is referred to as a role. It uses ident authentication by default.

This means PostgreSQL will associate roles with the system accounts created on your Linux. Let’s say for example a role exists in PostgreSQL, the same Linux user with the same name will have access to login as that role.

So, when we installed PostgreSQL, the installation process automatically created a user account called postgres that is associated with the default role called postgres.

Now we want to connect to PostgreSQL using the postgres role. We will switch over to the postgres account on our server passing the command

$ sudo -i -u postgres

Now, you will be prompted to enter a password. Enter a password and hit enter.

Next, to access PostgreSQL, pass the below command

$ psql

To access the Postgres prompt, pass this command

postgres=#

Now, you can begin interacting with the PostgreSQL database like issuing a query.

Quit the PostgreSQL prompt by running the following command:

postgres=# \q

This command will take you back to the postgres Linux command prompt.

postgres@ubuntu-dev:~$

To go back to your regular system user, you must execute the exit command like this:

postgres@ubuntu-dev:~$ exit

Setting up Postgres with Nodejs on Linux

Now that we have the PostgreSQL database installed, let us now move on to the next step of integrating Postgres with Nodejs projects on your Linux machine.

This post assumes you have installed Nodejs on your machine. If you haven’t, click here to learn how.

Step 1: Switch to your terminal and create your project directory.

$ mkdir POSTGRES_NODEJS_APP

Step 2: You may pass this command to list out files in the current directory. If your newly created project directory name, simply proceed to step 3.

$ ls

Step 3: Change your directory to your project directory.

$ cd POSTGRES_NODEJS_APP/

Step 4: Create your Nodejs application.

$ npm init -y

You should now see a package.json file created and should look like this:

{
"name": "POSTGRES_NODEJS_APP",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo "Error: no test specified" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC"
}

Step 5: Create the .js entry point file for your Nodejs project.

$ touch index.js

Step 6: Install the ‘pg’ and the ‘pgtools’ packages using NPM. The pgtools package will allow us to modify our database(s) on the PostgreSQL server.

$ npm i pg pgtools

Step 7: Open your .js file in your editor and set up a connection to your db, like this.

Step 8: Run your .js file to create your database.

$ node index.js

The output should look like this:

Result {
command: 'CREATE',
rowCount: NaN,
oid: null,
rows: [],
fields: [],
_parsers: [],
RowCtor: null,
rowAsArray: false,
_getTypeParser: [Function: bound ]
}

Step 9: To check if your connection was successful, run this command to get a list of your created databases. Now, look for your newly created database. If you find it, it means your DB was created and the connection was successful.

$ \l

The output should now look like this indicating a successful Postgres with Nodejs set up:

Conclusion

In this tutorial, I am going to walk you through installing and setting up Postgres with Nodejs on Linux operating systems. By the end of this hopefully beginner-level tutorial, you should have understood the steps to install Postgres with Nodejs on your Linux operating system.