Full-text search that works on a plane
SQLite FTS5 gives you ranked, snippet-annotated, as-you-type search with no server and no network. The parts that catch people out are the external-content pattern, the delete trigger, and what the default tokenizer does to accented text.
Create an FTS5 virtual table with content='your_table', keep it in sync with three triggers, and add ORDER BY rank to every query — without it FTS5 returns matches in rowid order, not by relevance. That is a working offline search index in about thirty lines of SQL, and no part of it needs a network.
Everything below was run on SQLite 3.53.4 through Python 3.14's sqlite3 module. The numbers are from that machine, on a corpus described where it appears.
The problem
An offline medical reference has to answer a query while the device has no signal — that is the entire point of it being offline. The obvious implementation is a LIKE scan:
SELECT * FROM articles WHERE title LIKE '%burn%' OR body LIKE '%burn%';
On 20,000 short documents (24 MB of text) that query takes about 31 ms on this machine. The equivalent FTS5 query, returning the same 624 rows, takes 0.017 ms:
limes LIKE 31.05 ms (624 rows) MATCH 0.017 ms (624 rows) 1862x
ovogenesis LIKE 34.04 ms (575 rows) MATCH 0.016 ms (575 rows) 2158x
31 ms is survivable once. It is not survivable on every keystroke of an as-you-type search box, and it grows linearly with the corpus while the index lookup does not.
Why the obvious approach fails
The obvious FTS5 approach fails too, in three quieter ways.
It stores your text twice. The first FTS5 table everyone writes is CREATE VIRTUAL TABLE articles_fts USING fts5(title, body). That table keeps its own complete copy of the text alongside the index. Measured on the corpus above, the database goes from 26.11 MB to 64.35 MB. The external-content form gets the same search for 38.25 MB.
The delete trigger looks right and corrupts the index. With content='articles', the FTS table is a view over a table it does not own, so you cannot delete from it the normal way — but SQLite lets you try:
CREATE TRIGGER a_ad AFTER DELETE ON articles BEGIN
DELETE FROM articles_fts WHERE rowid = old.id; -- wrong, and silent
END;
Nothing raises. Here is what the database does afterwards:
the DELETE raised nothing.
count(*) match 'burn' : 1
select body : DatabaseError: fts5: missing row 1 from content table 'main'.'a'
snippet() : DatabaseError: database disk image is malformed
integrity-check(0) : ok
integrity-check(1) : DatabaseError: database disk image is malformed
Counting matches still says the deleted row exists. Reading a column from it fails. And the routine integrity check — integrity-check with rank 0 — reports the database as fine. Only the external-content check, rank 1, sees it. If you have an FTS5 table in production and have never run that variant, run it today.
The default tokenizer half-folds accents. More on that below. It is the one nobody looks for, because it fails silently and only for some inputs — which is also why it survives testing.
The fix
Two indexes over one copy of the text. articles_fts is external-content: it holds postings and reads the text back out of articles when it needs to render a snippet. articles_fold is contentless — it holds postings and nothing else, because its only job is turning an accent-free query into rowids.
CREATE TABLE articles(id INTEGER PRIMARY KEY, title TEXT, body TEXT);
CREATE VIRTUAL TABLE articles_fts USING fts5(
title, body,
content='articles', content_rowid='id',
tokenize='unicode61 remove_diacritics 2',
prefix='2 3'
);
CREATE VIRTUAL TABLE articles_fold USING fts5(
txt, content='', contentless_delete=1,
tokenize='unicode61 remove_diacritics 2', prefix='2 3'
);
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
INSERT INTO articles_fold(rowid, txt) VALUES (new.id, fold(new.title || ' ' || new.body));
END;
CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
INSERT INTO articles_fts(articles_fts, rowid, title, body)
VALUES ('delete', old.id, old.title, old.body);
DELETE FROM articles_fold WHERE rowid = old.id;
END;
CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
INSERT INTO articles_fts(articles_fts, rowid, title, body)
VALUES ('delete', old.id, old.title, old.body);
DELETE FROM articles_fold WHERE rowid = old.id;
INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
INSERT INTO articles_fold(rowid, txt) VALUES (new.id, fold(new.title || ' ' || new.body));
END;
The delete on articles_fts is that 'delete' command, and it must be given the old column values — FTS5 re-tokenises them to know which postings to remove. Pass new values by mistake and you get the corruption above. The contentless table is different again: contentless_delete=1 (SQLite 3.43+) makes a plain DELETE legal there, and refuses the 'delete' command instead. Two tables, two opposite rules, in the same trigger.
Contentless is not a free lunch. On SQLite 3.53.4, reading a column, snippet() and highlight() from a content='' table all return NULL rather than an error, and 'rebuild' is refused outright. That is exactly why the folded index is only ever used to find rowids — never to display anything.
Ranking is opt-in
FTS5 computes a BM25 score for every match and then, unless told otherwise, throws it away and returns rows in rowid order:
=== 2. the default order is rowid, not relevance [water OR fever OR burn] ===
no ORDER BY : ['Fever in children', 'Burns, first aid', 'Dehydration']
ORDER BY rank : ['Burns, first aid', 'Fever in children', 'Dehydration']
bm25 scores : [('Burns, first aid', -1.916), ('Fever in children', -1.63), ('Dehydration', -0.353)]
title weighted: ['Fever in children', 'Burns, first aid', 'Dehydration']
Three things worth knowing. rank is a hidden column, so ORDER BY rank is the whole incantation. The scores are negative, and lower is better — FTS5 negates BM25 so that ascending order is best-first, which means a naive ORDER BY bm25(...) DESC sorts your worst results to the top. And bm25(articles_fts, 10.0, 1.0) takes one weight per column: weighting the title ten times the body moves "Fever in children" above "Burns, first aid", which is usually what a reference app wants.
snippet() and highlight()
A search result without context is a list of titles the user has to open one at a time. Two built-ins fix that:
SELECT highlight(articles_fts, 0, '[', ']'),
snippet(articles_fts, 1, '[', ']', '...', 8)
FROM articles_fts WHERE articles_fts MATCH 'burn' ORDER BY rank;
Burns, first aid | Cool a [burn] under cool running water for...
highlight() marks up a whole column; snippet() picks the densest window of up to N tokens around the match. Both take a zero-based column index, and both need the text — which is the reason articles_fts is external-content rather than contentless.
Prefix indexes, and when they pay
As-you-type search means querying de*, then deh*, then dehy*. FTS5 can serve a prefix query from the ordinary index by scanning terms, or from a dedicated prefix index. On the 20k-document corpus:
prefix query, 20k docs, best of 5 timed batches
c* 19999 hits no prefix index 16.937 ms prefix='2 3' 6.254 ms
ca* 17696 hits no prefix index 3.011 ms prefix='2 3' 0.319 ms
car* 8648 hits no prefix index 0.592 ms prefix='2 3' 0.158 ms
carb* 1126 hits no prefix index 0.054 ms prefix='2 3' 0.054 ms
carbo* 558 hits no prefix index 0.025 ms prefix='2 3' 0.025 ms
The prefix index earns its keep at two and three characters — 9x at ca* — and is indistinguishable from four characters on, to three decimal places. That is the right shape: short prefixes are the ones a user types first and the ones that match nearly everything. prefix='2 3' indexes exactly those two lengths and no more.
The tokenizer, and Vietnamese
unicode61 is the default tokenizer, and its default is remove_diacritics 1. That fold is partial. Indexing "Sốt cao ở trẻ em, đau bụng dưới" and querying without accents:
sot tre bung duoi o dau
remove_diacritics 0 . . . . . .
unicode61 (the default) . yes yes . . .
remove_diacritics 2 yes yes yes yes yes .
remove_diacritics 2 + fold() yes yes yes yes yes yes
The default folds trẻ to tre and bụng to bung, but leaves sốt, dưới and ở alone — because those carry two stacked marks, and level 1 only handles codepoints with a single diacritic. So a Vietnamese user typing without accents, which is how most people type on a phone, gets some of their results. A search that returns nothing is a bug report. A search that returns half is a product people stop trusting.
remove_diacritics 2 fixes the stacking. Nothing in unicode61 fixes đ, because đ (U+0111) is a letter with a stroke, not a letter with a combining mark — dau never matches đau at any level. That is what the second index is for: fold in your own code (NFD, drop combining marks, map đ→d) and index the result.
What it costs
Same 20,000-document corpus, each database VACUUMed after an optimize:
content table only, no FTS 26.11 MB baseline
+ FTS5, content='articles' 38.25 MB +12.14 MB 1.46x
+ FTS5, content='articles', prefix='2 3' 57.40 MB +31.29 MB 2.20x
+ both indexes (fts + folded) 77.08 MB +50.97 MB 2.95x
+ FTS5 storing its own copy of the text 64.35 MB +38.24 MB 2.46x
Read that honestly. The corpus is 20,000 documents assembled from a random sample of 4,000 dictionary words, so its vocabulary is far flatter than real prose and the index is correspondingly expensive — real text repeats itself and compresses better. The ordering is what generalises: external content is much cheaper than letting FTS5 keep its own copy (+12.14 MB against +38.24 MB), the prefix index costs more than the base index it supplements (+19.15 MB on top of +12.14 MB), and the folded index costs about as much again (+19.68 MB).
For a phone app that budget is the whole design conversation. Ship prefix='2 3' if search is the product; drop to prefix='2' or none if it is a side feature.
What FTS5 does not do
Two limits worth knowing before you promise anyone a search box.
There is no stemming by default. Index Cool the burn under running water and query it five ways:
unicode61 remove_diacritics 2 {'burn': 1, 'burns': 0, 'burned': 0, 'running': 1, 'run': 0}
porter unicode61 remove_diacritics 2 {'burn': 1, 'burns': 1, 'burned': 1, 'running': 1, 'run': 1}
burns does not find a document that only says burn. Wrapping the tokenizer — tokenize='porter unicode61 remove_diacritics 2' — fixes English, and does nothing for Vietnamese, which is not an inflected language and gets no benefit from a Porter stemmer at all.
There is no word segmentation. unicode61 splits on non-word characters, so trẻ em is two tokens rather than one word. tre em as a bare query is an implicit AND of two terms in any order; "tre em" in double quotes is the phrase. If word order carries meaning in your corpus, quote it — or use NEAR(tre em, 1), which also matches.
Check it yourself
Save the whole thing as fts5demo.py — the schema above, the two probes, and the corruption demo. No network, no API key, no model call:
import sqlite3, unicodedata
print("sqlite:", sqlite3.sqlite_version)
try:
sqlite3.connect(":memory:").execute("create virtual table t using fts5(x)")
except sqlite3.OperationalError as e:
raise SystemExit("FTS5 not compiled in: " + str(e))
print("FTS5: available\n")
def fold(s):
s = s.replace("đ", "d").replace("Đ", "D")
return "".join(c for c in unicodedata.normalize("NFD", s)
if not unicodedata.combining(c))
db = sqlite3.connect(":memory:")
db.create_function("fold", 1, fold, deterministic=True)
# paste the CREATE TABLE, both CREATE VIRTUAL TABLEs and all three triggers
# from "The fix" above between these quotes, unchanged:
SCHEMA = """
"""
db.executescript(SCHEMA)
DOCS = [
("Fever in children", "A fever is a temperature at or above 38 C. In a child under three months a fever needs same-day assessment. Give fluids often and watch for a rash that does not fade under pressure."),
("Burns, first aid", "Cool a burn under cool running water for twenty minutes. Do not use ice, butter or toothpaste. Cover the burn loosely with cling film."),
("Chest pain", "Crushing central chest pain spreading to the arm or jaw is an emergency. Chest pain that is sharp and worse on breathing in is more often pleuritic than cardiac."),
("Dehydration", "Dry mouth, dark urine and dizziness on standing suggest dehydration. Oral rehydration salts work better than water alone because they replace sodium as well as fluid."),
("Sốt cao ở trẻ em", "Sốt cao ở trẻ em dưới ba tháng tuổi cần được khám trong ngày. Cho trẻ uống nhiều nước và theo dõi phát ban."),
]
db.executemany("insert into articles(title, body) values (?,?)", DOCS)
db.commit()
db.execute("insert into articles_fts(articles_fts, rank) values('integrity-check', 1)")
Q = "water OR fever OR burn"
print(f"=== 2. the default order is rowid, not relevance [{Q}] ===")
print(" no ORDER BY :", [r[0] for r in db.execute(
"select title from articles_fts where articles_fts match ?", (Q,))])
print(" ORDER BY rank :", [r[0] for r in db.execute(
"select title from articles_fts where articles_fts match ? order by rank", (Q,))])
print(" bm25 scores :", [(r[0], round(r[1], 3)) for r in db.execute(
"select title, bm25(articles_fts) from articles_fts where articles_fts match ? order by rank", (Q,))])
print(" title weighted:", [r[0] for r in db.execute(
"select title from articles_fts where articles_fts match ? order by bm25(articles_fts,10.0,1.0)", (Q,))])
print("\n=== 3. snippet() and highlight() ===")
for t, b in db.execute("""select highlight(articles_fts, 0, '[', ']'),
snippet(articles_fts, 1, '[', ']', '...', 8)
from articles_fts where articles_fts match 'burn' order by rank"""):
print(" ", t, "|", b)
print("\n=== 4. prefix search, as you type ===")
for p in ("de", "deh", "dehy", "ch"):
print(f' "{p}*" ->', [r[0] for r in db.execute(
"select title from articles_fts where articles_fts match ? order by rank", (p + "*",))])
print("\n=== 5. what unicode61 does to Vietnamese ===")
probes = ("sot", "tre", "bung", "duoi", "o", "dau")
SAMPLE = "Sốt cao ở trẻ em, đau bụng dưới"
def probe(tok, transform=lambda s: s):
t = sqlite3.connect(":memory:")
t.execute(f"create virtual table v using fts5(x, tokenize=\"{tok}\")")
t.execute("insert into v(x) values (?)", (transform(SAMPLE),))
return ["yes" if t.execute("select count(*) from v where v match ?", (q,)).fetchone()[0]
else " . " for q in probes]
print(" " + " " * 36 + "".join(q.center(7) for q in probes))
for label, tok, tr in (
("remove_diacritics 0", "unicode61 remove_diacritics 0", lambda s: s),
("unicode61 (the default)", "unicode61", lambda s: s),
("remove_diacritics 2", "unicode61 remove_diacritics 2", lambda s: s),
("remove_diacritics 2 + fold()", "unicode61 remove_diacritics 2", fold)):
print(f" {label:<36}" + "".join(h.center(7) for h in probe(tok, tr)))
print(" real query, accents dropped:", [r[0] for r in db.execute(
"""select a.title from articles_fold f join articles a on a.id = f.rowid
where articles_fold match 'sot cao' order by rank""")])
print("\n=== 6. the delete trigger everyone writes first ===")
bad = sqlite3.connect(":memory:")
bad.executescript("""
create table a(id integer primary key, body text);
create virtual table af using fts5(body, content='a', content_rowid='id');
create trigger a_ai after insert on a begin
insert into af(rowid, body) values (new.id, new.body); end;
create trigger a_ad after delete on a begin
delete from af where rowid = old.id; end;
""")
bad.execute("insert into a(body) values ('Cool the burn under running water')")
bad.execute("delete from a where id = 1")
print(" the DELETE raised nothing.")
print(" count(*) match 'burn' :", bad.execute("select count(*) from af where af match 'burn'").fetchone()[0])
for label, sql in (("select body", "select body from af where af match 'burn'"),
("snippet()", "select snippet(af,0,'[',']','...',6) from af where af match 'burn'"),
("integrity-check(0)", "insert into af(af, rank) values('integrity-check', 0)"),
("integrity-check(1)", "insert into af(af, rank) values('integrity-check', 1)")):
try: print(f" {label:<21}:", bad.execute(sql).fetchall() or "ok")
except sqlite3.Error as e: print(f" {label:<21}: {type(e).__name__}: {e}")
print("\n=== 7. no stemming unless you ask ===")
for tok in ("unicode61 remove_diacritics 2", "porter unicode61 remove_diacritics 2"):
t = sqlite3.connect(":memory:")
t.execute(f"create virtual table v using fts5(x, tokenize='{tok}')")
t.execute("insert into v(x) values ('Cool the burn under running water')")
print(f" {tok:<38}", {q: t.execute("select count(*) from v where v match ?", (q,)).fetchone()[0]
for q in ("burn", "burns", "burned", "running", "run")})
python3 fts5demo.py
The first line tells you whether your SQLite has FTS5 compiled in; if it does not, create virtual table ... using fts5 raises no such module: fts5 and the script stops there. Then do the experiment that matters. Drop the fold(...) wrapper from both articles_fold inserts and rerun. With the tokenizer left at remove_diacritics 2, sot cao still matches — and a query for duoc (from được in the same sentence) goes from one hit to none, because đ was never a diacritic. Then also set that tokenizer back to plain unicode61, and sot cao returns nothing either.
The sizes and timings quoted above come from a second script, fts5cost.py. It builds each variant from the same seeded corpus, so the megabytes are byte-identical on any machine with the same SQLite; the milliseconds are not, and will track your disk and CPU.
import sqlite3, os, random, time
W = [w.strip().lower() for w in open('/usr/share/dict/words')
if 3 <= len(w.strip()) <= 12 and w.strip().isalpha()]
random.seed(1234); VOCAB = random.sample(W, 4000); rng = random.Random(99)
DOCS = [(' '.join(rng.choices(VOCAB, k=5)), ' '.join(rng.choices(VOCAB, k=120)))
for _ in range(20000)]
TOK = "tokenize='unicode61 remove_diacritics 2'"
EXT = f"content='articles', content_rowid='id', {TOK}"
FOLD = f"create virtual table fold using fts5(txt, content='', contentless_delete=1, {TOK}, prefix='2 3')"
VARIANTS = {
"content table only, no FTS": [],
"+ FTS5, content='articles'": [f"create virtual table ft using fts5(title, body, {EXT})"],
"+ FTS5, content='articles', prefix='2 3'": [f"create virtual table ft using fts5(title, body, {EXT}, prefix='2 3')"],
"+ both indexes (fts + folded)": [f"create virtual table ft using fts5(title, body, {EXT}, prefix='2 3')", FOLD],
"+ FTS5 storing its own copy of the text": [f"create virtual table ft using fts5(title, body, {TOK})"],
}
def build(path, ddl):
if os.path.exists(path): os.remove(path)
db = sqlite3.connect(path)
db.execute("create table articles(id integer primary key, title text, body text)")
db.executemany("insert into articles(title, body) values (?,?)", DOCS)
for sql in ddl: db.execute(sql)
if ddl:
db.execute("insert into ft(rowid, title, body) select id, title, body from articles")
db.execute("insert into ft(ft) values('optimize')")
if len(ddl) > 1:
db.execute("insert into fold(rowid, txt) select id, title||' '||body from articles")
db.execute("insert into fold(fold) values('optimize')")
db.commit(); db.execute("vacuum"); db.close()
return os.path.getsize(path)
sizes = {n: build(f"/tmp/fts_{i}.db", d) for i, (n, d) in enumerate(VARIANTS.items())}
base = next(iter(sizes.values()))
for n, sz in sizes.items():
print(f" {n:<42}{sz/1048576:7.2f} MB" +
(" baseline" if sz == base else f" +{(sz-base)/1048576:5.2f} MB {sz/base:.2f}x"))
def best(db, sql, args, n):
db.execute(sql, args).fetchone()
b = float('inf')
for _ in range(5):
t0 = time.perf_counter()
for _ in range(n): db.execute(sql, args).fetchone()
b = min(b, (time.perf_counter() - t0) / n * 1000)
return b
MATCH = "select count(*) from ft where ft match ?"
plain, pref = sqlite3.connect("/tmp/fts_1.db"), sqlite3.connect("/tmp/fts_2.db")
print("\n prefix query, 20k docs, best of 5 timed batches")
for t, n in (("c*", 50), ("ca*", 100), ("car*", 500), ("carb*", 2000), ("carbo*", 2000)):
h = plain.execute(MATCH, (t,)).fetchone()[0]
print(f" {t:<8}{h:>6} hits no prefix index {best(plain, MATCH, (t,), n):7.3f} ms"
f" prefix='2 3' {best(pref, MATCH, (t,), n):7.3f} ms")
LIKE = "select count(*) from articles where title like ? or body like ?"
print("\n LIKE vs MATCH, same rows")
for w in ("limes", "ovogenesis"):
la = best(plain, LIKE, (f"%{w}%",) * 2, 20)
ma = best(plain, MATCH, (w,), 2000)
h = plain.execute(LIKE, (f"%{w}%",) * 2).fetchone()[0]
print(f" {w:<12} LIKE {la:7.2f} ms ({h} rows) MATCH {ma:6.3f} ms ({h} rows) {la/ma:.0f}x")
python3 fts5cost.py
It needs /usr/share/dict/words, which macOS and most Linux distributions ship; any newline-delimited word list works in its place.
The code
Runnable, and CI keeps it that way: CSTSolution/examples/sqlite-fts5 — the schema, the correct delete trigger and the ranking demo.
git clone https://github.com/CSTSolution/examples
cd examples/sqlite-fts5
Where this goes next
This is the search layer in MedSearch — a reference that has to answer while the phone is in airplane mode, which rules out a search API and rules in a single SQLite file on the device, index and all.
Two threads run on from here. Getting the text into those rows in useful pieces is its own problem: chunking a document without destroying its meaning. And the reason an offline index is worth the size cost at all is the same reason we triage a mailbox without sending it — the strongest privacy control is the request you never make.
