Reading Time: < 1 minute

To connect to a Redshift database from Node.js, you can follow these steps:

Install the pg-promise package by running the following command in your project directory:

npm install pg-promise 

Import the pgp module from pg-promise in your Node.js file:

import pgp from "pg-promise"; 

Create an array to store connections and a class for managing the Redshift connection:

const connections = [];

export default class Redshift {
  // ...
}

Inside the Redshift class, define a static method to get a connection to the Redshift database:

static async getConnection() {
  const dbName = "myDb"; // Replace with your database name

  if (!connections[dbName]) {
    const dbUser = "dbUser"; // Replace with your database username
    const dbPassword = "dbPassword"; // Replace with your database password
    const dbHost = "myHost"; // Replace with your database host
    const dbPort = "dbPort"; // Replace with your database port

    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];
}

Implement a method to execute queries in the Redshift database:

static async executeQuery(query) {
  try {
    // Measure the query execution time
    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;
  }
}

You can now use the Redshift class to connect to the Redshift database and execute queries:

const query = "SELECT * FROM your_table";
const result = await Redshift.executeQuery(query);
console.log(result);

Remember to replace the placeholder values with your actual Redshift database credentials and settings.