hyperconn-pg

Early release. Validate against your AWS Secrets Manager and PostgreSQL setup before production use.

Multi-tenant PostgreSQL connection manager with AWS Secrets Manager integration

hyperconn-pg is the PostgreSQL equivalent of HyperConn. It helps services resolve tenant-specific PostgreSQL connection strings from AWS Secrets Manager and lazily create cached pg.Pool instances per service and organization.

Features

• Multi-tenant PostgreSQL connection management
• AWS Secrets Manager integration for tenant-specific credentials
• Lazy pool creation per serviceName and orgId
• Reuse of existing pools for repeated tenant access
• Optional pool eviction for inactive or excess cached pools
• TypeScript-first API
• Framework-agnostic package design

Installation

npm install hyperconn-pg

Prerequisites

1. AWS Secrets Manager setup

Each tenant secret must be stored under this name format:

<projectName>-<orgId>

Each secret value should be a JSON object containing PostgreSQL connection strings keyed by service name:

{
  "<microserviceName>-postgresql": "postgresql://username:password@host:5432/database?options=-c%20search_path%3Dorg_schema"
}

Example:

• Secret name: martech-org1
• Secret value:

{
  "core-postgresql": "postgresql://app:secret@db-host:5432/platform?options=-c%20search_path%3Dorg1"
}

When getConnection("core", "org1") is called, hyperconn-pg will fetch the secret martech-org1 and read the key core-postgresql.

2. AWS credentials

Provide AWS credentials using environment variables, IAM roles, or any credential source supported by the AWS SDK. If you want to pass explicit credentials into the package, use:

AWS_SECRETS_MANAGER_ACCESS_KEY_ID=your_access_key_id
AWS_SECRETS_MANAGER_SECRET_ACCESS_KEY=your_secret_access_key
AWS_SECRETS_MANAGER_REGION=your_aws_region

Quick Start

1. Import and create the manager

import { PostgresManagerService, PostgresManagerParams } from "hyperconn-pg";

const postgresManager = new PostgresManagerService();

2. Initialize the manager

const params: PostgresManagerParams = {
  projectName: "martech",
  secretManager: {
    accessKeyId: process.env.AWS_SECRETS_MANAGER_ACCESS_KEY_ID!,
    secretAccessKey: process.env.AWS_SECRETS_MANAGER_SECRET_ACCESS_KEY!,
    region: process.env.AWS_SECRETS_MANAGER_REGION!,
  },
  services: [
    { serviceName: "core" },
    { serviceName: "billing" },
  ],
  pgOptions: {
    max: 10,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 10000,
  },
  poolManagement: {
    maxPools: 50,
    idleTtlMs: 5 * 60 * 1000,
  },
};

await postgresManager.initialize(params);

3. Get a tenant-specific pool

const pool = await postgresManager.getConnection("core", "org1");

const result = await pool.query(
  "SELECT id, email FROM users WHERE active = $1 LIMIT 10",
  [true],
);

console.log(result.rows);

API Reference

PostgresManagerService

Main service for resolving tenant credentials and managing cached PostgreSQL pools.

initialize(params: PostgresManagerParams): Promise<void>

Initializes the manager and validates configuration.

getConnection(serviceName: string, orgId: string): Promise<Pool>

Returns a cached pg.Pool for the given service and organization. If the pool does not exist, the service:

1. applies pool eviction checks
2. loads the tenant secret from AWS Secrets Manager
3. creates a new pg.Pool
4. caches and returns it

PostgresManagerParams

import { PoolConfig } from "pg";

interface SecretManagerParams {
  accessKeyId: string;
  secretAccessKey: string;
  region: string;
}

interface PostgresManagerParams {
  projectName: string;
  secretManager: SecretManagerParams;
  services: Array<{
    serviceName: string;
  }>;
  pgOptions?: PoolConfig;
  poolManagement?: {
    maxPools?: number;
    idleTtlMs?: number;
  };
}

Configuration Notes

pgOptions

pgOptions is passed directly into the pg.Pool constructor, so you can use standard pg pool options such as:

pgOptions: {
  max: 10,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 10000,
  allowExitOnIdle: false,
}

poolManagement

poolManagement controls cache eviction in the manager layer, not PostgreSQL server settings:

poolManagement: {
  maxPools: 50,
  idleTtlMs: 300000,
}

• maxPools: maximum number of cached tenant pools the manager should keep
• idleTtlMs: how long a cached pool can stay unused before it becomes removable

Pool Drop Logic

hyperconn-pg currently uses a minimal, on-demand eviction strategy.

How it works:

• Pools are cached by serviceName_orgId
• A reused pool updates its in-memory metadata (lastUsedAt, accessCount)
• Eviction runs only when a new pool is about to be created
• Pools are removed only if they appear idle: pool.totalCount === 0 or pool.idleCount === pool.totalCount

There are two removal paths:

1. Idle TTL eviction Pools older than idleTtlMs since last use are removed if they are idle.

2. Max pool cap eviction If the cache is already at or above maxPools, the least recently used idle pools are removed before creating a new one.

This is intentionally conservative:

• there is no background timer
• there is no aggressive frequency-based scoring
• active pools are not forcibly shut down
• cleanup happens only during getConnection(...)

Logging

The manager currently emits small lifecycle logs:

• Creating pool for <service>_<orgId>
• Reusing pool for <service>_<orgId>
• Removing pool for <service>_<orgId>

These logs are prefixed with [hyperconn-pg].

Example Service Wrapper

import { PostgresManagerService } from "hyperconn-pg";

export class DatabaseService {
  private manager = new PostgresManagerService();

  async initialize(): Promise<void> {
    await this.manager.initialize({
      projectName: "martech",
      secretManager: {
        accessKeyId: process.env.AWS_SECRETS_MANAGER_ACCESS_KEY_ID!,
        secretAccessKey: process.env.AWS_SECRETS_MANAGER_SECRET_ACCESS_KEY!,
        region: process.env.AWS_SECRETS_MANAGER_REGION!,
      },
      services: [{ serviceName: "core" }],
      pgOptions: {
        max: 10,
        idleTimeoutMillis: 30000,
      },
      poolManagement: {
        maxPools: 25,
        idleTtlMs: 300000,
      },
    });
  }

  async getUsers(orgId: string) {
    const pool = await this.manager.getConnection("core", orgId);
    const result = await pool.query("SELECT * FROM users LIMIT 10");
    return result.rows;
  }
}

Security Best Practices

• Do not hardcode AWS credentials in source code
• Prefer IAM roles when running on AWS
• Restrict Secrets Manager access using least-privilege IAM policies
• Use SSL/TLS for PostgreSQL connections where applicable
• Rotate both AWS and database credentials regularly

Troubleshooting

Secret not found

Secret not found for service: <serviceName> and orgId: <orgId>

Check:

• the secret name matches <projectName>-<orgId>
• the JSON contains <serviceName>-postgresql
• the value for that key is a valid PostgreSQL connection string

AWS credential errors

Check:

• AWS credentials are present and valid
• the configured region is correct
• the runtime has permission to call secretsmanager:GetSecretValue

Too many pools

If tenant churn is high, configure:

• a lower maxPools
• a lower idleTtlMs
• appropriate pgOptions.max so each retained pool is not oversized

Current Limitations

• The package does not preload all tenant pools at startup
• The package does not run background eviction
• There is no explicit public shutdown method yet for draining all cached pools
• services are currently validated for presence, but not yet used to restrict getConnection(...)

License

MIT