@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

``bash
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

$3

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

$3

`typescript
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');
`

$3

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

$3

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

`json
{
"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

`typescript
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

`bash


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.