Skip to main content

Oracle Database

@sqb/oracle is the SQB adapter for Oracle Database. It's built on top of oracledb, the official Oracle Node.js driver, which runs in pure-JavaScript "Thin" mode by default and can optionally link a native Oracle Client library for "Thick" mode.

Install​

npm install @sqb/oracle oracledb

@sqb/connect and @sqb/builder are peer dependencies pulled in transitively if you already depend on them directly; otherwise install them alongside.

Registering the adapter​

Importing @sqb/oracle registers it as a side effect — there's nothing else to wire up:

import '@sqb/oracle';
import { SqbClient } from '@sqb/connect';

const client = new SqbClient({
  dialect: 'oracle', // or driver: 'oracledb'
  host: 'localhost',
  database: 'ORCLPDB1',
  user: 'myuser',
  password: 'mypassword',
});

Under the hood, @sqb/oracle's entry point imports @sqb/oracle-dialect (which teaches @sqb/builder Oracle's SQL syntax) and registers an OraAdapter instance with AdapterRegistry. OraAdapter reports driver: 'oracledb' and dialect: 'oracle'.

Configuration​

user/password map directly onto oracledb's ConnectionAttributes. host is parsed as a connection URL (host[:port][/database], with an optional user:password@ prefix that is used only if user/password aren't already set) and combined with port/database into an oracledb connectString:

new SqbClient({
  dialect: 'oracle',
  host: 'dbhost:1521/ORCLPDB1',
});

is equivalent to:

new SqbClient({
  dialect: 'oracle',
  host: 'dbhost',
  port: 1521,
  database: 'ORCLPDB1',
});

driverOptions is spread onto the ConnectionAttributes object, so it can carry any option oracledb supports (e.g. poolMin, externalAuth) that isn't already covered by the standard fields:

new SqbClient({
  dialect: 'oracle',
  host: 'dbhost:1521/ORCLPDB1',
  driverOptions: {
    externalAuth: true,
  },
});

Thin vs. Thick mode​

When @sqb/oracle is imported, it attempts once to locate and initialize a native Oracle Client library, searching directories listed in the LD_LIBRARY_PATH/ORA_HOME environment variables for libclntsh.so (Linux), libclntsh.dylib (macOS), or oci.dll (Windows). If one is found, oracledb switches to Thick mode; otherwise it stays in Thin mode.

The lookup runs at module load rather than on the first connect() (since v6.0.10) because oracledb.initOracleClient() must be called before the very first connection in the process — once any Thin-mode connection has been made anywhere, Thick mode can no longer be enabled for the rest of that process. Importing @sqb/oracle early therefore makes the choice deterministic, even if other code in the same process also uses oracledb directly.

note

OraClientConfiguration still declares a driverOptions.direct flag, but since v6.0.10 it no longer skips the client lookup — the lookup has already happened by the time connect() runs. To force Thin mode, make sure no Oracle Client library is reachable through LD_LIBRARY_PATH or ORA_HOME in the process environment.

Feature notes​

  • Cursors: supported (features.cursor: true), backed by oracledb result sets — see Cursors & Streaming.
  • Schemas: supported (features.schema: true) — setSchema()/getSchema() are implemented via ALTER SESSION SET CURRENT_SCHEMA and sys_context('userenv', 'current_schema') — see Schemas.
  • RETURNING: emulated with a follow-up SELECT for inserts (matched by ROWID) and updates (by re-running the original WHERE clause), since the adapter doesn't use oracledb's native RETURNING INTO bind variables.

Migrator support​

Supported since v6.0.8 — see Running migrations. Oracle commits implicitly on DDL, so its migration adapter refuses transaction: 'run'/'migration' and dryRun (see Transactional runs). OracleMigrationAdapter differs from the others in three ways:

  • No schema auto-creation. An Oracle schema is a user, and provisioning one is a DBA-level operation the adapter deliberately doesn't attempt. infoSchema, if given, is only used as a table-name prefix, and that schema/user must already exist.
  • Scripts are split into statements. oracledb runs exactly one statement per call, so a .task.sql script is split following the SQL*Plus convention: a PL/SQL block (trigger, procedure, function, package body, or a bare BEGIN/DECLARE block) ends with a lone / on its own line, and everything else is ;-separated DDL/DML.
  • Locking uses DBMS_LOCK. If that package isn't grantable in your environment, the adapter falls back to running without a lock rather than failing.

See also​