npm package: @tamasha/pg-connection
Description: Singleton Postgres connection manager using pg-promise with pool destroy
Author: Tamasha Team
Latest version: 1.0.2

# @tamasha/pg-connection

Simple PostgreSQL cluster connection manager. Register clusters with primary/replica databases and use load-balanced read/write operations.

## Features

- **Simple Setup**: Register clusters with primary and replica databases
- **Load Balancing**: Automatic distribution across replicas (round-robin or random)
- **Connection Reuse**: Single database instance per connection details
- **Proxy Access**: Transparent read/write separation
- **TypeScript Support**: Full type safety and IntelliSense

## Install

```bash
npm install @tamasha/pg-connection
```

Peer uses `pg-promise` under the hood.

## Usage

```ts
import { PgConnection } from "@tamasha/pg-connection";

// Register a cluster with primary and replica databases
const cluster = PgConnection.registerCluster({
  name: "my-app",
  primary: {
    cn: {
      host: "primary.db.local",
      port: 5432,
      database: "myapp",
      user: "appuser",
      password: "securepass",
      max: 20,
      min: 5,
    },
  },
  replicas: [{
    cn: {
      host: "replica.db.local",
      port: 5432,
      database: "myapp",
      user: "appuser",
      password: "securepass",
      max: 10,
      min: 2,
    },
  }],
  strategy: "round-robin", // or "random"
});

// Use for database operations
const readDb = cluster.readDb;   // Load-balanced reads
const writeDb = cluster.writeDb; // Primary writes

// Perform operations
const users = await readDb.any("SELECT * FROM users LIMIT 10");
await writeDb.none("INSERT INTO users(email) VALUES($1)", ["user@example.com"]);

// Cleanup when done
await cluster.destroy();
```

### Load Balancing

- **round-robin**: Cycles through replicas in order (default)
- **random**: Randomly selects from available replicas

### Simple Setup

```ts
// Single database (no replicas)
const cluster = PgConnection.registerCluster({
  name: "simple",
  primary: {
    cn: {
      host: "localhost",
      port: 5432,
      database: "mydb",
      user: "user",
      password: "pass",
    },
  },
});

const db = cluster.readDb; // Uses primary for all operations
await db.any("SELECT * FROM users");
await cluster.destroy();
```

## API

### Register Cluster
```ts
PgConnection.registerCluster({
  name: string,                    // Unique cluster name
  primary: ConnectionConfig,       // Primary database config
  replicas?: ConnectionConfig[],   // Optional replica configs
  strategy?: "round-robin" | "random" // Load balancing (default: "round-robin")
})
```

### Cluster Handle
Returns an object with:
- `name`: Cluster name
- `readDb`: Database proxy for reads (load-balanced across replicas)
- `writeDb`: Database proxy for writes (always uses primary)
- `destroy()`: Cleanup function

### Configuration
```ts
interface ConnectionConfig {
  cn: string | object;    // Connection string or object
  dc?: any;              // Database context
  options?: object;      // pg-promise options
}
```

## Examples

```bash
# Run the usage example
cd examples
npx ts-node usage.ts
```

Check `examples/usage.ts` for complete usage examples.

## Notes

- Based on pg-promise recommendations: only create one `Database` per connection details, otherwise the library emits a warning in development and you lose performance. See docs: `Database(cn, dcopt)` in the official reference.
- If a pool is shut down by a third party (`$pool.end()`), the cluster handle becomes invalid; you should register a new cluster.
- Each cluster maintains its own connection pools and load balancing state.

## Links

- pg-promise Database docs: [`Database`](https://vitaly-t.github.io/pg-promise/Database.html)