Part 5: A second resource: structure, relations and cascades

Adding "orders" is where a codebase either stays tidy or starts to rot. We'll make it tidy.

Series: Build a production-grade REST API in Go · Part 5 of 17 Where we are: the configurable API from Part 4.

One resource is easy. The real test of a structure is the second one: do you copy-paste the first handler and tweak it, or does the design make the new resource small? This post adds orders (each order belongs to a user) and, in doing so, reshapes the code so that every future resource is cheap.

The new API

Method & path	Does
`GET /orders`	list orders (optional `?user_id=`)
`POST /orders`	create an order `{"user_id":1,"item":"book","quantity":2}`
`GET /orders/{id}`	fetch one
`DELETE /orders/{id}`	delete one

In Part 4 everything lived in one Handler type in one file. With two resources that would mean a 400-line file and duplicated helpers. We split by resource and extract what is shared:

internal/handlers/
  base.go     shared helpers: parseID, decodeJSON, writeJSON, error mapping
  users.go    UserHandler
  orders.go   OrderHandler

internal/handlers/base.go (excerpt)

// Limits are the per-handler constraints. They are injected rather than
// hard-coded so each environment (and each test) can choose its own.
type Limits struct {
	// MaxBodyBytes caps request bodies. Without a cap, a client can stream
	// gigabytes at json.Decoder and exhaust memory.
	MaxBodyBytes int64
	MaxNameLen   int
}

// base holds what EVERY resource handler needs: logger, limits, and the
// response/decoding helpers. Each resource (users, orders, ...) embeds it, so
// adding a resource never means copy-pasting this plumbing. Embedding promotes
// these methods, so UserHandler can call h.writeJSON directly.
type base struct {
	log    *slog.Logger
	limits Limits
}

base holds what every resource handler needs: a logger, the limits, and helper methods (writeJSON, decodeJSON, parseID, storeError, ...). Each resource embeds it:

internal/handlers/orders.go (excerpt)

type OrderStore interface {
	List(ctx context.Context, userID int) ([]*storage.Order, error) // userID 0 = all
	Get(ctx context.Context, id int) (*storage.Order, error)
	Create(ctx context.Context, userID int, item string, quantity int) (*storage.Order, error)
	Delete(ctx context.Context, id int) error
}

type OrderHandler struct {
	base
	store OrderStore
}

func NewOrderHandler(store OrderStore, log *slog.Logger, limits Limits) *OrderHandler {
	return &OrderHandler{base: base{log: log, limits: limits}, store: store}
}

Embedding (base with no field name) promotes its methods, so inside an OrderHandler method you simply write h.writeJSON(...). It is not inheritance; it is composition with a convenient syntax. The payoff: adding a resource never means copying the helpers, and fixing a bug in decodeJSON fixes every handler at once.

Notice each resource declares its own small store interface (OrderStore) next to the handler that uses it. The users handler does not know orders exist, and vice versa.

Step 2: routes as a table of contents

Replace the contents of internal/routes/routes.go with

package routes

import (
	"net/http"

	"go-rest/internal/handlers"
)

// SetupRoutes registers every resource. Each resource gets its own small
// function so this file stays a readable table of contents as the API grows.
func SetupRoutes(mux *http.ServeMux, u *handlers.UserHandler, o *handlers.OrderHandler) {
	userRoutes(mux, u)
	orderRoutes(mux, o)
}

func userRoutes(mux *http.ServeMux, h *handlers.UserHandler) {
	mux.HandleFunc("GET /users", h.List)
	mux.HandleFunc("POST /users", h.Create)
	mux.HandleFunc("GET /users/{id}", h.Get)
	mux.HandleFunc("PUT /users/{id}", h.Update)
	mux.HandleFunc("DELETE /users/{id}", h.Delete)
}

func orderRoutes(mux *http.ServeMux, h *handlers.OrderHandler) {
	mux.HandleFunc("GET /orders", h.List)
	mux.HandleFunc("POST /orders", h.Create)
	mux.HandleFunc("GET /orders/{id}", h.Get)
	mux.HandleFunc("DELETE /orders/{id}", h.Delete)
}

Each resource gets its own function, so routes.go stays a readable index of the whole API no matter how large it grows. When someone asks "which endpoints exist?" this file is the answer.

Step 3: a relationship in the schema

An order belongs to a user. In SQL that is a foreign key:

internal/storage/storage.go (lines 77-93)

	const schema = `
		CREATE TABLE IF NOT EXISTS users (
			id   INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL CHECK (length(trim(name)) > 0)
		);

		-- ON DELETE CASCADE: deleting a user removes their orders (enforced
		-- because foreign_keys(1) is on). The alternative, RESTRICT, would
		-- instead refuse to delete a user who still has orders.
		CREATE TABLE IF NOT EXISTS orders (
			id         INTEGER PRIMARY KEY AUTOINCREMENT,
			user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
			item       TEXT    NOT NULL CHECK (length(trim(item)) > 0),
			quantity   INTEGER NOT NULL CHECK (quantity > 0),
			created_at TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
		);
		CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);`

Read the orders table closely:

user_id ... REFERENCES users(id): the database itself now refuses an order pointing at a non-existent user. Relying on application code alone for this is fragile, because a second code path (a script, another service, a future you) can forget the check.
ON DELETE CASCADE: deleting a user automatically deletes their orders. The alternative, RESTRICT, would refuse to delete a user who still has orders. Neither is "right"; it is a business decision. We chose cascade, and we'll test it.
CHECK (quantity > 0) and CHECK (length(trim(item)) > 0): defense in depth. The handler validates too, but the database is the last line of defense against bad data from any path.
The index on user_id speeds up "all orders of user X". Relational databases index primary keys automatically but not foreign keys, which is a common source of slow queries.
SQLite quirk: foreign keys are only enforced if you turn them on per connection, which is why Part 3 put foreign_keys(1) in the connection string.

Step 4: the store, and an atomic insert

internal/storage/orders.go (excerpt)

// ErrUserNotFound is distinct from ErrNotFound: it means "the order's owner
// doesn't exist", which handlers report differently from "order not found".
var ErrUserNotFound = errors.New("referenced user not found")

// Create inserts only if the user exists, in ONE atomic statement
// (INSERT ... SELECT ... WHERE EXISTS). A separate "SELECT user, then INSERT"
// would have a race: the user could be deleted in between. Zero rows inserted
// surfaces as sql.ErrNoRows from RETURNING. The FOREIGN KEY on the table stays
// as a backstop.
func (s *OrderStore) Create(ctx context.Context, userID int, item string, quantity int) (*Order, error) {
	o, err := scanOrder(s.db.QueryRowContext(ctx, `
		INSERT INTO orders (user_id, item, quantity)
		SELECT ?, ?, ? WHERE EXISTS (SELECT 1 FROM users WHERE id = ?)
		RETURNING `+orderCols,
		userID, item, quantity, userID))
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrUserNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("insert order: %w", err)
	}
	return o, nil
}

The interesting query is Create. A naive implementation would do two steps:

SELECT the user to check they exist
INSERT the order

Between those two statements another request could delete the user: a classic race condition (check-then-act). Our version is a single statement:

INSERT INTO orders (user_id, item, quantity)
SELECT ?, ?, ? WHERE EXISTS (SELECT 1 FROM users WHERE id = ?)
RETURNING ...

The existence check and the insert happen atomically. If the user doesn't exist, zero rows are inserted, RETURNING yields nothing, we see sql.ErrNoRows, and we translate that into a domain error, ErrUserNotFound. (The foreign key remains as a backstop.)

Notice we defined two not-found errors: ErrNotFound ("that order doesn't exist") and ErrUserNotFound ("the order's owner doesn't exist"). They map to different HTTP responses.

Step 5: choosing the right status code

In the handler:

if errors.Is(err, storage.ErrUserNotFound) {
    // The URL itself is valid; the *body* references something that
    // doesn't exist, so 422 is more accurate than 404.
    h.writeError(w, r, http.StatusUnprocessableEntity, "user_id does not exist")
    return
}

404 means "the resource at this URL doesn't exist". For POST /orders the URL is fine; the problem is a field inside the body. That is a 422.

Complete code for this part

This is the biggest restructuring so far, so here is everything, file by file. Note the first block: the old single handlers.go is deleted and replaced by three files. Paste, then go mod tidy && go build ./....

Already given earlier in this post: internal/routes/routes.go.

Delete internal/handlers/handlers.go

Create internal/handlers/base.go

package handlers

import (
	"context"
	"encoding/json"
	"errors"
	"io"
	"log/slog"
	"net/http"
	"strconv"

	"go-rest/internal/storage"
)

// Limits are the per-handler constraints. They are injected rather than
// hard-coded so each environment (and each test) can choose its own.
type Limits struct {
	// MaxBodyBytes caps request bodies. Without a cap, a client can stream
	// gigabytes at json.Decoder and exhaust memory.
	MaxBodyBytes int64
	MaxNameLen   int
}

// base holds what EVERY resource handler needs: logger, limits, and the
// response/decoding helpers. Each resource (users, orders, ...) embeds it, so
// adding a resource never means copy-pasting this plumbing. Embedding promotes
// these methods, so UserHandler can call h.writeJSON directly.
type base struct {
	log    *slog.Logger
	limits Limits
}

func (b base) parseID(w http.ResponseWriter, r *http.Request) (int, bool) {
	id, err := strconv.Atoi(r.PathValue("id"))
	if err != nil || id <= 0 {
		b.writeError(w, r, http.StatusBadRequest, "invalid id")
		return 0, false
	}
	return id, true
}

// decodeJSON reads a size-limited, strict JSON body into dst. Field validation
// stays in each resource's handler because the rules differ per resource.
func (b base) decodeJSON(w http.ResponseWriter, r *http.Request, dst any) bool {
	// MaxBytesReader (unlike io.LimitReader) makes reads fail with an error
	// once the limit is hit and tells the server to close the connection.
	r.Body = http.MaxBytesReader(w, r.Body, b.limits.MaxBodyBytes)

	dec := json.NewDecoder(r.Body)
	// Reject unknown fields so typos ("nmae") fail loudly instead of being
	// silently ignored.
	dec.DisallowUnknownFields()

	if err := dec.Decode(dst); err != nil {
		var tooBig *http.MaxBytesError
		if errors.As(err, &tooBig) {
			b.writeError(w, r, http.StatusRequestEntityTooLarge, "request body too large")
			return false
		}
		b.writeError(w, r, http.StatusBadRequest, "invalid JSON body")
		return false
	}
	// A second Decode must hit EOF; otherwise the body had trailing garbage
	// such as `{"name":"a"}{"name":"b"}`.
	if err := dec.Decode(&struct{}{}); !errors.Is(err, io.EOF) {
		b.writeError(w, r, http.StatusBadRequest, "body must contain a single JSON object")
		return false
	}
	return true
}

// storeError maps domain errors to HTTP status codes in ONE place. Every
// resource shares it, so "missing row => 404" behaves identically everywhere.
func (b base) storeError(w http.ResponseWriter, r *http.Request, op string, err error) {
	if errors.Is(err, storage.ErrNotFound) {
		b.writeError(w, r, http.StatusNotFound, "not found")
		return
	}
	b.internalError(w, r, op, err)
}

// internalError logs the real cause server-side but tells the client nothing
// about internals (paths, SQL, driver messages can help an attacker).
func (b base) internalError(w http.ResponseWriter, r *http.Request, op string, err error) {
	// A cancelled request context is the client leaving, not a server fault.
	if errors.Is(err, context.Canceled) {
		b.log.Info("request cancelled", "op", op, "method", r.Method, "path", r.URL.Path)
		return
	}
	b.log.Error("handler failed", "op", op, "method", r.Method, "path", r.URL.Path, "err", err)
	b.writeError(w, r, http.StatusInternalServerError, "internal server error")
}

func (b base) writeError(w http.ResponseWriter, r *http.Request, status int, msg string) {
	b.writeJSON(w, r, status, map[string]string{"error": msg})
}

// writeJSON: by the time Encode fails the status line is usually already sent,
// so we cannot change the response, but we can (and must) log it.
func (b base) writeJSON(w http.ResponseWriter, r *http.Request, status int, v any) {
	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(status)
	if err := json.NewEncoder(w).Encode(v); err != nil {
		b.log.Error("encode response", "path", r.URL.Path, "err", err)
	}
}

Create internal/handlers/orders.go

package handlers

import (
	"context"
	"errors"
	"fmt"
	"log/slog"
	"net/http"
	"strconv"
	"strings"
	"unicode/utf8"

	"go-rest/internal/storage"
)

const maxQuantity = 10_000

type OrderStore interface {
	List(ctx context.Context, userID int) ([]*storage.Order, error) // userID 0 = all
	Get(ctx context.Context, id int) (*storage.Order, error)
	Create(ctx context.Context, userID int, item string, quantity int) (*storage.Order, error)
	Delete(ctx context.Context, id int) error
}

type OrderHandler struct {
	base
	store OrderStore
}

func NewOrderHandler(store OrderStore, log *slog.Logger, limits Limits) *OrderHandler {
	return &OrderHandler{base: base{log: log, limits: limits}, store: store}
}

type orderInput struct {
	UserID   int    `json:"user_id"`
	Item     string `json:"item"`
	Quantity int    `json:"quantity"`
}

// List supports an optional filter: GET /orders?user_id=3
func (h *OrderHandler) List(w http.ResponseWriter, r *http.Request) {
	userID := 0
	if v := r.URL.Query().Get("user_id"); v != "" {
		var err error
		if userID, err = strconv.Atoi(v); err != nil || userID <= 0 {
			h.writeError(w, r, http.StatusBadRequest, "invalid user_id")
			return
		}
	}
	orders, err := h.store.List(r.Context(), userID)
	if err != nil {
		h.internalError(w, r, "list orders", err)
		return
	}
	h.writeJSON(w, r, http.StatusOK, orders)
}

func (h *OrderHandler) Get(w http.ResponseWriter, r *http.Request) {
	id, ok := h.parseID(w, r)
	if !ok {
		return
	}
	o, err := h.store.Get(r.Context(), id)
	if err != nil {
		h.storeError(w, r, "get order", err)
		return
	}
	h.writeJSON(w, r, http.StatusOK, o)
}

func (h *OrderHandler) Create(w http.ResponseWriter, r *http.Request) {
	var in orderInput
	if !h.decodeJSON(w, r, &in) {
		return
	}
	in.Item = strings.TrimSpace(in.Item)
	switch {
	case in.UserID <= 0:
		h.writeError(w, r, http.StatusUnprocessableEntity, "user_id is required")
		return
	case in.Item == "" || utf8.RuneCountInString(in.Item) > h.limits.MaxNameLen:
		h.writeError(w, r, http.StatusUnprocessableEntity, fmt.Sprintf("item is required and at most %d characters", h.limits.MaxNameLen))
		return
	case in.Quantity < 1 || in.Quantity > maxQuantity:
		h.writeError(w, r, http.StatusUnprocessableEntity, fmt.Sprintf("quantity must be between 1 and %d", maxQuantity))
		return
	}

	o, err := h.store.Create(r.Context(), in.UserID, in.Item, in.Quantity)
	if errors.Is(err, storage.ErrUserNotFound) {
		// The URL itself is valid; the *body* references something that
		// doesn't exist, so 422 is more accurate than 404.
		h.writeError(w, r, http.StatusUnprocessableEntity, "user_id does not exist")
		return
	}
	if err != nil {
		h.internalError(w, r, "create order", err)
		return
	}
	h.writeJSON(w, r, http.StatusCreated, o)
}

func (h *OrderHandler) Delete(w http.ResponseWriter, r *http.Request) {
	id, ok := h.parseID(w, r)
	if !ok {
		return
	}
	if err := h.store.Delete(r.Context(), id); err != nil {
		h.storeError(w, r, "delete order", err)
		return
	}
	w.WriteHeader(http.StatusNoContent)
}

Create internal/handlers/users.go

package handlers

import (
	"context"
	"fmt"
	"log/slog"
	"net/http"
	"strings"
	"unicode/utf8"

	"go-rest/internal/storage"
)

// UserStore is the behaviour the handlers need, declared *here, where it is
// used* ("accept interfaces, return structs"), so handlers can be tested with
// a fake store. *storage.UserStore satisfies it implicitly.
type UserStore interface {
	GetAll(ctx context.Context) ([]*storage.User, error)
	Get(ctx context.Context, id int) (*storage.User, error)
	Create(ctx context.Context, name string) (*storage.User, error)
	Update(ctx context.Context, id int, name string) (*storage.User, error)
	Delete(ctx context.Context, id int) error
}

type UserHandler struct {
	base
	store UserStore
}

func NewUserHandler(store UserStore, log *slog.Logger, limits Limits) *UserHandler {
	return &UserHandler{base: base{log: log, limits: limits}, store: store}
}

// userInput is the request-body shape. A separate type (instead of decoding
// straight into storage.User) means a client-supplied "id" can never leak
// through: the server owns IDs.
type userInput struct {
	Name string `json:"name"`
}

func (h *UserHandler) List(w http.ResponseWriter, r *http.Request) {
	users, err := h.store.GetAll(r.Context())
	if err != nil {
		h.internalError(w, r, "list users", err)
		return
	}
	h.writeJSON(w, r, http.StatusOK, users)
}

func (h *UserHandler) Get(w http.ResponseWriter, r *http.Request) {
	id, ok := h.parseID(w, r)
	if !ok {
		return
	}
	user, err := h.store.Get(r.Context(), id)
	if err != nil {
		h.storeError(w, r, "get user", err)
		return
	}
	h.writeJSON(w, r, http.StatusOK, user)
}

func (h *UserHandler) Create(w http.ResponseWriter, r *http.Request) {
	in, ok := h.decodeUser(w, r)
	if !ok {
		return
	}
	created, err := h.store.Create(r.Context(), in.Name)
	if err != nil {
		h.internalError(w, r, "create user", err)
		return
	}
	h.writeJSON(w, r, http.StatusCreated, created)
}

func (h *UserHandler) Update(w http.ResponseWriter, r *http.Request) {
	id, ok := h.parseID(w, r)
	if !ok {
		return
	}
	in, ok := h.decodeUser(w, r)
	if !ok {
		return
	}
	updated, err := h.store.Update(r.Context(), id, in.Name)
	if err != nil {
		h.storeError(w, r, "update user", err)
		return
	}
	h.writeJSON(w, r, http.StatusOK, updated)
}

func (h *UserHandler) Delete(w http.ResponseWriter, r *http.Request) {
	id, ok := h.parseID(w, r)
	if !ok {
		return
	}
	if err := h.store.Delete(r.Context(), id); err != nil {
		h.storeError(w, r, "delete user", err)
		return
	}
	// 204 No Content is the REST convention for a successful delete.
	w.WriteHeader(http.StatusNoContent)
}

func (h *UserHandler) decodeUser(w http.ResponseWriter, r *http.Request) (userInput, bool) {
	var in userInput
	if !h.decodeJSON(w, r, &in) {
		return in, false
	}
	in.Name = strings.TrimSpace(in.Name)
	switch {
	case in.Name == "":
		h.writeError(w, r, http.StatusUnprocessableEntity, "name is required")
		return in, false
	case utf8.RuneCountInString(in.Name) > h.limits.MaxNameLen:
		h.writeError(w, r, http.StatusUnprocessableEntity, fmt.Sprintf("name must be at most %d characters", h.limits.MaxNameLen))
		return in, false
	}
	return in, true
}

Create internal/storage/orders.go

package storage

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"time"
)

// ErrUserNotFound is distinct from ErrNotFound: it means "the order's owner
// doesn't exist", which handlers report differently from "order not found".
var ErrUserNotFound = errors.New("referenced user not found")

type Order struct {
	ID        int       `json:"id"`
	UserID    int       `json:"user_id"`
	Item      string    `json:"item"`
	Quantity  int       `json:"quantity"`
	CreatedAt time.Time `json:"created_at"`
}

type OrderStore struct {
	db *sql.DB
}

func NewOrderStore(db *sql.DB) *OrderStore {
	return &OrderStore{db: db}
}

const orderCols = "id, user_id, item, quantity, created_at"

// scanner is satisfied by both *sql.Row and *sql.Rows, so one scan function
// serves single-row and list queries.
type scanner interface{ Scan(dest ...any) error }

func scanOrder(s scanner) (*Order, error) {
	o := &Order{}
	var created string
	if err := s.Scan(&o.ID, &o.UserID, &o.Item, &o.Quantity, &created); err != nil {
		return nil, err
	}
	t, err := time.Parse(time.RFC3339, created)
	if err != nil {
		return nil, fmt.Errorf("parse created_at %q: %w", created, err)
	}
	o.CreatedAt = t
	return o, nil
}

// List returns all orders, or only one user's when userID > 0.
func (s *OrderStore) List(ctx context.Context, userID int) ([]*Order, error) {
	// "? = 0 OR user_id = ?" keeps ONE static query for both cases; values are
	// always bound as parameters, never concatenated into SQL (no injection).
	rows, err := s.db.QueryContext(ctx,
		"SELECT "+orderCols+" FROM orders WHERE ? = 0 OR user_id = ? ORDER BY id",
		userID, userID)
	if err != nil {
		return nil, fmt.Errorf("query orders: %w", err)
	}
	defer rows.Close()

	orders := make([]*Order, 0)
	for rows.Next() {
		o, err := scanOrder(rows)
		if err != nil {
			return nil, fmt.Errorf("scan order: %w", err)
		}
		orders = append(orders, o)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate orders: %w", err)
	}
	return orders, nil
}

func (s *OrderStore) Get(ctx context.Context, id int) (*Order, error) {
	o, err := scanOrder(s.db.QueryRowContext(ctx,
		"SELECT "+orderCols+" FROM orders WHERE id = ?", id))
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("get order %d: %w", id, err)
	}
	return o, nil
}

// Create inserts only if the user exists, in ONE atomic statement
// (INSERT ... SELECT ... WHERE EXISTS). A separate "SELECT user, then INSERT"
// would have a race: the user could be deleted in between. Zero rows inserted
// surfaces as sql.ErrNoRows from RETURNING. The FOREIGN KEY on the table stays
// as a backstop.
func (s *OrderStore) Create(ctx context.Context, userID int, item string, quantity int) (*Order, error) {
	o, err := scanOrder(s.db.QueryRowContext(ctx, `
		INSERT INTO orders (user_id, item, quantity)
		SELECT ?, ?, ? WHERE EXISTS (SELECT 1 FROM users WHERE id = ?)
		RETURNING `+orderCols,
		userID, item, quantity, userID))
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrUserNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("insert order: %w", err)
	}
	return o, nil
}

func (s *OrderStore) Delete(ctx context.Context, id int) error {
	res, err := s.db.ExecContext(ctx, "DELETE FROM orders WHERE id = ?", id)
	if err != nil {
		return fmt.Errorf("delete order %d: %w", id, err)
	}
	n, err := res.RowsAffected()
	if err != nil {
		return fmt.Errorf("rows affected: %w", err)
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

Change internal/storage/storage.go

--- a/internal/storage/storage.go
+++ b/internal/storage/storage.go
@@ -13,12 +13,12 @@ import (
 // ErrNotFound is a *sentinel error*: a package-level value callers compare
 // against with errors.Is.
 //
-// Why not errors.New("user not found") inline and compare err.Error()?
+// Why not errors.New("not found") inline and compare err.Error()?
 // String comparison is brittle: reword the message, or wrap the error with
 // fmt.Errorf("...: %w", err), and the comparison silently stops matching, so a
 // 404 turns into a 500. errors.Is walks the whole wrap chain, so it keeps
 // working however many layers add context.
-var ErrNotFound = errors.New("user not found")
+var ErrNotFound = errors.New("not found")
 
 type User struct {
 	ID   int    `json:"id"`
@@ -78,7 +78,19 @@ func InitDB(ctx context.Context, path string) (*sql.DB, error) {
 		CREATE TABLE IF NOT EXISTS users (
 			id   INTEGER PRIMARY KEY AUTOINCREMENT,
 			name TEXT NOT NULL CHECK (length(trim(name)) > 0)
-		);`
+		);
+
+		-- ON DELETE CASCADE: deleting a user removes their orders (enforced
+		-- because foreign_keys(1) is on). The alternative, RESTRICT, would
+		-- instead refuse to delete a user who still has orders.
+		CREATE TABLE IF NOT EXISTS orders (
+			id         INTEGER PRIMARY KEY AUTOINCREMENT,
+			user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+			item       TEXT    NOT NULL CHECK (length(trim(item)) > 0),
+			quantity   INTEGER NOT NULL CHECK (quantity > 0),
+			created_at TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
+		);
+		CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);`
 	// The CHECK is defence in depth: handlers validate input, but the database
 	// is the last line of defence against empty names from any other code path.
 
@@ -176,3 +188,22 @@ func (s *UserStore) Delete(ctx context.Context, id int) error {
 	}
 	return nil
 }
+
+func (s *UserStore) Get(ctx context.Context, id int) (*User, error) {
+	u := &User{}
+
+	err := s.db.QueryRowContext(
+		ctx,
+		"SELECT id, name FROM users WHERE id = ?",
+		id,
+	).Scan(&u.ID, &u.Name)
+
+	if err != nil {
+		if errors.Is(err, sql.ErrNoRows) {
+			return nil, ErrNotFound
+		}
+		return nil, fmt.Errorf("query user: %w", err)
+	}
+
+	return u, nil
+}

Change cmd/rest/main.go

--- a/cmd/rest/main.go
+++ b/cmd/rest/main.go
@@ -45,12 +45,14 @@ func run() error {
 	}
 	defer db.Close()
 
-	h := handlers.NewHandler(storage.NewUserStore(db), log, handlers.Limits{
+	limits := handlers.Limits{
 		MaxBodyBytes: cfg.MaxBodyBytes,
 		MaxNameLen:   cfg.MaxNameLen,
-	})
+	}
+	users := handlers.NewUserHandler(storage.NewUserStore(db), log, limits)
+	orders := handlers.NewOrderHandler(storage.NewOrderStore(db), log, limits)
 	mux := http.NewServeMux()
-	routes.SetupRoutes(mux, h)
+	routes.SetupRoutes(mux, users, orders)
 
 	srv := &http.Server{
 		Addr: cfg.Addr,

Try it

$ curl -XPOST localhost:7800/users -d '{"name":"Ann"}'
{"id":1,"name":"Ann"}

$ curl -XPOST localhost:7800/orders -d '{"user_id":1,"item":"book","quantity":2}'
{"id":1,"user_id":1,"item":"book","quantity":2,"created_at":"2026-10-18T06:43:25Z"}

$ curl -i -XPOST localhost:7800/orders -d '{"user_id":999,"item":"book","quantity":2}'
HTTP/1.1 422 Unprocessable Entity
{"error":"user_id does not exist"}

$ curl -i -XPOST localhost:7800/orders -d '{"user_id":1,"item":"book","quantity":0}'
HTTP/1.1 422 Unprocessable Entity
{"error":"quantity must be between 1 and 10000"}

$ curl "localhost:7800/orders?user_id=1"
[{"id":1,"user_id":1,"item":"book","quantity":2,"created_at":"2026-10-18T06:43:25Z"}]

And the cascade:

$ curl -XDELETE localhost:7800/users/1 -o /dev/null -w "%{http_code}\n"
204
$ curl -i localhost:7800/orders/1
HTTP/1.1 404 Not Found
{"error":"not found"}

Deleting the user removed the order, without a line of cascading code in Go. The database did it.

One small API detail

The generic ErrNotFound message became just "not found", because that error is now shared by users and orders. An error value that belongs to every resource should not name one.

Recap

Extract shared plumbing into an embedded base; keep one handler type per resource.
Each consumer declares the small interface it needs.
Put integrity rules in the database (foreign keys, CHECKs), not only in code.
Replace check-then-act with one atomic statement.
Pick status codes by what is wrong: bad URL (404) vs bad body field (422).

Exercises

Add PUT /orders/{id} to change the quantity. Which parts of the code do you have to touch? (Count them: that number is the cost of a new endpoint in this structure.)
Change the foreign key to ON DELETE RESTRICT. What error does SQLite return when you delete a user with orders? Map it to 409 Conflict.
Drop the foreign_keys(1) pragma and re-run the cascade demo. What happens, and why is it dangerous that it fails silently?

Part 5: A second resource: structure, relations and cascades

The new API

Step 2: routes as a table of contents

Step 3: a relationship in the schema

Step 4: the store, and an atomic insert

Step 5: choosing the right status code

Complete code for this part

Try it

One small API detail

Recap

Exercises

Comments

Build a production-grade REST API in Go

More from this blog

Part 17: Wrap-up: the checklist, what we skipped, and where to go next

Part 16: OpenAPI: a contract that can't drift, and a docs page you can trust

Part 15: Idempotency keys: making retries safe

Part 14: TLS: encrypting the wire, hot-reloading certificates, and verifying the database

Part 13: Protecting the login endpoint: layered rate limiting

The new API

Step 1: split the handlers, share the plumbing

Step 2: routes as a table of contents

Step 3: a relationship in the schema

Step 4: the store, and an atomic insert

Step 5: choosing the right status code

Complete code for this part

Try it

One small API detail

Recap

Exercises

Comments

Build a production-grade REST API in Go

More from this blog

Part 17: Wrap-up: the checklist, what we skipped, and where to go next

Part 16: OpenAPI: a contract that can't drift, and a docs page you can trust

Part 15: Idempotency keys: making retries safe

Part 14: TLS: encrypting the wire, hot-reloading certificates, and verifying the database

Part 13: Protecting the login endpoint: layered rate limiting