How to connect to a Redshift database from Node.js

Posted on Aug 11, 2020

featured image

Redshift it's a Postgres-based database so we can take advantage of pg-promise module

import pgp from "pg-promise";

const connections = [];

export default class Redshift {
  static async getConnection() {
    const dbName = "myDb";

    if (!connections[dbName]) {
      const dbUser = "dbUser";
      const dbPassword = "dbPassword";
      const dbHost = "myHost";
      const dbPort = "dbPort";

      const dbc = pgp({ capSQL: true });
      console.log(`Opening connection to: ${dbName}, host is: ${dbHost}`);

      const connectionString = `postgres://${dbUser}:${dbPassword}@${dbHost}:${dbPort}/${dbName}`;
      connections[dbName] = dbc(connectionString);

    return connections[dbName];

  static async executeQuery(query) {
    try {
      const date1 = new Date().getTime();
      const connection = await this.getConnection();
      const result = await connection.query(query);

      const date2 = new Date().getTime();
      const durationMs = date2 - date1;
      const durationSeconds = Math.round(durationMs / 1000);
      let dataLength = 0;

      if (result && result.length) dataLength = result.length;

        `[Redshift] [${durationMs}ms] [${durationSeconds}s] [${dataLength.toLocaleString()} records] ${query}`

      return result;
    } catch (e) {
      console.error(`Error executing query: ${query} Error: ${e.message}`);
      throw e;

See more about pg-promise

See also pg-promise self signed certificate error in Postgres

Photo by Clint Adair on Unsplash

pg redshift postgres nodejs dev
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!