@phila/db-oracle

Lambda-aware Oracle database connection using thin mode for City of Philadelphia AWS infrastructure. Provides a cached connection that reads credentials from AWS Secrets Manager.

Installation

npm install @phila/db-oracle
# or
pnpm add @phila/db-oracle

Features

• Lambda-optimized: Uses Oracle Database Thin Mode (no Oracle Client libraries required)
• Secrets Manager integration: Automatically retrieves credentials from AWS Secrets Manager
• Connection caching: Reuses connections across Lambda invocations
• Thin mode: No need for Oracle Instant Client in Lambda deployment package

Usage

Basic Usage

import { getConnection } from '@phila/db-oracle';

// Uses environment variables: DB_SECRET_ARN and DB_NAME
const connection = await getConnection();

const result = await connection.execute(
  'SELECT * FROM users WHERE id = :id',
  { id: userId }
);

With Options

import { getConnection } from '@phila/db-oracle';

const connection = await getConnection({
  secretArn: 'arn:aws:secretsmanager:us-east-1:123456789012:secret:db-credentials',
  serviceName: 'MYAPP'
});

const result = await connection.execute('SELECT * FROM users');

Environment Variables

The library uses the following environment variables if options are not provided:

• DB_SECRET_ARN - ARN of the AWS Secrets Manager secret containing database credentials
• DB_NAME - Service name or SID of the Oracle database

Secrets Manager Format

The secret in AWS Secrets Manager must be a JSON object with the following structure:

{
  "host": "database.example.com",
  "port": 1521,
  "username": "dbuser",
  "password": "dbpassword"
}

Oracle Thin Mode

This package uses Oracle Database Thin Mode (oracledb v6+), which means:

• ✅ No Oracle Instant Client required
• ✅ Smaller Lambda deployment packages
• ✅ Faster cold starts
• ✅ Works in Lambda without additional configuration

The connection string is automatically constructed as:

host:port/serviceName

Example: Lambda Function

import { APIGatewayProxyHandler } from 'aws-lambda';
import { getConnection } from '@phila/db-oracle';

export const handler: APIGatewayProxyHandler = async (event) => {
  const connection = await getConnection();
  
  const result = await connection.execute(
    'SELECT * FROM users WHERE id = :id',
    { id: event.pathParameters?.id }
  );
  
  return {
    statusCode: 200,
    body: JSON.stringify(result.rows?.[0])
  };
};

Connection Management

The connection is cached and reused across Lambda invocations. This is optimal for Lambda because:

• Warm containers can reuse the connection
• Reduces connection establishment overhead
• Improves response times for subsequent invocations

Note: For production use, consider implementing connection pooling or connection lifecycle management based on your specific requirements.

Error Handling

The library uses Node.js assertions to validate required parameters. Make sure to:

1. Set DB_SECRET_ARN environment variable or provide secretArn option
2. Set DB_NAME environment variable or provide serviceName option
3. Ensure the secret exists and has the correct format
4. Verify network connectivity from Lambda to Oracle database (VPC, security groups, etc.)

Development

# Build
pnpm build

# Run tests
pnpm test

# Watch mode
pnpm test:watch

# Lint
pnpm lint

Dependencies

• oracledb - Oracle Database driver for Node.js (v6+ with thin mode)
• @aws-sdk/client-secrets-manager - AWS SDK for retrieving secrets

License

Part of the City of Philadelphia AWS Infrastructure Library.