SQLite Full-Text Search with Spellfix

Published 2025 August 14

I would like to improve the full-text search capabilities of this website. Right now the search is built with the FTS5 extension which can be compiled with the main SQLite application. It's pretty handy. SQLite also supports a spellfix extension. Essentially, you can load the spellfix table with a vocabulary and then use it to fuzzy match misspelled words. Conveniently, the FTS5 extension also includes a vocabulary table which you can insert into the spellfix table.

Here is an example of how to do this. I am using the fileio extension to read Hamlet into a full-text search table. Then I load the vocabulary from Hamlet into the the spellfix table.

create virtual table hamlet_fts using fts5(content);
create virtual table hamlet_vocab using fts5vocab('hamlet_fts', 'row');
create virtual table hamlet_spellfix using spellfix1;

insert into hamlet_fts(content) select readfile('hamlet.txt');
insert into hamlet_spellfix(word, rank) select term, cnt from hamlet_vocab;

Then in your application you can preprocess full-text searches for potential misspellings. I still need some time to work out how exactly I want it all to work but the results are encouraging. This feature of SQLite is what got me excited about it in the first place. SQLite is definitely punching above its weight!