Wiring PostgreSQL Products

Lesson: 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 \to service \to repository \to 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.

The Big Picture: One Request, Four Layers

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.

Database Connection Configuration: Environment Variables and Defaults

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 .

Building a database URL from environment variables

TypeScript// src/lib/db/client.ts function 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}`; } process.env.DATABASE_URL is the “all-in-one” connection string. Many platforms (and container setups) provide this single variable because it’s easy to configure and copy around. If DATABASE_URL is not set, the code falls back to individual variables: PGHOST: the hostname where Postgres runs (e.g. localhost, db, 127.0.0.1, or a remote host). PGPORT: the port Postgres listens on (default Postgres port is 5432). PGUSER / PGPASSWORD: the credentials used to log in. PGDATABASE: which database to connect to. Each fallback has a default (like "localhost" or "postgres") so the project works in common development setups without extra configuration. Why default to localhost? In many dev environments, Postgres runs on the same machine/container as the app (or is exposed locally). In that case, localhost is the simplest default. When Postgres runs elsewhere (like in a separate Docker service or a hosted database), you override PGHOST (or provide DATABASE_URL) without changing any code. These defaults are a convenience, not a requirement—real deployments almost always set explicit environment variables. Why do we build a string like postgres://...? This line produces a connection string: TypeScriptreturn `postgres://${user}:${pass}@${host}:${port}/${db}`; It’s a standardized URL format Postgres clients understand. It packs all connection details into one string, which the pg library can consume directly. Example shape (conceptually): postgres://username:password@hostname:port/databaseName This lets pg connect without you manually wiring each setting into separate config fields.

Join the 1M+ learners on CodeSignal

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

TypeScript

// src/lib/db/client.ts

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

TypeScript

return `postgres://${user}:${pass}@${host}:${port}/${db}`;

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

TypeScript

export async function query<T>(
  text: string,
  params: unknown[] = [],
): Promise<T[]> {
  const res = await getPool().query<T>(text, params);
  return res.rows;
}

SQL

WHERE sku ILIKE $1 OR name ILIKE $1

TypeScript

[`%${q}%`, pageSize, offset]

TypeScript

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

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

TypeScript

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

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

TypeScript

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

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

TypeScript

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

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

TypeScript

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

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

TypeScript

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

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

TypeScript

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: normalizeCurrency(r.currency),
    inventory_count: r.inventory_count,
    status: normalizeProductStatus(r.status),
    created_at: r.created_at,
    updated_at: r.updated_at,
  };
}

TypeScript

export async function searchProducts(opts: {
  query?: string;
  page: number;
  pageSize: number;
}): Promise<Product[]> {
  const { query: q, page, pageSize } = opts;
  const offset = (page - 1) * pageSize;
  if (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);
  }
  const rows = await query<ProductRow>(
    `SELECT * FROM products
     ORDER BY created_at DESC
     LIMIT $1 OFFSET $2`,
    [pageSize, offset],
  );
  return rows.map(mapProductRow);
}

TypeScript

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

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

TypeScript

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

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

TypeScript

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

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

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

TypeScript

export async function query<T>(
  text: string,
  params: unknown[] = [],
): Promise<T[]> {
  const res = await getPool().query<T>(text, params);
  return res.rows;
}

SQL

WHERE sku ILIKE $1 OR name ILIKE $1

TypeScript

[`%${q}%`, pageSize, offset]

TypeScript

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

TypeScript

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

TypeScript

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

TypeScript

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

TypeScript

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

TypeScript

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

TypeScript

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: normalizeCurrency(r.currency),
    inventory_count: r.inventory_count,
    status: normalizeProductStatus(r.status),
    created_at: r.created_at,
    updated_at: r.updated_at,
  };
}

TypeScript

export async function searchProducts(opts: {
  query?: string;
  page: number;
  pageSize: number;
}): Promise<Product[]> {
  const { query: q, page, pageSize } = opts;
  const offset = (page - 1) * pageSize;
  if (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);
  }
  const rows = await query<ProductRow>(
    `SELECT * FROM products
     ORDER BY created_at DESC
     LIMIT $1 OFFSET $2`,
    [pageSize, offset],
  );
  return rows.map(mapProductRow);
}

TypeScript

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

TypeScript

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

TypeScript

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

Lesson: Wiring PostgreSQL Products

The Big Picture: One Request, Four Layers

Database Connection Configuration: Environment Variables and Defaults

Building a database URL from environment variables

Join the 1M+ learners on CodeSignal

Pools, Lazy Initialization, and the Singleton Pattern

Running Queries: What query(...) Actually Does

Transactions: What getPool().connect() Means (and Why It Exists)

Postgres Error Codes: Turning Low-Level Failures into Stable API Errors

Mapping error codes into API-friendly results

Repository: SQL, Pagination, and Row Mapping

SQL row shape vs domain model

Mapping rows to domain Product

Service: Results Instead of Exceptions

Route: Validate Inputs, Call Service, Return Envelopes

Recap