YugabyteDB 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: 'yugabytedb',
  host: 'localhost',
  database: 'yugabyte',
  username: 'yugabyte',
  password: process.env.DB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'yugabytedb',
  url: 'yugabytedb://yugabyte:yugabyte@localhost:5433/yugabyte',
});

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) },
  meta: { type: DataTypes.JSONB() },
});

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,
});

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

await User.findAll({ where: { meta: { [Op.contains]: { plan: 'pro' } } } });

await db.transaction(async (t) => {
  await User.create({ 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(() => {});
});

await db.transaction(async (t) => {
  await User.findAll({ where: { name: 'Ada' }, lock: { level: 'UPDATE', skipLocked: true }, transaction: t });
});

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

const orm = new Prorm({
  dialect: 'yugabytedb',
  host: 'localhost',
  // port defaults to 5433; override only if your YSQL server uses another port
  database: 'mydb',
  username: 'yugabyte',
  password: 'yugabyte',
});

await orm.connect();

import { YugabyteDBDialect, createYugabyteDBDialect } from 'prorm/dialects/yugabytedb';

const dialect = new YugabyteDBDialect({ database: 'mydb', username: 'yugabyte' });
// or
const dialect2 = createYugabyteDBDialect({ database: 'mydb', username: 'yugabyte' });

dialect.buildCreateTableSQL(
  'users',
  { id: { type: 'INTEGER', primaryKey: true } },
  { splitIntoTablets: 8 },
);

dialect.buildCreateTableSQL(
  'metrics',
  { ts: { type: 'TIMESTAMP' }, v: { type: 'INTEGER' } },
  {
    primaryKey: [{ name: 'ts', order: 'ASC' }],
    splitAtValues: [[1000], [2000]],
  },
);

dialect.buildCreateTableSQL('sessions', { id: { type: 'INTEGER' } }, { colocated: true });

dialect.buildCreateTableSQL('config', { id: { type: 'INTEGER' } }, { colocation: true });

dialect.buildCreateTableSQL(
  'events',
  { id: { type: 'INTEGER' }, created_at: { type: 'TIMESTAMP' } },
  { primaryKey: [{ name: 'id', order: 'HASH' }, { name: 'created_at', order: 'DESC' }] },
);

dialect.buildCreateTableGroupSQL('tg1');
// CREATE TABLEGROUP "tg1"

dialect.buildCreateTableGroupSQL('tg1', { ifNotExists: true, owner: 'app' });
// CREATE TABLEGROUP IF NOT EXISTS "tg1" OWNER "app"

dialect.buildCreateTableSQL('lookup', { id: { type: 'INTEGER', primaryKey: true } }, { tablegroup: 'tg1' });
// CREATE TABLE "lookup" ("id" INTEGER, PRIMARY KEY ("id")) TABLEGROUP "tg1"

await dialect.createTableGroup('tg1', { ifNotExists: true, owner: 'app' });
await dialect.dropTableGroup('tg1', { ifExists: true }); // DROP TABLEGROUP IF EXISTS "tg1"