How to Connect to a PostgreSQL Database Using Node.js

Most real-world applications interact with databases in one way or another. Databases make it easy to persist, analyze and interact with data.

Most programming languages offer support for connecting applications to databases like PostgreSQL. Learn how you can use the node-postgres module to connect a Node application to PostgreSQL.

How to Get Started

To begin by connecting to a local database, you’ll need to have the PostgreSQL server installed.

PostgresSQL is one of the most common relational database systems due to its flexibility and power. You might consider using it as an alternative to MySQL if your project is complex or you’re looking for a solution that embraces the open-source ethos.

Create a Node Application

The first step is to create a Node application that you will use to test out the connection code. In your preferred folder, create a new folder.

        mkdir postgres-node

Navigate to the folder you created and initialize npm.

        cd postgres-node
npm init -y

This command should generate a package.json file.

Next, create a new file and name it index.js. You will connect Node to PostgreSQL in this file.

Use node-postgres Module

The node-postgres module is an npm package that allows you to connect to and interact with a PostgreSQL database. There are two options you can use to connect Node with PostgreSQL using the node-postgres module: a single client or a connection pool. So what should you use?

Use a single client if you only need one static connection at a time. However, if you need to use concurrent and multiple requests, use a connection pool.

Install node-postgres

Run the following command in your terminal to install node-postgres.

        npm install pg

If you use node >= 14.x, you will need to install pg@8.2.x o later. You can specify the version number by suffixing it to the installation command like this.

        npm install pg=8.7.3

Connecting to PostgreSQL

The node-postgres module requires the following values to connect to a PostgreSQL database.

PGUSER - PostgreSQL username to connect as.
PGHOST - The name of the server host to connect to.
PGPASSWORD - The password of the PostgreSQL server.
PGDATABASE - The name of the database you are connecting to.
PGPORT - The port number to connect to at the server host.

Create a .env file and add these variables, substituting the corresponding values from your database:

        PGUSER=<PGUSER>
PGHOST=<PGHOST>
PGPASSWORD=<PGPASSWORD>
PGDATABASE=<PGDATABASE>
PGPORT=<PGPORT>

Install the dotenv package to access the .env file.

        npm install dotenv

In index.js, import and configure dotenv.

        const dotenv = require("dotenv")
dotenv.config()

Connect to a PostgreSQL Database Using a Single Client

The code below shows how you can connect Node to a PostgreSQL server using a single client.

        const { Client } = require("pg")
const dotenv = require("dotenv")
dotenv.config()
 
const connectDb = async () => {
    try {
        const client = new Client({
            user: process.env.PGUSER,
            host: process.env.PGHOST,
            database: process.env.PGDATABASE,
            password: process.env.PGPASSWORD,
            port: process.env.PGPORT
        })
 
        await client.connect()
        const res = await client.query('SELECT * FROM some_table')
        console.log(res)
        await client.end()
    } catch (error) {
        console.log(error)
    }
}
 
connectDb()

Using a Connection Pool

As already mentioned, a connection pool allows you to make multiple connection requests to your server.

        const { Pool } = require("pg");
const dotenv = require("dotenv");
dotenv.config();
 
const connectDb = async () => {
    try {
        const pool = new Pool({
            user: process.env.PGUSER,
            host: process.env.PGHOST,
            database: process.env.PGDATABASE,
            password: process.env.PGPASSWORD,
            port: process.env.PGPORT,
        });
 
        await pool.connect()
        const res = await pool.query('SELECT * FROM clients')
        console.log(res)
        await pool.end()
    } catch (error) {
        console.log(error)
    }
}
 
connectDb()

The above examples let you control the database configuration via a .env file. However, you can also supply these config values using standard environment variables. This approach lets you easily vary the configuration at start-up time.

Consider the following connection code.

        const connectDb = async () => {
    try {
        const pool = new Pool();
        const res = await pool.query('SELECT * FROM clients')
        console.log(res)
        await pool.end()
    } catch (error) {
        console.log(error)
    }
}

In this program, you are not passing the connection object as an argument when initializing Pool. Instead, you must specify the connection details when you invoke the program like this:

        PGUSER=<PGUSER> \
PGHOST=<PGHOST> \
PGPASSWORD=<PGPASSWORD> \
PGDATABASE=<PGDATABASE> \
PGPORT=<PGPORT> \
node index.js

Connecting Node to PostgreSQL like this allows you to write a more reusable program.

Doing More With PostgreSQL

To create an application that interacts with databases, you have to connect it to the database first. In this article, you learned how to connect Node to a PostgreSQL database using the node-Postgres module.

Apart from PostgreSQL, there are other database management systems like MySQL you can use in your application. The database tool you choose should be the most compatible with your data needs.