TopolearnFTS5 gives apps proper search over notes, messages or documents with no extra service.
LIKE '%word%' scans every row and can't rank results. FTS5 is SQLite's full-text search extension (included in most builds, including Python's). It stores an inverted index from tokens to rows.
- Create a virtual table:
CREATE VIRTUAL TABLE doc_fts USING fts5(title, body). All columns are text and untyped. - Query with
MATCH:WHERE doc_fts MATCH 'sqlite AND wal'. The query syntax supports implicit AND between terms,OR,NOT,"exact phrases", prefix searchdata*, column filterstitle:sqliteandNEAR(a b, 5). - Rank with the hidden
rankcolumn (ORDER BY rank), which defaults to bm25;bm25(doc_fts, 10.0, 1.0)weights the title higher. highlight(doc_fts, 1, '[', ']')andsnippet(...)produce marked-up excerpts.- Tokenizers:
unicode61(default, case-folding and diacritic removal),porterfor English stemming (tokenize='porter unicode61'), andtrigramfor substring search.
To avoid storing text twice, use an external-content table (content='docs', content_rowid='id') that indexes rows of a normal table; then you keep the index in sync with triggers on that table.
User input can contain characters that are FTS5 syntax, so a raw search string may raise a syntax error; quote it as a phrase (double the " characters inside) when you don't want operators.
Example
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE VIRTUAL TABLE note USING fts5(title, body, tokenize='porter unicode61')")
con.executemany("INSERT INTO note (title, body) VALUES (?, ?)", [
("Tuning SQLite", "Enable WAL mode and run PRAGMA optimize."),
("Indexes", "Covering indexes avoid table lookups when running queries."),
("Shopping", "Milk, bread and eggs."),
])
q = "SELECT title FROM note WHERE note MATCH ? ORDER BY rank"
print(con.execute(q, ("run",)).fetchall()) # porter: 'run' matches 'running' and 'run'
# [('Tuning SQLite',), ('Indexes',)] (order by bm25 score)
print(con.execute(q, ("title:sqlite OR eggs",)).fetchall())
# [('Shopping',), ('Tuning SQLite',)]
print(con.execute('''SELECT highlight(note, 1, '[', ']') FROM note
WHERE note MATCH 'wal' ''').fetchone()[0])
# Enable [WAL] mode and run PRAGMA optimize.
user_input = 'C++ "fast"'
safe = '"' + user_input.replace('"', '""') + '"' # treat as a phrase
print(con.execute(q, (safe,)).fetchall()) # [] and no syntax errorRemember
- FTS5 virtual tables + MATCH give indexed word search
- ORDER BY rank sorts by bm25 relevance
- Quote user input as a phrase to avoid FTS5 syntax errors
Watch out for
- Passing raw user input to MATCH and crashing on characters like quotes or hyphens.