Oracle Autonomous Database 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: 'oracleautonomous',
  username: 'ADMIN',
  password: process.env.ADB_PASSWORD,
  database: 'mydb', // with service, selects the tnsnames alias mydb_tp
  dialectOptions: {
    service: 'tp',
    configDir: '/opt/wallet_mydb',
    walletLocation: '/opt/wallet_mydb',
    walletPassword: process.env.WALLET_PASSWORD,
  },
});

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

const db = new Prorm({
  dialect: 'oracleautonomous',
  username: 'ADMIN',
  password: process.env.ADB_PASSWORD,
  database: 'mydb', // with `service`, selects the tnsnames alias mydb_tp
  dialectOptions: {
    service: 'tp',
    configDir: '/opt/wallet_mydb',
    walletLocation: '/opt/wallet_mydb',
    walletPassword: process.env.WALLET_PASSWORD,
  },
});

const db = new Prorm({
  dialect: 'oracleautonomous',
  username: 'ADMIN',
  password: process.env.ADB_PASSWORD,
  dialectOptions: {
    host: 'adb.us-ashburn-1.oraclecloud.com',
    port: 1521, // or 1522
    serviceName: 'abc123_mydb_low.adb.oraclecloud.com',
  },
});
// (DESCRIPTION=(RETRY_COUNT=20)(RETRY_DELAY=3)(ADDRESS=(PROTOCOL=TCPS)(PORT=1521)
//  (HOST=adb.us-ashburn-1.oraclecloud.com))(CONNECT_DATA=(SERVICE_NAME=abc123_mydb_low.adb.oraclecloud.com))
//  (SECURITY=(SSL_SERVER_DN_MATCH=YES)))

const d = db.dialect as OracleAutonomousDialect;

await d.createCloudCredential({
  credentialName: 'OBJ_CRED',
  username: 'me@example.com',
  password: authToken,
});
// BEGIN DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => :credential_name, username => :username, password => :password); END;

await d.copyData({
  table: 'SALES',
  credentialName: 'OBJ_CRED',
  fileUris: 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/ns/b/bkt/o/sales*.csv',
  format: { type: 'csv', skipheaders: 1 },
});

await d.createCloudExternalTable({
  table: 'EXT_SALES',
  fileUris: ['https://objectstorage.example.com/n/ns/b/bkt/o/sales.parquet'],
  columnList: 'id NUMBER, amount NUMBER',
  format: { type: 'parquet', schema: 'first' },
});