npm package: @phila/db-oracle
Description: Lambda-aware Oracle database connection using thin mode for City of Philadelphia AWS infrastructure
Latest version: 0.0.7

# @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

### Basic Usage

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

### With Options

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

### 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:

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