How to connect to a Redshift database from Node.js

See how to use pg-promise to connect to a AWS Redshift database


Aug 11, 2020
featured image

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

Redshit logo
      
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;

      console.log(
        `[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 https://www.npmjs.com/package/pg-promise

See also pg-promise self signed certificate error in Postgres

Photo by Clint Adair on Unsplash

pg redshift postgres nodejs dev