CockroachDB 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: 'cockroachdb',
  host: 'localhost',
  database: 'defaultdb',
  username: 'root',
});

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

export const db = new Prorm({
  dialect: 'cockroachdb',
  url: 'cockroachdb://root@localhost:26257/defaultdb',
});

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: { id: 1 }, lock: { level: 'UPDATE', nowait: true }, transaction: t });
});

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

const prorm = new Prorm({
  dialect: 'cockroachdb',
  host: 'localhost',
  port: 26257, // CockroachDB's default SQL port; used automatically if omitted
  database: 'defaultdb',
  username: 'root',
  password: '',
});

await prorm.connect();

const prorm = new Prorm({
  dialect: 'cockroachdb',
  host: 'your-cluster.cockroachlabs.cloud',
  port: 26257,
  database: 'defaultdb',
  username: 'user',
  password: process.env.COCKROACH_PASSWORD,
  dialectOptions: {
    ssl: { rejectUnauthorized: true },
    // statement/query timeouts (milliseconds) are also forwarded to pg:
    statementTimeout: 30000,
    queryTimeout: 30000,
  },
});

const User = prorm.define('User', {
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true }, // SERIAL
  email: { type: DataTypes.STRING(255), unique: true },
  profile: DataTypes.JSONB,
});

await User.sync();

const created = await User.create({ email: 'ada@example.com', profile: { tier: 'pro' } });

const dialect = prorm.getDialectInstance();

const tables = await dialect.showTables();          // SHOW TABLES
const desc = await dialect.describeTable('users');  // SHOW COLUMNS FROM "users"
const idx = await dialect.showIndexes('users');     // SHOW INDEXES FROM "users"

const dialect = prorm.getDialectInstance();

// Default: INSERT ... ON CONFLICT ... DO UPDATE
const conflictUpsert = dialect.buildUpsertQuery(
  'users',
  { id: 1, email: 'ada@example.com' },
  { conflictFields: ['id'], updateOnDuplicate: ['email'] },
);
// => INSERT INTO "users" ("id", "email") VALUES ($1, $2)
//    ON CONFLICT ("id") DO UPDATE SET "email" = excluded."email"

// Native CockroachDB UPSERT
const nativeUpsert = dialect.buildUpsertQuery(
  'users',
  { id: 1, email: 'ada@example.com' },
  { nativeUpsert: true },
);
// => UPSERT INTO "users" ("id", "email") VALUES ($1, $2)

dialect.buildInsertQuery(
  'users',
  { id: 1, email: 'ada@example.com' },
  { upsert: true, nativeUpsert: true, returning: true },
);
// => UPSERT INTO "users" ("id", "email") VALUES ($1, $2) RETURNING *

const dialect = prorm.getDialectInstance();

try {
  await dialect.query('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
} catch (err) {
  if (dialect.isRetryableError(err)) {
    // Safe to retry the transaction
  } else {
    throw err;
  }
}

const dialect = prorm.getDialectInstance();

const result = await dialect.runTransaction(
  async (tx) => {
    await tx.query('UPDATE accounts SET balance = balance - 100 WHERE id = $1', {
      replacements: [1],
    });
    await tx.query('UPDATE accounts SET balance = balance + 100 WHERE id = $1', {
      replacements: [2],
    });
    return 'transfer complete';
  },
  { maxRetries: 5 },
);

const Event = prorm.define('Event', {
  id: { type: DataTypes.UUID, primaryKey: true, defaultValue: DataTypes.UUIDV4 },
  tags: DataTypes.ARRAY(DataTypes.STRING(50)), // VARCHAR(50)[]
  payload: DataTypes.JSONB,
  ip: DataTypes.INET,
  createdAt: DataTypes.DATE, // TIMESTAMP
});

const dialect = prorm.getDialectInstance();

// Promote a database to multi-region and add regions
await dialect.setPrimaryRegion('us-east1');
await dialect.addRegion('us-west1', { ifNotExists: true });
await dialect.setSurvivalGoal('region'); // survive a full region failure

// Table localities: GLOBAL, REGIONAL BY TABLE, REGIONAL BY ROW
await dialect.setTableLocality('users', { type: 'global' });
await dialect.setTableLocality('sessions', { type: 'regional-by-row', column: 'region' });

await dialect.configureZone(
  { kind: 'table', name: 'orders' },
  { numReplicas: 5, gcTtlSeconds: 90000, constraints: '[+region=us-east1]' },
);

await dialect.resetZoneConfig({ kind: 'table', name: 'orders' });