Wiring PostgreSQL Products
Wiring PostgreSQL Products
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.
The Postgres Schema You’re Querying
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 exampleprice_cents,inventory_count, andstatus), 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 NULLmaps naturally tostring | nullin TypeScript. Thatnullhandling 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.
- You can test filtering with values like
query=SKU-10orquery=Hoodieand see matches on both SKU and name. - The seed script is idempotent because of
ON CONFLICT (sku) DO NOTHING, which makes local development much less annoying.
Minimal Database Interfaces for pg
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().
DbPoolmirrors what we rely on frompg.Pool: it can runquery()directly and canconnect()to get a client for transactions.DbClientmirrors what we rely on from a pooled client: it canquery()and may haverelease()when it comes from a pool.- Keeping these types minimal prevents tight coupling to
pginternals and makes the rest of the code easier to read: repositories and services don’t need to know anything about pool configuration.
Building a Safe Database URL Without Hardcoding
Creating One Pool and Reusing It
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:
poolis 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 fullDbQueryResult<T>) keeps repository code clean and focused on mapping.
Mapping Database Rows into Product Domain Types
Now that the DB client is ready, we move to the repository layer: src/lib/repositories/productsRepo.ts.
Repositories have two jobs:
- run SQL queries
- 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.
descriptionis explicitlystring | null. This is important becauseNULLis not the same asundefined, and your domain model also expectsnullfor missing descriptions.currencyandstatusare strings at the DB boundary, even though yourProductdomain type expects narrow unions ('USD'and'active' | 'archived').- Keeping a separate row type makes it obvious where “raw DB data” ends and “trusted domain data” begins.
The bridge between the two is mapProductRow():
- The mapping is mostly 1:1 because the schema was designed to match the domain model.
descriptionis passed through as-is sonullstaysnull. Accidentally converting this toundefinedwould violate theProducttype and can also confuse clients expecting consistent nullability.currencyandstatusare cast into the narrower domain unions usingas Product['currency']andas Product['status'].- Those casts are safe in this project because the DB schema enforces valid values (
currency DEFAULT 'USD'andCHECK (status IN ('active','archived'))). In other words, the DB guarantees the invariants the domain type requires.
Searching Products with Filtering and Pagination
Service Layer Results: Success and Failure With a Stable Shape
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
pageandpageSize, 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. - The defaults (
page=1,pageSize=20) provide a consistent baseline behavior for listing endpoints. pageSizeis capped at100as a safety measure. Even if a caller tries to pass1000, the service clamps it to prevent expensive queries.- On success, it returns
ok(products), keeping the result shape uniform.
When something goes wrong, we don’t want raw Postgres errors leaking through. handleException() converts exceptions into stable service errors, including mapping Postgres error codes when possible:
isPostgresError()checks whether the error looks like a Postgres error with acodefield.mapPostgresError()converts DB-specific codes (like unique violations or missing tables) into your API-level error codes (CONFLICT,DB_ERROR, etc.) along with an HTTP status.- For non-Postgres errors, we fall back to
INTERNAL_ERRORwith a500status. This keeps the service contract predictable even when something unexpected happens.
Route Handler: Turning Service Results into API Responses
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 witherror(...). - 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.
Verifying the Full Pipeline in the Playground
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
queryto list all products, or typeSKU-10/Hoodieto test search. - You can test pagination quickly by setting
pageSize=2and flippingpagebetween1and2. Because the repository usesORDER BY created_at DESC, the ordering stays stable while you paginate. - If you ever see a
DB_ERRORresponse, 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.
Recap
In this final lesson, you connected your previously “stubbed but safe” products endpoint to real PostgreSQL data:
- You confirmed the minimal
DbPoolandDbClientinterfaces insrc/lib/db/types.tscover exactly what the app needs frompg. - You implemented environment-driven DB URL resolution in
src/lib/db/client.tsso 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
Productdomain objects insrc/lib/repositories/productsRepo.ts, carefully handlingdescription: string | nulland castingcurrency/statusinto narrow unions. - You implemented real search + pagination with
ILIKE,LIMIT, andOFFSET, and returnedProduct[]consistently from both the filtered and unfiltered paths. - You implemented
listProducts()insrc/lib/services/productsService.tsto normalize defaults, cap pageSize at 100, and return a stableServiceResultshape—mapping database exceptions into structured errors. - You updated
src/app/api/products/route.tsto translate service failures into API errors using the service’scode,message,details, andhttpStatus.
At this point, GET /api/products is a real backend endpoint: validated inputs, clean layering, real SQL, and consistent responses.
