PostgreSQL Connection Pool

Common errors

• Error: connect ECONNREFUSED ::1:5432

  cause The application could not establish a connection to the PostgreSQL database. This usually indicates the database server is not running, is not accessible from the application's host, or has incorrect firewall rules.
  fix Verify that the PostgreSQL server is running and listening on the specified host and port (default 5432). Check firewall rules and ensure the database user has network access.

• Error: Client was not released to the pool

  cause A client was acquired from the pool using `pool.connect()` but was not returned via `client.release()`, leading to resource exhaustion.
  fix Ensure every `pool.connect()` call is paired with a `client.release()` call, typically within a `try...finally` block to guarantee release even if errors occur.

• TypeError: Pool is not a constructor

  cause The `Pool` constructor was imported incorrectly, most commonly by trying to destructure a CommonJS default export (e.g., `const { Pool } = require('pg-pool')`) or by using a named import for a CommonJS default export in an ESM context (e.g., `import { Pool } from 'pg-pool'`).
  fix For CommonJS, use `const Pool = require('pg-pool')`. For ESM, use `import Pool from 'pg-pool'`.

• Error: parameter "user" must be a string

  cause A required configuration property (like `user`, `host`, `database`, `password`) for the `Pool` constructor is missing or has an incorrect type, often due to faulty URL parsing.
  fix Review your `pg-pool` configuration object. Ensure all mandatory properties are present and their values are of the correct type (e.g., strings for `user`, `password`, `host`, `database`, number for `port`). Double-check your URL parsing logic if using `process.env.DATABASE_URL`.

Warnings

• gotcha The `Pool` constructor does not accept a database URL string directly. Developers must manually parse the URL (e.g., using Node.js's built-in `URL` module) into a configuration object before passing it to the Pool constructor.
  Fix: Use `const { URL } = require('url');` to parse your `process.env.DATABASE_URL` into a configuration object with `user`, `password`, `host`, `port`, and `database` properties.
• gotcha The `maxUses` configuration option causes a client to be disconnected and replaced after it has been used a specified number of times. While useful for preventing resource leaks in long-running applications, it can lead to unexpected connection churn if not understood, potentially impacting performance due to frequent client recreation.
  Fix: Monitor connection metrics and client acquisition times if `maxUses` is enabled. Adjust the value based on your application's workload and desired connection lifetime.
• gotcha `pg-pool` requires `node-postgres` (`pg`) as a peer dependency. If `pg` is not installed or if its version does not meet `pg-pool`'s requirements (e.g., `>=8.0`), `pg-pool` will not function correctly.
  Fix: Ensure `pg` is installed in your project: `npm install pg`. Check your `package.json` for compatible versions of `pg` as specified by `pg-pool`'s peer dependency.
• gotcha Failing to release a client back to the pool after use (e.g., by omitting `client.release()` in a `finally` block) will exhaust the connection pool, causing subsequent `pool.connect()` calls to hang indefinitely or time out. This is a common source of application unresponsiveness.
  Fix: Always ensure `client.release()` is called in a `finally` block when acquiring clients manually with `pool.connect()`. Consider using the `pool.query()` helper method for simple queries, as it automatically acquires and releases the client.
• gotcha While `ssl: true` is commonly used, its default behavior (e.g., `rejectUnauthorized`) can vary. In some environments (like Heroku), `ssl: { rejectUnauthorized: false }` might be necessary, but this reduces security. In production, ensure proper SSL certificate validation.
  Fix: Configure SSL explicitly. Use `ssl: true` for default behavior, and consider `ssl: { rejectUnauthorized: false }` only when absolutely necessary for self-signed certificates in development or tightly controlled environments. For production, ensure your database provides trusted certificates and enable `rejectUnauthorized: true`.

Install

• npm install pg-pool npm
• yarn add pg-pool yarn
• pnpm add pg-pool pnpm

Imports

• Pool
  wrong:

  const { Pool } = require('pg-pool')

  correct:

  const Pool = require('pg-pool')

  pg-pool exports the Pool constructor as its default (module.exports) in CommonJS. Destructuring will result in `undefined`.
• Pool (ESM)
  wrong:

  import { Pool } from 'pg-pool'

  correct:

  import Pool from 'pg-pool'

  When using ESM syntax to import this CommonJS package, the default export (the Pool constructor) is accessed directly, not via named import destructuring.
• Pool (TypeScript Type)
  wrong:

  import type { Pool } from 'pg-pool'

  correct:

  import type Pool from 'pg-pool'

  For TypeScript type imports, follow the same pattern as runtime ESM imports to correctly resolve the default export of the CommonJS module.

Quickstart

This quickstart demonstrates how to initialize `pg-pool` by parsing a database URL, acquire and release clients using `async/await`, and use the convenient `pool.query` helper, along with proper error handling and pool shutdown.

const Pool = require('pg-pool');
const { URL } = require('url'); // Node.js built-in URL parser

// In a real application, ensure process.env.DATABASE_URL is set,
// e.g., DATABASE_URL=postgres://user:password@localhost:5432/mydatabase
const databaseUrl = process.env.DATABASE_URL || 'postgres://testuser:testpass@localhost:5432/testdb';

const params = new URL(databaseUrl);
const auth = params.username && params.password ? params.username.split(':') : [params.username, params.password];

const config = {
  user: auth[0],
  password: auth[1],
  host: params.hostname,
  port: parseInt(params.port || '5432'), // Default PostgreSQL port
  database: params.pathname.split('/')[1],
  ssl: params.protocol === 'https:' || (process.env.NODE_ENV === 'production' && databaseUrl.includes('sslmode=require')), // Use SSL in production or if protocol specifies
  max: 10, // Max clients in the pool
  idleTimeoutMillis: 30000, // Close idle clients after 30 seconds
  connectionTimeoutMillis: 2000, // Return error after 2 seconds if connection not established
};

const pool = new Pool(config);

(async () => {
  let client;
  try {
    // Acquire a client from the pool
    client = await pool.connect();
    console.log('Client acquired from pool.');

    // Execute a query using the client
    const res = await client.query('SELECT $1::text as message', ['Hello from pg-pool with async/await!']);
    console.log('Query result:', res.rows[0].message);

    // Demonstrate direct pool.query (acquires, queries, and releases client automatically)
    const timeRes = await pool.query('SELECT NOW() as current_time');
    console.log('Current database time via direct query:', timeRes.rows[0].current_time);

  } catch (err) {
    console.error('Database operation failed:', err.message, err.stack);
  } finally {
    // Release the client back to the pool to prevent resource leaks
    if (client) {
      client.release();
      console.log('Client released back to pool.');
    }
    // It's good practice to end the pool when the application is shutting down
    // For a quickstart, we end it here, but in a real app it's usually on process exit.
    await pool.end();
    console.log('Pool has been ended.');
  }
})();