Postgres with Nodejs Install and Set-Up: Easy 7 Minute Step-by-Step Guide for Windows

Postgres With Nodejs Featured Image

In this tutorial, I am going to walk you through the easiest ways to install and set up Postgres with Nodejs on Windows.

If you always wanted a PostgreSQL database integrated into your Nodejs project but didn’t know how, this tutorial is the right place for you!

Most Nodejs development you flicked on the web only contain a NoSQL type database integration because the MERN stack is so popular. You will mostly find MongoDB integration almost everywhere with Nodejs.

However, many developers find convenience in or are habituated to using SQL databases, among which Postgres with Nodejs is a favorite of many.

By the end of this easy step-by-step tutorial on Postgres with Nodejs on Windows, you should have a PostgreSQL database up and running for your awesome web application.

This article is apportioned into two parts. The first part covers up the installation of PostgreSQL. The second part of the guide conquers setting up Postgres with Nodejs.

What is the PostgreSQL Database?

PostgreSQL calls itself the world’s most advanced open-source database. This SQL-compliant relational database management system is free and extensible.

The PostgreSQL DBMS supports relational i.e., SQL and non-relational i.e., JSON querying.

It is a highly stable and a reputed database management system with over 30 years of continuous development. It can be used as a primary database for web and mobile applications data storage.

Setting up Postgres with Nodejs

Let’s get started with our Postgres with Nodejs installation.

Installing & Setting Postgres with Nodejs on Windows

1) Download PostgreSQL Installer for Windows

To install Postgres with Nodejs on Windows, you will first need an installer. Jump to this official download page of PostgreSQL installers on EnterpriseDB.

2) Pick your Preferred Version of PostgreSQL

Postgres With Nodejs Edb Download Page Win
Source: PostgreSQL Tutorial

Your download should now start. Once it does, it will take some time depending on your internet connection.

3) Set up PostgreSQL using Installer

To be able to set up PostgreSQL on Windows, you must have admin privileges on your current user.

Now, open the installer you just downloaded and follow the steps below.

Step 1: Click Next.

Postgres With Nodejs Install Win Step 1

Step 2: Choose the directory in which you want PostgreSQL to be installed. You may choose your own path or stick to the default. Once you’re done, click Next.

Postgres With Nodejs Install Win Step 2

Step 3: Select software components you want to install:

Postgres With Nodejs Install Win Step 3
  • PostgreSQL Server – Installs the PostgreSQL database server.
  • pgAdmin 4 – Installs a PostgreSQL database GUI management tool.
  • Command Line Tools – Installs command-line tools such as psql, pg_restore, etc. necessary to interact with your PostgreSQL DB server using CLI.
  • Stack Builder – Installs a GUI that enables you to download and install drivers that work with Postgres. You can choose to omit this as it is optional.

Step 4: Select your preferred database directory or stick to the default. Once you’re done click Next.

Postgres With Nodejs Install Win Step 4

Step 5: Enter a password for the database superuser (postgres).

Postgres With Nodejs Install Win Step 5

Step 6: Select a port that your PostgreSQL database will listen to. The default is set to 5432. Also, make sure no other applications are using this port.

Postgres With Nodejs Install Win Step 6

Step 7: Choose a locale for your database cluster. The default locale uses the operating system locale. You can stick to the default if you prefer.

Postgres With Nodejs Install Win Step 7

Step 8: You will now be shown a summary of your install. Check if everything is as per your choice. Click Next if everything looks fine.

Postgres With Nodejs Install Win Step 8

Step 9: Now if you are ready to install Postgres, click Next.

Postgres With Nodejs Install Win Step 9

Wait for the installation to finish. It may take a few minutes to complete.

Postgres With Nodejs Install Win Step 10

Step 10: Hit Finish once the PostgreSQL installation is complete.

Postgres With Nodejs Install Win Step 11

4) Verify your PostgreSQL Installation

To quickly verify your installation, you can use the psql client.

Step 1: Jump to your Start menu and look for the PostgreSQL 12 folder (the version number might vary depending on the PostgreSQL version you installed). Click the SQL Shell (psql) client and open it.

Postgres With Nodejs Verify Win Step 1
Source: PostgreSQL Tutorial

Step 2: Configure your local PostgreSQL account. You must enter the password in here that you created during installation.

Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (12.3)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

Step 3: Finally, you may now run the SELECT version() command.

Postgres With Nodejs Verified Win
Source: PostgreSQL Tutorial

Setting up Postgres with Nodejs

Now that we have our database set up, let us move on to the next step of integrating Postgres with Nodejs projects.

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

So, let’s get started.

Step 1: Open your terminal or Command Prompt and create your project directory.

$ mkdir POSTGRES_NODEJS_APP

Step 2: To check if your directory is created by listing out the files and directories in your current directory, pass this command. If you find your newly created project directory name, simply jump to the next step.

$ ls

Step 3: Change the directory to your project directory.

$ cd POSTGRES_NODEJS_APP/

Step 4: Create your Nodejs Project.

$ npm init -y

Your package.json file should now be 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 .js 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.

const pgtools = require("pgtools");
const config = {
  user: "postgres",
  host: "localhost",
  password: "some+random?password!",
  port: 5432
};

pgtools.createdb(config, "myFirstDb", function(err, res) {
  if (err) {
    console.error(err);
    process.exit(-1);
  }
  console.log(res);
});

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. Run the below command which is a shorthand for

\list

$ \l

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

List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
myFirstDb | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
postgres | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |

Conclusion

In this tutorial, I am going to walk you through the easiest ways to install and set up Postgres with Nodejs on Windows. If you always wanted a PostgreSQL database integrated into your Nodejs project but don’t know how this tutorial is the right place for you!

Noteworthy References