CONNECTING TO A POSTGRESQL DATABASE WITH NODE.JS: A STEP-BY-STEP GUIDE
Tags: node, webdev, postgres, database
Published: 2023-03-20 10:08:52

## 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](https://nodejs.org/en).
- **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](https://www.postgresql.org/download/).

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:

1. **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:

```bash
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.

2. **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:

```bash
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:

```bash
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:

```bash
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:

```bash
{
  "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.

3. **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:**

```bash
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:

```bash
├── 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:

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

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

```bash
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:

```bash
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:

```bash
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:

```bash
       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:

```bash
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.