Wiring PostgreSQL Products

Wiring PostgreSQL Products | CodeSignal Learn

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 \to service \to repository \to PostgreSQL \to 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

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

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() . TypeScript 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>;} 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.

Building a Safe Database URL Without Hardcoding

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. TypeScriptfunction 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}`; } 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.

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

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. TypeScriptexport 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; }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(): TypeScriptexport 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, }; }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.

Searching Products with Filtering and Pagination

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: TypeScriptconst offset = (page - 1) * pageSize; 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: TypeScriptif (q && q.length > 0) { const rows = await query<ProductRow>( `SELECT * FROM products WHERE sku ILIKE $1 OR name ILIKE $1 ORDER BY created_at DESC LIMIT $2 OFFSET $3`, [`%${q}%`, pageSize, offset] ); return rows.map(mapProductRow); } 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. The sort order is ORDER BY created_at DESC, which keeps results stable and predictable. This matters because pagination without a stable ordering can produce “jumping” results. The function always returns rows.map(mapProductRow) so callers always get Product[], never raw DB rows. When the query is missing, we run a simpler “list all products” query—but keep the same ordering: TypeScriptconst rows = await query<ProductRow>( `SELECT * FROM products ORDER BY created_at DESC LIMIT $1 OFFSET $2`, [pageSize, offset] ); return rows.map(mapProductRow); This avoids unnecessary WHERE logic when there’s no filtering, which keeps the query easy for Postgres to execute efficiently. The sort order stays the same (created_at DESC), so filtered and unfiltered requests behave consistently. Again, we map rows into Product domain objects so the rest of the app never has to deal with DB-shaped strings for unions.

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: TypeScriptexport 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 }; }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: TypeScriptexport 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); } }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: TypeScriptfunction 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 }); }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.

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: TypeScript 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); 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.

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. TypeScriptconst 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]);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.

Wiring PostgreSQL Products

Wiring PostgreSQL Products

The Postgres Schema You’re Querying

Join the 1M+ learners on CodeSignal

Minimal Database Interfaces for pg

Building a Safe Database URL Without Hardcoding

Creating One Pool and Reusing It

Mapping Database Rows into Product Domain Types

Searching Products with Filtering and Pagination

Service Layer Results: Success and Failure With a Stable Shape

Route Handler: Turning Service Results into API Responses

Verifying the Full Pipeline in the Playground

Recap