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.

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.

CREATE TABLE IF NOT EXISTS products (
  id uuid PRIMARY KEY,
  sku text UNIQUE NOT NULL,
  name text NOT NULL,
  description text NULL,
  price_cents integer NOT NULL CHECK (price_cents >= 0),
  currency text NOT NULL DEFAULT 'USD',
  inventory_count integer NOT NULL CHECK (inventory_count >= 0),
  status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','archived')),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

• 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.

INSERT INTO products (
  id, sku, name, description, price_cents, currency, inventory_count, status
)
VALUES
  (uuid_generate_v4(), 'SKU-100', 'Sample Tee', 'Comfortable cotton t-shirt', 1999, 'USD', 100, 'active'),
  (uuid_generate_v4(), 'SKU-101', 'Classic Hoodie', 'Warm fleece hoodie', 4999, 'USD', 45, 'active'),
  ...
ON CONFLICT (sku) DO NOTHING;

• You can test filtering with values like query=SKU-10 or query=Hoodie and 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.

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().

export interface DbQueryResult<T> {
  rows: T[];
}

export interface DbClient {
  query<T>(text: string, params?: unknown[]): Promise<DbQueryResult<T>>;
  release?: () => void;
}

export interface DbPool {
  query<T>(text: string, params?: unknown[]): Promise<DbQueryResult<T>>;
  connect: () => Promise<DbClient>;
  end?: () => Promise<void>;
}

• 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.

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:

let pool: DbPool | null = null;

export function getPool(): DbPool {
  if (pool) return pool;
  const connectionString = envDatabaseUrl();
  pool = new Pool({ connectionString }) as unknown as DbPool;
  return pool;
}

• 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:

export async function query<T>(text: string, params: unknown[] = []): Promise<T[]> {
  const res = await getPool().query<T>(text, params);
  return res.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.

export interface ProductRow {
  id: string;
  sku: string;
  name: string;
  description: string | null;
  price_cents: number;
  currency: string;
  inventory_count: number;
  status: string;
  created_at: string;
  updated_at: string;
}

• 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 '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():

export function mapProductRow(r: ProductRow): Product {
  return {
    id: r.id,
    sku: r.sku,
    name: r.name,
    description: r.description,
    price_cents: r.price_cents,
    currency: r.currency as Product['currency'],
    inventory_count: r.inventory_count,
    status: r.status as Product['status'],
    created_at: r.created_at,
    updated_at: r.updated_at,
  };
}

• The mapping is mostly 1:1 because the schema was designed to match the domain model.
• description is passed through as-is so null stays null. Accidentally converting this to undefined would violate the Product type and can also confuse clients expecting consistent nullability.
• currency and status are cast into the narrower domain unions using as Product['currency'] and as Product['status'].
• Those casts are safe in this project because the DB schema enforces valid values (currency DEFAULT 'USD' and CHECK (status IN ('active','archived'))). In other words, the DB guarantees the invariants the domain type requires.

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:

export type ServiceResult<T> = { ok: true; value: T } | { ok: false; error: ServiceError };

export function ok<T>(value: T): ServiceResult<T> {
  return { ok: true, value };
}

export function fail(params: ServiceError): ServiceResult<never> {
  return { ok: false, error: params };
}

• 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:

export async function listProducts(params: { query?: string; page?: number; pageSize?: number }): Promise<ServiceResult<Product[]>> {
  try {
    const page = params.page && params.page > 0 ? params.page : 1;
    const pageSize = params.pageSize && params.pageSize > 0 ? Math.min(params.pageSize, 100) : 20;
    const products = await searchProducts({ query: params.query, page, pageSize });
    return ok(products);
  } catch (e: unknown) {
    return handleException(e);
  }
}

• 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.
• The defaults (page=1, pageSize=20) provide a consistent baseline behavior for listing endpoints.
• pageSize is capped at 100 as a safety measure. Even if a caller tries to pass 1000, 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:

function handleException(e: unknown): ServiceResult<never> {
  if (isPostgresError(e)) return fail(mapPostgresError(e));
  const message = (e as Error)?.message ?? 'Internal error';
  return fail({ code: 'INTERNAL_ERROR', message, httpStatus: 500 });
}

• isPostgresError() checks whether the error looks like a Postgres error with a code field.
• 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_ERROR with a 500 status. This keeps the service contract predictable even when something unexpected happens.

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:

const products = await listProducts({ query: query.value, page: page.value, pageSize: pageSize.value });
if (!products.ok) return error(products.error.code, products.error.message, products.error.details, products.error.httpStatus);

return success(products.value);

• 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.

const productsPath = useMemo(() => {
  const sp = new URLSearchParams();
  if (query.length > 0) sp.set('query', query);
  if (page.length > 0) sp.set('page', page);
  if (pageSize.length > 0) sp.set('pageSize', pageSize);
  return `/api/products?${sp.toString()}`;
}, [query, page, pageSize]);

• 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 src/lib/services/productsService.ts to normalize defaults, cap pageSize at 100, and return a stable ServiceResult shape—mapping database exceptions into structured errors.
• You updated src/app/api/products/route.ts to translate service failures into API errors using the service’s code, message, details, and httpStatus.

At this point, GET /api/products is a real backend endpoint: validated inputs, clean layering, real SQL, and consistent responses.