npm package: @phila/db-postgres
Description: Lambda-aware PostgreSQL connection pooling for City of Philadelphia AWS infrastructure
Latest version: 0.0.8

# @phila/db-postgres

Lambda-aware PostgreSQL connection pooling for City of Philadelphia AWS infrastructure. Provides a cached connection pool that reads credentials from AWS Secrets Manager.

## Installation

```bash
npm install @phila/db-postgres
# or
pnpm add @phila/db-postgres
```

## Features

- **Lambda-optimized**: Connection pool configured for Lambda cold starts and warm containers
- **Secrets Manager integration**: Automatically retrieves credentials from AWS Secrets Manager
- **Connection caching**: Reuses connections across Lambda invocations
- **SSL support**: Secure connections with SSL enabled by default

## Usage

### Basic Usage

```typescript
import { getPool } from '@phila/db-postgres';

// Uses environment variables: DB_SECRET_ARN and DB_NAME
const pool = await getPool();

const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);
```

### With Options

```typescript
import { getPool } from '@phila/db-postgres';

const pool = await getPool({
  secretArn: 'arn:aws:secretsmanager:us-east-1:123456789012:secret:db-credentials',
  database: 'myapp'
});

const result = await pool.query('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` - Name of the database to connect to

### Secrets Manager Format

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

```json
{
  "host": "database.example.com",
  "port": 5432,
  "username": "dbuser",
  "password": "dbpassword"
}
```

## Connection Pool Configuration

The pool is configured for Lambda environments:

- **Max connections**: 1 (Lambda-optimized)
- **Idle timeout**: 120 seconds
- **Connection timeout**: 5 seconds
- **SSL**: Enabled with `rejectUnauthorized: false`

These settings ensure:
- Efficient connection reuse in warm Lambda containers
- Fast connection establishment
- Proper cleanup of idle connections

## Example: Lambda Function

```typescript
import { APIGatewayProxyHandler } from 'aws-lambda';
import { getPool } from '@phila/db-postgres';

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

## 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 `database` option
3. Ensure the secret exists and has the correct format

## Development

```bash
# Build
pnpm build

# Run tests
pnpm test

# Watch mode
pnpm test:watch

# Lint
pnpm lint
```

## Dependencies

- `pg` - PostgreSQL client for Node.js
- `@aws-sdk/client-secrets-manager` - AWS SDK for retrieving secrets

## License

Part of the City of Philadelphia AWS Infrastructure Library.