PostgreSQL with Express

Connecting With pg

The pg package is the standard PostgreSQL driver for Node.js. Like mysql2, it exposes a Pool for managing multiple reusable connections, and a Promise-based query API that works naturally with async/await.

Parameterized queries in pg use numbered placeholders ($1, $2, ...) rather than the bare ? used by MySQL drivers, but serve exactly the same safety purpose.

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

const pool = new Pool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  max: 10,
});

module.exports = pool;

max: 10 caps the pool at ten simultaneous connections, adjust based on your database's connection limits.

Parameterized Queries in pg

const { rows } = await pool.query(
  'SELECT * FROM users WHERE id = $1',
  [userId]
);

await pool.query(
  'INSERT INTO users (email, name) VALUES ($1, $2)',
  [email, name]
);

• $1, $2, ... are positional placeholders matched to the values array in order.
• pool.query() returns an object with a rows array (and other metadata like rowCount).
• Never interpolate request data directly into a SQL string.
• RETURNING * in an INSERT/UPDATE statement returns the affected row(s) without a second query.

PostgreSQL (pg) Cheatsheet

Common query patterns using the pg package.

Using RETURNING for Fewer Round Trips

PostgreSQL's RETURNING clause lets an INSERT or UPDATE statement hand back the affected row directly, avoiding a separate SELECT to fetch what you just wrote.

router.post('/', async (req, res, next) => {
  try {
    const { email, name } = req.body;
    const { rows } = await pool.query(
      'INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *',
      [email, name]
    );
    res.status(201).json(rows[0]);
  } catch (err) {
    next(err);
  }
});

Transactions With a Checked-Out Client

As with MySQL, multi-step writes that must succeed or fail together should run inside a transaction on a single client checked out from the pool.

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();
}

Common Mistakes

• Mixing up $1/$2 placeholder order with the values array, sending the wrong value to the wrong column.
• Forgetting client.release() after a manually checked-out client, exhausting the pool over time.
• Not using RETURNING and instead running an unnecessary follow-up SELECT after every insert.
• Skipping transactions for multi-table writes that logically belong together.

Key Takeaways

• The pg package is the standard Node.js driver for PostgreSQL, with a Promise-based pooled API.
• Use $1, $2, ... parameterized placeholders, never string-built SQL.
• RETURNING lets inserts/updates hand back the affected row in the same query.
• Wrap related multi-step writes in an explicit transaction.