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
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.
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.
Step 3: Select software components you want to install:
- 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.
Step 5: Enter a password for the database superuser (postgres).
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.
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.
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.
Step 9: Now if you are ready to install Postgres, click Next.
Wait for the installation to finish. It may take a few minutes to complete.
Step 10: Hit Finish once the PostgreSQL installation is complete.
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.
Step 2: Configure your local PostgreSQL account. You must enter the password in here that you created during installation.
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.
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.
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.
Step 3: Change the directory to your project directory.
Step 4: Create your Nodejs Project.
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.
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.
Step 7: Open your .js file in your editor and set up a connection to your db, like this.
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.
The output should look like this:
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
The output should now look like this indicating a successful Postgres with Nodejs set up:
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!
