Week 8: Connecting from Application Code

1. Connecting with node-postgres

npm install pg

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

const pool = new Pool({
  host: 'localhost',
  database: 'learn_sql',
  user: 'postgres',
  password: process.env.DB_PASSWORD,
});

async function getCustomer(id) {
  const result = await pool.query('SELECT * FROM customers WHERE id = $1', [id]);
  return result.rows[0];
}

result.rows is a plain array of JS objects, one per row, with keys matching the query's column names — this is the same shape whether the query returned one row or ten thousand, which is exactly why the count-checking patterns from earlier weeks (checking result.rows.length) work identically here.

2. Parameterized Queries & SQL Injection

// NEVER do this
const query = "SELECT * FROM customers WHERE email = '" + userInput + "'";
// if userInput is: ' OR '1'='1
// the query becomes: SELECT * FROM customers WHERE email = '' OR '1'='1'
// — which returns every row in the table

const result = await pool.query(
  'SELECT * FROM customers WHERE email = $1',
  [userInput]
);

With $1, the query text and the value are sent to Postgres separately — the database parses the query's structure first, then substitutes the parameter purely as data, never as SQL syntax. A malicious value containing ' OR '1'='1 is treated as a literal string to search for, not as SQL that changes the query's meaning — this is the exact same protection Week 12's equivalent lesson in this site's Go course provides through its own driver's placeholder syntax.

3. Connection Pooling

Opening a new database connection is genuinely expensive — a TCP handshake plus Postgres's own connection setup, repeated on every single query, quickly becomes the actual bottleneck in a busy application.

const pool = new Pool({
  host: 'localhost',
  database: 'learn_sql',
  max: 20,                     // maximum simultaneous connections
  idleTimeoutMillis: 30000,    // close an idle connection after 30s
  connectionTimeoutMillis: 5000, // fail fast if the pool is exhausted
});

pool.query(...) (used throughout this week) automatically checks out a connection from the pool, runs the query, and returns it — the same pattern as Week 12's connection pool concept in this site's Go course, applied here through node-postgres's own pool implementation rather than database/sql's.

4. Where a Query Builder or ORM Fits

Raw SQL, as written all week, is the clearest and most predictable option — every query does exactly, and only, what it says. A query builder (like Knex) or an ORM (like Prisma) trades some of that directness for less repetition on common patterns.

// raw SQL
await pool.query('SELECT * FROM orders WHERE customer_id = $1', [id]);

// a query builder (Knex)
await knex('orders').where('customer_id', id);

// an ORM (Prisma)
await prisma.order.findMany({ where: { customerId: id } });

An ORM earns its cost on straightforward CRUD across many tables, with type generation and less boilerplate. It's worth dropping back to raw SQL — most ORMs support this directly — for anything genuinely complex: a multi-level CTE, a window function, or a query where the generated SQL turns out to perform badly. Everything from Weeks 1–7 stays directly relevant either way; an ORM generates SQL, it doesn't replace the need to understand it.

5. Hands-on Exercise

6. Knowledge Check

Four quick questions. Expand each to check your answer.