Item History & Live Search-as-You-Type

Food Tracker (React + Express)

Chapter 7 · Item History & Live Search-as-You-Type

Every item ever added stays in the items table forever — Chapter 2's own design decision, active or used. This chapter surfaces that whole history, searchable in real time as the user types.

The Search Route

// routes/items.js (appended)
router.get("/search", (req, res) => {
  const q = req.query.q || "";
  const items = db.prepare(`
    SELECT * FROM items
    WHERE name LIKE '%' || ? || '%'
    ORDER BY added_at DESC
    LIMIT 50
  `).all(q);
  res.json(items);
});

Deliberately no status filter — unlike Chapter 6's own alerts query, 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. '%' || ? || '%' wraps the parameter in SQL wildcards while still keeping it fully parameterized — the same SQL-injection protection from Chapter 2 applies here without any extra effort.

The same real SQL advantage the Django sibling already named

Food Tracker (React + Firebase) had to add a lowercase-copy field, nameLower, purely because Firestore has no native case-insensitive substring search at all — every item's name has to be duplicated into a second, search-friendly field just to make matching work. SQLite's own LIKE operator is case-insensitive for ASCII text by default, with no shadow field, no duplicated data, and no extra write-time bookkeeping required. This is the exact same honest advantage Food Tracker (Django) already claimed for its own icontains lookup — a genuine, real SQL win, not specific to any one framework built on top of SQL.

The honest limit of this convenience

A leading wildcard ('%' || ? || '%') can never use a standard database index efficiently, even if one existed on name — the database has no choice but to check every row, since a match could start anywhere in the string. At this app's own realistic scale (Chapter 6's own point, repeated here), that cost is genuinely invisible. It would become a real, different problem at a scale large enough to need dedicated full-text search — a tool like SQLite's own FTS5 extension, or an external search service, neither of which this course builds.

A Reusable Debounce Hook

Firing a search request on every single keystroke would flood the server with requests for a query the user hasn't finished typing yet. Debouncing 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.expiry_date}`}
          </li>
        ))}
      </ul>
    </div>
  );
}

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 and unresponsive. 200–400ms is a common, comfortable range for this kind of type-ahead search — 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 an Express 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 why this course's search route needs no shadow field the way Food Tracker (React + Firebase)'s nameLower field does, and name the SQLite feature responsible.

📄 View solution

Exercise 2

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

Exercise 3

Explain why the search route deliberately has no status filter, unlike Chapter 6's own alerts route.

📄 View solution

Chapter 7 Quick Reference

Route: GET /api/items/search?q=... — SQL LIKE '%'||?||'%', no status filter, the full history
Real SQL advantage: SQLite's LIKE is case-insensitive for ASCII by default — no nameLower shadow field needed, unlike the Firebase sibling
Honest limit: a leading wildcard can't use an index — invisible at this app's scale, a real cost at a larger one
useDebouncedSearch: a reusable hook, delay tunable, cleanup cancels the previous pending timer on every keystroke
Same lesson as Chapter 4: always clean up what the previous effect run started
Next chapter: Marking Items Used