node-postgres

Common errors

• Error: connect ECONNREFUSED <host>:<port>

  cause The PostgreSQL server is not running or is inaccessible from the application's host and port.
  fix Ensure your PostgreSQL server is running and configured to accept connections from your application's host. Check firewall rules and the database's `pg_hba.conf`.

• Error: password authentication failed for user "<user>"

  cause The provided username or password for the database connection is incorrect.
  fix Verify the `user` and `password` in your connection string or configuration object match the credentials configured on the PostgreSQL server.

• Error: database "<database_name>" does not exist

  cause The specified database name does not exist on the PostgreSQL server.
  fix Ensure the `database` name in your connection string or configuration object is correct and the database has been created on the PostgreSQL server.

• Error: Client was released and cannot be used to send queries anymore

  cause An attempt was made to use a `pg` client instance after it had already been released back to the connection pool.
  fix Ensure you do not attempt to use a `pg` client after calling `client.release()`. If you need to perform more queries, obtain a new client from the pool.

• error: column "<column_name>" does not exist

  cause A column referenced in your SQL query is not present in the specified table.
  fix Carefully check the spelling and existence of the column name in your SQL query against your actual database schema.

Warnings

• gotcha Always release client connections obtained from a pool to prevent resource exhaustion and application hangs.
  Fix: Call `client.release()` when a client is no longer needed. For robust error handling, use a `try...finally` block to ensure release, or leverage `pool.connect().then(client => { ... }).finally(() => client.release())`.
• gotcha Concatenating user input directly into SQL queries can lead to severe SQL injection vulnerabilities.
  Fix: Always use parameterized queries (e.g., `client.query('SELECT * FROM users WHERE id = $1', [userId])`) to safely pass user-provided values to the database.
• gotcha PostgreSQL `timestamp` and `timestamptz` types are returned as JavaScript `Date` objects, which can lead to timezone issues if not handled carefully, as JS `Date` objects are sensitive to the local timezone.
  Fix: For consistent time handling, consider configuring `pg.types.setTypeParser` to parse timestamps as strings or UTC Date objects, or ensure your application logic explicitly handles timezone conversions (e.g., storing and displaying all times in UTC).
• gotcha The `pg-native` package is an optional peer dependency. If it's not installed, `pg` will silently fall back to its pure JavaScript implementation, which may have different performance characteristics for some operations.
  Fix: If you intend to use the native bindings for potential performance benefits, explicitly install it: `npm install pg-native`.

Install

• npm install pg npm
• yarn add pg yarn
• pnpm add pg pnpm

Imports

• Pool

  import { Pool } from 'pg';

Quickstart

Connects to a PostgreSQL database using a connection pool, executes a simple `SELECT NOW()` query, logs the result, and properly releases the client and closes the pool.

import { Pool } from 'pg';

async function runQuery() {
  // Ensure your POSTGRES_URL environment variable is set,
  // e.g., 'postgresql://user:password@host:5432/database'
  const pool = new Pool({
    connectionString: process.env.POSTGRES_URL ?? 'postgresql://user:password@localhost:5432/testdb',
  });

  try {
    const client = await pool.connect();
    const res = await client.query('SELECT NOW() AS current_time');
    console.log('Current time from PostgreSQL:', res.rows[0].current_time);
    client.release(); // Release the client back to the pool
  } catch (err) {
    console.error('Database query failed', err);
  } finally {
    await pool.end(); // Close the pool when done with all operations
  }
}

runQuery();