Oracle Dialect

$ npm install ts-prorm-orm oracledb

$ pnpm add ts-prorm-orm oracledb

$ yarn add ts-prorm-orm oracledb

$ bun add ts-prorm-orm oracledb

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

export const db = new Prorm({
  dialect: 'oracle',
  host: 'localhost',
  database: 'FREEPDB1', // the service name
  username: 'app',
  password: process.env.DB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'oracle',
  url: 'oracle://app:secret@localhost:1521/FREEPDB1',
});

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

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 prorm = new Prorm({
  dialect: 'oracle',
  host: 'localhost',
  port: 1521,      // default 1521
  database: 'ORCL', // the service name
  username: 'system',
  password: 'password',
});

await prorm.connect();

// From OracleDialect.connect():
connectString: `${host}:${port}/${database}` // e.g. "localhost:1521/ORCL"

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

const oracle = createOracleDialect({
  host: 'localhost',
  port: 1521,
  database: 'ORCL',
  username: 'system',
  password: 'password',
  thinMode: true, // stay in pure-JS thin mode; skip Instant Client init
  pool: { max: 10, min: 2, increment: 2, timeout: 30 },
  schema: 'APP', // default schema for sequences/procedures/triggers
});

await oracle.connect();
const { rows } = await oracle.query('SELECT * FROM DUAL');
await oracle.disconnect();

// thinMode: true  -> pure-JS driver, no Oracle Instant Client required.
// thinMode: false -> calls oracledb.initOracleClient() (thick mode, needs Instant Client).

const oracle = createOracleDialect({
  /* ... */
  retry: {
    max: 3,
    timeout: 5000,
    backoff: true,
    backoffMultiplier: 2,
    backoffMax: 10000,
    match: ['ORA-12514', 'ORA-12541', 'ORA-12154', 'ECONNREFUSED'],
  },
});

await oracle.createTable('EMPLOYEES', {
  ID:          { type: 'INTEGER', primaryKey: true },
  NAME:        { type: { key: 'STRING', length: 100 }, allowNull: false },
  SALARY:      { type: { key: 'DECIMAL', precision: 12, scale: 2 } },
  IS_ACTIVE:   { type: 'BOOLEAN', defaultValue: true },  // -> NUMBER(1) DEFAULT 1
  RESUME:      { type: 'BLOB' },
  BIO:         { type: 'TEXT' },                          // -> CLOB
  CREATED_AT:  { type: 'TIMESTAMP' },
});

await oracle.createSequence({
  name: 'EMPLOYEE_SEQ',
  startWith: 1000,
  incrementBy: 1,
  cache: 20,
  schema: 'APP',
});

// Fetch the next value (SELECT ... .NEXTVAL FROM DUAL)
const nextId = await oracle.nextSequenceValue('EMPLOYEE_SEQ');

// Existence check / listing
await oracle.hasSequence('EMPLOYEE_SEQ', 'APP');
const all = await oracle.listSequences(); // ["APP.EMPLOYEE_SEQ", ...]

// Drop (ifExists is emulated for pre-23c — see Quirks)
await oracle.dropSequence('EMPLOYEE_SEQ', { ifExists: true, schema: 'APP' });

// Named binds
await oracle.query(
  'SELECT * FROM "EMPLOYEES" WHERE "DEPT_ID" = :dept',
  { replacements: { dept: 10 } },
);

// Positional binds
await oracle.query(
  'SELECT * FROM "EMPLOYEES" WHERE "SALARY" > :1 AND "DEPT_ID" = :2',
  { replacements: [50000, 10] },
);

const { sql, values } = oracle.buildInsertQuery('EMPLOYEES', {
  ID: 1001, NAME: 'Ada', SALARY: 90000,
});
// INSERT INTO "EMPLOYEES" ("ID", "NAME", "SALARY") VALUES (:1, :2, :3)

const { sql } = oracle.buildInsertQuery(
  'EMPLOYEES',
  { NAME: 'Ada', SALARY: 90000 },
  { returning: ['ID'] },
);
// INSERT INTO "EMPLOYEES" ("NAME", "SALARY") VALUES (:1, :2) RETURNING "ID" INTO :3

const { sql } = oracle.buildSelectQuery({
  tableName: 'EMPLOYEES',
  order: { SALARY: 'DESC' },
  limit: 10,
  offset: 20,
});
// SELECT * FROM ( ... ) WHERE ROWNUM > 20 AND ROWNUM <= 30

const { sql } = oracle.buildUpsertQuery(
  'EMPLOYEES',
  { ID: 1001, NAME: 'Ada', SALARY: 90000 },
  { conflictFields: ['ID'] },
);
// MERGE INTO "EMPLOYEES" USING (SELECT :1 AS "ID", :2 AS "NAME", :3 AS "SALARY" FROM DUAL) "src"
//   ON ("EMPLOYEES"."ID" = "src"."ID")
//   WHEN MATCHED THEN UPDATE SET ...
//   WHEN NOT MATCHED THEN INSERT (...) VALUES (...)

const tx = await oracle.startTransaction({ isolationLevel: 'SERIALIZABLE' });
// ... do work ...
await oracle.commitTransaction(tx);   // commits + closes the tx connection
// or: await oracle.rollbackTransaction(tx);