@bernierllc/postgres-client

Direct PostgreSQL client with connection pooling, prepared statements, retry logic, and transaction support.

Installation

npm install @bernierllc/postgres-client

Features

• Connection Pooling: Efficient connection management using pg.Pool
• Prepared Statements: Parameterized queries to prevent SQL injection
• Transaction Support: Automatic commit/rollback with simple API
• Retry Logic: Configurable retry behavior for transient failures
• Structured Errors: Consistent error handling with PostgreSQLResult wrapper
• Logger Integration: Built-in logging using @bernierllc/logger
• TypeScript Support: Full type safety with strict mode

Usage

Basic Setup

import { PostgreSQLClient } from '@bernierllc/postgres-client';

const client = new PostgreSQLClient({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'postgres',
  password: 'secret',
  // Optional: enable retry logic (default: true)
  enableRetry: true,
  maxRetries: 3,
  // Optional: enable debug logging (default: false)
  debug: false,
});

Using Connection String

const client = new PostgreSQLClient({
  connectionString: process.env.DATABASE_URL,
  enableRetry: true,
  maxRetries: 3,
});

Query Operations

SELECT Query

interface User {
  id: number;
  name: string;
  email: string;
  created_at: Date;
}

const result = await client.query<User>(
  'SELECT * FROM users WHERE email = $1',
  ['[email protected]']
);

if (result.success) {
  console.log('Found users:', result.data);
  console.log('Row count:', result.rowCount);
} else {
  console.error('Query failed:', result.error);
}

INSERT Query

const result = await client.query(
  'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
  ['John Doe', '[email protected]']
);

if (result.success) {
  console.log('Inserted user:', result.data);
}

UPDATE Query

const result = await client.query(
  'UPDATE users SET name = $1 WHERE id = $2 RETURNING *',
  ['Jane Doe', 123]
);

if (result.success) {
  console.log('Updated rows:', result.rowCount);
}

DELETE Query

const result = await client.query(
  'DELETE FROM users WHERE id = $1 RETURNING *',
  [123]
);

if (result.success) {
  console.log('Deleted rows:', result.rowCount);
}

Transactions

Execute multiple operations atomically:

const result = await client.transaction(async (txClient) => {
  // Insert user
  await txClient.query(
    'INSERT INTO users (name, email) VALUES ($1, $2)',
    ['Alice', '[email protected]']
  );

  // Insert user profile
  await txClient.query(
    'INSERT INTO profiles (user_email, bio) VALUES ($1, $2)',
    ['[email protected]', 'Software Engineer']
  );

  // Both operations commit together
});

if (result.success) {
  console.log('Transaction committed successfully');
} else {
  console.error('Transaction failed and was rolled back:', result.error);
}

Advanced Usage

Access Underlying Pool

For advanced use cases, access the pg.Pool directly:

const pool = client.getPool();

// Use pool methods directly
pool.on('connect', (client) => {
  console.log('New client connected');
});

pool.on('error', (err) => {
  console.error('Pool error:', err);
});

Close Connections

// Close all connections when shutting down
await client.close();

Configuration

PostgreSQLClientConfig

interface PostgreSQLClientConfig extends PoolConfig {
  // PostgreSQL connection settings
  host?: string;
  port?: number;
  database?: string;
  user?: string;
  password?: string;
  connectionString?: string;

  // Pool configuration
  max?: number; // Maximum number of clients (default: 10)
  idleTimeoutMillis?: number; // Close idle clients after ms (default: 30000)
  connectionTimeoutMillis?: number; // Connection timeout in ms (default: 2000)

  // Retry configuration
  enableRetry?: boolean; // Enable retry logic (default: true)
  maxRetries?: number; // Maximum retry attempts (default: 3)

  // Logging
  debug?: boolean; // Enable debug logging (default: false)
}

Default Configuration

{
  enableRetry: true,
  maxRetries: 3,
  debug: false,
  max: 10,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000
}

API Reference

PostgreSQLClient

Constructor

constructor(config: PostgreSQLClientConfig)

Creates a new PostgreSQL client with connection pooling.

query(sql: string, params?: unknown[]): Promise<PostgreSQLResult<T[]>>

Execute a SQL query with optional parameterized values.

Parameters:

• sql - SQL query string with $1, $2, etc. placeholders
• params - Array of parameter values (optional)

Returns: PostgreSQLResult<T[]> with query results

transaction(callback: TransactionCallback): Promise<PostgreSQLResult>

Execute multiple operations in a transaction.

Parameters:

• callback - Async function receiving a transaction client

Returns: PostgreSQLResult<void> indicating success or failure

getPool(): Pool

Get the underlying pg.Pool for advanced usage.

close(): Promise

Close all connections in the pool.

PostgreSQLResult

interface PostgreSQLResult<T = unknown> {
  success: boolean;
  data?: T;
  error?: string;
  rowCount?: number;
}

Error Handling

All operations return a PostgreSQLResult with structured error information:

const result = await client.query('SELECT * FROM users');

if (!result.success) {
  console.error('Query failed:', result.error);
  // Handle error (log, retry, fallback, etc.)
  return;
}

// Safe to access result.data
const users = result.data;

Retry Behavior

The client automatically retries transient failures (connection timeouts, network errors):

• Initial delay: 1000ms
• Max delay: 10000ms
• Jitter: Random variation to prevent thundering herd
• Exponential backoff: Delay increases with each retry

Disable retry for specific operations:

const client = new PostgreSQLClient({
  connectionString: process.env.DATABASE_URL,
  enableRetry: false, // Disable all retries
});

Security

SQL Injection Prevention

Always use parameterized queries:

// ✅ SAFE - Parameterized query
const result = await client.query(
  'SELECT * FROM users WHERE email = $1',
  [userInput]
);

// ❌ UNSAFE - String concatenation
const result = await client.query(
  `SELECT * FROM users WHERE email = '${userInput}'`
);

Connection Security

Use SSL for production connections:

const client = new PostgreSQLClient({
  host: 'prod-db.example.com',
  database: 'production',
  user: 'app',
  password: process.env.DB_PASSWORD,
  ssl: {
    rejectUnauthorized: true,
  },
});

Integration Status

Logger Integration

Status: Integrated

Justification: This package uses @bernierllc/logger for structured logging of all database operations. Logs include query execution, connection events, transaction management, and error details to help with debugging and monitoring database interactions.

Pattern: Direct integration - logger is a required dependency for this package.

NeverHub Integration

Status: Not applicable

Justification: This is a core database client package that provides PostgreSQL connectivity. It does not participate in service discovery, event publishing, or service mesh operations. Database clients are infrastructure utilities that don't require service registration or discovery.

Pattern: Core utility - no service mesh integration needed. Service-level packages that use this client can integrate with NeverHub if needed.

Docs-Suite Integration

Status: Ready

Format: TypeDoc-compatible JSDoc comments are included throughout the source code. All public APIs are documented with examples and type information.

Examples

User CRUD Operations

import { PostgreSQLClient } from '@bernierllc/postgres-client';

const db = new PostgreSQLClient({
  connectionString: process.env.DATABASE_URL,
});

// Create user
async function createUser(name: string, email: string) {
  const result = await db.query(
    'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
    [name, email]
  );
  return result.data?.[0];
}

// Get user by ID
async function getUser(id: number) {
  const result = await db.query(
    'SELECT * FROM users WHERE id = $1',
    [id]
  );
  return result.data?.[0];
}

// Update user
async function updateUser(id: number, name: string) {
  const result = await db.query(
    'UPDATE users SET name = $1 WHERE id = $2 RETURNING *',
    [name, id]
  );
  return result.data?.[0];
}

// Delete user
async function deleteUser(id: number) {
  const result = await db.query(
    'DELETE FROM users WHERE id = $1 RETURNING *',
    [id]
  );
  return result.rowCount ?? 0 > 0;
}

Complex Transaction

async function transferBalance(fromUserId: number, toUserId: number, amount: number) {
  const result = await db.transaction(async (tx) => {
    // Deduct from sender
    const deduct = await tx.query(
      'UPDATE accounts SET balance = balance - $1 WHERE user_id = $2 AND balance >= $1 RETURNING *',
      [amount, fromUserId]
    );

    if (deduct.rows.length === 0) {
      throw new Error('Insufficient balance');
    }

    // Add to receiver
    await tx.query(
      'UPDATE accounts SET balance = balance + $1 WHERE user_id = $2',
      [amount, toUserId]
    );

    // Log transaction
    await tx.query(
      'INSERT INTO transactions (from_user_id, to_user_id, amount) VALUES ($1, $2, $3)',
      [fromUserId, toUserId, amount]
    );
  });

  return result.success;
}

See Also

• @bernierllc/logger - Logging integration
• @bernierllc/retry-policy - Retry logic utilities
• @bernierllc/supabase-client - Supabase wrapper client
• @bernierllc/database-adapter - Database abstraction layer

License

Copyright (c) 2025 Bernier LLC. All rights reserved.