Capstone: A Blog API

Prisma Fundamentals

Chapter 10 · Capstone: The Data Layer for a Small Blog API

This chapter brings the whole course together in one small working application: a JSON API for the blog, built with Express and Prisma. It uses the final schema, every kind of query from Chapters 5–9, and proper handling of Prisma's errors. Each section names the chapter it comes from.

Step 1: The Final Schema (Chapters 3, 7, 8)

generator client {
  provider = "prisma-client"
  output   = "../generated/prisma"
}

datasource db {
  provider = "sqlite"
}

enum Role {
  READER
  AUTHOR
  ADMIN
}

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String?
  role      Role      @default(READER)
  profile   Profile?
  posts     Post[]
  comments  Comment[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  userId Int     @unique
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Post {
  id          Int       @id @default(autoincrement())
  title       String
  slug        String    @unique
  content     String?
  published   Boolean   @default(false)
  viewCount   Int       @default(0)
  publishedAt DateTime?
  authorId    Int
  author      User      @relation(fields: [authorId], references: [id])
  tags        Tag[]
  comments    Comment[]
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  @@index([authorId])
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

model Comment {
  id        Int      @id @default(autoincrement())
  body      String
  createdAt DateTime @default(now())
  postId    Int
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  authorId  Int?
  author    User?    @relation(fields: [authorId], references: [id], onDelete: SetNull)

  @@index([postId])
  @@index([authorId])
}

npx prisma migrate dev --name capstone      # Chapter 4
npx prisma generate
npm install express@5
npm install --save-dev @types/express

Step 2: One Shared Client (Chapters 2, 9)

// lib/prisma.ts
import "dotenv/config";
import { PrismaBetterSqlite3 } from "@prisma/adapter-better-sqlite3";
import { PrismaClient } from "../generated/prisma/client";

const adapter = new PrismaBetterSqlite3({ url: process.env.DATABASE_URL! });

export const prisma = new PrismaClient({
  adapter,
  log: process.env.NODE_ENV === "development" ? ["query"] : [],
});

Step 3: Listing and Searching Posts (Chapters 6, 9)

// server.ts
import express from "express";
import { prisma } from "./lib/prisma";
import { Prisma } from "./generated/prisma/client";

const app = express();
app.use(express.json());
const PAGE_SIZE = 10;

// A reusable "public" shape for posts: never the whole author record
const postSummary = {
  title: true, slug: true, publishedAt: true, viewCount: true,
  author: { select: { name: true } },
  tags:   { select: { name: true } },
} satisfies Prisma.PostSelect;

// GET /posts?q=prisma&tag=orm&page=2
app.get("/posts", async (req, res) => {
  const q    = String(req.query.q ?? "").trim();
  const tag  = String(req.query.tag ?? "").trim();
  const page = Math.max(1, Number(req.query.page) || 1);

  const where: Prisma.PostWhereInput = { published: true };
  if (q)   where.OR   = [{ title: { contains: q } }, { content: { contains: q } }];
  if (tag) where.tags = { some: { name: tag } };

  const [posts, total] = await Promise.all([
    prisma.post.findMany({
      where,
      select: postSummary,
      orderBy: [{ publishedAt: "desc" }, { id: "desc" }],
      skip: (page - 1) * PAGE_SIZE,
      take: PAGE_SIZE,
    }),
    prisma.post.count({ where }),
  ]);

  res.json({ posts, page, totalPages: Math.ceil(total / PAGE_SIZE) });
});

What this route gets right

Search and tag filters are only added when present, so an empty query can't drop the published rule (Chapter 6). select keeps author emails out of the response (Chapter 9), and the authors and tags are loaded with the list, so there's no N+1 problem. The same where feeds both the page and the count.

Step 4: One Post, With Comments (Chapters 5, 9)

app.get("/posts/:slug", async (req, res) => {
  const post = await prisma.post.findFirst({
    where: { slug: req.params.slug, published: true },
    select: {
      ...postSummary,
      content: true,
      comments: {
        orderBy: { createdAt: "asc" },
        take: 50,
        select: { body: true, createdAt: true, author: { select: { name: true } } },
      },
    },
  });
  if (!post) return res.status(404).json({ error: "Post not found" });
  res.json(post);
});

findFirst is used rather than findUnique because the condition includes published, which isn't unique: a draft with this slug should look like it doesn't exist.

Step 5: Writing Data (Chapters 5, 8)

// POST /posts  { "title", "slug", "content", "authorEmail", "tags": ["prisma"] }
app.post("/posts", async (req, res) => {
  const { title, slug, content, authorEmail, tags = [] } = req.body;
  if (!title || !slug || !authorEmail) {
    return res.status(400).json({ error: "title, slug and authorEmail are required" });
  }

  const post = await prisma.post.create({
    data: {
      title, slug, content,
      author: { connect: { email: authorEmail } },
      tags: {
        connectOrCreate: [...new Set<string>(tags)].map((name) => ({
          where: { name }, create: { name },
        })),
      },
    },
    select: postSummary,
  });
  res.status(201).json(post);
});

// POST /posts/:slug/publish
app.post("/posts/:slug/publish", async (req, res) => {
  const post = await prisma.post.update({
    where: { slug: req.params.slug },
    data:  { published: true, publishedAt: new Date() },
    select: postSummary,
  });
  res.json(post);
});

// POST /posts/:slug/view -- safe under concurrent requests
app.post("/posts/:slug/view", async (req, res) => {
  const { viewCount } = await prisma.post.update({
    where: { slug: req.params.slug },
    data:  { viewCount: { increment: 1 } },
    select: { viewCount: true },
  });
  res.json({ viewCount });
});

Step 6: Turning Prisma Errors Into HTTP Responses (Chapters 5, 7)

Express 5 passes errors from async route handlers to the error handler automatically, so one function can translate Prisma's error codes for every route:

app.use((err: unknown, req: express.Request, res: express.Response, next: express.NextFunction) => {
  if (err instanceof Prisma.PrismaClientKnownRequestError) {
    if (err.code === "P2002") return res.status(409).json({ error: "That value is already taken" });
    if (err.code === "P2025") return res.status(404).json({ error: "Not found" });
    if (err.code === "P2003") return res.status(409).json({ error: "Other records still depend on this one" });
  }
  console.error(err);
  res.status(500).json({ error: "Something went wrong" });
});

const server = app.listen(3000, () => console.log("Blog API on http://localhost:3000"));

// Close the database connection cleanly on shutdown
process.on("SIGTERM", () => server.close(() => prisma.$disconnect()));

Request	Prisma result	HTTP response
Create a post with a slug that exists	`P2002` unique constraint	409 Conflict
Publish or view a slug that doesn't exist	`P2025` record not found	404 Not Found
Create a post for an unknown author email	`connect` finds no user (`P2025`)	404 Not Found
Delete a user who still has posts	Restricted by the foreign key (`P2003`)	409 Conflict

Trying It Out

npx tsx server.ts

# in another terminal
curl -X POST localhost:3000/posts -H "Content-Type: application/json" \
  -d '{"title":"Hello API","slug":"hello-api","authorEmail":"alan@example.com","tags":["prisma","api"]}'
curl -X POST localhost:3000/posts/hello-api/publish
curl "localhost:3000/posts?tag=prisma"
curl localhost:3000/posts/hello-api

Where Each Part Came From

Chapter	Used in this capstone
1 — Where Prisma Fits	The reason for a typed, schema-first data layer
2 — Project Setup	The Prisma 7 client with a driver adapter, in one shared file
3 — The Schema	Models, enum, defaults, `@unique`, `@updatedAt`
4 — Migrate	Applying the final schema, then `generate`
5 — CRUD	`create`, `update`, `increment`, error codes
6 — Filtering & Pagination	Search, `OR`, safe optional filters, sorted offset paging
7 — Relations I	Author, profile and comment relations; `Restrict`, `Cascade`, `SetNull`
8 — Relations II	Tags, `connect` and `connectOrCreate` nested writes
9 — Reading Related Data	Nested `select`, relation filters, no N+1, query logging

What's Deliberately Missing

Authentication and permissions — anyone can create or publish posts. A real API must check who's asking.
Input validation beyond basic checks — a library such as Zod would validate request bodies properly.
Transactions, aggregation, raw SQL, testing, performance and production migrations — the subjects of Prisma Intermediate/Advanced, which starts where this course ends.

Hands-On Exercises

Exercise 1

Add POST /posts/:slug/comments, which lets a guest or a registered user (by optional email) add a comment to a published post. Return 404 for missing or unpublished posts.

📄 View solution

Exercise 2

Add GET /authors/:email, returning the author's name, bio and their five most-viewed published posts, without exposing their email in the response body.

📄 View solution

Exercise 3

Add PUT /posts/:slug/tags, which replaces a post's tags with a given list, and GET /tags/unused, which lists tags on no posts. Test both with curl.

📄 View solution

Course Quick Reference

Setup: pin @7; generator prisma-client with output; driver adapter; prisma7.config.ts with dotenv
Schema: models, types, ?, @id, @default, @unique, @updatedAt, @map, enums, @@id/@@unique/@@index
Migrate: migrate dev --name then generate; --create-only to edit; migrate deploy in production
CRUD: create, findUnique/findFirst/findMany, update, upsert, delete, *Many, increment
Queries: operators, AND/OR/NOT, case sensitivity by database, orderBy, offset and cursor paging
Relations: @relation(fields, references), one-to-one via @unique, implicit and explicit many-to-many, onDelete
Nested writes: create, connect, connectOrCreate, disconnect, set
Reading: include vs select, some/every/none, avoid N+1, log: ["query"]
Errors: P2002 duplicate, P2025 not found, P2003 foreign key

Course Complete

This completes Prisma Fundamentals, 10/10 chapters. Next: Prisma Intermediate/Advanced.