Welcome back 👋 In the previous lesson, you introduced domain types and reusable validation helpers, then used them in GET /api/products so query params like query, page, and pageSize are parsed and rejected consistently on bad input. That work sets us up perfectly for this final step: once inputs are validated and well-typed, we can safely plug in the database and return real products.

In this lesson, you’ll wire the full request pipeline end-to-end: route → service → repository → PostgreSQL → response envelope. Along the way, you’ll set up a safe Postgres client with connection pooling, map raw database rows into your Product domain type, implement search + pagination in SQL, and surface database failures as consistent API errors.

Before we write TypeScript, it helps to understand what the database promises to return. The schema for products lives in src/db/schema.sql, and it’s designed to match your domain model closely.

• The column names and shapes line up with src/lib/types/domain.ts (for example price_cents, inventory_count, and status), which is why mapping DB rows to domain objects can stay straightforward.
• Constraints like CHECK (status IN ('active','archived')) are a big deal: they mean the DB will never store an invalid status, which is why we can safely cast status into a narrow TypeScript union later.
• description text NULL maps naturally to string | null in TypeScript. That null handling is one of the most common “gotchas” when mapping SQL data to domain types.

Seed data is provided in src/db/seed.sql so you can query real rows immediately.

Rather than let pg types leak everywhere, this project defines small “just what we need” interfaces in src/lib/db/types.ts. These are intentionally tiny: the rest of the app only needs a query() method, and transactions need a client that can optionally release().

• DbPool mirrors what we rely on from pg.Pool: it can run query() directly and can connect() to get a client for transactions.
• DbClient mirrors what we rely on from a pooled client: it can query() and may have release() when it comes from a pool.
• Keeping these types minimal prevents tight coupling to pg internals and makes the rest of the code easier to read: repositories and services don’t need to know anything about pool configuration.

Next up is src/lib/db/client.ts, which is responsible for constructing the connection string and owning the pool.

The first piece is envDatabaseUrl(). The goal is to support two common setups:

• Hosted/managed environments that provide a single DATABASE_URL.
• Local dev or “sidecar” environments that provide individual PGHOST, PGPORT, etc.

• The function prefers DATABASE_URL when it exists, because that’s the most common pattern in hosted platforms and one-line configuration setups.
• If DATABASE_URL is missing, it falls back to individual env vars. This is what makes local dev and sidecar containers work without requiring any hardcoded URL in your code.
• The defaults (localhost, 5432, postgres, etc.) are intentionally reasonable for a typical local Postgres setup, which reduces “first run” friction.

In Next.js dev mode, files can be re-evaluated frequently because of hot reload. If you create a new pool every time, you can accidentally open lots of connections and eventually hit connection limits.

That’s why getPool() uses a simple singleton approach:

• pool is stored in module scope so it persists across calls within the same runtime.
• The first time getPool() runs, it creates the pool; after that, it returns the existing instance.
• This pattern is especially important during development hot reloads, where repeatedly initializing pools can lead to “too many clients” errors.

The simple query helper uses the pool and returns rows:

• Repositories call query<T>() so they don’t need to care about pool internals at all.
• Returning T[] (not the full DbQueryResult<T>) keeps repository code clean and focused on mapping.

Now that the DB client is ready, we move to the repository layer: src/lib/repositories/productsRepo.ts.

Repositories have two jobs:

1. run SQL queries
2. map raw rows into domain objects that the rest of the backend can trust

The ProductRow type reflects what comes out of Postgres. Notice that currency and status are plain string here—because that’s what SQL returns.

• description is explicitly string | null. This is important because NULL is not the same as undefined, and your domain model also expects null for missing descriptions.
• currency and status are strings at the DB boundary, even though your Product domain type expects narrow unions ('USD' and ).

Still in src/lib/repositories/productsRepo.ts, the key query function is searchProducts({ query, page, pageSize }).

The first step is computing the offset for page-based pagination:

• This is the standard “page number to offset” formula. Page 1 starts at offset 0, page 2 starts at pageSize, and so on.
• Doing this math in the repository keeps SQL construction simple: the query just receives LIMIT and OFFSET.

When a search query is present and non-empty, we add a filter using ILIKE so matching is case-insensitive:

• The %${q}% pattern turns the query into a “contains” match, so query=SKU can match many SKUs and query=hood can match “Classic Hoodie”.
• The SQL uses parameter placeholders ($1, $2, $3) rather than interpolating values into the query string, which protects against SQL injection.

Now we move up one layer to src/lib/services/productsService.ts. Services exist to normalize inputs, apply small business rules, and—crucially—return results in a consistent way so routes can respond uniformly.

This service uses ServiceResult<T> from src/lib/services/types.ts, which standardizes success vs failure:

• The route doesn’t have to “guess” whether something threw an exception or returned a weird error object.
• Instead, it always gets either { ok: true, value } or { ok: false, error }, which is very easy to handle.

The listProducts() service normalizes paging defaults and caps pageSize at 100:

• Even though the route validates page and pageSize, the service still normalizes defaults. This makes the service safe to call from other places in the future without requiring every caller to duplicate the same logic.

Finally, we connect everything in the route: src/app/api/products/route.ts.

This file already validates query params (using the helpers from the previous lesson), then calls the service and converts the service result into a response envelope.

Here’s the important “service result handling” portion:

• If the service returns { ok: false }, we translate it directly into an API error envelope with error(...).
• The code/message/details come from the service, and the HTTP status comes from products.error.httpStatus. This is what keeps DB failures (or conflicts, etc.) consistent and correctly status-coded.
• If the service succeeds, we return success(products.value), meaning callers always receive { data: Product[], meta: { timestamp } }.
• This is the full pipeline working together: route owns HTTP + validation, service owns normalization + error mapping, repository owns SQL + mapping, DB client owns pooling.

The Playground page at src/app/playground/page.tsx builds a /api/products?... URL from the inputs and prints whatever JSON comes back.

• This makes it easy to test both filtered and unfiltered queries: clear query to list all products, or type SKU-10 / Hoodie to test search.
• You can test pagination quickly by setting pageSize=2 and flipping page between 1 and 2. Because the repository uses ORDER BY created_at DESC, the ordering stays stable while you paginate.
• If you ever see a DB_ERROR response, the Playground message points you toward schema/seed setup. In this unit, those errors should also be shaped consistently because the service maps Postgres errors into structured service errors, and the route turns those into API error envelopes.

In this final lesson, you connected your previously “stubbed but safe” products endpoint to real PostgreSQL data:

• You confirmed the minimal DbPool and DbClient interfaces in src/lib/db/types.ts cover exactly what the app needs from pg.
• You implemented environment-driven DB URL resolution in src/lib/db/client.ts so local dev and sidecar setups work without hardcoding.
• You ensured getPool() reuses a single pool instance to avoid connection explosions during hot reload.
• You mapped raw database rows into Product domain objects in src/lib/repositories/productsRepo.ts, carefully handling description: string | null and casting currency / status into narrow unions.
• You implemented real search + pagination with ILIKE, LIMIT, and OFFSET, and returned Product[] consistently from both the filtered and unfiltered paths.
• You implemented listProducts() in to normalize defaults, cap pageSize at 100, and return a stable shape—mapping database exceptions into structured errors.