Redshift popular data warehousing product developed by Amazon Web Services (AWS). It is based on the PostgreSQL database engine and is designed for handling large-scale analytical workloads. Redshift allows you to store, manage, and analyze structured data efficiently, making it ideal for data warehousing and business intelligence applications.

As a columnar database, Redshift organizes data by column rather than by row. This storage structure enables faster query performance and better compression rates, especially when dealing with large volumes of data. Redshift also offers features such as parallel query execution, automatic data compression, and scalability to handle high-concurrency workloads.

With its simplicity, scalability, and integration with other AWS services, Redshift has become a popular choice for organizations that need to process and analyze large amounts of data in real-time, allowing them to make data-driven decisions and gain insights into their business operations.

Connecting to a Redshift database from Node.js is possible, but it involves some steps. Redshift is a popular data warehousing product by Amazon and allows for easy storage and analysis of structured data. With Node.js and Redshift, you can store, manage and analyze your data quickly and easily.

In this post, I’ll show you how to connect to a Redshift database from Node.js.

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

Code snippet to connect to Redshift from Node.js

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

Resources

• NPM module pg-promise
• pg-promise self signed certificate error in Postgres