Full-text search over SQLite

A help center needs a search box long before it needs a search service. SQLite ships FTS5, a full-text index with ranking, and D1 and Durable Objects both carry it. The part between the box and the index is the app's to write: reading what a person typed so a stray quote stays a character, deciding that a title match beats a body match, and showing where each result matched.

This guide adds search to a help center's articles. @sdxc/search parses the query, builds the FTS5 statement, ranks it with per-column weights, highlights matches and rebuilds the index in batches. @sdxc/pagination pages the results, @sdxc/jobs runs the rebuild, and @sdxc/result carries every failure.

npm add @sdxc/search @sdxc/pagination @sdxc/jobs @sdxc/result @sdxc/http remix

Keep a search table beside the content

FTS5 indexes rows by an integer rowid, and it wants every searched column on one row. The articles here are keyed by a text id, so the searchable text lives in a table of its own: one row per article, an INTEGER PRIMARY KEY the index mirrors, and the article's id as a unique column. The columns a search filters by, the article's section and publish date, ride along on the same row, so a query reads one table.

The migration creates that table, the index over it, and the triggers that keep the index in step with every write:

CREATE TABLE "article_search" (
	"id" INTEGER PRIMARY KEY,
	"article_id" TEXT NOT NULL UNIQUE
		REFERENCES "articles" ("id") ON DELETE CASCADE,
	"slug" TEXT NOT NULL,
	"section" TEXT NOT NULL,
	"published_at" INTEGER,
	"title" TEXT NOT NULL,
	"tags" TEXT NOT NULL DEFAULT '',
	"body" TEXT NOT NULL DEFAULT ''
);

CREATE VIRTUAL TABLE "article_search_fts" USING fts5(
	"title", "tags", "body",
	content='', contentless_delete=1,
	tokenize='unicode61 remove_diacritics 2'
);

CREATE TRIGGER "article_search_fts_insert" AFTER INSERT ON "article_search" BEGIN
	DELETE FROM "article_search_fts" WHERE "rowid" = new."id";
	INSERT INTO "article_search_fts" ("rowid", "title", "tags", "body")
	VALUES (new."id", new."title", new."tags", new."body");
END;

CREATE TRIGGER "article_search_fts_update"
AFTER UPDATE OF "title", "tags", "body" ON "article_search" BEGIN
	DELETE FROM "article_search_fts" WHERE "rowid" = old."id";
	INSERT INTO "article_search_fts" ("rowid", "title", "tags", "body")
	VALUES (new."id", new."title", new."tags", new."body");
END;

CREATE TRIGGER "article_search_fts_delete" AFTER DELETE ON "article_search" BEGIN
	DELETE FROM "article_search_fts" WHERE "rowid" = old."id";
END;

INSERT INTO "article_search"
	("article_id", "slug", "section", "published_at", "title", "tags", "body")
SELECT "id", "slug", "section", "published_at", "title", "tags", "body"
FROM "articles";

content='' makes the index contentless: it holds the inverted index and nothing else, since the text is already in article_search. contentless_delete=1 lets a contentless index delete a row, and needs SQLite 3.43, which D1 and Durable Objects run. remove_diacritics 2 makes resume find résumé.

Each trigger deletes the rowid before inserting it. A trigger runs under the conflict policy of the statement that fired it, so under the upsert in the next section a plain insert would keep the old words indexed beside the new ones. The last statement backfills the articles that already exist, and the insert trigger indexes each row it writes.

Store body as the text a reader reads. If articles are written in Markdown, write the plain-text rendering into article_search, so ** and link URLs never match a query or show up in an excerpt.

Describe the search

The table is declared for remix/data-table like any other:

import { column as c, table } from "remix/data-table";

export const articleSearch = table({
	name: "article_search",
	columns: {
		id: c.integer().primaryKey(),
		article_id: c.text(),
		slug: c.text(),
		section: c.text(),
		published_at: c.integer().nullable(),
		title: c.text(),
		tags: c.text(),
		body: c.text(),
	},
});

defineSearch describes which of its columns are searched, how much a match in each counts, and which FTS5 table indexes them. It holds no connection, so one definition at module scope serves every request and every tenant's database:

import { defineSearch, parseQuery } from "@sdxc/search";

import { articleSearch } from "~/database/schema/article-search";

export const ARTICLE_SEARCH = defineSearch({
	table: articleSearch,
	columns: [
		{ name: "title", weight: 10, field: "title" },
		{ name: "tags", weight: 6 },
		{ name: "body", weight: 1 },
	],
	fts: { table: "article_search_fts" },
});

export function parseArticleQuery(text: string) {
	return parseQuery(text, {
		fields: ARTICLE_SEARCH.fields,
		filters: ["section"],
	});
}

List columns in the order the FTS5 table declares them, because the weights are handed to bm25() by position. With these weights a word in the title counts ten times a word in the body, so an article titled "Reset your password" outranks one that mentions passwords in passing, while a body that is about nothing else can still beat a passing title. field: "title" lets a reader write title:invoice to search the title alone.

The key defaults to the table's primary key, id, which is the integer the index's rowid mirrors. A description that can never work throws a RangeError when the module loads: an empty columns, a weight of zero, a column the table lacks, or a non-integer key with fts.

Keep the table current

An article's write action writes its search row too, in one upsert, so the row is created the first time and replaced after:

import type { Database } from "remix/data-table";

import { sql } from "remix/data-table";

export interface SearchableArticle {
	id: string;
	slug: string;
	section: string;
	publishedAt: number | null;
	title: string;
	tags: string[];
	body: string;
}

export async function indexArticle(db: Database, article: SearchableArticle) {
	await db.exec(sql`
		insert into "article_search"
			("article_id", "slug", "section", "published_at", "title", "tags", "body")
		values (${article.id}, ${article.slug}, ${article.section},
			${article.publishedAt}, ${article.title}, ${article.tags.join(" ")},
			${article.body})
		on conflict ("article_id") do update set
			"slug" = excluded."slug",
			"section" = excluded."section",
			"published_at" = excluded."published_at",
			"title" = excluded."title",
			"tags" = excluded."tags",
			"body" = excluded."body"
	`);
}

The triggers follow every write to article_search, so nothing else touches the index. Deleting an article takes its search row with it through ON DELETE CASCADE, and the delete trigger takes it out of the index. Drafts are indexed too: whether a row is published is checked when the search runs, so publishing an article later needs no second write.

Rebuild the index in batches

The triggers keep the index correct from the migration on. Rebuild it when what it holds changes shape: a new tokenizer, a column added to the index, or after a D1 export, which D1 refuses while a virtual table exists. For an export, drop the three triggers and the index, export, recreate both, and rebuild.

reindex writes the next limit rows with a key above after, and answers how many it wrote and where the next batch starts. Each batch is a short statement, which is what a D1 request budget wants, and a batch that fails or runs twice leaves the index correct. A job that writes one batch and enqueues the next walks a table of any size:

import { job, jobs } from "@sdxc/jobs";
import * as s from "remix/data-schema";

export default jobs({
	reindexArticleSearch: job({
		input: s.object({ after: s.nullable(s.number()) }),
	}),
});

import { createJobHandler } from "@sdxc/jobs";
import { isFailure } from "@sdxc/result";

import jobs from "~/app/jobs";
import { dispatcher } from "~/app/jobs/dispatcher";
import { ARTICLE_SEARCH } from "~/app/search/articles";

export default createJobHandler(jobs.reindexArticleSearch, async (ctx) => {
	let progress = await ARTICLE_SEARCH.reindex(ctx.database, {
		after: ctx.input.after,
		limit: 500,
	});
	if (isFailure(progress)) {
		return ctx.retry({ delay: "1 minute", cause: progress.error });
	}

	ctx.log.set({ search: { indexed: progress.data.indexed } });
	if (progress.data.next === null) return;
	await dispatcher.enqueue(jobs.reindexArticleSearch, {
		after: progress.data.next,
	});
});

Enqueue it with { after: null } to start from the first row. Articles written while it runs stay current through the triggers, whichever side of the batch they fall on. Background jobs and cron covers the dispatcher and the ctx.database middleware.

Parse what the reader typed

parseArticleQuery reads the box the way people expect a search box to read. Every word must match and matches as a prefix, so pass finds "password". "exact phrase" matches the phrase, -word leaves out results holding it, refund OR chargeback accepts either, title:invoice searches one field and section:billing is a filter. Operators count only in capitals.

Nothing a person types is a syntax error. An unmatched quote, c++, it's or a:b with no declared a all read as ordinary words, and characters FTS5 would treat as syntax never reach it. What the parse answers is one of three things:

• success(null) for a blank box, which is no search at all.

• success(query) with the clauses to match and the filters to apply.

• failure(ValidationError) for a query that cannot run: past 256 characters, more than 8 terms, or nothing left to find once exclusions are set aside, as with -legacy alone.

The error carries one issue, at the path q, with a message written for the reader, such as "A search holds at most 8 terms." Show it beside the box, with the text kept in it to edit, so the reader learns what to change. The limits are options: maxLength and maxTerms raise or lower them, and prefix: "last" makes only the final word a prefix, the reading for a box that searches as the reader types.

Search, filter and page

ARTICLE_SEARCH.query answers a query that orders by relevance, best first, and composes like any remix/data-table query. The filters the parse read out are yours to apply, because only the app knows what section:billing means:

import type { ParsedQuery } from "@sdxc/search";

import { inList, notInList } from "remix/data-table";

export function matchingArticles(db: Database, query: ParsedQuery) {
	let found = ARTICLE_SEARCH.query(db, query).where(
		sql`"published_at" <= ${Date.now()}`,
	);

	for (let filter of query.filters) {
		found = found.where(
			filter.exclude
				? notInList("section", filter.values)
				: inList("section", filter.values),
		);
	}

	let hasTerms = query.clauses.some((clause) => !clause.exclude);
	return hasTerms ? found : found.orderBy("published_at", "desc");
}

published_at <= now leaves out drafts, whose date is null, and articles scheduled for later. A query of filters alone, section:billing, matches every published article in the section with nothing to rank by, so it lists the newest first.

Each row comes back with every column of article_search plus rank. With FTS5, rank is the weighted bm25() score, where lower is better. A search result list is one a reader pages by number and expects a total for, so page it by offset:

import { redirect } from "@sdxc/http/response";
import { internalServerError } from "@sdxc/http/response/html";
import { createPaging, Pagination } from "@sdxc/pagination";
import { isFailure } from "@sdxc/result";
import { createAction } from "remix/router";

import { toHit } from "~/app/search/hits";
import { matchingArticles, parseArticleQuery } from "~/app/search/articles";
import { SearchPage } from "~/resources/views/search";
import routes from "~/routes/web";

const PAGING = createPaging({ perPage: 10, maxPerPage: 50 });

export default createAction(routes.search, async (ctx) => {
	let text = ctx.url.searchParams.get("q") ?? "";
	let parsed = parseArticleQuery(text);
	if (isFailure(parsed)) {
		let message = parsed.error.issues[0]?.message ?? "This search cannot run.";
		let page = <SearchPage state="invalid" query={text} message={message} />;
		return ctx.render(page, { status: 400 });
	}
	if (parsed.data === null) return ctx.render(<SearchPage state="blank" />);

	let params = PAGING.parse(ctx.url.searchParams);
	if (isFailure(params)) {
		let first = new URLSearchParams({ q: text });
		return redirect(`${routes.search.href()}?${first}`);
	}

	let query = parsed.data;
	let page = await Pagination.byOffset(
		matchingArticles(ctx.db, query),
		params.data,
	);
	if (isFailure(page)) {
		ctx.log.fail(page.error);
		return internalServerError("Search is unavailable right now.");
	}

	let headers = PAGING.paginate(new Headers(), page.data, { url: ctx.url });
	let hits = page.data.items.map((row) => toHit(row, query));
	return ctx.render(
		<SearchPage
			state="results"
			query={text}
			hits={hits}
			pagination={page.data.pagination}
			url={ctx.url}
		/>,
		{ headers },
	);
});

A page past the end renders the last page, as Pagination clamps it, and a malformed ?page= starts the query over from its first page. A statement the database refuses reaches Pagination as a SearchError, and byOffset answers it as a failure holding a QueryFailedError, which the log records before the page answers 500. Paginate lists covers the parameters and the Link headers paginate writes.

Offset paging suits relevance order, which is how a reader expects results. When search only narrows a list that has its own order, such as the newest articles in one section, page it by keyset over that order with Pagination.byKeyset instead: a stable order pages exactly, while relevance shifts each time the index changes between two pages.

Highlight what matched

highlight splits a text into { text, match } segments that concatenate back to the text, and excerpt picks a window of words around the first match. Both compare the way the index does, ignoring case and diacritics, and both keep the text exactly as written:

import type { SearchRow } from "@sdxc/search";
import type { Excerpt, HighlightSegment, ParsedQuery } from "@sdxc/search/query";

import { excerpt, highlight } from "@sdxc/search/query";

import type { articleSearch } from "~/database/schema/article-search";

export interface SearchHit {
	href: string;
	title: HighlightSegment[];
	excerpt: Excerpt;
}

export function toHit(
	row: SearchRow<typeof articleSearch>,
	query: ParsedQuery,
): SearchHit {
	return {
		href: `/help/${row.slug}`,
		title: highlight(row.title, query, { field: "title" }),
		excerpt: excerpt(row.body, query, { words: 28 }),
	};
}

field: "title" tells highlight which declared field the text is, so title:invoice marks "invoice" in titles and nowhere else, while unscoped words mark in every text. Excluded words never highlight. An excerpt of a body with no match, such as an article that matched on a tag, is its opening words, and truncatedStart and truncatedEnd say whether to draw an ellipsis on either side.

Segments are data, so the view renders each match as a <mark> element with no HTML built from strings. Build a search dialog draws them with @sdxc/ui's Highlight. @sdxc/search/query holds only the parser and the highlighters, so a browser bundle can parse and highlight with the same functions the server searched with.

Search without FTS5

Leave out fts and the same definition runs every query as LIKE over the columns. The table you already have is all it reads, so this search ships without a migration:

import { defineSearch } from "@sdxc/search";

import { faqs } from "~/database/schema/faqs";

export const FAQ_SEARCH = defineSearch({
	table: faqs,
	columns: [
		{ name: "question", weight: 3 },
		{ name: "answer", weight: 1 },
	],
});

LIKE matches anywhere inside a word, so voice finds "invoice". rank is then the negated weighted count of the columns each term hit, still lower-is-better, so the rest of this guide reads the same. LIKE reads every row, which suits a table of hundreds of rows, not hundreds of thousands. Highlight its results with { mode: "substring" }, so the marks agree with what matched.

FTS5's trigram tokenizer is the middle ground: an index that matches substrings. Declare the virtual table with tokenize='trigram' and the definition with fts: { table: "faqs_fts", tokenizer: "trigram" }. A trigram index needs three characters to look anything up, so a query holding a shorter term runs as LIKE on its own.

LIKE binds two values per term per column, and SQLite on D1 and Durable Objects binds at most 100 per statement. A query over that limit is refused before it runs with a ParameterBudgetError, a SearchError. With the default 8 terms, a LIKE search stays under it up to 6 columns.

Where to go next

• Build a search dialog: the search box in every page's header, its live results, and the results page.

• Paginate lists: offset and keyset paging, the page parameters, and the numbered pager.

• Background jobs and cron: the dispatcher the rebuild runs on.

• Query D1 and Durable Object SQL: the adapters a search runs on.

• @sdxc/search: the full query syntax, every option and error.