Connecting from Node.js with the pg Library

12 min read

Connecting from Node.js with the pg Library

The pg package (node-postgres) is the standard Node.js driver for PostgreSQL. It is mature, well-tested, and used under the hood by ORMs like Prisma and Drizzle. At CPCODELAB we use pg directly for scripts and lean APIs, and Drizzle ORM for full applications.

bash

npm install pg
npm install --save-dev @types/pg

Using a Connection Pool

Always use a connection pool in application code. Creating a new TCP connection to Postgres on every request is expensive. The pool maintains a set of reusable connections and hands them out as needed.

// lib/db.js
import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,               // maximum pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

export default pool;

Running Queries

import pool from './lib/db.js';

// Parameterised query — ALWAYS use $1, $2 placeholders, never string interpolation
async function getUserByEmail(email) {
  const { rows } = await pool.query(
    'SELECT id, full_name, email, role FROM users WHERE email = $1',
    [email]
  );
  return rows[0] ?? null;
}

// Insert and return the created row
async function createPost(authorId, title, slug, body) {
  const { rows } = await pool.query(
    `INSERT INTO posts (author_id, title, slug, body)
     VALUES ($1, $2, $3, $4)
     RETURNING id, slug, created_at`,
    [authorId, title, slug, body]
  );
  return rows[0];
}

Manual Transactions with the pg Client

async function transferBalance(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, fromId]
    );
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();  // always release back to the pool
  }
}

Never interpolate user input into a SQL string with template literals. Always use parameterised queries ($1, $2, ...). SQL injection is one of the most dangerous and common web vulnerabilities.

Ready to test yourself?

Practice PostgreSQL— quiz & coding exercises