Oracle #

# Install the npm package
npm install oracledb

# Type definitions — needs a separate installation
npm install --save-dev @types/oracledb

# macOS (via Homebrew)
brew install instantclient-basic

# Ubuntu/Debian
# Download from: https://www.oracle.com/database/technologies/instant-client/downloads.html
# Extract to /opt/oracle/instantclient_21_x
sudo apt-get install libaio1
export LD_LIBRARY_PATH=/opt/oracle/instantclient_21_x:$LD_LIBRARY_PATH

# Windows — add the Instant Client folder to PATH

// src/db/connection.ts — oracledb initialization
import oracledb, {
  Connection,
  Pool,
  BindParameters,
  ExecuteOptions,
} from 'oracledb';

// Global configuration — call once at startup
oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;  // results as objects, not arrays
oracledb.autoCommit = false;                       // always commit explicitly
oracledb.fetchArraySize = 100;                     // rows fetched per server round-trip

// tsconfig.json
{
  "compilerOptions": {
    "target": "ES2020",
    "module": "commonjs",
    "lib": ["ES2020"],
    "strict": true,
    "esModuleInterop": true,
    "outDir": "./dist",
    "rootDir": "./src"
  }
}

import oracledb, { Pool, Connection, PoolAttributes } from 'oracledb';

const poolConfig: PoolAttributes = {
  user: process.env.DB_USER ?? 'system',
  password: process.env.DB_PASSWORD ?? '',
  connectString: process.env.DB_CONNECT_STRING ?? 'localhost:1521/XEPDB1',
  // connectString formats:
  // - Easy Connect: "host:port/service_name"
  // - TNS alias:    "MYDB" (requires tnsnames.ora)
  // - Full:         "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=svc)))"
  poolMin: 2,            // minimum connections always alive
  poolMax: 10,           // maximum connections
  poolIncrement: 1,      // how many connections to add when needed
  poolTimeout: 60,       // seconds before idle connections are removed from the pool
  poolPingInterval: 60,  // check idle connections every N seconds
  sessionCallback: initSession,  // called when a new connection is created
};

// Session initialization — runs once per new pool connection
async function initSession(
  connection: Connection,
  requestedTag: string,
  callback: (error?: Error) => void
): Promise<void> {
  try {
    // Set timezone, date format, and other session parameters
    await connection.execute(
      `ALTER SESSION SET
         NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
         NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'
         TIME_ZONE = 'Asia/Jakarta'`
    );
    callback();
  } catch (err) {
    callback(err instanceof Error ? err : new Error(String(err)));
  }
}

let poolInstance: Pool | null = null;

export async function getPool(): Promise<Pool> {
  if (!poolInstance) {
    poolInstance = await oracledb.createPool(poolConfig);
  }
  return poolInstance;
}

export async function getConnection(): Promise<Connection> {
  const pool = await getPool();
  return pool.getConnection();
}

export async function closePool(): Promise<void> {
  if (poolInstance) {
    await poolInstance.close(10);  // wait max 10 seconds for active connections to finish
    poolInstance = null;
  }
}

process.on('SIGTERM', closePool);
process.on('SIGINT', closePool);

// Type for the USERS table (Oracle column names are always uppercase)
interface UserRow {
  ID: number;
  NAMA: string;
  EMAIL: string;
  PASSWORD_HASH: string;
  ROLE: string;
  AKTIF: number;         // Oracle has no native BOOLEAN — use NUMBER(1)
  DIBUAT_PADA: Date;
  DIPERBARUI_PADA: Date | null;
}

// A friendlier type for use in the application
interface User {
  id: number;
  nama: string;
  email: string;
  passwordHash: string;
  role: 'admin' | 'user' | 'moderator';
  aktif: boolean;
  dibuatPada: Date;
  diperbarui_pada: Date | null;
}

// Mapper function from Oracle row to domain type
function mapRowToUser(row: UserRow): User {
  return {
    id: row.ID,
    nama: row.NAMA,
    email: row.EMAIL,
    passwordHash: row.PASSWORD_HASH,
    role: row.ROLE as User['role'],
    aktif: row.AKTIF === 1,
    dibuatPada: row.DIBUAT_PADA,
    diperbarui_pada: row.DIPERBARUI_PADA,
  };
}

// With lowercase aliases in SQL — the result is directly camelCase-friendly
interface UserFromQuery {
  id: number;
  nama: string;
  email: string;
  aktif: number;
  dibuat_pada: Date;
}

// Query with explicit column aliases
const SQL_SELECT_USER = `
  SELECT
    u.id          AS "id",
    u.nama        AS "nama",
    u.email       AS "email",
    u.aktif       AS "aktif",
    u.dibuat_pada AS "dibuat_pada"
  FROM users u
  WHERE u.id = :id
`;
// Columns in double quotes in Oracle become case-sensitive

import oracledb from 'oracledb';
import { getConnection } from './db/connection';

// ── Simple SELECT
async function semuaUser(): Promise<User[]> {
  const conn = await getConnection();
  try {
    const result = await conn.execute<UserRow>(
      `SELECT id, nama, email, role, aktif, dibuat_pada
       FROM users
       WHERE aktif = 1
       ORDER BY dibuat_pada DESC`
    );
    return (result.rows ?? []).map(mapRowToUser);
  } finally {
    await conn.close();  // REQUIRED: always close in finally
  }
}

// ── SELECT with a bind parameter — use :namaParam
async function cariUserById(id: number): Promise<User | null> {
  const conn = await getConnection();
  try {
    const result = await conn.execute<UserRow>(
      'SELECT * FROM users WHERE id = :id AND aktif = 1',
      { id }  // bind by name: { id: value }
    );
    const row = result.rows?.[0];
    return row ? mapRowToUser(row) : null;
  } finally {
    await conn.close();
  }
}

// ── SELECT with multiple bind parameters
async function produkByKategoriDanHarga(
  kategoriId: number,
  hargaMin: number,
  hargaMax: number
): Promise<Produk[]> {
  const conn = await getConnection();
  try {
    const result = await conn.execute<ProdukRow>(
      `SELECT *
       FROM produk
       WHERE kategori_id = :kategoriId
         AND harga BETWEEN :hargaMin AND :hargaMax
         AND stok > 0
       ORDER BY harga ASC`,
      { kategoriId, hargaMin, hargaMax }
    );
    return (result.rows ?? []).map(mapRowToProduk);
  } finally {
    await conn.close();
  }
}

// ── SELECT with LIKE — Oracle uses || for concatenation
async function cariProdukByNama(keyword: string): Promise<Produk[]> {
  const conn = await getConnection();
  try {
    // Escape Oracle's special LIKE characters: %, _, \
    const safeKeyword = keyword.replace(/[%_\\]/g, '\\$&');
    const result = await conn.execute<ProdukRow>(
      `SELECT * FROM produk
       WHERE LOWER(nama) LIKE LOWER(:keyword) ESCAPE '\'
       FETCH FIRST 20 ROWS ONLY`,
      { keyword: `%${safeKeyword}%` }
    );
    return (result.rows ?? []).map(mapRowToProduk);
  } finally {
    await conn.close();
  }
}

// ── SELECT with IN — Oracle can't bind arrays directly
async function produkByIds(ids: number[]): Promise<Produk[]> {
  if (ids.length === 0) return [];
  const conn = await getConnection();
  try {
    // Create dynamic bind variables: :id0, :id1, :id2, ...
    const binds: Record<string, number> = {};
    const placeholders = ids.map((id, i) => {
      binds[`id${i}`] = id;
      return `:id${i}`;
    });

    const result = await conn.execute<ProdukRow>(
      `SELECT * FROM produk WHERE id IN (${placeholders.join(', ')})`,
      binds
    );
    return (result.rows ?? []).map(mapRowToProduk);
  } finally {
    await conn.close();
  }
}

// ANTI-PATTERN: string interpolation — vulnerable to SQL injection
async function cariUserTidakAman(email: string): Promise<UserRow[]> {
  const conn = await getConnection();
  try {
    const result = await conn.execute<UserRow>(
      `SELECT * FROM users WHERE email = '${email}'`  // DON'T!
    );
    return result.rows ?? [];
  } finally {
    await conn.close();
  }
}

// CORRECT: always use bind parameters
async function cariUserAman(email: string): Promise<User | null> {
  const conn = await getConnection();
  try {
    const result = await conn.execute<UserRow>(
      'SELECT * FROM users WHERE email = :email',
      { email }  // the value is processed separately by Oracle — safe
    );
    const row = result.rows?.[0];
    return row ? mapRowToUser(row) : null;
  } finally {
    await conn.close();
  }
}

import oracledb from 'oracledb';
import { getConnection } from './db/connection';

interface InputUser {
  nama: string;
  email: string;
  passwordHash: string;
  role?: string;
}

// ── INSERT with RETURNING INTO to get the ID
async function buatUser(input: InputUser): Promise<number> {
  const conn = await getConnection();
  try {
    const result = await conn.execute(
      `INSERT INTO users (id, nama, email, password_hash, role, aktif, dibuat_pada)
       VALUES (users_seq.NEXTVAL, :nama, :email, :passwordHash, :role, 1, SYSDATE)
       RETURNING id INTO :newId`,
      {
        nama: input.nama,
        email: input.email,
        passwordHash: input.passwordHash,
        role: input.role ?? 'user',
        newId: { dir: oracledb.BIND_OUT, type: oracledb.NUMBER },
      }
    );

    await conn.commit();
    // RETURNING INTO is returned in outBinds
    const outBinds = result.outBinds as { newId: number[] };
    return outBinds.newId[0];
  } catch (error) {
    await conn.rollback();
    throw error;
  } finally {
    await conn.close();
  }
}

// ── Batch INSERT with executemany — far more efficient than a loop
async function buatBanyakProduk(
  produkList: Array<{ nama: string; harga: number; stok: number; kategoriId: number }>
): Promise<number> {
  if (produkList.length === 0) return 0;

  const conn = await getConnection();
  try {
    const binds = produkList.map(p => ({
      nama: p.nama,
      harga: p.harga,
      stok: p.stok,
      kategoriId: p.kategoriId,
    }));

    const result = await conn.executeMany(
      `INSERT INTO produk (id, nama, harga, stok, kategori_id, dibuat_pada)
       VALUES (produk_seq.NEXTVAL, :nama, :harga, :stok, :kategoriId, SYSDATE)`,
      binds,
      { autoCommit: true }  // commit automatically after all rows are inserted
    );

    return result.rowsAffected ?? 0;
  } catch (error) {
    await conn.rollback();
    throw error;
  } finally {
    await conn.close();
  }
}

import oracledb from 'oracledb';
import { getConnection } from './db/connection';

// ── UPDATE with RETURNING INTO
async function updateStokProduk(
  id: number,
  deltaStok: number
): Promise<{ stokBaru: number } | null> {
  const conn = await getConnection();
  try {
    const result = await conn.execute(
      `UPDATE produk
       SET stok = stok + :delta
       WHERE id = :id AND (stok + :delta2) >= 0
       RETURNING stok INTO :stokBaru`,
      {
        id,
        delta: deltaStok,
        delta2: deltaStok,  // Oracle needs a separate bind for the same value
        stokBaru: { dir: oracledb.BIND_OUT, type: oracledb.NUMBER },
      }
    );

    await conn.commit();
    const out = result.outBinds as { stokBaru: number[] };
    if (!out.stokBaru || out.stokBaru.length === 0) return null;
    return { stokBaru: out.stokBaru[0] };
  } catch (error) {
    await conn.rollback();
    throw error;
  } finally {
    await conn.close();
  }
}

// ── UPDATE specific fields
async function updateUser(
  id: number,
  data: Partial<Pick<User, 'nama' | 'email'>>
): Promise<boolean> {
  const entries = Object.entries(data).filter(([, v]) => v !== undefined);
  if (entries.length === 0) return false;

  // Whitelist — REQUIRED for interpolated column names
  const allowedColumns: Record<string, string> = {
    nama: 'nama',
    email: 'email',
  };

  const setClauses: string[] = [];
  const binds: Record<string, unknown> = { id };

  for (const [key, value] of entries) {
    const col = allowedColumns[key];
    if (!col) continue;
    setClauses.push(`${col} = :${key}`);
    binds[key] = value;
  }

  if (setClauses.length === 0) return false;

  const conn = await getConnection();
  try {
    const result = await conn.execute(
      `UPDATE users
       SET ${setClauses.join(', ')}, diperbarui_pada = SYSDATE
       WHERE id = :id`,
      binds
    );
    await conn.commit();
    return (result.rowsAffected ?? 0) > 0;
  } catch (error) {
    await conn.rollback();
    throw error;
  } finally {
    await conn.close();
  }
}

// ── DELETE
async function hapusUser(id: number): Promise<boolean> {
  const conn = await getConnection();
  try {
    const result = await conn.execute(
      'DELETE FROM users WHERE id = :id',
      { id }
    );
    await conn.commit();
    return (result.rowsAffected ?? 0) > 0;
  } catch (error) {
    await conn.rollback();
    throw error;
  } finally {
    await conn.close();
  }
}

import oracledb from 'oracledb';
import { getConnection } from './db/connection';

interface ItemPesanan {
  produkId: number;
  jumlah: number;
  hargaSatuan: number;
}

async function buatPesanan(
  userId: number,
  items: ItemPesanan[]
): Promise<number> {
  const conn = await getConnection();

  try {
    // Oracle: the transaction starts automatically with the first query
    const totalHarga = items.reduce(
      (sum, item) => sum + item.hargaSatuan * item.jumlah, 0
    );

    // 1. Insert the order
    const r1 = await conn.execute(
      `INSERT INTO pesanan (id, user_id, total_harga, status, dibuat_pada)
       VALUES (pesanan_seq.NEXTVAL, :userId, :total, 'pending', SYSDATE)
       RETURNING id INTO :newId`,
      {
        userId,
        total: totalHarga,
        newId: { dir: oracledb.BIND_OUT, type: oracledb.NUMBER },
      }
    );

    const out1 = r1.outBinds as { newId: number[] };
    const pesananId = out1.newId[0];

    // 2. Process each item
    for (const item of items) {
      await conn.execute(
        `INSERT INTO pesanan_item
           (id, pesanan_id, produk_id, jumlah, harga_satuan)
         VALUES (pesanan_item_seq.NEXTVAL, :pesananId, :produkId, :jumlah, :harga)`,
        {
          pesananId,
          produkId: item.produkId,
          jumlah: item.jumlah,
          harga: item.hargaSatuan,
        }
      );

      // Decrease stock — fails if stock is insufficient
      const rStok = await conn.execute(
        `UPDATE produk
         SET stok = stok - :jumlah
         WHERE id = :produkId AND stok >= :jumlah2`,
        { jumlah: item.jumlah, produkId: item.produkId, jumlah2: item.jumlah }
      );

      if ((rStok.rowsAffected ?? 0) === 0) {
        await conn.rollback();
        throw new Error(`Stok tidak cukup untuk produk ID ${item.produkId}`);
      }
    }

    // Commit only if all steps succeeded
    await conn.commit();
    return pesananId;

  } catch (error) {
    try { await conn.rollback(); } catch { /* ignore rollback errors */ }
    throw error;
  } finally {
    await conn.close();
  }
}

import oracledb from 'oracledb';
import { getConnection } from './db/connection';

// ── Calling a stored procedure
async function spCariUser(email: string): Promise<User | null> {
  const conn = await getConnection();
  try {
    const result = await conn.execute(
      'BEGIN sp_cari_user(:email, :cursor); END;',
      {
        email,
        cursor: { dir: oracledb.BIND_OUT, type: oracledb.CURSOR },
      }
    );

    const out = result.outBinds as { cursor: oracledb.ResultSet<UserRow> };
    const cursor = out.cursor;
    const rows = await cursor.getRows<UserRow>(1);
    await cursor.close();

    return rows[0] ? mapRowToUser(rows[0]) : null;
  } finally {
    await conn.close();
  }
}

// ── Stored procedure with OUTPUT parameters
async function spDaftarUser(
  input: InputUser
): Promise<{ userId: number; pesanError: string | null }> {
  const conn = await getConnection();
  try {
    const result = await conn.execute(
      `BEGIN
         sp_daftar_user(
           p_nama         => :nama,
           p_email        => :email,
           p_password     => :password,
           p_user_id      => :userId,
           p_pesan_error  => :pesanError
         );
       END;`,
      {
        nama: input.nama,
        email: input.email,
        password: input.passwordHash,
        userId:     { dir: oracledb.BIND_OUT, type: oracledb.NUMBER },
        pesanError: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 500 },
      }
    );

    await conn.commit();
    const out = result.outBinds as { userId: number; pesanError: string | null };
    return { userId: out.userId, pesanError: out.pesanError };
  } catch (error) {
    await conn.rollback();
    throw error;
  } finally {
    await conn.close();
  }
}

// ── PL/SQL anonymous block — for logic that doesn't need to be a permanent SP
async function prosesDataBatch(batchId: number): Promise<{ berhasil: number; gagal: number }> {
  const conn = await getConnection();
  try {
    const result = await conn.execute(
      `DECLARE
         v_berhasil NUMBER := 0;
         v_gagal    NUMBER := 0;
       BEGIN
         FOR rec IN (SELECT id FROM antrian_proses WHERE batch_id = :batchId)
         LOOP
           BEGIN
             UPDATE produk SET stok = stok + 1 WHERE id = rec.id;
             v_berhasil := v_berhasil + 1;
           EXCEPTION
             WHEN OTHERS THEN
               v_gagal := v_gagal + 1;
           END;
         END LOOP;
         :berhasil := v_berhasil;
         :gagal    := v_gagal;
       END;`,
      {
        batchId,
        berhasil: { dir: oracledb.BIND_OUT, type: oracledb.NUMBER },
        gagal:    { dir: oracledb.BIND_OUT, type: oracledb.NUMBER },
      }
    );

    await conn.commit();
    const out = result.outBinds as { berhasil: number; gagal: number };
    return { berhasil: out.berhasil, gagal: out.gagal };
  } finally {
    await conn.close();
  }
}

import oracledb from 'oracledb';

// The most commonly encountered Oracle error codes
const ORA_ERRORS = {
  UNIQUE_VIOLATION: 1,        // ORA-00001: unique constraint violated
  INTEGRITY_VIOLATION: 2291,  // ORA-02291: integrity constraint (FK) violated
  CHILD_EXISTS: 2292,         // ORA-02292: child record found (delete FK)
  NOT_NULL_VIOLATION: 1400,   // ORA-01400: cannot insert NULL
  VALUE_TOO_LARGE: 12899,     // ORA-12899: value too large for column
  DEADLOCK: 60,               // ORA-00060: deadlock detected
  SNAPSHOT_TOO_OLD: 1555,     // ORA-01555: snapshot too old
  TABLE_NOT_EXIST: 942,       // ORA-00942: table or view does not exist
  SEQUENCE_NOT_EXIST: 2289,   // ORA-02289: sequence does not exist
  INVALID_NUMBER: 1722,       // ORA-01722: invalid number
} as const;

class DatabaseError extends Error {
  constructor(message: string, public readonly code?: number) {
    super(message);
    this.name = 'DatabaseError';
  }
}

class UniqueViolationError extends DatabaseError {
  constructor(constraint?: string) {
    super(`Duplikat data${constraint ? ` pada constraint '${constraint}'` : ''}`);
    this.name = 'UniqueViolationError';
  }
}

class ForeignKeyError extends DatabaseError {
  constructor(message = 'Referensi data tidak valid') {
    super(message);
    this.name = 'ForeignKeyError';
  }
}

class DeadlockError extends DatabaseError {
  constructor() {
    super('Deadlock terdeteksi — coba ulangi operasi');
    this.name = 'DeadlockError';
  }
}

function tanganiOracleError(error: unknown): never {
  // oracledb errors have an errorNum property
  if (error && typeof error === 'object' && 'errorNum' in error) {
    const oraError = error as { errorNum: number; message: string };
    switch (oraError.errorNum) {
      case ORA_ERRORS.UNIQUE_VIOLATION: {
        // Extract the constraint name from the message: ORA-00001: unique constraint (SCHEMA.CONST) violated
        const match = oraError.message.match(/\((.+?)\)/);
        throw new UniqueViolationError(match?.[1]);
      }
      case ORA_ERRORS.INTEGRITY_VIOLATION:
        throw new ForeignKeyError('Record referensi tidak ditemukan');
      case ORA_ERRORS.CHILD_EXISTS:
        throw new ForeignKeyError('Tidak bisa menghapus — masih ada data yang mereferensikan');
      case ORA_ERRORS.NOT_NULL_VIOLATION:
        throw new DatabaseError('Field wajib tidak boleh kosong', oraError.errorNum);
      case ORA_ERRORS.VALUE_TOO_LARGE:
        throw new DatabaseError('Data melebihi panjang maksimum kolom', oraError.errorNum);
      case ORA_ERRORS.DEADLOCK:
        throw new DeadlockError();
    }
  }
  throw new DatabaseError(`Error Oracle: ${String(error)}`);
}

// Automatic retry for deadlocks
async function withDeadlockRetry<T>(
  fn: () => Promise<T>,
  maxRetry = 3
): Promise<T> {
  for (let attempt = 1; attempt <= maxRetry; attempt++) {
    try {
      return await fn();
    } catch (error) {
      if (error instanceof DeadlockError && attempt < maxRetry) {
        await new Promise(res => setTimeout(res, 200 * attempt));
        continue;
      }
      throw error;
    }
  }
  throw new Error('Unreachable');
}

import oracledb from 'oracledb';
import { getConnection } from './db/connection';

type SortField = 'nama' | 'email' | 'dibuat_pada';
const ALLOWED_SORT = new Set<SortField>(['nama', 'email', 'dibuat_pada']);

interface PaginasiResult<T> {
  data: T[];
  total: number;
  halaman: number;
  perHalaman: number;
  totalHalaman: number;
}

// ── Oracle 12c+ — OFFSET...FETCH
async function getUserPaginasi(
  halaman: number,
  perHalaman: number,
  sortBy: SortField = 'dibuat_pada',
  sortOrder: 'ASC' | 'DESC' = 'DESC'
): Promise<PaginasiResult<User>> {
  const safeSort = ALLOWED_SORT.has(sortBy) ? sortBy : 'dibuat_pada';
  const safeOrder = sortOrder === 'ASC' ? 'ASC' : 'DESC';
  const offset = (halaman - 1) * perHalaman;

  const conn = await getConnection();
  try {
    const [dataResult, countResult] = await Promise.all([
      conn.execute<UserRow>(
        `SELECT id, nama, email, role, aktif, dibuat_pada
         FROM users
         WHERE aktif = 1
         ORDER BY ${safeSort} ${safeOrder}
         OFFSET :offset ROWS FETCH NEXT :perHalaman ROWS ONLY`,
        { offset, perHalaman }
      ),
      conn.execute<{ TOTAL: number }>(
        'SELECT COUNT(*) AS TOTAL FROM users WHERE aktif = 1'
      ),
    ]);

    const total = countResult.rows?.[0]?.TOTAL ?? 0;

    return {
      data: (dataResult.rows ?? []).map(mapRowToUser),
      total,
      halaman,
      perHalaman,
      totalHalaman: Math.ceil(total / perHalaman),
    };
  } finally {
    await conn.close();
  }
}

// ── Oracle 11g and older — ROW_NUMBER()
async function getUserPaginasiLegacy(
  halaman: number,
  perHalaman: number
): Promise<User[]> {
  const conn = await getConnection();
  try {
    const rn_start = (halaman - 1) * perHalaman + 1;
    const rn_end = halaman * perHalaman;

    const result = await conn.execute<UserRow>(
      `SELECT * FROM (
         SELECT u.*, ROW_NUMBER() OVER (ORDER BY dibuat_pada DESC) AS rn
         FROM users u
         WHERE aktif = 1
       )
       WHERE rn BETWEEN :rnStart AND :rnEnd`,
      { rnStart: rn_start, rnEnd: rn_end }
    );
    return (result.rows ?? []).map(mapRowToUser);
  } finally {
    await conn.close();
  }
}