Real Product Search Without a Search Vendor

"Add search" on most e-commerce builds quietly turns into "add a search vendor" — Algolia, Elasticsearch, or a paid platform app, each with its own subscription and its own copy of your catalog to keep in sync. Acme Widgets' data already lives in Turso (libSQL, SQLite-compatible), and SQLite ships a real full-text search engine built in: FTS5. No extra service, no extra bill.

An index that references the data instead of duplicating it

The setup is one virtual table, created against the existing products table:

CREATE VIRTUAL TABLE IF NOT EXISTS products_fts USING fts5(
  name, tagline, description, content='products', content_rowid='rowid'
)

content='products' is what makes this an external-content FTS5 table — it doesn't store its own copy of the name/tagline/description text, it indexes those columns and points back to the real row in products by rowid. There's one source of truth for the actual product data; the FTS5 table is purely a search index over it.

Triggers keep the index honest

An external-content table doesn't update itself when the underlying rows change, so three triggers do that automatically on every write:

CREATE TRIGGER IF NOT EXISTS products_ai AFTER INSERT ON products BEGIN
  INSERT INTO products_fts(rowid, name, tagline, description)
  VALUES (new.rowid, new.name, new.tagline, new.description);
END;

CREATE TRIGGER IF NOT EXISTS products_ad AFTER DELETE ON products BEGIN
  INSERT INTO products_fts(products_fts, rowid, name, tagline, description)
  VALUES('delete', old.rowid, old.name, old.tagline, old.description);
END;

CREATE TRIGGER IF NOT EXISTS products_au AFTER UPDATE ON products BEGIN
  INSERT INTO products_fts(products_fts, rowid, name, tagline, description)
  VALUES('delete', old.rowid, old.name, old.tagline, old.description);
  INSERT INTO products_fts(rowid, name, tagline, description)
  VALUES (new.rowid, new.name, new.tagline, new.description);
END;

Add a product, edit a description, retire a SKU — the index reflects it on the very next search, with no reindex job, no queue, and no "search is a day behind the catalog" bug class to ever debug.

The actual search query

User input gets split on whitespace, quotes stripped, and each term gets a trailing * for prefix matching — so "wid" still matches "widget" — then joined back together, which FTS5 treats as an implicit AND across terms:

const ftsQuery = trimmed
  .split(/\s+/)
  .map((term) => term.replace(/["]/g, "") + "*")
  .join(" ");

const matches = await db.all(sql`
  SELECT p.id as id FROM products p
  JOIN products_fts f ON f.rowid = p.rowid
  WHERE products_fts MATCH ${ftsQuery}
  ORDER BY rank
  LIMIT ${limit}
`);

ORDER BY rank uses FTS5's built-in relevance ranking — no separate scoring logic to write. The matching IDs then get used to fetch the real product rows (with variants and ratings attached), which come back in whatever order that second query naturally returns them in — so the code re-sorts them to match the original rank order before rendering results.

Where this doesn't scale — and where that's fine

This is a good fit for a catalog in the hundreds to low thousands of products, which describes most small-to-mid storefronts. It's honestly not: typo-tolerant ("wdiget" finding "widget"), synonym-aware, or semantically ranked beyond FTS5's default relevance scoring, and there's no faceted-search UI included for free. A catalog in the tens of thousands of SKUs, or a genuine need for typo-tolerant/semantic search, is exactly the point where bringing in a real search vendor stops being overkill and starts being the right call.

Until then, this is what "customization" bought for free in the cost comparison: a feature that's usually a line item on someone else's invoice, built as application code you already own. If your current search setup is a paid app bolted onto a catalog this size, tell us what you're using now and we'll give you a straight answer on whether this is overkill for your situation or exactly what you need.