OceanBase Dialect

$ npm install ts-prorm-orm mysql2

$ pnpm add ts-prorm-orm mysql2

$ yarn add ts-prorm-orm mysql2

$ bun add ts-prorm-orm mysql2

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

export const db = new Prorm({
  dialect: 'oceanbase',
  host: 'localhost',
  database: 'test',
  username: 'app',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    tenant: 'app_tenant', // sent as app@app_tenant
  },
});

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

export const db = new Prorm({
  dialect: 'oceanbase',
  url: 'oceanbase://app:secret@localhost:2881/test',
  dialectOptions: {
    tenant: 'app_tenant', // sent as app@app_tenant
  },
});

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 });
  // 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 { Prorm } from 'ts-prorm-orm';

const db = new Prorm({
  dialect: 'oceanbase',
  host: '127.0.0.1',
  port: 2881, // observer direct; use 2883 through obproxy
  username: 'app',
  password: '',
  database: 'test',
  dialectOptions: {
    tenant: 'prod_tenant', // composed into the username as app@prod_tenant
    cluster: 'obcluster', // optional; appended as #obcluster
  },
});

user@tenant#cluster
cluster:tenant:user

OceanBaseDialect.buildTenantUsername('app', 'prod_tenant', 'obcluster');
// 'app@prod_tenant#obcluster'

db.dialect.buildCreateTableSql(
  'orders',
  { id: { type: 'BIGINT', primaryKey: true } },
  { replicaNum: 3, duplicateScope: 'cluster', primaryZone: 'zone1' },
);

db.dialect.buildCreateTableGroupSql('tg_orders', { sharding: 'PARTITION' });

OceanBase (MySQL mode) does not support the SERIALIZABLE isolation level.
Supported levels are READ COMMITTED and REPEATABLE READ.

db.dialect.buildUpsertQuery('kv', { k: 'a', v: 1 });