🔌 Using the pg Library

The pg Library

pg — often written node-postgres — is the de facto PostgreSQL driver for Node.js. It has been actively maintained since 2010, is written in pure JavaScript (no native compile step), and underpins a huge slice of production Node apps. When you type npm install pg, this is what you get.

Its job is narrow and honest: send SQL to Postgres over the wire, and turn the response into JavaScript. Every row comes back as an object whose keys are your column names.

This lesson assumes the db.js module and .env setup from the previous lesson. If you skipped it, that's where the pool and credentials come from.

Client vs Pool

pg offers two connection objects, and picking the right one matters.

A single Client (scripts / CLIs)

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

async function oneOffTask() {
  const client = new Client({
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    database: process.env.DB_NAME,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
  });

  try {
    await client.connect();
    const res = await client.query('SELECT NOW()');
    console.log('Server time:', res.rows[0].now);
  } finally {
    await client.end();   // always close a standalone Client
  }
}

A Pool (everything server-side)

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

const pool = new Pool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20,                       // most connections the pool will open
  idleTimeoutMillis: 30000,      // close idle clients after 30s
  connectionTimeoutMillis: 2000, // give up waiting after 2s
});

// pool.query() checks out a client, runs the query, and returns it for you
const res = await pool.query('SELECT NOW()');

Running Queries (CRUD)

Every query goes through the same call: db.query(text, params). The text is your SQL with $1, $2 placeholders; params is the array of values to fill them. The call returns a promise, so we await it.

// ❌ NEVER interpolate input into SQL — this is SQL injection
const q = `SELECT * FROM users WHERE email = '${req.body.email}'`;

// ✅ ALWAYS parameterize — the value is sent separately from the SQL
const result = await db.query(
  'SELECT * FROM users WHERE email = $1',
  [req.body.email]
);

SELECT — reading rows

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

// All rows
async function getAllUsers() {
  const result = await db.query('SELECT * FROM users ORDER BY created_at DESC');
  return result.rows;                 // an array of objects
}

// One row by id — returns undefined if not found
async function getUserById(id) {
  const result = await db.query('SELECT * FROM users WHERE id = $1', [id]);
  return result.rows[0];
}

// A JOIN across two tables
async function getUserWithPosts(userId) {
  const result = await db.query(
    `SELECT u.id, u.username, p.id AS post_id, p.title
       FROM users u
       LEFT JOIN posts p ON p.user_id = u.id
      WHERE u.id = $1`,
    [userId]
  );
  return result.rows;
}

INSERT — creating rows

Add RETURNING * so PostgreSQL hands back the row it just created, including the generated id and any defaults — no second query needed.

async function createUser({ username, email, passwordHash }) {
  const result = await db.query(
    `INSERT INTO users (username, email, password_hash, created_at)
     VALUES ($1, $2, $3, NOW())
     RETURNING *`,
    [username, email, passwordHash]
  );
  return result.rows[0];              // the brand-new user, with its id
}

UPDATE — changing rows

async function updateUserEmail(id, email) {
  const result = await db.query(
    `UPDATE users
        SET email = $1, updated_at = NOW()
      WHERE id = $2
      RETURNING *`,
    [email, id]
  );
  if (result.rowCount === 0) throw new Error('User not found');
  return result.rows[0];
}

DELETE — removing rows

async function deleteUser(id) {
  const result = await db.query(
    'DELETE FROM users WHERE id = $1 RETURNING *',
    [id]
  );
  if (result.rowCount === 0) throw new Error('User not found');
  return result.rows[0];              // the row that was deleted
}

The Result Object

Every successful query resolves to a result object. You'll use rows constantly and rowCount often; the rest is occasionally handy.

const result = await db.query('SELECT id, name FROM items LIMIT 3');

console.log(result.command);              // "SELECT"
console.log(result.rowCount);             // 3
console.log(result.rows);                 // [{ id: 1, name: '...' }, ...]
console.log(result.fields.map(f => f.name)); // ['id', 'name']

SELECT
3
[ { id: 1, name: 'First item' }, { id: 2, name: 'Second item' }, ... ]
[ 'id', 'name' ]

Transactions

Some operations must happen all together or not at all. Transferring money debits one account and credits another — if the second step fails, you must undo the first, or money vanishes. A transaction gives you that all-or-nothing guarantee with BEGIN, COMMIT, and ROLLBACK.

The catch: all statements in one transaction must run on the same connection. So instead of pool.query() (which may pick a different client each call), you check out one client, run everything on it, and release it when done.

The pattern below is the one to memorize. Note the try/catch/finally: commit on success, roll back on any error, and always release the client — even when something throws.

const pool = require('./db').pool;  // export the pool for transactions

async function transferFunds(fromId, toId, amount) {
  const client = await pool.connect();   // check out one dedicated client
  try {
    await client.query('BEGIN');

    // Debit the sender, but only if funds are sufficient
    const debit = await client.query(
      `UPDATE accounts SET balance = balance - $1
        WHERE id = $2 AND balance >= $1
        RETURNING *`,
      [amount, fromId]
    );
    if (debit.rowCount === 0) {
      throw new Error('Insufficient funds or account not found');
    }

    // Credit the recipient
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );

    await client.query('COMMIT');          // everything worked → make it permanent
    return { success: true };
  } catch (err) {
    await client.query('ROLLBACK');        // any failure → undo it all
    throw err;
  } finally {
    client.release();                      // ALWAYS return the client to the pool
  }
}

PostgreSQL & JS Types

pg converts PostgreSQL types into JavaScript for you, mostly intuitively — with a few gotchas worth memorizing.

JSON and arrays, both directions

// Storing: a JS object goes straight into a JSONB column, an array into TEXT[]
await db.query(
  'INSERT INTO posts (title, tags, meta) VALUES ($1, $2, $3)',
  ['Hello', ['intro', 'news'], { views: 0, pinned: true }]
);

// Reading: JSONB comes back as a live object, arrays as arrays
const { rows } = await db.query('SELECT tags, meta FROM posts WHERE id = $1', [1]);
console.log(rows[0].tags[0]);       // 'intro'   (real array)
console.log(rows[0].meta.pinned);   // true      (already an object)

// Query inside JSON with the ->> operator (parameterize the value!)
const dark = await db.query(
  `SELECT * FROM users WHERE preferences->>'theme' = $1`,
  ['dark']
);

Error Handling

When a query fails, pg throws an error carrying a PostgreSQL SQLSTATE code on err.code. Branching on that code lets you turn a raw database error into a friendly message.

async function createUser(userData) {
  try {
    const result = await db.query(
      `INSERT INTO users (username, email, password_hash)
       VALUES ($1, $2, $3) RETURNING *`,
      [userData.username, userData.email, userData.passwordHash]
    );
    return result.rows[0];
  } catch (err) {
    if (err.code === '23505') {                 // unique violation
      if (err.constraint === 'users_email_key') {
        throw new Error('That email is already registered');
      }
      throw new Error('That value is already taken');
    }
    console.error('DB error:', { code: err.code, detail: err.detail });
    throw new Error('Database error occurred');   // don't leak internals to clients
  }
}

Practice & Quiz

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

async function createTask(title) {
  const result = await db.query(
    'INSERT INTO tasks (title) VALUES ($1) RETURNING *',
    [title]
  );
  return result.rows[0];
}

async function getTask(id) {
  const result = await db.query('SELECT * FROM tasks WHERE id = $1', [id]);
  return result.rows[0];   // undefined if not found
}

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

async function archiveAndLog(taskId) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    const upd = await client.query(
      'UPDATE tasks SET done = TRUE WHERE id = $1 RETURNING *',
      [taskId]
    );
    if (upd.rowCount === 0) throw new Error('Task not found');

    await client.query(
      'INSERT INTO audit_log (task_id, action) VALUES ($1, $2)',
      [taskId, 'archived']
    );

    await client.query('COMMIT');
    return upd.rows[0];
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Best Practices & Pitfalls

✅ Do

• Parameterize every external value with $1, $2 — no exceptions
• Use async/await with try/catch around database calls
• Return created/updated rows with RETURNING * to avoid extra queries
• For transactions, check out a client and release it in finally
• Check rowCount to distinguish "updated" from "nothing matched"

❌ Don't

• Concatenate user input into SQL strings (SQL injection)
• Forget client.release() — it leaks connections and stalls the pool
• Assume COUNT or BIGINT is a number — it's a string
• Send raw PostgreSQL error text to the client
• Use pool.query() for the middle of a transaction

Summary

📚 Additional Resources

• node-postgres — Queries & parameterization
• node-postgres — Transactions
• PostgreSQL — Error codes appendix (SQLSTATE)

🚀 What's Next?

You've used pool.query() and checked out clients — but why is a pool so much faster than opening connections yourself? Next up, Connection Pooling opens the hood: how checkout/return works, how to size the pool, and how to monitor it under load.