Snowflake Dialect

$ npm install ts-prorm-orm snowflake-sdk

$ pnpm add ts-prorm-orm snowflake-sdk

$ yarn add ts-prorm-orm snowflake-sdk

$ bun add ts-prorm-orm snowflake-sdk

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

export const db = new Prorm({
  dialect: 'snowflake',
  database: 'ANALYTICS',
  username: 'ANALYST',
  password: process.env.SNOWFLAKE_PASSWORD,
  dialectOptions: {
    account: 'myorg-myaccount',
    warehouse: 'COMPUTE_WH',
    schema: 'PUBLIC',
  },
});

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 } from 'ts-prorm-orm';

const db = new Prorm({
  dialect: 'snowflake',
  username: 'analyst',
  password: 's3cret',
  database: 'ANALYTICS',
  schema: 'PUBLIC',
  dialectOptions: {
    account: 'xy12345.us-east-1', // Snowflake account identifier
    warehouse: 'COMPUTE_WH', // compute cluster to activate on connect
    role: 'TRANSFORMER', // optional default role
  },
});

await db.connect();

db.dialect.escape({ tags: ['a', 'b'], active: true });
// PARSE_JSON('{"tags":["a","b"],"active":true}')

db.dialect.buildJsonQuery('data', 'active', true);
// { sql: '"data":active::STRING = ?', values: [true] }

db.dialect.buildUpsertQuery(
  'users',
  { id: 1, name: 'Alice', email: 'a@x.io' },
  { conflictFields: ['id'] },
);

db.dialect.buildUpsertQuery(
  'users',
  { id: 1, name: 'Alice', email: 'a@x.io' },
  { conflictFields: ['id'], updateOnDuplicate: ['name'] },
);
// ... WHEN MATCHED THEN UPDATE SET "name" = source."name" ...

await db.dialect.setClusteringKey('events', ['event_date', 'customer_id']);
// ALTER TABLE "PUBLIC"."events" CLUSTER BY ("event_date", "customer_id")

await db.dialect.dropClusteringKey('events');
// ALTER TABLE "PUBLIC"."events" DROP CLUSTERING KEY