Instancing of postgres with pgbouncer and pooling

[NikhilVerma]

I have a question about using postgres.js instance with pgbouncer

Here is the setup:

1. An instanced version of postgres
2. PgBouncer - I use pgbouncer in my infra (as other dependencies out of my control need

Here is the rough code for the instance setup

import postgres from "postgres";
import env from "env-var";

const DB_MAX_CONNECTIONS = env.get("DB_MAX_CONNECTIONS").default("10").asIntPositive();
const DB_USE_PREPARED_STATEMENTS = env.get("DB_USE_PREPARED_STATEMENTS").default("true").asBool();
const DB_IDLE_TIMEOUT = env.get("DB_IDLE_TIMEOUT").default("0").asIntPositive(); // in seconds

class DatabaseClient {
	static instances = new Map<string, DatabaseClient>();
	client: ReturnType<typeof postgres> | null = null;

	public static getInstance(dbName?: string): DatabaseClient {
		const key = dbName ?? "default";
		if (!DatabaseClient.instances.has(key)) {
			DatabaseClient.instances.set(key, new DatabaseClient());
		}
		return DatabaseClient.instances.get(key)!;
	}

	public getClient(dbName?: string) {
		if (!this.client) {
			const host = process.env.PG_HOST;
			const port = process.env.PG_PORT;
			const user = process.env.PG_USER;
			const password = process.env.PG_PASSWORD;
			const database = dbName ?? process.env.PG_DB;
			const missingVars: Array<string> = [];

			if (!host) {
				missingVars.push("PG_HOST");
			}
			if (!port) {
				missingVars.push("PG_PORT");
			}
			if (!user) {
				missingVars.push("PG_USER");
			}
			if (!password) {
				missingVars.push("PG_PASSWORD");
			}
			if (!database) {
				missingVars.push("PG_DB");
			}

			if (missingVars.length > 0) {
				throw new Error(
					`Database configuration environment variables are not set: ${missingVars.join(", ")}`
				);
			}

			// Encode special characters in connection parameters
			const encodedUser = encodeURIComponent(user!);
			const encodedPassword = encodeURIComponent(password!);
			const encodedHost = encodeURIComponent(host!);
			const encodedDatabase = encodeURIComponent(database!);

			const connectionString = `postgres://${encodedUser}:${encodedPassword}@${encodedHost}:${port}/${encodedDatabase}`;

			const sslmode = process.env.PG_SSL_MODE || "enabled";

			this.client = postgres(connectionString, {
				prepare: DB_USE_PREPARED_STATEMENTS,
				max: DB_MAX_CONNECTIONS,
				idle_timeout: DB_IDLE_TIMEOUT,
				connect_timeout: 10,
				ssl: sslmode !== "disabled" ? { rejectUnauthorized: false } : false
			});
		}
		return this.client;
	}

	public async closeClient(): Promise<void> {
		if (this.client) {
			await this.client.end();
			this.client = null;
		}
	}
}

export const getPostgresClient = (dbName?: string) => 
	DatabaseClient.getInstance(dbName).getClient(dbName);

export const closeClient = async () => {
	const clients = Array.from(DatabaseClient.instances.values());
	await Promise.all(clients.map(client => client.closeClient()));
	DatabaseClient.instances.clear();
};

I see that postgres.js handles connection pooling on it's own, But I am not certain if using instances of it would be better? It I could create an instance for each query, with max: 1 would it end up a better solution?

So something like:

import postgres from "postgres";
import env from "env-var";

const DB_MAX_CONNECTIONS = env.get("DB_MAX_CONNECTIONS").default("10").asIntPositive();
const DB_USE_PREPARED_STATEMENTS = env.get("DB_USE_PREPARED_STATEMENTS").default("true").asBool();
const DB_IDLE_TIMEOUT = env.get("DB_IDLE_TIMEOUT").default("0").asIntPositive();

export function getPostgresClient(dbName) {
	const host = process.env.PG_HOST;
	const port = process.env.PG_PORT;
	const user = process.env.PG_USER;
	const password = process.env.PG_PASSWORD;
	const database = dbName ?? process.env.PG_DB;
	const missingVars = [];

	if (!host) {
		missingVars.push("PG_HOST");
	}
	if (!port) {
		missingVars.push("PG_PORT");
	}
	if (!user) {
		missingVars.push("PG_USER");
	}
	if (!password) {
		missingVars.push("PG_PASSWORD");
	}
	if (!database) {
		missingVars.push("PG_DB");
	}

	if (missingVars.length > 0) {
		throw new Error(
			`Database configuration environment variables are not set: ${missingVars.join(", ")}`
		);
	}

	const encodedUser = encodeURIComponent(user);
	const encodedPassword = encodeURIComponent(password);
	const encodedHost = encodeURIComponent(host);
	const encodedDatabase = encodeURIComponent(database);

	const connectionString = `postgres://${encodedUser}:${encodedPassword}@${encodedHost}:${port}/${encodedDatabase}`;

	const sslmode = process.env.PG_SSL_MODE || "enabled";

	return postgres(connectionString, {
		prepare: DB_USE_PREPARED_STATEMENTS,
		max: 1, // Only 1 connection for each instance
		idle_timeout: DB_IDLE_TIMEOUT,
		connect_timeout: 10,
		ssl: sslmode !== "disabled" ? { rejectUnauthorized: false } : false
	});
}