Wiring PostgreSQL Products

Welcome back 👋 Up to now, your /api/products endpoint has been about reliable API behavior: validating query parameters and returning consistent success(...) / error(...) envelopes. In Unit 2, you also introduced domain models and DTOs, so your backend has a clear “source of truth” for product data and a clear boundary around what clients are allowed to send.

In this lesson, we finally connect products to PostgreSQL. You’ll walk through the entire pipeline—route → service → repository → database—and, just as importantly, you’ll learn the core database concepts behind the code: what a pool is, why we use a lazy singleton, how queries run without leaking connections, and how raw Postgres errors become stable API failures.

When the browser calls GET /api/products, your backend doesn’t jump straight into SQL. It moves through layers that keep responsibilities clear:

• Route (HTTP layer): validates query params and turns results into success(...) or error(...).
• Service (workflow layer): applies defaults and converts exceptions into structured results (ok(...) / fail(...)).
• Repository (data access layer): runs SQL and maps database rows into domain Product objects.
• DB client (infrastructure layer): manages connection pooling and provides query/transaction helpers.

This structure pays off immediately: your route stays readable, and the database code stays centralized and reusable.

Before you can query Postgres, your app needs to know where the database is and how to authenticate.

That information comes from environment variables (values provided to the running process, not hardcoded in your code). They’re accessed in Node.js through process.env.

The logic for building the connection string is in src/lib/db/client.ts.

// src/lib/db/client.ts

function envDatabaseUrl(): string {
  const url = process.env.DATABASE_URL;
  if (url && url.length > 0) return url;
  const host = process.env.PGHOST ?? "localhost";
  const port = process.env.PGPORT ?? "5432";
  const user = process.env.PGUSER ?? "postgres";
  const pass = process.env.PGPASSWORD ?? "postgres";
  const db = process.env.PGDATABASE ?? "postgres";
  return `postgres://${user}:${pass}@${host}:${port}/${db}`;
}

• process.env.DATABASE_URL is the “all-in-one” connection string. Many platforms (and container setups) provide this single variable because it’s easy to configure and copy around.

• If DATABASE_URL is not set, the code falls back to individual variables:

  • PGHOST: the hostname where Postgres runs (e.g. localhost, db, 127.0.0.1, or a remote host).
  • PGPORT: the port Postgres listens on (default Postgres port is 5432).
  • PGUSER / PGPASSWORD: the credentials used to log in.
  • PGDATABASE: which database to connect to.

• Each fallback has a default (like "localhost" or "postgres") so the project works in common development setups without extra configuration.

Why default to localhost?

• In many dev environments, Postgres runs on the same machine/container as the app (or is exposed locally). In that case, localhost is the simplest default.
• When Postgres runs elsewhere (like in a separate Docker service or a hosted database), you override PGHOST (or provide DATABASE_URL) without changing any code.
• These defaults are a convenience, not a requirement—real deployments almost always set explicit environment variables.

Why do we build a string like postgres://...?

This line produces a connection string:

return `postgres://${user}:${pass}@${host}:${port}/${db}`;

• It’s a standardized URL format Postgres clients understand.
• It packs all connection details into one string, which the pg library can consume directly.
• Example shape (conceptually): postgres://username:password@hostname:port/databaseName

This lets pg connect without you manually wiring each setting into separate config fields.