PostgreSQL with Node.js — Complete Guide 2026

Introduction

Why This Matters

PostgreSQL is the most capable open-source relational database in 2026 — it handles JSON, full-text search, time-series data, and ACID transactions at scale. The pg (node-postgres) library is the foundation that every Node.js PostgreSQL library (Drizzle, Prisma, Knex) is built on top of. Understanding it directly gives you maximum control, debugging clarity, and the ability to squeeze performance from your database layer.

Installing and Connecting

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

// src/db/pool.ts
import { Pool, PoolConfig } from 'pg';
 
const config: PoolConfig = {
  host: process.env.DB_HOST ?? 'localhost',
  port: parseInt(process.env.DB_PORT ?? '5432', 10),
  database: process.env.DB_NAME ?? 'mydb',
  user: process.env.DB_USER ?? 'postgres',
  password: process.env.DB_PASSWORD,
  // Pool configuration
  max: 20,               // Maximum connections in pool
  min: 2,                // Minimum idle connections
  idleTimeoutMillis: 30_000,  // Close idle connections after 30s
  connectionTimeoutMillis: 2_000, // Timeout waiting for connection
  // SSL for production
  ssl: process.env.NODE_ENV === 'production'
    ? { rejectUnauthorized: true }
    : false,
};
 
export const pool = new Pool(config);
 
// Test connection on startup
pool.on('connect', () => console.log('New DB connection established'));
pool.on('error', (err) => console.error('Unexpected DB error:', err));
 
// Graceful shutdown
process.on('SIGTERM', async () => {
  await pool.end();
  console.log('DB pool closed');
});

Type-Safe Queries

Generic type parameters on pool.query<T> give you typed result rows.

// src/db/users.ts
import { pool } from './pool';
 
interface User {
  id: number;
  email: string;
  name: string;
  created_at: Date;
}
 
// Parameterized query — always use $1, $2, never string concatenation
export async function getUserById(id: number): Promise<User | null> {
  const result = await pool.query<User>(
    'SELECT id, email, name, created_at FROM users WHERE id = $1',
    [id]
  );
  return result.rows[0] ?? null;
}
 
export async function getUsersByEmail(email: string): Promise<User[]> {
  const result = await pool.query<User>(
    'SELECT id, email, name, created_at FROM users WHERE email ILIKE $1 LIMIT 100',
    [`%${email}%`]
  );
  return result.rows;
}
 
export async function createUser(
  email: string,
  name: string,
  hashedPassword: string
): Promise<User> {
  const result = await pool.query<User>(
    `INSERT INTO users (email, name, password_hash, created_at)
     VALUES ($1, $2, $3, NOW())
     RETURNING id, email, name, created_at`,
    [email, name, hashedPassword]
  );
  return result.rows[0];
}

Transactions

Use transactions when multiple writes must succeed or fail together. Always release the client back to the pool in a finally block.

// src/db/transactions.ts
import { pool } from './pool';
 
interface Order {
  id: number;
  userId: number;
  total: number;
}
 
export async function placeOrder(
  userId: number,
  items: Array<{ productId: number; quantity: number; price: number }>
): Promise<Order> {
  const client = await pool.connect();
 
  try {
    await client.query('BEGIN');
 
    // Create order
    const orderResult = await client.query<Order>(
      'INSERT INTO orders (user_id, status, created_at) VALUES ($1, $2, NOW()) RETURNING *',
      [userId, 'pending']
    );
    const order = orderResult.rows[0];
 
    // Insert order items
    for (const item of items) {
      await client.query(
        'INSERT INTO order_items (order_id, product_id, quantity, price) VALUES ($1, $2, $3, $4)',
        [order.id, item.productId, item.quantity, item.price]
      );
 
      // Decrement stock — will throw if stock goes negative (CHECK constraint)
      await client.query(
        'UPDATE products SET stock = stock - $1 WHERE id = $2',
        [item.quantity, item.productId]
      );
    }
 
    // Calculate and update total
    const total = items.reduce((sum, i) => sum + i.price * i.quantity, 0);
    await client.query(
      'UPDATE orders SET total = $1, status = $2 WHERE id = $3',
      [total, 'confirmed', order.id]
    );
 
    await client.query('COMMIT');
    return { ...order, total };
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Bulk Insert with unnest

For bulk inserts, avoid inserting row by row. Use PostgreSQL's unnest() to pass arrays — this is orders of magnitude faster.

export async function bulkInsertUsers(
  users: Array<{ email: string; name: string }>
): Promise<void> {
  if (users.length === 0) return;
 
  const emails = users.map((u) => u.email);
  const names = users.map((u) => u.name);
 
  await pool.query(
    `INSERT INTO users (email, name, created_at)
     SELECT unnest($1::text[]), unnest($2::text[]), NOW()
     ON CONFLICT (email) DO NOTHING`,
    [emails, names]
  );
}

Connection Pooling in Production

The pg pool is process-local. When running multiple Node.js instances (clusters, containers), each process maintains its own pool. This can exhaust PostgreSQL's max_connections limit (default 100).

Use PgBouncer as a connection pooler in front of PostgreSQL to multiplex thousands of application connections into a small number of real database connections.

# PgBouncer Docker example
docker run -d --name pgbouncer \
  -e DATABASE_URL="postgres://user:pass@postgres:5432/mydb" \
  -e POOL_SIZE=20 \
  -p 6432:5432 \
  edoburu/pgbouncer

// Point your app at PgBouncer, not PostgreSQL directly
const pool = new Pool({
  host: 'pgbouncer',
  port: 6432,
  // Use 'transaction' pool mode in PgBouncer for standard Node.js usage
  max: 5, // Fewer connections needed per app instance
});

Migrations

Never mutate your database schema manually in production. Use a migration tool.

// Using node-pg-migrate
// package.json scripts
// "migrate": "node-pg-migrate up"
// "migrate:create": "node-pg-migrate create"
 
// migrations/001_create_users.js
exports.up = (pgm) => {
  pgm.createTable('users', {
    id: { type: 'serial', primaryKey: true },
    email: { type: 'varchar(255)', notNull: true, unique: true },
    name: { type: 'varchar(255)', notNull: true },
    password_hash: { type: 'text', notNull: true },
    created_at: { type: 'timestamptz', notNull: true, default: pgm.func('NOW()') },
  });
  pgm.createIndex('users', 'email');
};
 
exports.down = (pgm) => {
  pgm.dropTable('users');
};

Common Mistakes

Mistake 1 — String interpolation in queries: query(SELECT * FROM users WHERE id = $\{id\}`)` is SQL injection. Always use parameterized queries with `$ 1, $2`.

Mistake 2 — Not releasing client after transaction: if you forget client.release() in the finally block, connections leak and the pool runs dry.

Mistake 3 — Using a single connection for all queries: a Client object is a single connection. Use Pool in production so queries can run concurrently.

Mistake 4 — Ignoring the error event on the pool: unhandled pool errors crash Node.js. Always add pool.on('error', handler).

Best Practices

Use pool.query() for single statements and pool.connect() only when you need explicit transactions.
Set a statement_timeout at the session level to kill runaway queries: SET statement_timeout = '5s'.
Index foreign keys and columns used in WHERE clauses — PostgreSQL does not create foreign key indexes automatically.
Use RETURNING in INSERT and UPDATE statements to avoid a second round-trip to fetch the created/updated row.
Store passwords as bcrypt hashes — never store plaintext or use reversible encryption.

Key Takeaways

The pg Pool manages multiple connections and reuses them across requests — always prefer Pool over a single Client.
Parameterized queries ($1, $2) are the only safe way to include user input in SQL — string interpolation causes SQL injection.
Acquire a dedicated client from the pool for transactions and always release it in a finally block.
Use PostgreSQL unnest() for bulk inserts instead of looping individual INSERT statements.
PgBouncer is essential in multi-instance deployments to prevent exhausting PostgreSQL connection limits.
Always use a migration tool for schema changes — never apply DDL manually in production.
The RETURNING clause eliminates a round-trip by returning inserted or updated rows in the same statement.
Index all foreign key columns and frequently filtered fields — PostgreSQL does not index foreign keys automatically.

Introduction#

Why This Matters#

Installing and Connecting#

Type-Safe Queries#

Transactions#

Bulk Insert with unnest#

Connection Pooling in Production#

Migrations#

Common Mistakes#

Best Practices#

Key Takeaways#

Related reading