Project Setup

The domain

A book catalog API: books, authors, reviews, search. Read-heavy — listing books, viewing details, searching, and browsing reviews happen far more often than creating books or posting reviews. This is the ideal domain for caching: many reads, few writes, and the same data served to many users.

Create the project

mkdir cached-catalog
cd cached-catalog
npm init -y
npm install @hectoday/http zod srvx better-sqlite3
npm install -D typescript @types/node @types/better-sqlite3 tsx

Create tsconfig.json:

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "ES2022",
    "moduleResolution": "bundler",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "rootDir": "./src",
    "outDir": "dist",
    "types": ["node"]
  },
  "include": ["src"]
}

Add "type": "module" and a dev script to package.json:

{
  "type": "module",
  "scripts": {
    "dev": "tsx watch src/server.ts"
  }
}

To start the server:

npm run dev

The database

// src/db.ts
import Database from "better-sqlite3";

const db = new Database("catalog.db");
db.pragma("journal_mode = WAL");
db.pragma("foreign_keys = ON");
db.pragma("busy_timeout = 5000");

db.exec(`
  CREATE TABLE IF NOT EXISTS authors (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    bio TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
  );

  CREATE TABLE IF NOT EXISTS books (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    author_id TEXT NOT NULL,
    genre TEXT NOT NULL,
    description TEXT,
    published_at TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (author_id) REFERENCES authors(id)
  );

  CREATE TABLE IF NOT EXISTS reviews (
    id TEXT PRIMARY KEY,
    book_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    body TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (book_id) REFERENCES books(id),
    UNIQUE (book_id, user_id)
  );

  CREATE INDEX IF NOT EXISTS idx_books_author ON books(author_id);
  CREATE INDEX IF NOT EXISTS idx_books_genre ON books(genre);
  CREATE INDEX IF NOT EXISTS idx_reviews_book ON reviews(book_id);
`);

// Seed data
const authorCount = (db.prepare("SELECT COUNT(*) AS c FROM authors").get() as any).c;
if (authorCount === 0) {
  const authors = [
    ["author-1", "Ursula K. Le Guin", "American novelist"],
    ["author-2", "Jorge Luis Borges", "Argentine writer"],
    ["author-3", "Octavia Butler", "Science fiction author"],
  ];
  for (const [id, name, bio] of authors) {
    db.prepare("INSERT INTO authors (id, name, bio) VALUES (?, ?, ?)").run(id, name, bio);
  }

  const books = [
    ["book-1", "The Left Hand of Darkness", "author-1", "science-fiction"],
    ["book-2", "A Wizard of Earthsea", "author-1", "fantasy"],
    ["book-3", "Ficciones", "author-2", "fiction"],
    ["book-4", "Kindred", "author-3", "science-fiction"],
    ["book-5", "Parable of the Sower", "author-3", "science-fiction"],
  ];
  for (const [id, title, authorId, genre] of books) {
    db.prepare("INSERT INTO books (id, title, author_id, genre) VALUES (?, ?, ?, ?)").run(
      id,
      title,
      authorId,
      genre,
    );
  }

  const reviews = [
    ["rev-1", "book-1", "user-1", 5],
    ["rev-2", "book-1", "user-2", 4],
    ["rev-3", "book-2", "user-1", 5],
    ["rev-4", "book-3", "user-2", 5],
    ["rev-5", "book-4", "user-1", 4],
    ["rev-6", "book-4", "user-3", 5],
    ["rev-7", "book-5", "user-2", 4],
  ];
  for (const [id, bookId, userId, rating] of reviews) {
    db.prepare("INSERT INTO reviews (id, book_id, user_id, rating) VALUES (?, ?, ?, ?)").run(
      id,
      bookId,
      userId,
      rating,
    );
  }
}

export default db;

[!NOTE] The schema uses patterns from the Database Design course: TEXT primary keys (UUIDs), foreign keys with indexes, CHECK constraints on ratings, and a UNIQUE constraint preventing duplicate reviews.

The app

// src/app.ts
import { setup, route } from "@hectoday/http";
import db from "./db.js";

export const app = setup({
  routes: [
    route.get("/health", { resolve: () => Response.json({ status: "ok" }) }),

    route.get("/books", {
      resolve: () => {
        const books = db
          .prepare(
            `
          SELECT books.*, authors.name AS author_name
          FROM books JOIN authors ON books.author_id = authors.id
          ORDER BY books.title
        `,
          )
          .all();
        return Response.json(books);
      },
    }),

    route.get("/books/top", {
      resolve: () => {
        const books = db
          .prepare(
            `
          SELECT books.id, books.title, authors.name AS author_name,
                 AVG(reviews.rating) AS avg_rating, COUNT(reviews.id) AS review_count
          FROM books
          JOIN authors ON books.author_id = authors.id
          JOIN reviews ON reviews.book_id = books.id
          GROUP BY books.id
          ORDER BY avg_rating DESC, review_count DESC
          LIMIT 10
        `,
          )
          .all();
        return Response.json(books);
      },
    }),

    route.get("/books/:id", {
      resolve: (c) => {
        const book = db
          .prepare(
            `
          SELECT books.*, authors.name AS author_name,
                 (SELECT AVG(rating) FROM reviews WHERE book_id = books.id) AS avg_rating,
                 (SELECT COUNT(*) FROM reviews WHERE book_id = books.id) AS review_count
          FROM books
          JOIN authors ON books.author_id = authors.id
          WHERE books.id = ?
        `,
          )
          .get(c.input.params.id);
        if (!book) return Response.json({ error: "Not found" }, { status: 404 });
        return Response.json(book);
      },
    }),
  ],
});

// src/server.ts
import { serve } from "srvx";
import { app } from "./app.js";
serve({ fetch: app.fetch, port: 3000 });

Every endpoint queries the database on every request. By the end of this course, each endpoint will have appropriate caching.

Exercises

Exercise 1: Start the server. Call /books/top 10 times with timing. Each call runs the full query.

Exercise 2: Call /books/:id for the same book 10 times. The same JOIN + subqueries run every time.

Exercise 3: Identify which endpoints return the same data for all users vs user-specific data.