🐘 PostgreSQL Lesson 06 — Node.js + pg

1. Setup node-postgres (pg)

npm install pg dotenv

# .env
PGHOST=localhost
PGPORT=5432
PGUSER=postgres
PGPASSWORD=yourpassword
PGDATABASE=myapp

# OR single connection string:
DATABASE_URL=postgresql://postgres:password@localhost:5432/myapp

// db.js — connection pool
const { Pool } = require('pg');
require('dotenv').config();

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  // OR:
  host:     process.env.PGHOST,
  port:     process.env.PGPORT,
  user:     process.env.PGUSER,
  password: process.env.PGPASSWORD,
  database: process.env.PGDATABASE,
  max: 10,              // max pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
  ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: false } : false
});

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

module.exports = pool;

2. Queries

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

// Simple query
async function getAllUsers() {
  const { rows } = await pool.query('SELECT * FROM users WHERE active = true');
  return rows;
}

// Parameterized query (SQL injection safe)
async function getUserById(id) {
  const { rows } = await pool.query(
    'SELECT id, name, email, role FROM users WHERE id = $1',
    [id]   // $1 = first parameter (PG uses $1, $2 not ?)
  );
  return rows[0] || null;
}

// Multiple params
async function createUser(name, email, hashedPassword) {
  const { rows } = await pool.query(
    `INSERT INTO users (name, email, password)
     VALUES ($1, $2, $3)
     RETURNING id, name, email, created_at`,
    [name, email, hashedPassword]
  );
  return rows[0];
}

// rowCount
async function deleteUser(id) {
  const result = await pool.query('DELETE FROM users WHERE id = $1', [id]);
  return result.rowCount > 0;  // true if deleted
}

// Named query with complex SQL
async function getUserWithPosts(userId) {
  const { rows } = await pool.query(`
    SELECT
      u.id, u.name, u.email,
      COUNT(p.id) AS post_count,
      JSON_AGG(JSON_BUILD_OBJECT('id', p.id, 'title', p.title) ORDER BY p.created_at DESC) AS posts
    FROM users u
    LEFT JOIN posts p ON p.user_id = u.id
    WHERE u.id = $1
    GROUP BY u.id, u.name, u.email
  `, [userId]);
  return rows[0];
}

3. Transactions in Node.js

// Get client from pool for transactions
async function transferFunds(fromId, toId, amount) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, fromId]
    );

    // Check sufficient funds
    const { rows } = await client.query(
      'SELECT balance FROM accounts WHERE id = $1',
      [fromId]
    );
    if (rows[0].balance < 0) {
      throw new Error('Insufficient funds');
    }

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

    await client.query('COMMIT');
    return { success: true };

  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();  // always release back to pool!
  }
}

4. Full Express + PostgreSQL Example

// routes/users.js
const express = require('express');
const pool    = require('../db');
const router  = express.Router();

router.get('/', async (req, res, next) => {
  try {
    const { page = 1, limit = 10, search } = req.query;
    const offset = (page - 1) * limit;

    let query = 'SELECT id, name, email, role, created_at FROM users';
    const params = [];

    if (search) {
      query += ' WHERE name ILIKE $1 OR email ILIKE $1';
      params.push(`%${search}%`);
    }

    query += ` ORDER BY created_at DESC LIMIT $${params.length+1} OFFSET $${params.length+2}`;
    params.push(limit, offset);

    const { rows } = await pool.query(query, params);
    const { rows: [{ count }] } = await pool.query('SELECT COUNT(*) FROM users');

    res.json({
      data: rows,
      total: Number(count),
      page: Number(page),
      totalPages: Math.ceil(count / limit)
    });
  } catch (err) {
    next(err);
  }
});

module.exports = router;