Data Modeling & Express API Routes

Food Tracker (React + Express)

Chapter 2 · Data Modeling & Express API Routes

Every course in this quartet stores the same shape of data. What genuinely differs here is deliberate: this course reaches for the plainest possible way to store it — no ORM at all, just SQL, written directly.

The Shared Pantry Item Schema

The same fields Food Tracker (Django) modeled with Django's ORM and Food Tracker (FastAPI) models with SQLAlchemy, expressed here as a plain SQL table:

-- schema.sql
CREATE TABLE IF NOT EXISTS items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  barcode TEXT,
  category TEXT,
  expiry_date TEXT,              -- nullable: NULL once used, or never set
  status TEXT NOT NULL DEFAULT 'active',
  added_at TEXT NOT NULL DEFAULT (datetime('now')),
  used_at TEXT
);

Every design decision from the Django course's own Chapter 2 still applies exactly as reasoned there: expiry_date is nullable — NULL once an item is marked used, not deleted — and the row itself never gets removed, so the combined history list (Chapter 8) always has something to show. added_at defaults to the current time at insert, mirroring Django's own auto_now_add — the database itself stamps it, so the client never has to be trusted to supply an honest timestamp.

Why No ORM Here — A Deliberate, Honest Choice

Python has one dominant default in each of this quartet's other two backend courses — Django's own built-in ORM, and SQLAlchemy for FastAPI. Node's own ecosystem has no single equivalent default; several exist (Prisma, Sequelize, Drizzle, Knex), each with real adoption but none as close to "the obvious choice" as Django's own ORM is for Django. Rather than pick one somewhat arbitrarily, this course uses better-sqlite3 directly — a genuine SQL library, not an ORM at all — and writes real SQL by hand throughout.

npm install better-sqlite3

// db.js
import Database from "better-sqlite3";
import fs from "fs";

const db = new Database("foodtracker.db");
db.exec(fs.readFileSync("./schema.sql", "utf8"));

export default db;

The real cost of skipping an ORM

Django's own migration system (makemigrations/migrate) tracks every model change as a versioned, reversible file. This course has none of that — changing the schema later means hand-writing an ALTER TABLE statement and running it manually, with no built-in history of what changed or when. For a single-table app at this course's own realistic scale, that's a reasonable tradeoff; it stops being one the moment the schema grows large or the team grows past one person.

better-sqlite3 is deliberately synchronous

Unlike most Node database drivers, better-sqlite3 has no async/await at all — db.prepare(sql).get() returns its result immediately, blocking the event loop for that one query. This is a real, intentional departure from Node's usual async-everything convention, justified by SQLite's own local-file nature (no network round-trip to wait on) and by the library's own measured performance advantage over async alternatives for this exact use case.

Express Route Structure

Routes live in their own module, mounted under a common prefix — the pattern every later chapter's own routes build on:

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

const router = Router();

router.get("/", (req, res) => {
  const items = db.prepare("SELECT * FROM items ORDER BY added_at DESC").all();
  res.json(items);
});

router.post("/", (req, res) => {
  const { name, barcode, category, expiry_date } = req.body;
  const result = db.prepare(
    "INSERT INTO items (name, barcode, category, expiry_date) VALUES (?, ?, ?, ?)"
  ).run(name, barcode, category, expiry_date);
  res.status(201).json({ id: result.lastInsertRowid });
});

export default router;

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

Note the parameterized ? placeholders in the INSERT — better-sqlite3 handles escaping automatically, the same real protection against SQL injection that an ORM would otherwise provide implicitly. Skipping an ORM does not mean skipping this protection; it just means it's the developer's own responsibility to always use placeholders rather than string-concatenating values into a query.

This chapter's own honest tradeoff, stated plainly

Writing raw SQL directly is more transparent — nothing is generated or hidden behind an abstraction layer — and avoids picking among several competing, less-dominant Node ORMs. The real cost is everything an ORM would otherwise give for free: schema migrations, a query builder, and object-relational mapping convenience. This course accepts that cost deliberately, at a scale small enough that it stays a reasonable one.

Where This Course Is Headed

Barcode lookup next, then the camera-scanning React component (shared almost verbatim with Food Tracker (React + Firebase)), building on this chapter's own routing pattern for every remaining feature.

Hands-On Exercises

Exercise 1

Explain why this course uses better-sqlite3 directly instead of an ORM, and name the one concrete capability this choice gives up compared to Django's own migration system.

📄 View solution

Exercise 2

Explain what makes better-sqlite3 unusual among Node database libraries, and why that unusual choice is still justified for this specific app.

📄 View solution

Exercise 3

Explain why the ? placeholders in the INSERT statement matter for security, and what would happen if a value were string-concatenated into the SQL directly instead.

📄 View solution

Chapter 2 Quick Reference

Schema: id, name, barcode, category, expiry_date (nullable), status, added_at, used_at — same shape as every sibling course
No ORM: better-sqlite3, real SQL written by hand, deliberately (Node has no single dominant ORM the way Django does)
Real cost: no migrations system — schema changes are manual ALTER TABLE statements
Deliberately synchronous: better-sqlite3 has no async/await, unlike most Node DB drivers
Routes: Router-per-resource, mounted under /api/items — the pattern every later chapter reuses
Security: parameterized ? placeholders prevent SQL injection, the same as an ORM would
Next chapter: Barcode Lookup: Integrating Open Food Facts