Mapping To Databases

Why this matters

API Engineers constantly translate incoming API payloads into persisted data and back again. Good mapping prevents duplicates, preserves integrity, supports idempotency, and keeps services fast. Typical tasks you will face:

• Designing idempotent create/update endpoints that map cleanly to upserts.
• Persisting orders, payments, and user profiles with correct foreign keys and constraints.
• Choosing between normalization vs denormalization for performance and consistency.
• Handling schema evolution without breaking clients (e.g., new fields, enums, optional attributes).
• Managing transactions so multi-table writes succeed or fail atomically.

Concept explained simply

Mapping to databases is the craft of turning API requests and events into safe, queryable rows/documents—and turning stored data back into stable API responses.

Mental model

Think of three layers:

• API contract: What the client sends/receives (fields, types, required/optional).
• Persistence model: Tables/collections, keys, constraints, indexes.
• Mapping rules: How each request field is validated, transformed, and written; how records are loaded and shaped for responses.

The mapping rules must protect integrity (constraints, transactions), support idempotency (avoid duplicates), and remain evolvable (version-friendly fields, defaults).

Core principles for reliable mappings

• Keys and identity:
  • Use stable business keys when available (e.g., email, external_id), but beware they can change; store both a surrogate primary key (id) and the external key with a unique constraint.
  • Generate UUIDs server-side if no stable key exists.
• Types, nullability, and defaults:
  • Match or safely coerce types (string vs integer vs decimal).
  • Make optional fields nullable; set sensible defaults for new columns.
  • Validate enumerations. Prefer check constraints or lookup tables for critical enums.
• Normalization vs denormalization:
  • Normalize for integrity (FKs, no duplication of facts).
  • Denormalize selectively for read performance (materialized views, cached aggregates) with a clear refresh/update strategy.
• Transactions and integrity:
  • Wrap multi-table changes in a single transaction.
  • Use foreign keys, unique constraints, and check constraints to guard invariants.
• Idempotency and upserts:
  • For create-like operations, support idempotency keys or use ON CONFLICT/UPSERT patterns keyed by a business identifier.
  • Return the same result for repeated identical requests.
• Soft deletes and history:
  • Use deleted_at or status flags for soft deletes; scope queries to active rows.
  • Consider append-only audit/event tables for traceability.
• Timestamps and time zones:
  • Store timestamps in UTC; convert for display at the edge.
  • Record both created_at and updated_at; consider event_time vs processing_time for streams.
• Schema evolution:
  • Add new nullable columns with defaults; backfill gradually.
  • Prefer additive changes in APIs; map missing fields to defaults.

Worked examples

Example 1: POST /orders → relational tables

Incoming JSON:

{
  "customer_external_id": "C-904",
  "currency": "USD",
  "items": [
    {"sku": "SKU-1", "qty": 2, "price": 12.50},
    {"sku": "SKU-2", "qty": 1, "price": 7.00}
  ]
}

Tables:

customers(id PK, external_id UNIQUE, name, email)
orders(id PK, customer_id FK, currency, total_amount, created_at)
order_items(id PK, order_id FK, sku, qty, price)

Mapping steps:

1. Find customer by external_id; if absent, create placeholder customer (or reject if business requires existing).
2. Compute total = sum(qty * price) from items; validate currency.
3. In one transaction: insert order; insert order_items; return order with items.

Integrity:

• FK ensures order_items relate to a valid order.
• Check qty > 0; price >= 0.

Example 2: Idempotent customer upsert by email

Request payload:

{
  "email": "alex@example.com",
  "name": "Alex",
  "phone": "+1-555-0101"
}

Table:

customers(
  id PK,
  email TEXT UNIQUE NOT NULL,
  name TEXT,
  phone TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
)

Mapping: Use an UPSERT keyed by email. Repeat calls with the same email safely update.

INSERT INTO customers(email, name, phone)
VALUES ($1, $2, $3)
ON CONFLICT (email) DO UPDATE
  SET name = EXCLUDED.name,
      phone = EXCLUDED.phone,
      updated_at = now()
RETURNING *;

Example 3: Event ingestion → audit log + materialized view

Incoming event:

{
  "event_id": "evt_1001",
  "type": "payment.captured",
  "order_id": "O-100",
  "amount": 1999,
  "currency": "USD",
  "occurred_at": "2026-01-01T10:00:00Z"
}

Tables:

payment_events(event_id PK, type, order_external_id, amount_cents, currency, occurred_at)
order_payments(order_id FK→orders.id, total_captured_cents)

Mapping approach:

1. Insert into payment_events with event_id as the primary key (prevents duplicates and enables idempotency).
2. Within the same transaction, update order_payments aggregate: total_captured_cents = total_captured_cents + amount_cents.

This combines an append-only log (for history) with a denormalized aggregate (for fast reads).

Your turn: exercises

Complete these in a SQL-capable scratch environment or on paper. Match the instructions to the listed expected outputs.

Exercise 1: Customer upsert by email

Design an idempotent upsert mapping for a customers table with a unique email. Include the SQL and a brief note on how the API should behave when the same request is sent twice.

Need a nudge? Hints

• Use a unique constraint on email.
• In PostgreSQL, ON CONFLICT (email) DO UPDATE is standard.

Exercise 2: Create order with stock check

Given tables inventory(sku PK, stock INT), orders, order_items: map a POST /orders to a transaction that checks stock for each item, inserts the order and items, and decrements stock. If any item is short, the whole operation must fail.

Need a nudge? Hints

• SELECT ... FOR UPDATE on inventory rows before decrementing.
• Raise an error if stock < requested qty.

Checklist before you move on

• ☐ I used constraints (unique/FK) to encode business rules.
• ☐ I wrapped multi-table writes in a transaction.
• ☐ I thought through idempotency (repeat requests return the same result).
• ☐ I validated quantities, prices, and currency.

Common mistakes and how to self-check

• Relying on app logic only, no constraints.
  • Self-check: Can a duplicate email slip in if the app restarts mid-write? Add a unique index.
• No transaction around multi-table writes.
  • Self-check: If the service crashes after inserting an order but before items, what persists? Use a single transaction.
• Ignoring idempotency for create endpoints.
  • Self-check: What happens if a network retry replays the request? Ensure UPSERT or idempotency keys.
• Storing local times.
  • Self-check: Are all timestamps UTC? Convert at the edge for users.
• Hardcoding enums without validation.
  • Self-check: Can API send an unknown status? Add a check constraint or lookup table.

Practical projects

• Build an Orders API with idempotent creates and an audit log. Include upserted customers by email and stock checks with transactional writes.
• Create a minimal payments ingestion worker that writes an append-only payment_events table and maintains a per-order total captured materialized view.
• Add soft deletes to a products table, retrofit all queries to exclude deleted rows, and add a unique index that respects deleted_at.

Who this is for

• API Engineers and Backend Developers integrating services with relational or document databases.
• Anyone responsible for data integrity across microservices or partner APIs.

Prerequisites

• Comfort with basic SQL (SELECT/INSERT/UPDATE/DELETE) or a familiar ORM.
• Understanding of HTTP APIs (status codes, JSON, idempotency).
• Basic knowledge of transactions and indexes.

Learning path

1. Start with key design: choose primary keys and external identifiers.
2. Add constraints: uniques, foreign keys, and checks that mirror business rules.
3. Design idempotent create/update flows with upserts or idempotency keys.
4. Handle multi-table writes with transactions and clear error handling.
5. Plan for schema changes: defaults, nullability, and backward-compatible responses.

Next steps

• Implement one practical project end-to-end with tests that simulate retries and partial failures.
• Profile slow reads; consider safe denormalization or indexed materialized views.
• Add observability: log idempotency keys, transaction failures, and constraint violations.

Mini challenge

You need to add a new optional field discount_code to orders. Make this change without breaking existing clients and without downtime. Outline the DB migration, API response change, and how you will backfill or default values.

Quick Test

Take the quick test to check your understanding. This test is available to everyone; only logged-in users get saved progress.