Using Sequelize with Redshift

Question

Is it possible to use Sequelize with Redshift? If not, what are the alternatives? I need an ORM for Node.js with built-in support for transactions, hence Sails.js is not an option. I've also looked at Bookshelf, but could not find any support for Redshift either.

Answer 1

I've been able to get Sequelize to at least connect to Redshift (and make a simple SELECT query) with these options:

var Sequelize = require('sequelize');
Sequelize.HSTORE.types.postgres.oids.push('dummy'); // avoid auto-detection and typarray/pg_type error
module.exports = new Sequelize(process.env.REDSHIFT_DATABASE, process.env.REDSHIFT_USER, process.env.REDSHIFT_PASSWORD, {
  host: process.env.REDSHIFT_HOST,
  port: process.env.REDSHIFT_PORT,
  dialect: 'postgres',
  pool: false,
  keepDefaultTimezone: true, // avoid SET TIMEZONE
  databaseVersion: '8.0.2' // avoid SHOW SERVER_VERSION
});

Answer 2

Sequelize is not compatible with Redshift. Though Redshift is written on top of Postgres, it is a columnar DB and major core functions are rewritten.

While trying to connect to it gives an error 'Set Time Zone is not supported'

The following thread shows a few people overriding the time zone error but facing other issues subsequently. 'Using Node 'pg' library to connect to Amazon Redshift

if Redshift is the mandatory you may use the node-jdbc package to connect with Redshift https://github.com/CraZySacX/node-jdbc

of if ORM is mandatory, you should may try moving your data store to pure Postgres