Data Modeling with Prisma: schema.prisma & Prisma Migrate

Food Tracker (React + Express + Prisma)

Chapter 2 · Data Modeling with Prisma: schema.prisma & Prisma Migrate

Every Food Tracker course stores the same shape of data. Food Tracker (React + Express) reaches for the plainest possible way to store it — raw SQL, no ORM at all. This chapter is where that choice actually diverges: the same schema, expressed instead as a Prisma model, migrated with Prisma Migrate, and queried through Prisma Client's generated methods.

The Shared Pantry Item Schema, in Prisma

The same fields the sibling course wrote as a raw CREATE TABLE statement, expressed here as a Prisma model:

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

model Item {
  id         Int       @id @default(autoincrement())
  name       String
  barcode    String?
  category   String?
  expiryDate DateTime?
  status     String    @default("active")
  addedAt    DateTime  @default(now())
  usedAt     DateTime?
}

Every design decision from the sibling course's own Chapter 2 still applies exactly as reasoned there: expiryDate is nullable — null once an item is marked used, not deleted — and the row itself is never removed, so the combined history list (Chapter 7) always has something to show. addedAt defaults to the current time at insert via @default(now()), the direct Prisma equivalent of the sibling's own DEFAULT (datetime('now')) — the database layer stamps it either way, so the client is never trusted to supply an honest timestamp.

Raw SQL column (sibling course)	Prisma field (this course)	What actually changed
id INTEGER PRIMARY KEY AUTOINCREMENT	id Int @id @default(autoincrement())	Same behavior — a type plus attributes, not a full DDL clause
name TEXT NOT NULL	name String	Non-nullable is the default for a plain type in Prisma; nullability is opt-in, not opt-out
barcode TEXT (nullable)	barcode String?	Nullability is the `?` on the type itself
expiry_date TEXT (nullable)	expiryDate DateTime?	A genuine type change — a real `DateTime`, not a formatted text string. Chapter 6 revisits exactly this distinction.
status TEXT NOT NULL DEFAULT 'active'	status String @default("active")	Same behavior
added_at TEXT NOT NULL DEFAULT (datetime('now'))	addedAt DateTime @default(now())	Same DB-stamped-timestamp idea; again, a real `DateTime` instead of a formatted string
used_at TEXT (nullable)	usedAt DateTime?	Same typed-vs-text distinction as `expiryDate`

A quieter change worth noticing: field naming

Prisma's own convention is camelCase field names (expiryDate, addedAt, usedAt), not the sibling course's snake_case column names (expiry_date, added_at, used_at). That's not a cosmetic detail — it means every JSON response this course's API sends back uses different field names than the sibling's, and the React frontend has to match whichever backend it's actually talking to. A small, easy-to-miss consequence of adopting Prisma's own idiomatic style rather than mirroring the sibling's naming exactly.

Why Prisma Here

The sibling course's own reasoning still holds: Node has no single dominant ORM the way Django has its own built-in one — Prisma, Sequelize, Drizzle, and Knex all have real adoption, with no one of them as close to "the obvious choice" as Django's ORM is for Django. Prisma is used here specifically because it's the most widely adopted schema-first option in the current Node/TypeScript ecosystem, and because it's the same tool Website Rebuild with Next.js already uses elsewhere on this site — a genuine consistency benefit, not just a preference.

// db.js
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

export default prisma;

Applying the schema above means running an actual migration, not hand-writing an ALTER TABLE statement:

npx prisma migrate dev --name init

This creates a real, timestamped folder under prisma/migrations/ containing the generated SQL for that specific change, applies it to the local dev.db file, records that it's been applied, and regenerates Prisma Client so its query methods match the schema exactly. Every future schema change — adding a field, changing a type — goes through the same command again, each one producing its own new migration file.

The real cost of Prisma's own schema-first workflow

Editing schema.prisma alone changes nothing — the migration has to actually run before Prisma Client's generated methods reflect the new shape, and forgetting that step is a genuinely common source of confusing "field doesn't exist" errors that have nothing to do with the code being wrong. The sibling course has no equivalent extra step; a hand-written SQL query is correct or it isn't, with no separate generation stage in between.

Express Route Structure

The same route shape as the sibling course, rewritten against Prisma Client's generated, asynchronous methods:

// routes/items.js
import { Router } from "express";
import prisma from "../db.js";

const router = Router();

router.get("/", async (req, res) => {
  const items = await prisma.item.findMany({
    orderBy: { addedAt: "desc" }
  });
  res.json(items);
});

router.post("/", async (req, res) => {
  const { name, barcode, category, expiryDate } = req.body;
  const item = await prisma.item.create({
    data: { name, barcode, category, expiryDate }
  });
  res.status(201).json(item);
});

export default router;

// server/index.js (mounting the router — identical to the sibling course)
import itemsRouter from "./routes/items.js";
app.use("/api/items", itemsRouter);

Two concrete differences worth naming against the sibling's own version of the exact same route. First, both handlers are declared async, with await in front of every Prisma call — the sync-vs-async split Chapter 1 already covered. Second, the POST handler here responds with the entire created item, since create() returns the full row by default; the sibling's own better-sqlite3 version could only return { id: result.lastInsertRowid }, since a raw INSERT doesn't hand back the row it just created.

On SQL injection: the sibling course's protection comes from remembering to always use ? placeholders instead of string-concatenating values into a query — a real safeguard, but one the developer has to keep applying correctly every time. Prisma Client's query methods take structured objects, not raw query strings, so there's no string-concatenation path available through the normal API at all. The same protection exists, but here it's structural rather than a discipline to maintain.

This chapter's own honest tradeoff, stated plainly

Prisma trades the sibling course's total transparency — every query is exactly the SQL string sitting right there in the route handler — for real migrations, a generated query API, and SQL-injection protection that's structural rather than a habit to maintain. The cost, named honestly in the warn-box above: a schema change now requires an explicit migration step before it takes effect, where the sibling course's raw SQL takes effect the moment the query runs.

Where This Course Is Headed

Barcode lookup next, with a BarcodeCache model reusing this chapter's own schema pattern, then the camera-scanning React component — reused essentially unchanged from the sibling course, since it's frontend-only and never touches the database layer.

Hands-On Exercises

Exercise 1

Using this chapter's own compare-table, explain what actually changes about the expiryDate field going from the sibling course's TEXT column to this course's DateTime? field — and why that change matters for later chapters, not just this one.

📄 View solution

Exercise 2

Explain the field-naming difference between this course (expiryDate, addedAt) and the sibling course (expiry_date, added_at), and what concrete part of the app that difference actually affects.

📄 View solution

Exercise 3

Explain why the POST route in this chapter can respond with the full created item, while the sibling course's own POST route can only respond with an id — and name the real, concrete step (not present in the sibling course) that has to happen before a schema.prisma change actually takes effect.

📄 View solution

Chapter 2 Quick Reference

Schema: the same fields as every sibling course, defined as an Item model in schema.prisma — id, name, barcode, category, expiryDate, status, addedAt, usedAt
Real type change: expiryDate/usedAt/addedAt are genuine DateTime fields here, not formatted text strings — Chapter 6 builds directly on this
Naming: camelCase fields (Prisma's own convention) instead of the sibling's snake_case columns — a real difference in every JSON response
Migrations: npx prisma migrate dev --name init — versioned, tracked, real SQL generated automatically
Real cost: schema.prisma changes do nothing on their own until a migration actually runs
Routes: the same Router-per-resource pattern as the sibling, now async/await throughout
Security: SQL injection protection is structural here — Prisma Client's methods take structured data, not raw query strings
Next chapter: Barcode Lookup: Integrating Open Food Facts