How to connect to a PostgreSQL database in Node.js


Posted on Jul 28, 2020


featured image
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:a75568307daad4wb1432b5d173719bae7ba908ea06e7d0ebef8bf7bd434eb655547@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
postgres pg sql nodejs dev
Search
Side Widget
You can put anything you want inside of these side widgets. They are easy to use, and feature the new Bootstrap 4 card containers!