PostgreSQL Node.js: Connect & Query

Create a free account to save your progress

Earn XP, track streaks, and sync your dashboard across devices.

Lesson

You've got PostgreSQL installed. Now let's connect it to your Node.js application. The pg package (also called node-postgres) is the standard driver that virtually every Node.js application uses to talk to PostgreSQL.

Setting up the connection

First, install the package:

npm install pg

Now create a database connection. You have two choices: Client for single connections, or Pool for connection pooling. For web applications, you almost always want a pool.

Connection pooling

Think of a connection poolWhat is connection pool?A set of pre-opened database connections that your app reuses instead of opening and closing a new one for every request. like a fleet of taxis. Instead of hailing a new taxi every time you need a ride (expensive and slow), you grab one from the pool that's already waiting. When you're done, you return it so someone else can use it.

import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: process.env.NODE_ENV === 'production' 
    ? { rejectUnauthorized: false } 
    : false,
  max: 20,                    // Maximum connections in pool
  idleTimeoutMillis: 30000,   // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Timeout when getting connection
});

// Log connection events
pool.on('connect', () => {
  console.log('Connected to PostgreSQL');
});

pool.on('error', (err) => {
  console.error('PostgreSQL pool error:', err);
});

Why pooling matters

Creating database connections is expensive, it involves network overhead and authentication. A pool keeps connections open and reuses them, making your app much faster and more scalable.

Basic queries

Once you have a pool, running queries is straightforward:

// Simple query
const result = await pool.query('SELECT NOW()');
console.log(result.rows[0].now);  // Current timestamp

// With parameters (ALWAYS use this for user input!)
const userId = 1;
const result = await pool.query(
  'SELECT * FROM users WHERE id = CODE_BLOCK',
  [userId]
);
const user = result.rows[0];

// Multiple parameters
await pool.query(
  'INSERT INTO users (name, email, age) VALUES (CODE_BLOCK, $2, $3)',
  ['Alice', 'alice@example.com', 25]
);

// Pagination with LIMIT and OFFSET
const page = 1;
const perPage = 20;
const { rows } = await pool.query(
  'SELECT * FROM users ORDER BY created_at DESC LIMIT CODE_BLOCK OFFSET $2',
  [perPage, (page - 1) * perPage]
);

Notice the INLINE_CODE, $2 syntax? These are parameter placeholders. Never concatenate user input into SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. strings, that's how SQL injectionWhat is sql injection?An attack where user input is inserted directly into a database query, letting the attacker read, modify, or delete data. Parameterized queries prevent it. attacks happen.

Getting data back from INSERT

PostgreSQL has a killer feature: the RETURNING clause. It lets you get data back from INSERT, UPDATE, and DELETE statements:

// Insert and get the new record back
const result = await pool.query(
  'INSERT INTO users (name, email) VALUES (CODE_BLOCK, $2) RETURNING *',
  ['Bob', 'bob@example.com']
);

const newUser = result.rows[0];
console.log(newUser.id);        // Auto-generated ID
console.log(newUser.created_at); // Auto-generated timestamp

One query instead of two. Your database will thank you.

The repositoryWhat is repository?A project folder tracked by Git that stores your files along with the complete history of every change, inside a hidden .git directory. pattern

As your app grows, scattering SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. queries everywhere becomes a mess. The repository pattern centralizes all database logic in one place:

class UserRepository {
  constructor(pool) {
    this.pool = pool;
  }
  
  async findById(id) {
    const { rows } = await this.pool.query(
      'SELECT * FROM users WHERE id = CODE_BLOCK',
      [id]
    );
    return rows[0] || null;
  }
  
  async findByEmail(email) {
    const { rows } = await this.pool.query(
      'SELECT * FROM users WHERE email = CODE_BLOCK',
      [email]
    );
    return rows[0] || null;
  }
  
  async create(userData) {
    const { name, email, password } = userData;
    const { rows } = await this.pool.query(
      `INSERT INTO users (name, email, password) 
       VALUES (CODE_BLOCK, $2, $3) 
       RETURNING id, name, email, created_at`,
      [name, email, password]
    );
    return rows[0];
  }
  
  async update(id, updates) {
    const { name, email } = updates;
    const { rows } = await this.pool.query(
      `UPDATE users 
       SET name = CODE_BLOCK, email = $2, updated_at = NOW() 
       WHERE id = $3 
       RETURNING *`,
      [name, email, id]
    );
    return rows[0] || null;
  }
  
  async delete(id) {
    const result = await this.pool.query(
      'DELETE FROM users WHERE id = CODE_BLOCK',
      [id]
    );
    return result.rowCount > 0;  // True if something was deleted
  }
}

// Usage
const userRepo = new UserRepository(pool);
const user = await userRepo.findById(1);

This pattern makes testing easier, allows you to swap databases later, and keeps your business logic clean.

Transactions

Sometimes you need multiple operations to succeed or fail together. Think of transferring money between bank accounts, you can't deduct from one without adding to the other.

async function transferMoney(fromId, toId, amount) {
  const client = await pool.connect();
  
  try {
    // Start transaction
    await client.query('BEGIN');
    
    // Deduct from sender
    await client.query(
      'UPDATE accounts SET balance = balance - CODE_BLOCK WHERE id = $2',
      [amount, fromId]
    );
    
    // Add to receiver
    await client.query(
      'UPDATE accounts SET balance = balance + CODE_BLOCK WHERE id = $2',
      [amount, toId]
    );
    
    // Record the transaction
    await client.query(
      'INSERT INTO transactions (from_id, to_id, amount) VALUES (CODE_BLOCK, $2, $3)',
      [fromId, toId, amount]
    );
    
    // Commit if all succeeded
    await client.query('COMMIT');
    
  } catch (error) {
    // Rollback on any error
    await client.query('ROLLBACK');
    throw error;
  } finally {
    // Always release the client!
    client.release();
  }
}

Critical

When you manually connect from the pool (with pool.connect()), you MUST release the client in a finally block. Otherwise, you leak connections and eventually exhaust the pool.

Error handling

Database operations can fail. Handle them gracefully:

try {
  const result = await pool.query(
    'INSERT INTO users (email) VALUES (CODE_BLOCK)',
    [email]
  );
} catch (error) {
  if (error.code === '23505') {
    // Unique violation (duplicate email)
    throw new Error('Email already registered');
  }
  if (error.code === '23503') {
    // Foreign key violation
    throw new Error('Referenced record does not exist');
  }
  // Re-throw unknown errors
  throw error;
}

Common PostgreSQL error codes:

23505: Unique constraint violation
23503: Foreign keyWhat is foreign key?A column in one database table that references a row in another table, creating a link between the two. violation
23502: Not null violation
42P01: Table doesn't exist
28000: AuthenticationWhat is authentication?Verifying who a user is, typically through credentials like a password or token. failed

Quick reference: pg package patterns

Pattern	Code	When to use
Simple query	`pool.query(sql, params)`	Most queries
Transaction	`pool.connect()` + `BEGIN`/`COMMIT`	Multiple related operations
RETURNING	`INSERT ... RETURNING *`	Get data from INSERT/UPDATE
Pagination	`LIMIT INLINE_CODE OFFSET $2`	Large result sets
Error codes	`error.code === '23505'`	Handle specific errors

Connecting Node.js to PostgreSQL unlocks the full power of a professional database. With connection pooling, parameterized queries, and transactions, you're ready to build production-grade applications.

Done

Complete & Next