Expiry Alerts with Prisma's Typed Date Queries

Food Tracker (React + Express + Prisma)

Chapter 6 · Expiry Alerts with Prisma's Typed Date Queries

Every item added in Chapter 5 now has a real row, with a real (or null) expiryDate. This chapter turns that stored date into something useful — and it's where Chapter 2's own promise about a real DateTime field, instead of a formatted text string, actually pays off.

The Alerts Route

// routes/items.js (appended)
router.get("/alerts", async (req, res) => {
  const threeDaysFromNow = new Date(Date.now() + 3 * 24 * 60 * 60 * 1000);

  const items = await prisma.item.findMany({
    where: {
      status: "active",
      expiryDate: {
        not: null,
        lte: threeDaysFromNow
      }
    },
    orderBy: { expiryDate: "asc" }
  });

  res.json(items);
});

status: "active" excludes anything already marked used (Chapter 8's own territory); not: null excludes items with no expiry date at all — both conditions matter for the same reasons as the sibling course. lte: threeDaysFromNow is where this route genuinely diverges: instead of SQLite's own date('now', '+3 days') computed inside the query string, the three-day window is computed as a real JS Date in ordinary JavaScript, then handed to Prisma as a query parameter.

Chapter 2's own promise, paid off

The sibling course's real gotcha: its expiry_date <= date('now', '+3 days') comparison only gives the correct chronological answer because every date happens to be stored as YYYY-MM-DD text, consistently — a format lexicographic comparison agrees with, purely by discipline. Nothing in that schema stops a future bug from inserting a date in a different format and silently breaking every comparison, with no error anywhere. This course's expiryDate field is typed as DateTime in schema.prisma, and every write to it goes through Prisma's own serialization — there's no code path in this app that can write an expiryDate value in some other, inconsistent shape. Under the hood, on SQLite, the comparison still ultimately relies on consistently-formatted stored values, exactly like the sibling's own comparison does — the real difference is that here, consistency is structurally guaranteed by the type system, not maintained by every developer remembering to format dates the same way every time.

No composite index needed here either — unrelated to Prisma, still true

Food Tracker (React + Firebase)'s own equivalent query needs a composite index, because Firestore requires one whenever a query filters on one field and orders by another simultaneously. Neither this course nor its raw-SQL sibling has that requirement — SQLite runs a query like this correctly with no index declared at all, and at this app's own realistic scale (a single household's pantry), a full table scan is genuinely fast enough not to matter. This has nothing to do with Prisma specifically; it's the same underlying SQLite engine either way, just reached through a different query API.

A Custom Hook for the Dashboard

Unchanged from the sibling course — this hook has no idea what database, or what query engine, sits behind the route it calls:

// hooks/useExpiryAlerts.js
import { useState, useEffect, useCallback } from "react";

function useExpiryAlerts() {
  const [alerts, setAlerts] = useState([]);
  const [loading, setLoading] = useState(true);

  const refresh = useCallback(async () => {
    setLoading(true);
    const response = await fetch("/api/items/alerts");
    setAlerts(await response.json());
    setLoading(false);
  }, []);

  useEffect(() => { refresh(); }, [refresh]);

  return { alerts, loading, refresh };
}

refresh is exposed deliberately, not just called once internally — Chapter 8's own "mark used" action needs a way to trigger a fresh alerts fetch immediately afterward.

The Dashboard Component

function ExpiryDashboard() {
  const { alerts, loading } = useExpiryAlerts();

  if (loading) return <p>Loading...</p>;
  if (alerts.length === 0) return <p>Nothing expiring soon.</p>;

  return (
    <ul>
      {alerts.map((item) => (
        <li key={item.id}>
          {item.name} — expires {item.expiryDate.slice(0, 10)}
        </li>
      ))}
    </ul>
  );
}

A real, honest cost of the DateTime type: display formatting

The sibling course's item.expiry_date is a plain "2026-09-01" string, ready to display as-is. This course's item.expiryDate is a full ISO-8601 timestamp once it's traveled through Prisma and JSON — something like "2026-09-01T00:00:00.000Z" — because a JS Date object always serializes to a complete timestamp, not just a date. Displaying it directly would show that whole string, time component and all; .slice(0, 10) above is the plainest fix, grabbing just the date portion. The sibling course never has to do this. This is the honest flip side of Chapter 2's own typed-field payoff: a real type buys correctness at the storage and query layer, and adds a small formatting step at the display layer that a plain text field never needed.

Keep the "how many days is soon" threshold in one place

3 * 24 * 60 * 60 * 1000 is written directly in the route above for clarity in this chapter, but a real app should pull that number from one shared constant rather than repeating the literal calculation anywhere the query might be duplicated later.

Where This Course Is Headed

Item history and live search-as-you-type next — a debounced search built as a custom React hook, hitting a new Express search endpoint backed by Prisma's own contains filter.

Hands-On Exercises

Exercise 1

Using this chapter's own finding-box, explain the real difference between "this comparison happens to work because every date is formatted consistently" (the sibling course) and "this comparison is guaranteed to work because of the field's own type" (this course) — even though both ultimately store the date as text in SQLite.

📄 View solution

Exercise 2

Explain why this course's dashboard needs item.expiryDate.slice(0, 10) while the sibling course's dashboard can display item.expiry_date directly — what's actually different about the value each one receives?

📄 View solution

Exercise 3

Explain why "three days from now" is computed in plain JavaScript in this course's route, rather than inside the query the way the sibling's date('now', '+3 days') does it — and name one real consequence of computing it that way instead.

📄 View solution

Chapter 6 Quick Reference

Route: GET /api/items/alerts — status: "active", expiryDate: { not: null, lte: threeDaysFromNow }, ordered ascending
Chapter 2's payoff: a typed DateTime field structurally guarantees consistent date formatting — the sibling's identical-looking comparison only works by developer discipline
Real new cost: a DateTime value serializes to a full ISO timestamp in JSON, so the dashboard needs .slice(0, 10) (or real formatting) the sibling never needed
Where the date math happens: in JavaScript here, inside the SQL string in the sibling — unrelated to Prisma vs. raw SQL specifically, just a different query API's own capabilities
No composite index needed: same as the sibling — a SQLite fact, not a Prisma one, still a real contrast against the Firebase sibling
useExpiryAlerts: unchanged from the sibling course — a custom hook exposing alerts, loading, and a callable refresh
Next chapter: Item History & Live Search-as-You-Type