SQL Databases¶

Raw SQL queries still run half the internet ORMs are great until you need to debug a complex JOIN that generates 400 lines of garbage SQL - learn the raw drivers first , then abstract when you understand what's happening under the hood

node-postgres: Client vs Pool¶

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

// Client - single connection , good for scripts
const client = new Client({ connectionString: process.env.DATABASE_URL })
await client.connect()
const { rows } = await client.query('SELECT NOW()')
await client.end()

// Pool - connection pool for web servers
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 20 })
const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [id])

Use Pool in web servers , Client in migration scripts and CLI tools A web server with Client handles one request at a time - congratulations , you built a single-threaded database

Parameterized Queries - The Only Way¶

String interpolation in SQL queries is how databases get owned Parameterized queries prevent SQL injection by separating query structure from data

// DANGER - SQL injection vulnerable
const query = `SELECT * FROM users WHERE email = '${email}'`
// If email = "'; DROP TABLE users; --" - your users table is gone

// SAFE - parameterized query
const query = 'SELECT * FROM users WHERE email = $1'
const { rows } = await pool.query(query, [email])

// Multiple parameters
const { rows } = await pool.query(
  'SELECT * FROM users WHERE email = $1 AND status = $2',
  [email, 'active']
)

PostgreSQL uses $1, $2 syntax - MySQL uses ?

// PostgreSQL
await pool.query('SELECT * FROM users WHERE id = $1 AND name = $2', [id, name])

// MySQL (mysql2)
await pool.query('SELECT * FROM users WHERE id = ? AND name = ?', [id, name])

Mix them up and your parameter values end up in the wrong columns - silently

Transactions: BEGIN / COMMIT / ROLLBACK¶

const client = await pool.connect()
try {
  await client.query('BEGIN')

  const { rows } = await client.query(
    'UPDATE accounts SET balance = balance - $1 WHERE id = $2 RETURNING balance',
    [amount, fromAccount]
  )

  if (rows[0].balance < 0) {
    throw new Error('insufficient funds')
  }

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

  await client.query('COMMIT')
} catch (err) {
  await client.query('ROLLBACK')
  throw err
} finally {
  client.release()
}

Always release the client in finally - one un-released client and your pool slowly fills with zombie connections that never die

Transaction with Savepoints¶

await client.query('BEGIN')
try {
  await client.query('UPDATE inventory SET quantity = quantity - 1 WHERE id = $1', [productId])

  // Savepoint before risky operation
  await client.query('SAVEPOINT before_payment')

  try {
    await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, userId])
  } catch (err) {
    // Roll back to savepoint , not entire transaction
    await client.query('ROLLBACK TO SAVEPOINT before_payment')
    // Restock inventory
    await client.query('UPDATE inventory SET quantity = quantity + 1 WHERE id = $1', [productId])
  }

  await client.query('COMMIT')
} catch (err) {
  await client.query('ROLLBACK')
} finally {
  client.release()
}

Savepoints let you partially roll back a transaction without losing all the work - think nested error recovery

mysql2 Similar Patterns¶

const mysql = require('mysql2/promise')
const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 10
})

// Parameterized query
const [rows] = await pool.query('SELECT * FROM users WHERE email = ?', [email])

// Transaction
const conn = await pool.getConnection()
await conn.beginTransaction()
try {
  await conn.query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [amount, fromId])
  await conn.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [amount, toId])
  await conn.commit()
} catch (err) {
  await conn.rollback()
  throw err
} finally {
  conn.release()
}

mysql2 uses ? for all parameters - simple but positional order matters Get parameter order wrong and it's a silent data corruption bug , not a crash

Connection Pool Tuning¶

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  min: 2,
  idleTimeoutMillis: 30000,
  acquireTimeoutMillis: 10000,
  createTimeoutMillis: 5000
})

// Monitor pool usage
setInterval(() => {
  console.log({
    totalCount: pool.totalCount,
    idleCount: pool.idleCount,
    waitingCount: pool.waitingCount
  })
}, 10000)

max should match your DB's max_connections divided by the number of app instances
If you have 3 Node instances and DB allows 100 connections , set max to ~30 per instance
waitingCount climbing steadily means you need more connections or faster queries

// Kill queries that take too long
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  statement_timeout: 10000  // 10 seconds max
})

// Or per-query timeout
await pool.query({
  text: 'SELECT * FROM heavy_report($1)',
  values: [dateRange],
  statement_timeout: 30000
})

Long-running queries hold connections hostage Set statement_timeout and save your pool from starvation

Least Privilege DB Users¶

-- BAD: app user has full access
CREATE USER app_user WITH PASSWORD 'password123';
GRANT ALL PRIVILEGES ON DATABASE mydb TO app_user;

-- GOOD: scoped permissions
CREATE USER app_user WITH PASSWORD 'secure_pass_123';

-- Read/write on specific tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Only SELECT for reporting
CREATE USER report_user WITH PASSWORD 'report_pass_123';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_user;

-- Revoke schema create (prevent table creation)
REVOKE CREATE ON SCHEMA public FROM app_user;

Your app user should never have DDL permissions If an attacker gets SQL injection with a user that can CREATE TABLE , they can persist data in your database as a backdoor

Prerequisites¶

db_01_connecting.md - connections before queries

next -> db_03_nosql.md