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.
