Yellowbrick Dialect

$ 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: 'yellowbrick',
  host: 'yb.example.com',
  database: 'warehouse',
  username: 'analyst',
  password: process.env.YB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'yellowbrick',
  url: 'yellowbrick://analyst:secret@yb.example.com:5432/warehouse',
});

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

const User = db.define('User', {
  id: { type: DataTypes.INTEGER(), primaryKey: 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({ id: 1, 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({ id: 1, email: 'ada@example.com', name: 'Ada' });
await User.update({ name: 'Grace' }, { where: { email: 'ada@example.com' } });
await User.destroy({ where: { id: 1 } });

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

await db.transaction(async (t) => {
  await User.create({ id: 2, email: 'grace@example.com', name: 'Grace' }, { transaction: t });
  // A nested transaction is a SAVEPOINT; its failure undoes only its own work.
  await db
    .transaction(async (sp) => {
      await User.destroy({ where: { id: 1 }, transaction: sp });
      throw new Error('keep the row');
    }, { transaction: t })
    .catch(() => {});
});

import { YellowbrickDialect } from 'prorm/dialects/yellowbrick';

const dialect = new YellowbrickDialect({
  host: 'yb.example.com',
  username: 'analyst',
  password: process.env.YB_PASSWORD,
  database: 'warehouse',
});

dialect.buildCreateTableSql('events', {
  id: { type: DataTypes.BIGINT, allowNull: false },
  ts: { type: DataTypes.DATE },
}, {
  distribute: { on: 'id' },
  sortOn: 'ts',
  partitions: [
    { type: 'hash', column: 'id', partitions: 64, isNull: true },
    {
      type: 'range',
      column: 'ts',
      between: [{ date: '2024-01-01' }, { date: '2024-12-31' }],
      each: { interval: '1 month' },
      outsideRange: true,
    },
  ],
});

dialect.buildCreateTableAsQuery(
  'stats',
  { tableName: 'matches', where: { season: 5 } },
  { distribute: { on: 'season' }, sortOn: 'team' },
);
// CREATE TABLE "stats" AS SELECT * FROM "matches" WHERE "season" = $1
//   DISTRIBUTE ON ("season") SORT ON ("team")        values: [5]

dialect.buildCreateSequenceSql({ name: 'matchid', startWith: 1000 });
// CREATE SEQUENCE "matchid" START WITH 1000