vohzd.com

Rebuilding bash.org search on libsql FTS5

8 min read

bash.org is gone. The IRC quote database that ate whole afternoons of my teens is a dead domain, and the only thing left is other people's dumps. One of them, dwrodri/bash_irc_quotes, is a single MIT licensed TSV with an id, a score and a quote body per row. That is enough to rebuild the site, so I did: an importer under scripts/bashorg/, a SQLite file with an FTS5 index next to it, and a page at /random/bashorg with top, search and random modes.

Fetch the TSV, parse it into rows, write those rows into two tables in one transaction. scripts/bashorg/run.ts is the entire pipeline.

const tsv = await fetchTsv({ refresh });

log.progress("parsing tsv");
const { quotes, skipped } = parseTsv(tsv);
log.ok(`parsed ${quotes.length} quotes (${skipped} skipped)`);

const selected = limit && limit > 0 ? quotes.slice(0, limit) : quotes;
persistQuotes(selected);

--refresh forces a re-fetch and --limit=500 cuts the import short, which is what I used while the schema was still moving. fetch.ts caches the raw download at data/bashorg/raw/compiled.tsv and returns the cached copy unless you ask for a refresh.

The TSV is nearly a TSV

The header row is ID\tScore\tQuote and every row after it is three fields. The problem is the third field. IRC quotes contain tabs, so you cannot split on tabs and take the parts. parse.ts finds the first two tab positions by hand and treats everything after the second one as the body, whatever is in it.

const tab1 = line.indexOf("\t");
const tab2 = line.indexOf("\t", tab1 + 1);
const idStr = line.slice(0, tab1);
const scoreStr = line.slice(tab1 + 1, tab2);
const bodyRaw = line.slice(tab2 + 1);

The other wrinkle is newlines. A quote is multi line, a TSV row is not, so the dump writes every original newline as the two characters backslash and n. Unescaping that is a global replace plus dropping the trailing one that every row ends with. It is also lossy. A quote that genuinely contained the characters \n comes out the far side with a real line break instead, and I decided I could live with that. parse.test.ts pins the case I care about more, a line ending in a backslash, where the second backslash of the pair gets eaten by the newline escape.

Everything else the parser does is throwing rows away. Malformed lines with fewer than two tabs, ids or scores that are not numbers, empty bodies, and duplicate ids tracked with a Set. It counts them into skipped and prints the number so I can see if the dump has drifted.

Why FTS5 and not LIKE

I could have shipped WHERE body LIKE '%' || ? || '%', and at this size it would even feel fine. I did not, for two reasons that have nothing to do with speed.

The first is word boundaries. LIKE '%cat%' matches "concatenate", and a quote database full of nicknames and typos is exactly the corpus where that is unbearable. FTS5 tokenises, so cat matches from the start of a token rather than anywhere inside one. The route appends a * to every token, so it still reaches "cats" and "catalogue", but never "concatenate".

The second is ranking. A LIKE scan gives you no ordering except the one you invent, and the obvious invention, order by score, puts the same handful of famous quotes on top of every search. FTS5 hands you rank, which is bm25, for free.

So the schema is two tables. The base table is plain Drizzle, generated by drizzle-kit:

CREATE TABLE `bashorg_quote` (
    `id` integer PRIMARY KEY NOT NULL,
    `score` integer DEFAULT 0 NOT NULL,
    `body` text NOT NULL,
    `line_count` integer NOT NULL,
    `char_count` integer NOT NULL,
    `imported_at` integer NOT NULL
);
--> statement-breakpoint
CREATE INDEX `bashorg_quote_score_idx` ON `bashorg_quote` (`score`);

The second migration is the interesting one, and Drizzle has no idea it exists. Virtual tables are not in the schema language, so 0001_bashorg_fts5.sql is hand written and dropped into the migrations folder with an entry added to the journal:

CREATE VIRTUAL TABLE `bashorg_search` USING fts5(
    id UNINDEXED,
    score UNINDEXED,
    body,
    tokenize = 'porter unicode61 remove_diacritics 2'
);

id and score are carried along as UNINDEXED columns so a hit can be turned into a link and a score without a join. Only body is indexed, so bm25 has a single column to weigh. The tokeniser is the porter stemmer over unicode61 with diacritics stripped, so "laughing" finds "laughed" and "naïve" finds "naive".

Keeping the virtual table in step

The usual advice is triggers on the base table. I have none, deliberately. There is exactly one writer, the importer, and it does not update rows, it replaces the lot. persist.ts wipes both tables and refills them inside a single transaction:

sqlite.exec("BEGIN TRANSACTION");
try {
  sqlite.exec("DELETE FROM bashorg_search");
  sqlite.exec("DELETE FROM bashorg_quote");
  // prepared inserts into both, row by row
  sqlite.exec("COMMIT");
} catch (err) {
  sqlite.exec("ROLLBACK");
  throw err;
}

Each quote is inserted twice, once into the base table and once into the index, and the index insert passes the quote id as the FTS5 rowid as well as into the id column. That is the only sync mechanism there is. If the insert loop throws halfway, the rollback takes both tables back to the previous import rather than leaving a half indexed database serving half a site.

The importer talks to bun:sqlite directly with WAL on and a 5 second busy timeout. The server opens the same file through @libsql/client. Two drivers, one file, fine because the writer runs offline and the reader only reads.

Turning a search box into a MATCH expression

You cannot put user input into an FTS5 MATCH unescaped. The match syntax has operators, NEAR, AND, *, quotes, and a stray one throws an error rather than returning nothing. search-util.ts normalises the input into something that can only ever be a phrase query:

export function buildMatchQuery(input: string): string {
  const tokens = input
    .trim()
    .split(/\s+/)
    .filter((t) => t.length > 0 && t.length <= 100)
    .slice(0, 16);
  if (tokens.length === 0) return "";
  return tokens.map((tok) => `"${escapeFtsToken(tok)}"*`).join(" ");
}

Every token is wrapped in double quotes, with any internal double quote doubled, so nothing inside can be read as an operator. The trailing * prefix match is what makes typing into the box feel live. Joining with a space gives implicit AND. Tokens longer than 100 characters are dropped and the whole thing is capped at 16 tokens, because someone will paste a paragraph in there eventually. If the result is empty the route returns an empty response without touching the database.

Rank, snippets, and the count for the pager

The search route is two queries. The first pulls the page:

SELECT
  id,
  score,
  snippet(bashorg_search, 2, '<mark>', '</mark>', '...', 16) AS snippet
FROM bashorg_search
WHERE bashorg_search MATCH ?
ORDER BY rank
LIMIT ? OFFSET ?

ORDER BY rank is bm25 ascending, and FTS5 returns bm25 as a negative number, so ascending means best first. In practice that pushes short quotes with the term repeated above long quotes that mention it once, which is the right instinct for one liners. It also means the ordering ignores the bash.org score entirely. The score is still on the card if you want to judge it.

snippet() does the highlighting server side. Column 2 is body, the markers are <mark> tags, and 16 is the token budget around the match.

The count is the awkward part. FTS5 will not give you the size of a result set as part of the paged query, so the route runs a second statement with the same match expression:

SELECT COUNT(*) AS c FROM bashorg_search WHERE bashorg_search MATCH ?

That is a second full match, not free, but it is the honest way to get the number. The /api/bashorg/top route does the same with a plain COUNT(*) on the base table. Both routes clamp limit into 1 to 100 with a default of 20 and floor offset at zero, so a limit of 900 cannot get through.

The page uses those totals in three places: the "Mirror of N quotes" line in the intro, the "N matches" label beside the search box, and deciding whether the "Load more" button exists. It keeps an accumulated array and an offset per mode and appends each response. Search input is debounced at 220ms, and the mode and query live in the URL so a search can be linked.

The rough edges I know about

Quote ids are the original bash.org ids, so they are sparse. Nothing renumbers them, and the single quote route runs the id through Number.parseInt and rejects NaN, zero and anything below it before hitting the database. That is looser than it sounds, because /api/bashorg/12abc still resolves to quote 12.

The bad one is escaping. The full body path renders with {{ }} interpolation inside a <pre>, so an IRC nickname in angle brackets is escaped and shows up as written. The search path renders the snippet with v-html, because the <mark> tags have to be real tags, and nothing on the way there escapes the rest: the route hands back the raw snippet() string and the page binds it straight in. So <nick> is parsed as an unknown HTML tag and vanishes from the line, which is the harmless half. A quote body holding <img src=x onerror=...> goes in as live markup and the handler runs. That is stored XSS out of a dump I did not write, so escaping the body before the markers go in is not a cosmetic fix, and it is the next thing I touch.

Everything else has been boring in the good way. bun run bashorg:import takes seconds, and there is no search service to run, monitor or pay for.

Built with Nuxt on Bun. Every article, quote and game here belongs to whoever made it.