openGauss 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: 'opengauss',
  host: 'localhost',
  database: 'postgres',
  username: 'app',
  password: process.env.DB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'opengauss',
  url: 'opengauss://app:secret@localhost:5432/postgres',
});

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(() => {});
});

const sample = db.getDialectInstance()!.buildSelectQuery({
  tableName: 'users',
  sample: { method: 'BERNOULLI', percent: 10 },
});
await db.query(sample.sql);

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

const db = new Prorm({
  dialect: 'opengauss',
  host: '127.0.0.1',
  port: 5432,
  username: 'app',
  password: '...',
  database: 'postgres',
});

syntax error at or near "CONFLICT"

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

db.dialect.buildUpsertQuery('users', { id: 1, name: 'Ada', email: 'a@x.io' }, {
  updateOnDuplicate: ['name'],
});
// ... ON DUPLICATE KEY UPDATE "name" = VALUES("name")

openGauss does not support RETURNING together with ON DUPLICATE KEY UPDATE
(its only upsert form). Re-fetch the row after the upsert instead.

db.dialect.buildCreatePartitionedTableSQL(
  'events',
  { id: { type: 'INTEGER' }, ts: { type: 'TIMESTAMP' } },
  {
    type: 'range',
    column: 'ts',
    partitions: [
      { name: 'p2024', lessThan: '2025-01-01' },
      { name: 'pmax', lessThan: 'MAXVALUE' },
    ],
  },
);

db.dialect.buildAddPartitionSQL({ parentTable: 'events', name: 'p2026', bound: { from: 0, to: 100 } });

db.dialect.buildStorageClause({ orientation: 'COLUMN', compression: 'HIGH' });
// " WITH (ORIENTATION = COLUMN, COMPRESSION = HIGH)"

openGauss maps SERIALIZABLE to REPEATABLE READ: it has no serializable-snapshot
isolation, so this transaction is not protected against write skew.