Amazon Redshift

$ npm install ts-prorm-orm pg

$ pnpm add ts-prorm-orm pg

$ yarn add ts-prorm-orm pg

$ bun add ts-prorm-orm pg

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'redshift',
  host: 'my-cluster.abc123.us-east-1.redshift.amazonaws.com',
  database: 'dev',
  username: 'admin',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    ssl: { rejectUnauthorized: true },
  },
});

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'redshift',
  url: 'redshift://admin:secret@my-cluster.abc123.us-east-1.redshift.amazonaws.com:5439/dev',
  dialectOptions: {
    ssl: { rejectUnauthorized: true },
  },
});

import { DataTypes } from 'ts-prorm-orm';

const User = db.define('User', {
  id: { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  email: { type: DataTypes.STRING(255), allowNull: false, unique: true },
  name: { type: DataTypes.STRING(100) },
});

await db.sync(); // CREATE TABLE — see Generated SQL below

import { Op } from 'ts-prorm-orm';

await User.create({ email: 'ada@example.com', name: 'Ada' });

const page = await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

await User.create({ email: 'ada@example.com', name: 'Ada' });
await User.update({ name: 'Grace' }, { where: { email: 'ada@example.com' } });
await User.destroy({ where: { id: 1 } });

await User.upsert({ id: 1, email: 'ada@example.com', name: 'Ada Lovelace' });

await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

await db.transaction(async (t) => {
  await User.create({ email: 'grace@example.com', name: 'Grace' }, { transaction: t });
  await User.update({ name: 'Grace Hopper' }, { where: { email: 'grace@example.com' }, transaction: t });
});

import { Prorm, DataTypes } from 'ts-prorm-orm';

const prorm = new Prorm({
  dialect: 'redshift',
  host: 'my-cluster.abc123.us-east-1.redshift.amazonaws.com',
  port: 5439,
  database: 'analytics',
  username: 'admin',
  password: process.env.REDSHIFT_PASSWORD,
  dialectOptions: {
    ssl: { rejectUnauthorized: false },
    // Cluster-wide defaults for tables created without an explicit key:
    distKey: 'user_id',
    sortKey: ['created_at'],
  },
});

await prorm.authenticate();

// The RedshiftDialect type is not re-exported from the package root; cast to
// reach the Redshift-only methods.
const dialect = prorm.getDialectInstance() as any;

await dialect.createTable(
  'events',
  {
    event_id:   { type: DataTypes.BIGINT, autoIncrement: true },
    user_id:    { type: DataTypes.BIGINT },
    event_type: { type: DataTypes.STRING, encode: 'ZSTD' }, // column compression
    created_at: { type: DataTypes.DATE },
  },
  {
    diststyle: 'KEY',        // EVEN | KEY | ALL | AUTO
    distKey: 'user_id',      // required when diststyle is 'KEY'
    sortKey: ['created_at'], // COMPOUND by default
    sortkeyType: 'COMPOUND', // or 'INTERLEAVED'
  },
);

await dialect.copyFromS3('events', 's3://my-bucket/events/2026-07-05/', {
  iamRole: 'arn:aws:iam::123456789012:role/RedshiftCopyRole',
  format: 'PARQUET',           // CSV (default) | JSON | PARQUET | AVRO | ORC
  region: 'us-east-1',
  compression: 'GZIP',         // GZIP | BZIP2 | ZSTD
  maxError: 100,               // continue past a threshold of bad rows
});

await dialect.unloadToS3(
  'SELECT user_id, count(*) FROM events GROUP BY user_id',
  's3://my-bucket/exports/user_counts/',
  {
    iamRole: 'arn:aws:iam::123456789012:role/RedshiftUnloadRole',
    format: 'PARQUET',   // PARQUET | CSV | JSON (default: delimited text)
    parallel: true,      // set false to force a single output file
    manifest: true,
    allowOverwrite: true,
  },
);

await dialect.upsert('events', {
  event_id: 42,
  user_id: 7,
  event_type: 'click',
}, {
  conflictFields: ['event_id'],
});

await dialect.upsert('events', row, { conflictFields: ['event_id'], useMerge: true });

await dialect.vacuum('events', { mode: 'FULL', boost: true }); // FULL | SORT ONLY | DELETE ONLY | REINDEX
await dialect.analyze('events', { predicateColumns: true });   // or { columns: ['user_id'] }
await dialect.analyzeCompression('events');                    // recommends column encodings