Wistle Postgres Provider

Overview

Wistle Postgres Provider is a Node.js library designed to manage multiple PostgreSQL database connections efficiently. It supports master and read replica configurations, automatic transaction handling, load balancing for read replicas, and health checks for all database connections.This is designed for node.js applications using Postgres for CURD Operations at high scale.

Features

• Centralized management of multiple PostgreSQL database connections.
• Support for master and read replica configurations.
• Automatic transaction handling.
• Read replica load balancing.
• Health checks for all database connections.
• Graceful shutdown of all connections.

Installation

Install the package via NPM:

npm install @wistle/postgres

Usage

1. Define Configuration

Define the configuration for your databases, including the master and optional read replicas.

import { DatabaseProviderConfig } from "postgres-provider";

const config: DatabaseProviderConfig = {
    userDb: {
        main: {
            user: "username",
            host: "localhost",
            database: "userdb",
            password: "password",
            port: 5432,
        },
        replicas: [
            {
                user: "username",
                host: "replica1.local",
                database: "userdb",
                password: "password",
                port: 5432,
            },
            {
                user: "username",
                host: "replica2.local",
                database: "userdb",
                password: "password",
                port: 5432,
            },
        ],
    },
    orderDb: {
        main: {
            user: "username",
            host: "localhost",
            database: "orderdb",
            password: "password",
            port: 5432,
        },
    },
};

2. Create Database Provider

Set up the PostgresProvider with your database configurations.

import { PostgresProvider } from "postgres-provider";

PostgresProvider.addConnections(config);

3. Retrieve Connection and Execute Queries

Retrieve a connection and perform database operations.

import { PostgresProvider } from "postgres-provider";

async function performDatabaseOperations() {
    const userDb = PostgresProvider.getConnection("userDb");

    // Executing a query
    const result = await userDb.executeQueryOnPool("SELECT * FROM users");
    console.log(result.rows);

    // Executing a query on read replica
    const readResult = await userDb.executeQueryOnReadReplicaPool("SELECT * FROM users");
    console.log(readResult.rows);

    // Transaction example
    const client = await userDb.getConnectionWithTransaction();
    try {
        await userDb.executeQueryOnPool("INSERT INTO users (name) VALUES ($1)", ["John Doe"], client);
        await userDb.commitTransaction(client);
    } catch (error) {
        await userDb.rollbackTransaction(client);
        throw error;
    }
}

performDatabaseOperations().catch(console.error);

4. Health Check

Check the health of all database connections.

import { PostgresProvider } from "postgres-provider";

async function checkDatabaseHealth() {
    try {
        await PostgresProvider.checkAllConnectionsHealth();
        console.log("All connections are healthy");
    } catch (error) {
        console.error("Some connections are unhealthy", error);
    }
}

checkDatabaseHealth().catch(console.error);

5. Graceful Shutdown

Close all connections gracefully when shutting down the application.

import { PostgresProvider } from "postgres-provider";

async function shutdown() {
    await PostgresProvider.closeAllConnections();
    console.log("All connections closed");
}

process.on("SIGTERM", shutdown);
process.on("SIGINT", shutdown);

Classes and Methods

PostgresService

Methods

• getConnectionWithTransaction(isolationLevel: string): Promise<PoolClient>
• executeQueryOnPool<T>(queryText: string, params?: any[], connection?: PoolClient): Promise<QueryResult<T>>
• executeQueryOnPoolFlaggedReadOnly<T>(queryText: string, params?: any[], readOnly: boolean, connection?: PoolClient): Promise<QueryResult<T>>
• executeQueryOnReadReplicaPool<T>(queryText: string, params?: any[]): Promise<QueryResult<T>>
• commitTransaction(client: PoolClient): Promise<void>
• rollbackTransaction(connection: PoolClient): Promise<void>
• close(): Promise<void>
• checkHealth(): Promise<boolean>

PostgresProvider

Provider Methods

• static addConnections(config: DatabaseProviderConfig): void
• static getConnection(connectionName: string): PostgresService
• static async checkAllConnectionsHealth(): Promise<void>
• static async closeAllConnections(): Promise<void>

Best Practices

• Always release connections back to the pool.
• Perform health checks to ensure all connections are active.
• Gracefully shut down connections when the application terminates.
• Use appropriate error handling around database operations.

Contributing

Contributions are welcome! Please open an issue or submit a pull request on GitHub.

License

This project is licensed under the MIT License.