Item History & Live Search-as-You-Type with Prisma's contains Filter

Food Tracker (React + Express + Prisma)

Chapter 7 · Item History & Live Search-as-You-Type with Prisma's contains Filter

Every item ever added stays in the database forever — Chapter 2's own design decision, active or used. This chapter surfaces that whole history, searchable in real time — and it's the one chapter in this course where a naive Prisma port genuinely regresses a feature that already worked correctly in the sibling course, verified directly against Prisma's own current documentation rather than assumed.

The Search Route — First Pass

// routes/items.js (appended)
router.get("/search", async (req, res) => {
  const q = req.query.q || "";
  const items = await prisma.item.findMany({
    where: { name: { contains: q } },
    orderBy: { addedAt: "desc" },
    take: 50
  });
  res.json(items);
});

Deliberately no status filter, matching the sibling course exactly — this route searches the entire history, active and used items both, since re-adding something bought before is exactly the case this search exists for.

A Real, Verified Regression

The sibling course's own LIKE '%' || ? || '%' is case-insensitive for ASCII automatically — a genuine, well-known SQLite behavior. It's tempting to assume Prisma's contains just carries that same behavior forward on top of the identical SQLite database. Checked directly against Prisma's own current documentation, it doesn't:

Verified against Prisma's own docs, not assumed

Prisma's documentation states it plainly for the SQLite provider: "By default, text fields created by Prisma Client in SQLite databases do not support case-insensitive filtering." The mode: "insensitive" option that fixes this cleanly on PostgreSQL or MongoDB is not supported on SQLite at all. So the route above, exactly as written, is case-sensitive — searching "milk" would not find an item named "Milk". This is a genuine functional regression against the sibling course's own working search, not a hypothetical edge case.

Why this is worth stopping on

Every other chapter in this course has framed Prisma's own costs as things that are inconvenient, or that need an extra step, but never things that silently break a feature the sibling already got right for free. This one does exactly that, unless it's caught and fixed. It's a genuine example of why porting an app to an ORM isn't purely mechanical — a query that looks like a faithful translation of the original SQL can behave differently in a way that only shows up when someone actually searches for something.

The Real Fix: COLLATE NOCASE

Prisma's own documented fix is to add SQLite's COLLATE NOCASE to the column itself, restoring ASCII case-insensitive comparison for every filter type — contains, startsWith, endsWith, and equals alike. Because name already exists as a plain column since Chapter 2, and SQLite doesn't support altering a column's collation in place, this means generating a migration without applying it, editing the SQL by hand, then applying the edited version:

# generates the migration file without running it
npx prisma migrate dev --name add_name_collate_nocase --create-only

Then editing the generated migration.sql to recreate the table with name declared COLLATE NOCASE — SQLite's own standard pattern for changing an existing column, since columns can't be altered directly (simplified here to the essential steps; a production migration would also need to reapply any indexes):

-- migration.sql (hand-edited)
PRAGMA foreign_keys=OFF;

CREATE TABLE "new_Item" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL COLLATE NOCASE,
  "barcode" TEXT,
  "category" TEXT,
  "expiryDate" DATETIME,
  "status" TEXT NOT NULL DEFAULT 'active',
  "addedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "usedAt" DATETIME
);

INSERT INTO "new_Item" SELECT * FROM "Item";
DROP TABLE "Item";
ALTER TABLE "new_Item" RENAME TO "Item";

PRAGMA foreign_keys=ON;

# applies the hand-edited migration
npx prisma migrate dev

With that migration applied, the exact same route code from the first pass — nothing about the query itself changes — now matches case-insensitively, because the comparison behavior lives in the column's own collation, not in the query.

--create-only exists specifically for this

Prisma normally applies a migration immediately after generating it. --create-only generates the SQL file and stops there, deliberately leaving room to hand-edit it before anything touches the real database — exactly the workflow this fix needs, and a real, documented part of the Prisma CLI, not a workaround.

A Reusable Debounce Hook

Unchanged from the sibling course — firing a search request on every keystroke would flood the server, so this delays the actual fetch until typing pauses:

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

function useDebouncedSearch(query, delay = 300) {
  const [results, setResults] = useState([]);

  useEffect(() => {
    if (!query) {
      setResults([]);
      return;
    }

    const timeoutId = setTimeout(async () => {
      const response = await fetch(`/api/items/search?q=${encodeURIComponent(query)}`);
      setResults(await response.json());
    }, delay);

    return () => clearTimeout(timeoutId);
  }, [query, delay]);

  return results;
}

The cleanup line is not optional — the same lesson as Chapter 4's camera stream

Without return () => clearTimeout(timeoutId), every keystroke would still schedule its own timer, and every one of those timers would eventually fire — the delay would only push the flood of requests later, not prevent it. Because useEffect re-runs this whole function on every query change, each run's cleanup cancels the previous run's still-pending timer before scheduling a new one — the same "always clean up what the last effect run started" discipline Chapter 4's camera-stream cleanup already taught, applied here to a timer instead of a media stream.

The Search Component

function SearchBox() {
  const [query, setQuery] = useState("");
  const results = useDebouncedSearch(query);

  return (
    <div>
      <input
        value={query}
        onChange={(e) => setQuery(e.target.value)}
        placeholder="Search item history..."
      />
      <ul>
        {results.map((item) => (
          <li key={item.id}>
            {item.name} — {item.status === "used" ? "used" : `active, expires ${item.expiryDate.slice(0, 10)}`}
          </li>
        ))}
      </ul>
    </div>
  );
}

The honest limit of contains, unrelated to Prisma specifically

A leading wildcard — what contains compiles down to — can never use a standard database index efficiently, even with one declared on name, since a match could start anywhere in the string. The same real limitation the sibling course names for its own raw LIKE '%...%' applies here identically; it's a fact about this query shape on SQLite, not something Prisma changes either way. At this app's own realistic scale, that cost is genuinely invisible.

300ms is a reasonable default, not a universal constant

Too short a delay defeats the purpose of debouncing at all; too long makes the search feel sluggish. 200–400ms is a common, comfortable range — worth tuning against real typing speed rather than treating as a fixed rule.

Where This Course Is Headed

Marking items used next — a React action and a Prisma-backed PATCH endpoint, tying directly back into both this chapter's own search results and Chapter 6's own alerts dashboard.

Hands-On Exercises

Exercise 1

Explain the real regression this chapter found: why does the naive contains-based search route fail to match "Milk" when a user searches "milk," and why wouldn't simply adding mode: "insensitive" to the query fix it?

📄 View solution

Exercise 2

Explain why fixing this requires hand-editing a generated migration file with COLLATE NOCASE, rather than a change to schema.prisma or to the search route's own query code.

📄 View solution

Exercise 3

Explain what would happen if useDebouncedSearch's useEffect omitted its cleanup function, and why the fix is described as "the same lesson" as Chapter 4's camera-stream cleanup.

📄 View solution

Chapter 7 Quick Reference

Route: GET /api/items/search?q=... — prisma.item.findMany with contains, no status filter, the full history
Real, verified regression: Prisma's contains defaults to case-sensitive on SQLite; mode: "insensitive" isn't supported on SQLite at all — unlike the sibling's automatically case-insensitive raw LIKE
The real fix: COLLATE NOCASE added to the column via a hand-edited migration (prisma migrate dev --create-only, edit the SQL, then apply it)
Honest limit, unchanged from the sibling: a leading wildcard can't use an index — a SQLite fact, not a Prisma one
useDebouncedSearch: unchanged from the sibling course — cleanup cancels the previous pending timer on every keystroke
Next chapter: Marking Items Used