@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

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

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

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:

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

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

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