Agu O. Wisdom

Posted on Mar 20, 2023 • Edited on Sep 3, 2023

CONNECTING TO A POSTGRESQL DATABASE WITH NODE.JS: A STEP-BY-STEP GUIDE

#node #webdev #postgres #database

Introduction

Node.js is a popular open-source JavaScript runtime that allows developers to build powerful and scalable applications.

When it comes to working with databases, PostgreSQL is a top choice for many developers due to its robust features and reliability.
In this guide, I'll show you how to connect your Node.js application to a PostgreSQL database, so you can store and retrieve data with ease. Whether you're building a web app, API, or any other type of application, this guide will give you the knowledge you need to get started.

Prerequisites

Before we get started, there are a few things you'll need:

Basic knowledge of JavaScript and Node.js: This guide assumes that you have some familiarity with JavaScript and Node.js. If you're new to these technologies, I recommend checking out some tutorials to get you up to speed.
Basic knowledge of PostgreSQL: We won't be diving into all the details of PostgreSQL in this guide, it's important that you have a general understanding of what it is and how to use it.
Node.js installed on your machine: To connect to a PostgreSQL database with Node.js, you'll need to have Node.js installed on your system. If you haven't already installed it, you can download the latest version from the official Node.js website.
A code editor: You'll also need a code editor to be able to follow along with this guide. There are many options available, such as Visual Studio Code, Sublime Text, Atom, etc.
PostgreSQL installed on your machine: Finally, you'll need to have PostgreSQL installed on your system. If you haven't installed it yet, you can download the latest version from the official PostgreSQL website.

Once you have all the prerequisites in place, you're ready to learn how to connect to your PostgreSQL database with Node.js.

Connecting to PostgreSQL with Node.js

To connect to a PostgreSQL database with Node.js, We'll need to follow these steps:

Installing Dependencies

We'll be needing the following packages for this guide:

pg: This is the official PostgreSQL client for Node.js. It provides a simple API for querying the database and handling the results. We'll use it to establish a connection to our PostgreSQL database.
db-migrate-pg: This is a PostgreSQL driver for the db-migrate library. It provides an easy way to manage database migrations, which are changes to the database schema over time. We'll use it to create and update our database schema as needed.
dotenv: This is a zero-dependency module that loads environment variables from a .env file into process.env. We'll use it to store our database connection details securely.

To install these dependencies, open your terminal, navigate to your project directory and run the following command:

npm install pg db-migrate-pg dotenv

This will install the latest versions of each package and add them to your project's node_modules directory.

Creating a Database Connection

Before we can connect to our PostgreSQL database, we need to create a new database in the psql terminal. Open your terminal and enter the following command:

CREATE DATABASE your_database_name

Replace your_database_name with a name for your database. This will create a new database with the specified name.

Now that we have a database set up, let's create the necessary files to connect to it. We'll need to create the following files:

server.js This is where we'll write some code to establish a connection with PostgreSQL using the pg package.
.env file: This is where we'll store our environment variables, including our database connection details. It's important to use an .env file in our project to keep sensitive information like database passwords and connection details secure.
database.json file: This is where we'll store the information for testing that our database was successfully connected.

Let's start by creating the server.js file. In this file, we'll establish a connection with our PostgreSQL database using the pg package.

Open the server.js file and add the following code:

require ('dotenv').config();
const { Pool }  = require ('pg');


const {
POSTGRES_HOST,
POSTGRES_DB,
POSTGRES_USER,
POSTGRES_PASSWORD
} = process.env

export const Client = new Pool({
    host: POSTGRES_HOST,
    database: POSTGRES_DB,
    user: POSTGRES_USER,
    password: POSTGRES_PASSWORD
});

In this code, we're using the pg package to create a new pool instance using the database connection details loaded from our .env file. We then export this pool instance so that it can be used elsewhere in our application.
A typical use case is when we are creating models for our database, we can import it into our model file and use it to establish a connection with our database.

Next, let's create the .env file. This file should be located in the root directory of your project, and it should contain the following lines:

POSTGRES_USER=your_database_username
POSTGRES_HOST=your_database_host
POSTGRES_DB=your_database_name
POSTGRES_PASSWORD=your_database_password

Replace each of the your_database_* values with the appropriate values for your database connection. Please make sure that this file secure, as it contains sensitive information.

Finally, let's create the database.json file. This file should be located in the root directory of your project, and it should contain the following lines:

{
  "dev": {
    "driver": "pg",
    "user": "your_database_username",
    "host": "your_database_host",
    "database": "your_database_name",
    "password": "your_database_password"
  }
}

Replace each of the your_database_* values with the appropriate values for your database connection. This file is used for running our database migration to ensure that our database connection is working. This file should also be kept secure.

Running Database Migration.

Finally, to test that we have successfully connected to our database, let's create a database migration using db-migrate-pg

Step 1. Run the following command to create a database migration:

db-migrate create users --sql-file

This command is telling db-migrate-pg to create a migration of users.

If the command ran successfully, you should see a folder named migration in the root folder of your project. Your root folder should look like this:

├── database.json
├── migrations
│   ├── 20230318081649-users.js
│   └── sqls
│       ├── 20230318081649-users-down.sql
│       └── 20230318081649-users-up.sql
├── package.json
├── package-lock.json
└── sever.js

Step 2. Create a Table

Inside the sqls sub-folder of the migration folder, open the users-up-sql file and insert the following code:

CREATE TABLE users(
    name VARCHAR(100),
    email VARCHAR(100),
    password VARCHAR
);

Open the users-down-sql file and insert the following code:

DROP TABLE users;

The above codes will create and drop the users table respectively in the database.

Step 3. Run the Migration

Finally, let's run the migration using the following command:

To Create the users table, run:

db-migrate up

This command will run the code in the users-up-sql file and create the table users in the database.

If the code ran successfully, then your terminal output will look like so:

bigwiz@bigwiz:~postgres/pg-node_tut$ db-migrate up
received data: CREATE TABLE users(
    name VARCHAR(100),
    email VARCHAR(100),
    password VARCHAR
);
[INFO] Processed migration 20230318081649-users
[INFO] Done

Now check your database to ensure that everything was successful, your database should look like so:

       List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | migrations | table | me
 public | users      | table | me
(2 rows)

To DELETE the table, run the following command:

db-migrate DOWN

The above command will run the down migration in the users-down-sql file.

That's it! With everything in place, we can now establish a connection to our PostgreSQL database using Node.js.

Conclusion

Connecting to a PostgreSQL database with Node.js is an essential skill for any backend developer. By following the steps outlined in this article, you should now have a good understanding of how to establish a connection with PostgreSQL.

Additionally, you now understand how to also create database migrations in PostgreSQL. Remember to always keep your code organized and secured.