How to connect to a PostgreSQL database in Node.js

A simple example about how to connect to a PostgreSQL database using Node.js

Let’s create a folder to host our example. Open a terminal and type:

mkdir node-js-postgresql

Enter to the folder

cd node-js-postgresql

Use package.json generator. Follow the steps

npm init
npm install

Install pg module in our project

npm install pg --save

In case you have a database url connection you will have to parse it. There’s a module to parse such as url. Example url:

postgres://hfbwxykfdgkurg:a75568307daad4b1432b5d173719ba7ba908ea06e7d0ebe8bf7bd434eb655547@ec2-108-21-167-137.compute-1.amazonaws.com:5432/w5tftigeor6odh

Install the module

npm install parse-database-url --save

Create a file called db_helper.js

const parseDbUrl = require("parse-database-url");

//we have our connection url in an environment config variable. Each developer will have his own
//a connection url will look like this:
//postgres://hfbwxykfdgkurg:a75568307daad4b1432b5d173719ba7ba908ea06e7d0ebe8bf7bd434eb655547@ec2-108-21-167-137.compute-1.amazonaws.com:5432/w5tftigeor6odh
const dbConfig = parseDbUrl(process.env.DATABASE_URL);
const Pool = require("pg").Pool;
const pool = new Pool({
    user: dbConfig.user,
    host: dbConfig.host,
    database: dbConfig.database,
    password: dbConfig.password,
    port: dbConfig.port,
    ssl: true,
});

module.exports.execute = pool;

In the line number 6 we have a call to a configuration environment variable

process.env.DATABASE_URL

It’s a good way to avoid versioning sensitive data like a database connection or other credentials. To run this example you can just hard-code it

Create a file called index.js

const dbHelper = require("./db_helper");

//deal with the promise
findUserById(1234)
    .then(user => {
        console.info(user);
    })
    .catch(error => {
        console.error(error);
    });

/**
 *
 * @param userId
 * @returns a Promise with the user row for the given id
 * @throws error if there's a connection issue or if the user was not found by the id
 */
async function findUserById(userId) {
    const query = "SELECT * FROM users WHERE id = $1 LIMIT 1";
    const bindings = [userId];
    const result = await dbHelper.execute.query(query, bindings);
    if (result.rows.length > 0) {
        return result.rows[0];
    } else {
        throw Error("User not found by id " + userId);
    }
}

Run the example

node index.js

That’s it 🙂

Full source code:

https://github.com/andrescanavesi/node-js-postgresql

Photo by Kevin Ku on Unsplash

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s