PostgreSQL with Node.js — A Complete Guide

MongoDB was the default database for Node.js for years, but PostgreSQL has become the dominant choice for production applications in 2026 — and for good reason: ACID compliance, advanced indexing, native JSON support (when you need flexibility), and the strongest query optimizer of any open-source database. This article covers connecting Node.js to PostgreSQL, connection pooling configurations, parameterized queries, transactions, and the production patterns that keep your database fast under load.

Setting Up PostgreSQL with Node.js: The Right Way

Connecting Node.js to PostgreSQL is straightforward, but production setups require more than a basic connection. Start by installing the pg package: npm install pg. Avoid using the pg-native variant unless you have specific performance needs — it adds complexity with native compilation. Use environment variables for credentials, never hardcode them. For connection pooling, create a pool instance that reuses connections efficiently. This reduces overhead from establishing new connections per request. Always handle pool errors globally to avoid silent failures. A common mistake is forgetting to close the pool on application shutdown, leading to hanging connections. Use process.on('SIGINT', ...) to gracefully close the pool.

const { Pool } = require('pg');

const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: parseInt(process.env.DB_PORT, 10) || 5432,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

pool.on('error', (err) => {
  console.error('Unexpected error on idle client', err);
  process.exit(-1);
});

module.exports = pool;

Schema Design and Migrations: Version Control Your Database

Never create tables manually in production. Use migration tools like node-pg-migrate or knex to version control schema changes. Write migrations as idempotent scripts that can be run multiple times safely. For example, use CREATE TABLE IF NOT EXISTS and ALTER TABLE ... IF EXISTS. Always include rollback scripts for each migration. Store migration files in a migrations directory and run them as part of your deployment pipeline. A common pitfall is running migrations during peak traffic — schedule them during maintenance windows. Use transactions in migrations to ensure atomicity: if one step fails, the entire migration rolls back.

exports.up = async (db) => {
  await db.query(`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      email VARCHAR(255) UNIQUE NOT NULL,
      name VARCHAR(100) NOT NULL,
      created_at TIMESTAMPTZ DEFAULT NOW()
    );
  `);
};

exports.down = async (db) => {
  await db.query(`DROP TABLE IF EXISTS users;`);
};

CRUD Operations: Parameterized Queries Are Non-Negotiable

When performing CRUD operations, always use parameterized queries to prevent SQL injection. Never concatenate user input into SQL strings. The pg library supports $1, $2 placeholders. For inserts, use RETURNING * to get the inserted row. For updates, use WHERE clauses with parameters. Use ON CONFLICT for upserts. For bulk inserts, use pg-promise or batch inserts with unnest for performance. Avoid ORMs for complex queries — raw SQL gives you full control. However, for simple CRUD, an ORM like TypeORM can reduce boilerplate, but be aware of the N+1 problem.

const pool = require('./db');

async function createUser(email, name) {
  const result = await pool.query(
    `INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *`,
    [email, name]
  );
  return result.rows[0];
}

async function getUserById(id) {
  const result = await pool.query(
    `SELECT * FROM users WHERE id = $1`,
    [id]
  );
  return result.rows[0] || null;
}

async function updateUser(id, name) {
  const result = await pool.query(
    `UPDATE users SET name = $1 WHERE id = $2 RETURNING *`,
    [name, id]
  );
  return result.rows[0] || null;
}

async function deleteUser(id) {
  await pool.query(`DELETE FROM users WHERE id = $1`, [id]);
}

module.exports = { createUser, getUserById, updateUser, deleteUser };

Transactions: Atomic Operations for Data Integrity

Transactions ensure that a series of database operations either all succeed or all fail. Use BEGIN, COMMIT, and ROLLBACK manually or use the pool.connect() method to get a client and manage the transaction. Always release the client in a finally block. Handle errors by rolling back and re-throwing. For nested transactions, use savepoints. Avoid long-running transactions — they hold locks and can cause deadlocks. Keep transactions as short as possible. In Node.js, use async/await with try-catch for clean transaction management.

const pool = require('./db');

async function transferFunds(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const fromResult = await client.query(
      `UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1 RETURNING *`,
      [amount, fromId]
    );
    if (fromResult.rows.length === 0) {
      throw new Error('Insufficient funds');
    }
    await client.query(
      `UPDATE accounts SET balance = balance + $1 WHERE id = $2`,
      [amount, toId]
    );
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

module.exports = { transferFunds };

Error Handling and Retries: Graceful Degradation

Database operations can fail due to network issues, deadlocks, or constraint violations. Implement retry logic with exponential backoff for transient errors. Use a library like p-retry or write your own. Distinguish between transient errors (e.g., 40P01 deadlock) and permanent errors (e.g., 23505 unique violation). Log errors with context (query, parameters, error code). Use a centralized error handler that returns appropriate HTTP status codes. For connection errors, consider circuit breaker pattern to avoid overwhelming the database.

const { Pool } = require('pg');
const pool = new Pool();

async function queryWithRetry(text, params, retries = 3) {
  for (let attempt = 1; attempt <= retries; attempt++) {
    try {
      return await pool.query(text, params);
    } catch (err) {
      if (attempt === retries) throw err;
      // Retry only on deadlock or connection errors
      if (err.code === '40P01' || err.code === '08000' || err.code === '08006') {
        const delay = Math.pow(2, attempt) * 100;
        await new Promise(resolve => setTimeout(resolve, delay));
      } else {
        throw err;
      }
    }
  }
}

module.exports = { queryWithRetry };

Performance Optimization: Indexing and Query Tuning

Indexes are critical for query performance but come with write overhead. Use EXPLAIN ANALYZE to identify slow queries. Create indexes on columns used in WHERE, JOIN, and ORDER BY. Use composite indexes for multi-column filters. Avoid over-indexing — each index slows down writes. Use partial indexes for conditional queries. For full-text search, use GIN indexes. Monitor slow queries with pg_stat_statements. In Node.js, log queries that exceed a threshold (e.g., 100ms) for analysis.

-- Create index on email for fast lookup
CREATE INDEX idx_users_email ON users (email);

-- Composite index for common query pattern
CREATE INDEX idx_users_name_created ON users (name, created_at);

-- Partial index for active users
CREATE INDEX idx_users_active ON users (id) WHERE active = true;

-- Analyze query performance
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

Connection Pool Tuning: Right-Sizing for Traffic

The default pool size of 10 may not suit your workload. Too few connections cause queuing; too many overwhelm the database. Start with max = 20 and adjust based on concurrent requests and database CPU. Use pgbouncer for connection pooling at the database level, especially with serverless functions. Monitor pool metrics: active, idle, waiting clients. Use pool.totalCount and pool.waitingCount in health checks. Set idleTimeoutMillis to release idle connections after inactivity. For high-traffic apps, consider using pg-pool with custom acquire timeout.

const pool = require('./db');

setInterval(() => {
  console.log({
    total: pool.totalCount,
    idle: pool.idleCount,
    waiting: pool.waitingCount,
  });
}, 5000);

// Health check endpoint
app.get('/health', async (req, res) => {
  try {
    const client = await pool.connect();
    client.release();
    res.json({ status: 'ok', pool: { total: pool.totalCount, idle: pool.idleCount, waiting: pool.waitingCount } });
  } catch (err) {
    res.status(503).json({ status: 'error', message: err.message });
  }
});

Security Best Practices: Defense in Depth

Beyond parameterized queries, secure your database with least privilege. Create separate database users for read-only, read-write, and admin roles. Use pg_hba.conf to restrict IP addresses. Encrypt connections with TLS: set ssl: { rejectUnauthorized: true } in the pool config. Never store secrets in code — use a vault or environment variables. Regularly rotate passwords. Use row-level security for multi-tenant apps. Audit queries with pgaudit. In Node.js, sanitize inputs even if using parameterized queries (e.g., strip HTML).

-- Create read-only user
CREATE USER readonly WITH PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Create read-write user
CREATE USER app_user WITH PASSWORD 'another_strong_password';
GRANT CONNECT, CREATE ON DATABASE mydb TO app_user;
GRANT USAGE, CREATE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Enable TLS in Node.js
const pool = new Pool({
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync('/path/to/server-ca.pem').toString(),
  }
});

Testing Database Interactions: Integration Tests with Real Data

Unit testing database code requires a real database. Use a test database that is created and destroyed per test suite. Use testcontainers to spin up a disposable PostgreSQL instance in Docker. Write tests that cover CRUD operations, transactions, error handling, and edge cases. Use fixtures to seed data. Avoid mocking the database — it leads to false positives. For CI, use a service container with PostgreSQL. Run tests in parallel with separate databases to avoid conflicts.

const { createUser, getUserById } = require('./userRepository');
const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.TEST_DATABASE_URL });

beforeAll(async () => {
  await pool.query(`CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
  )`);
});

afterAll(async () => {
  await pool.query(`DROP TABLE IF EXISTS users`);
  await pool.end();
});

test('createUser inserts a new user', async () => {
  const user = await createUser('test@example.com', 'Test User');
  expect(user).toMatchObject({ email: 'test@example.com', name: 'Test User' });
  expect(user.id).toBeDefined();
});

test('getUserById returns null for non-existent user', async () => {
  const user = await getUserById(999);
  expect(user).toBeNull();
});

Production Monitoring and Alerting: Know Your Database

Monitor PostgreSQL with tools like pg_stat_activity for active queries, pg_stat_statements for query performance, and pg_locks for deadlocks. Set up alerts for long-running queries, connection pool exhaustion, replication lag, and disk space. Use a metrics dashboard (e.g., Grafana) with Prometheus exporters. In Node.js, expose database metrics via a /metrics endpoint for Prometheus. Log slow queries with a custom logger. Have a runbook for common incidents: connection spikes, deadlocks, replication failures.

const prometheus = require('prom-client');
const pool = require('./db');

const dbQueryDuration = new prometheus.Histogram({
  name: 'db_query_duration_seconds',
  help: 'Duration of database queries in seconds',
  labelNames: ['query'],
  buckets: [0.01, 0.05, 0.1, 0.5, 1, 5],
});

// Monkey-patch pool.query to measure duration
const originalQuery = pool.query.bind(pool);
pool.query = async (text, params) => {
  const end = dbQueryDuration.startTimer({ query: text.slice(0, 50) });
  try {
    return await originalQuery(text, params);
  } finally {
    end();
  }
};

// Expose metrics endpoint
app.get('/metrics', async (req, res) => {
  res.set('Content-Type', prometheus.register.contentType);
  res.end(await prometheus.register.metrics());
});

Scaling PostgreSQL: Read Replicas and Connection Management

As traffic grows, offload read queries to read replicas. Configure the Node.js app to use a separate pool for reads and writes. Use a library like pg-pool with multiple endpoints. For write-heavy workloads, consider sharding or using a distributed database. Use pgbouncer in transaction mode to reduce connection overhead. For serverless, use pg with @aws-sdk/client-rds-data or similar. Implement caching with Redis to reduce database load. Always test scaling strategies under load.

const { Pool } = require('pg');

const writePool = new Pool({
  host: process.env.DB_WRITE_HOST,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20,
});

const readPool = new Pool({
  host: process.env.DB_READ_HOST,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 50,
});

async function queryRead(text, params) {
  return readPool.query(text, params);
}

async function queryWrite(text, params) {
  return writePool.query(text, params);
}

module.exports = { queryRead, queryWrite };

Backup and Disaster Recovery: Plan for the Worst

Regular backups are non-negotiable. Use pg_dump for logical backups and pg_basebackup for physical backups. Automate backups with cron and store them off-site (e.g., S3). Test restores regularly — a backup is only as good as its restore. Use point-in-time recovery (PITR) with WAL archiving. In Node.js, you can trigger backups via a cron job but avoid doing it from the app server. Have a disaster recovery plan with RTO and RPO. Document the restore process and practice it.

#!/bin/bash
# Logical backup
pg_dump -h localhost -U postgres mydb > /backups/mydb_$(date +%Y%m%d_%H%M%S).sql

# Physical backup with WAL archiving
pg_basebackup -h localhost -U postgres -D /backups/base_$(date +%Y%m%d) -X stream -P

# Upload to S3
aws s3 cp /backups/ s3://my-backup-bucket/ --recursive