Retrieving Order Data

Welcome back! 👋 Now that checkout can safely create orders, the next step is making those orders easy to retrieve in a clean, client-friendly way. In a real system, “checkout” is only half the story—you also need “My Orders” lists, order detail pages, and backend admin views that can load an order and its line items reliably.

In this lesson you’ll implement the read side of the Orders API end-to-end: the repository performs the SQL reads and maps database rows into domain objects, the service layer applies pagination defaults and returns consistent ServiceResults, and the Remix routes validate input and translate service results into standard success(...) / error(...) envelopes.

In the previous lesson, you implemented checkout at POST /api/carts/:id/checkout, including a transactional repository workflow that snapshots cart items into order_items, computes totals, decrements inventory, and seals the cart as checked_out. That means orders now exist in the database—so in this lesson, we’ll focus on the “read path”: listing orders and fetching a single order with its items.

There are two main read endpoints:

• GET /api/orders → returns a paginated list of orders (newest first)
• GET /api/orders/:id → returns a single order, including items

The most important design goal is consistency: routes should never handle raw SQL rows, never guess status codes, and never implement business rules. Instead:

• repositories do SQL + mapping
• services do defaulting + result shaping
• routes do validation + HTTP response envelope

Before we even talk about pagination or fetching by ID, the repository establishes a pattern: DB rows are not returned directly. Everything gets mapped into domain-shaped objects using mapOrderRow and mapItemRow.

This code lives in src/lib/repositories/ordersRepo.ts:

// src/lib/repositories/ordersRepo.ts
export interface OrderRow {
  id: string;
  cart_id: string;
  status: string;
  subtotal_cents: number;
  tax_cents: number;
  total_cents: number;
  currency: string;
  created_at: string;
  updated_at: string;
}

export interface OrderItemRow {
  id: string;
  order_id: string;
  product_id: string;
  quantity: number;
  unit_price_cents: number;
  created_at: string;
  updated_at: string;
}

function mapOrderRow(r: OrderRow): Order {
  return {
    id: r.id,
    cart_id: r.cart_id,
    status: ["pending", "paid", "shipped", "cancelled"].includes(r.status)
      ? (r.status as Order["status"])
      : "pending",
    subtotal_cents: r.subtotal_cents,
    tax_cents: r.tax_cents,
    total_cents: r.total_cents,
    currency: r.currency === "USD" ? "USD" : "USD",
    created_at: r.created_at,
    updated_at: r.updated_at,
  };
}

function mapItemRow(r: OrderItemRow): OrderItem {
  return {
    id: r.id,
    order_id: r.order_id,
    product_id: r.product_id,
    quantity: r.quantity,
    unit_price_cents: r.unit_price_cents,
    created_at: r.created_at,
    updated_at: r.updated_at,
  };
}

• OrderRow and OrderItemRow describe the database shape, which is important because repositories work at the SQL boundary and must be explicit about column names and types.
• mapOrderRow normalizes the order’s status string into a safe domain value. If the database contains an unexpected status, the code defaults to "pending" to avoid leaking invalid states to the rest of the system.
• mapItemRow does the same transformation for order items, ensuring the rest of your code deals with OrderItem objects rather than ad-hoc row blobs.
• This mapping layer is what keeps routes/services “clean”: they never need to know SQL column names or worry about data normalization—they just work with domain objects.