ziggeo-aws-redshift

Navigation

Overview

Installation

Setup

Usage

• Query API

• CLI

• Models

• ORM

Change Logs

Upcoming Features

License

Overview

This package is a simple wrapper for common functionality you want when using Redshift. It can do

• Redshift connections & querying
• Creating and running migrations
• Create and manage models
• CRUD API with ORM wrapper with type validation

Warning!!!!!! This is new and still under development. The API is bound to change. Use at your own risk.

Installation

Install the package by running

npm install aws-redshift

Link to npm repository https://www.npmjs.com/package/aws-redshift

Setup

The code to connect to redshift should be something like this:

//redshift.js
var Redshift = require('aws-redshift');

var client = {
  user: user,
  database: database,
  password: password,
  port: port,
  host: host,
};

// The values passed in to the options object will be the difference between a connection pool and raw connection
var redshiftClient = new Redshift(client, [options]);

module.exports = redshiftClient;

There are two ways to setup a connection to redshift.

• Connection Pooling - you can open a connection pool and open connections to Redshift which will be managed by pg-pool (https://github.com/brianc/node-pg-pool)
• Raw Connection - a one time connection you must manually initialize and close to run queries

***By default aws-redshift uses connection pooling

Raw Connection

Pass in the rawConnection parameter in the redshift instantiation options to specify a raw connection. Raw connections need extra code to specify when to connect and disconnect from Redshift. Here's an example of the raw connection query

var redshiftClient = new Redshift(client, {rawConnection: true});

Connection Pooling

Connection pooling works by default with no extra configuration. Here's an example of connection pooling

Setup Options

There are two options that can be passed into the options object in the Redshift constructor.

Option	Type	Description
rawConnection	Boolean	If you want a raw connection, pass true with this option
longStackTraces	Boolean	Default: true. If you want to disable bluebird's longStackTraces, pass in false

Usage

Query API

CLI

Models

ORM

Query API

Please see examples/ folder for full code examples using both raw connections and connection pools.

For those looking for a library to build robust, injection safe SQL, I like sql-bricks to build query strings.

Both Raw Connections and Connection Pool connections have two query functions that are bound to the initialized Redshift object: query() and a parameterizedQuery().

All query() and parameterizedQuery() functions support both callback and promise style. If there's a function as a third argument, the callback will fire. If there's no third function argument, but instead (query, [options]).then({})... the promise will fire.

//raw connection
var redshiftClient = require('./redshift.js');

redshiftClient.connect(function(err){
  if(err) throw err;
  else{
    redshiftClient.query('SELECT * FROM "TableName"', [options], function(err, data){
      if(err) throw err;
      else{
        console.log(data);
        redshiftClient.close();
      }
    });
  }
});

//connection pool
var redshiftClient = require('./redshift.js');

// options is an optional object with one property so far {raw: true} returns 
// just the data from redshift. {raw: false} returns the data with the pg object
redshiftClient.query(queryString, [options])
.then(function(data){
    console.log(data);
})
.catch(function(err){
    console.error(err);
});
//instead of promises you can also use callbacks to get the data

Parameterized Queries

If you parameterize the SQL string yourself, you can call the parameterizeQuery() function

//connection pool
var redshiftClient = require('./redshift.js');

// options is an optional object with one property so far {raw: true} returns 
// just the data from redshift. {raw: false} returns the data with the pg object
redshiftClient.parameterizedQuery('SELECT * FROM "TableName" WHERE "parameter" = $1', [42], [options], function(err, data){
  if(err) throw err;
  else{
    console.log(data);
  }
});
//you can also use promises to get the data

Template Literal Queries

If you use template literals to write your SQL, you can use a tagged template parser like https://github.com/felixfbecker/node-sql-template-strings to parameterize the template literal

//connection pool
var redshiftClient = require('./redshift.js');
var SQL = require('sql-template-strings');

// options is an optional object with one property so far {raw: true} returns 
// just the data from redshift. {raw: false} returns the data with the pg object
let value = 42;

redshiftClient.query(SQL`SELECT * FROM "TableName" WHERE "parameter" = ${value}`, [options], function(err, data){
  if(err) throw err;
  else{
    console.log(data);
  }
});
//you can also use promises to get the data

rawQuery()

If you want to make a one time raw query, but you don't want to call connect & disconnect manually and you dont want to use conection pooling, you can use rawQuery()

//connection pool
var redshiftClient = require('./redshift.js');

// options is an optional object with one property so far {raw: true} returns 
// just the data from redshift. {raw: false} returns the data with the pg object
redshiftClient.rawQuery('SELECT * FROM "TableName"', [options], function(err, data){
  if(err) throw err;
  else{
    console.log(data);
  }
});
//you can also use promises to get the data

Query Options

There's only a single query option so far. For the options object, the only valid option is {raw: true}, which returns just the data from redshift. {raw: false} or not specifying the value will return the data along with the entire pg object with data such as row count, table statistics etc.

CLI

There's a CLI with options for easy migration management. Creating a migration will create a redshift_migrations/ folder with a state file called .migrate in it which contains the state of your completed migrations. The .migrate file keeps track of which migrations have been run, and when you run db:migrate, it computes the migrations that have not yet been run on your Redshift instance and runs them and saves the state of .migrate

WARNING!!! IF YOU HAVE SEPARATE DEV AND PROD REDSHIFT INSTANCES, DO NOT COMMIT THE .migrate FILE TO YOUR VCS OR DEPLOY TO YOUR SERVERS. YOU'LL NEED A NEW VERSION OF THIS FILE FOR EVERY INSTANCE OF REDSHIFT.

Create a new migration file in redshift_migrations/ folder

node_modules/.bin/aws-redshift migration:create <filename>

Run all remaining migrations on database

node_modules/.bin/aws-redshift db:migrate <filename>

Undo last migration

node_modules/.bin/aws-redshift db:migrate:undo <filename>

Creating a model using the command line

node_modules/.bin/aws-redshift model:create <filename>

Models

A model will look like this

'use strict';
  var person = {
    'tableName': 'people',
    'tableProperties': {
      'id': {
        'type': 'key'
      },
      'name': { 
        'type': 'string',
        'required': true
      },
      'email': { 
        'type': 'string',
        'required': true
      }
    }
  };
  module.exports = person;

Importing and using model with ORM

There are two ways you could import and use redshift models. The first is using redshift.import in every file where you want to use the model ORM.

var redshift = require("../redshift.js");
var person = redshift.import("./redshift_models/person.js");

person.create({name: 'John', email: '[e-mail]'}, function(err, data){
    if(err) throw err;
    else{
      console.log(data);
    }
  });

The alternative(my preferred way) is to abstract the import calls and export all the models with the redshift object right after initialization

//redshift.js
...redshift connection code...

var person = redshift.import("./redshift_models/person.js");
redshift.models = {};
redshift.models.person = person;

module.exports = redshift;

//usage in person.js
var redshiftConnection = require('./redshift.js');
var person = redshift.models.person;

person.create({name: 'John', email: '[e-mail]'}, function(err, data){
    if(err) throw err;
    else{
      console.log(data);
    }
  });

ORM API

There are 3 functions supported by the ORM

/**
 * create a new instance of object
 * @param  {Object or Array}   data Object/Array with keys/values to create in database. keys are column names, values are data
 * @param  {Function} cb   
 * @return {Object}        Object that's inserted into redshift
 */
Person.create({emailAddress: '[e-mail]', name: 'John'}, function(err, data){
  if(err) throw err;
  else console.log(data);
});
 
/**
 * update an existing item in redshift
 * @param  {Object}   whereClause The properties that identify the rows to update. Essentially the WHERE clause in the UPDATE statement
 * @param  {Object}   data        Properties to overwrite in the record
 * @param  {Function} callback    
 * @return {Object}               Object that's updated in redshift
 *
 */
Person.update({id: 72}, {emailAddress: '[e-mail]', name: 'John'}, function(err, data){
  if(err) throw err;
  else console.log(data);
});

/**
 * delete rows from redshift
 * @param  {Object}   whereClause The properties that identify the rows to update. Essentially the WHERE clause in the UPDATE statement
 * @param  {Function} cb   
 * @return {Object}        Object that's deleted from redshift
 */
Person.delete({emailAddress: '[e-mail]', name: 'John'}, function(err, data){
  if(err) throw err;
  else console.log(data);
});

Change logs

• v1.0.1 Supported node.js v15

Upcoming features

• Ability to customize location of .migrate file or even from S3
• Model checking prior to queries to verify property name and type
• Add class & instance methods to model

License

MIT