Week 4: Prisma Schema, Migrations & Queries

1. Defining a Prisma Schema

Prisma is an ORM built around a single declarative file, schema.prisma, where you describe your database connection, which client to generate, and every model (table) in your database. Everything else — migrations, the type-safe client, even autocompletion in your editor — is generated from this one file, so it functions as the single source of truth for your data layer.

npm install prisma --save-dev
npm install @prisma/client
npx prisma init --datasource-provider postgresql

prisma init creates a prisma/schema.prisma file and a .env with a DATABASE_URL placeholder. A schema has three parts: a generator block (what to generate — almost always the JS/TS client), a datasource block (which database and how to connect), and one or more model blocks (your tables):

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        String   @id @default(uuid())
  title     String
  body      String
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Each field has a type (String, Boolean, DateTime, and more) plus optional attributes: @id marks the primary key, @default(uuid()) auto-generates a UUID on insert, @unique enforces a uniqueness constraint at the database level (not just in your application code), and @updatedAt automatically stamps the current time on every update. The posts Post[] field on User and the author/authorId pair on Post together define a one-to-many relation — you'll go much deeper on relation modeling in Week 5, but this week's models are enough to establish the pattern.

DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

2. Migrations with prisma migrate dev

A migration is a versioned, timestamped SQL file that describes exactly how to change the database schema from one state to the next. Rather than writing that SQL by hand, prisma migrate dev diffs your current schema.prisma against the migration history, generates the SQL to make the database match, applies it to your local dev database, and regenerates the Prisma client — all in one command.

npx prisma migrate dev --name init

# Prisma will:
# 1. Compare schema.prisma to the migration history
# 2. Generate prisma/migrations/<timestamp>_init/migration.sql
# 3. Apply it to the database configured in DATABASE_URL
# 4. Regenerate the type-safe client in node_modules/@prisma/client

-- CreateTable
CREATE TABLE "User" (
    "id" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

Every migration file gets committed to git — the prisma/migrations/ directory is your schema's changelog, and it's what lets a teammate (or a CI pipeline, or the production deploy in Week 14) reconstruct the exact same database structure by replaying every migration in order. When you change a model later — add a column, add an index — you edit schema.prisma and run prisma migrate dev --name add-post-tags again; Prisma diffs against what's already in prisma/migrations/ and generates only the incremental change, not a full rebuild.

# migrate dev is for local development only -- it can prompt interactively
# and, in some cases, ask to reset the database. In CI/CD and production,
# use the non-interactive deploy command instead:
npx prisma migrate deploy

3. The Generated Client & Basic CRUD

prisma generate (run automatically by migrate dev, and manually whenever you only change the schema without a full migration) produces a PrismaClient class with a method for every model, fully typed against your actual schema — autocomplete knows every field name and type, and passing the wrong shape is a compile error, not a runtime surprise.

import { PrismaClient } from "@prisma/client";

// A single shared instance for the whole app -- creating a new
// PrismaClient per request would exhaust the database's connection
// pool almost immediately.
export const prisma = new PrismaClient();

// Create
const user = await prisma.user.create({
  data: { email: "ada@example.com", name: "Ada Lovelace" },
});

// Read one
const found = await prisma.user.findUnique({
  where: { id: user.id },
});

// Read one, throwing if not found (useful paired with Week 3's NotFoundError)
const foundOrThrow = await prisma.user.findUniqueOrThrow({
  where: { id: user.id },
});

// Read many, with filtering, sorting, and pagination
const users = await prisma.user.findMany({
  where: { email: { contains: "@example.com" } },
  orderBy: { createdAt: "desc" },
  take: 20,
  skip: 0,
});

// Update
const updated = await prisma.user.update({
  where: { id: user.id },
  data: { name: "Ada King" },
});

// Delete
await prisma.user.delete({
  where: { id: user.id },
});

findUnique requires filtering on a field marked @id or @unique in the schema and returns null if nothing matches; findMany can filter on any field and always returns an array, empty if nothing matches. Reaching for findUniqueOrThrow when a missing record really is exceptional (rather than an expected empty state) saves you a manual if (!result) check — Prisma throws a PrismaClientKnownRequestError you can catch and translate into your Week 3 NotFoundError shape.

4. Connecting Express Routes to Real Persistence

With the client set up, swapping an Express route from an in-memory array to real persistence is mostly mechanical — the Router, validation, and centralized error handling from Weeks 2 and 3 stay exactly as they are; only the data-access line inside each handler changes.

import { Router } from "express";
import { Prisma } from "@prisma/client";
import { prisma } from "../lib/prisma.js";
import { validateBody } from "../middleware/validate.js";
import { createUserSchema, type CreateUserInput } from "../schemas/user.js";
import { NotFoundError, ConflictError } from "../lib/errors.js";
import { asyncHandler } from "../lib/async-handler.js";

const usersRouter = Router();

usersRouter.get("/", asyncHandler(async (req, res) => {
  const users = await prisma.user.findMany({ orderBy: { createdAt: "desc" } });
  res.json(users);
}));

usersRouter.get("/:id", asyncHandler(async (req, res) => {
  const user = await prisma.user.findUnique({ where: { id: req.params.id } });
  if (!user) throw new NotFoundError("User");
  res.json(user);
}));

usersRouter.post("/", validateBody(createUserSchema), asyncHandler(async (req, res) => {
  const input = req.body as CreateUserInput;

  try {
    const user = await prisma.user.create({ data: input });
    res.status(201).location(`/api/users/${user.id}`).json(user);
  } catch (err) {
    // Prisma's error code for a violated unique constraint
    if (err instanceof Prisma.PrismaClientKnownRequestError && err.code === "P2002") {
      throw new ConflictError("A user with that email already exists");
    }
    throw err;
  }
}));

export default usersRouter;

That P2002 code is Prisma's way of surfacing a database-level constraint violation (the @unique on email) as a catchable, typed error instead of a raw SQL exception — checking for it and translating it into Week 3's ConflictError is what makes a duplicate email return a clean 409 with your API's standard error shape, instead of an unhandled 500.

import { prisma } from "./lib/prisma.js";

// ... app setup, app.listen() ...

process.on("SIGTERM", async () => {
  await prisma.$disconnect();
  process.exit(0);
});

5. Hands-on Exercise

6. Knowledge Check

Four quick questions. Expand each to check your answer.