Wiring PostgreSQL Products

Welcome back 👋 Up to now, your /api/products endpoint has been about reliable API behavior: validating query parameters and returning consistent success(...) / error(...) envelopes. In Unit 2, you also introduced domain models and DTOs, so your backend has a clear “source of truth” for product data and a clear boundary around what clients are allowed to send.

In this lesson, we finally connect products to PostgreSQL. You’ll walk through the entire pipeline—route → service → repository → database—and, just as importantly, you’ll learn the core database concepts behind the code: what a pool is, why we use a lazy singleton, how queries run without leaking connections, and how raw Postgres errors become stable API failures.

When the browser calls GET /api/products, your backend doesn’t jump straight into SQL. It moves through layers that keep responsibilities clear:

• Route (HTTP layer): validates query params and turns results into success(...) or error(...).
• Service (workflow layer): applies defaults and converts exceptions into structured results (ok(...) / fail(...)).
• Repository (data access layer): runs SQL and maps database rows into domain Product objects.
• DB client (infrastructure layer): manages connection pooling and provides query/transaction helpers.

This structure pays off immediately: your route stays readable, and the database code stays centralized and reusable.

Before you can query Postgres, your app needs to know where the database is and how to authenticate.

That information comes from environment variables (values provided to the running process, not hardcoded in your code). They’re accessed in Node.js through process.env.

The logic for building the connection string is in src/lib/db/client.ts.

Once we have a connection string, we need a reliable way to execute queries without constantly opening and closing connections.

That’s where a connection pool comes in—and why the code uses a lazy singleton.

What is a connection pool?

A connection pool is a managed set of reusable database connections.

• Opening a new database connection is expensive: it involves network handshakes, authentication, and setup.
• If you created a fresh connection for every request, your app would get slower and your database could get overwhelmed.
• A pool keeps a small set of ready-to-use connections. When your app needs one, it borrows it briefly and returns it when done.

What does “singleton” mean here?

A singleton is a single shared instance of something—in this case, one Pool instance for the whole app process.

• You do not want a new pool per request.
• You want one pool that all requests share.

What does “lazy” mean here?

Lazy initialization means “don’t create it until we actually need it.”

• The pool is not created when the file loads.
• It’s created the first time code calls getPool().

This matters in real apps because you sometimes want the server to boot even if the database isn’t used immediately.

The lazy singleton 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 starts as null, meaning “not created yet.”
• The first call to getPool() creates the pool and caches it in the pool variable.
• Every later call returns the same pool instance.
• This is the “lazy singleton” pattern: lazy creation + single shared instance.

Some operations require multiple SQL statements that must succeed or fail together. That’s what transactions are for.

This project includes a transaction helper:

export async function withTransaction<T>(
  fn: (client: DbClient) => Promise<T>,
): Promise<T> {
  const client = await getPool().connect();
  try {
    await client.query("BEGIN");
    const result = await fn(client);
    await client.query("COMMIT");
    return result;
  } catch (e: unknown) {
    await client.query("ROLLBACK");
    throw e;
  } finally {
    if (client.release) client.release();
  }
}

What is getPool().connect()?

• pool.query(...) is the “simple” mode where the pool borrows a connection automatically.
• pool.connect() is the “manual” mode: you explicitly ask the pool for a specific connection (a “client”) that you control.

You need manual mode for transactions because all statements in a transaction must run on the same connection.

Why release() is critical

• If you forget to release the client, it never returns to the pool.
• Enough leaked clients = the pool runs out of connections, and the app starts hanging or failing.
• The finally block guarantees release happens even if an error occurs.

Postgres uses numeric/string error codes (like "23505") that aren’t meaningful to most API clients.

This project maps those raw errors into stable API error shapes in src/lib/db/errors.ts.

Postgres errors are “typed” by code:

export interface PostgresError extends Error {
  code: string;
  detail?: string;
  constraint?: string;
}

• code is the main identifier (like “unique constraint violation”).
• detail and constraint often include extra debugging info.

Detecting Postgres errors:

export function isPostgresError(err: unknown): err is PostgresError {
  return Boolean(
    err &&
    typeof err === "object" &&
    "code" in err &&
    typeof (err as { code?: unknown }).code === "string",
  );
}

• Not every thrown value is a Postgres error.
• This guard prevents unsafe assumptions before mapping.

export function mapPostgresError(err: PostgresError): {
  code: ApiErrorCode;
  message: string;
  httpStatus: number;
  details?: unknown;
} {
  if (err.code === "23505") {
    return {
      code: "CONFLICT",
      message: "Duplicate value violates unique constraint",
      httpStatus: 409,
      details: { constraint: err.constraint, detail: err.detail },
    };
  }
  if (err.code === "42P01") {
    return {
      code: "DB_ERROR",
      message: "Database schema missing or invalid. Run migrations.",
      httpStatus: 500,
    };
  }
  return {
    code: "DB_ERROR",
    message: "Database error",
    httpStatus: 500,
    details: { code: err.code, detail: err.detail },
  };
}

• "23505" → unique violation → CONFLICT + HTTP 409. This is the classic “you tried to insert a duplicate SKU” type of problem.
• "42P01" → missing table/schema → DB_ERROR + HTTP 500. This is usually a setup issue (“schema not applied”).
• Unknown codes still become DB_ERROR, but we keep code in details to help debugging without relying on it for API behavior.

This mapping is crucial because it makes your API stable even if the database changes its internal error messages.

Now that the database layer is ready, the repository src/lib/repositories/productsRepo.ts is where SQL queries live.

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;
}

• This reflects raw SQL results.
• currency and status are plain strings here because that’s what SQL returns.

Normalizing values for safety:

function normalizeCurrency(_currency: string): Product["currency"] {
  return "USD";
}

function normalizeProductStatus(status: string): Product["status"] {
  return PRODUCT_STATUSES.includes(status as Product["status"])
    ? (status as Product["status"])
    : "active";
}

• This ensures the domain model never receives unexpected values.
• Even if the DB is misconfigured, the backend’s domain stays predictable.

The service layer src/lib/services/productsService.ts turns database exceptions into structured service results.

Handling exceptions centrally:

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 });
}

• Postgres failures are mapped into stable API errors.
• Anything else becomes INTERNAL_ERROR, protecting the API from leaking implementation details.

The listProducts flow:

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

• Defaults are applied here so the repository always receives concrete values.
• The service returns ok(rows) instead of “just rows,” making success/failure explicit.

Finally, app/routes/api.products.ts ties everything together and produces the actual HTTP response.

export async function loader({ request }: LoaderFunctionArgs) {
  try {
    const searchParams = new URL(request.url).searchParams;
    const query = searchParams.get("query") ?? undefined;

    const page = parseOptionalIntParam(searchParams, "page", { min: 1 });
    if (!page.ok)
      return error("VALIDATION_ERROR", page.message, undefined, 400);

    const pageSize = parseOptionalIntParam(searchParams, "pageSize", {
      min: 1,
      max: 100,
    });
    if (!pageSize.ok)
      return error("VALIDATION_ERROR", pageSize.message, undefined, 400);

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

    return success(result.value);
  } catch (e: unknown) {
    const message = e instanceof Error ? e.message : "Internal error";
    return error("INTERNAL_ERROR", message, undefined, 500);
  }
}

• Validation happens first, before touching the database.
• Service results are converted into HTTP success/error envelopes cleanly.
• Errors preserve meaningful HTTP status codes (like 409 for conflicts, 500 for DB errors).

In this lesson, you learned not just what files exist, but why they’re designed this way:

• Environment variables let the same code run across local/dev/hosted setups.
• A lazy singleton pool avoids expensive reconnecting and prevents resource exhaustion.
• query() runs SQL through the pool safely without leaking connections.
• connect() + release() are critical for transactions (and connection safety).
• Postgres error codes are mapped into stable API errors with useful HTTP statuses.
• Repositories own SQL and mapping; services own orchestration and error conversion; routes own validation and HTTP envelopes.