Full-Text Search in Local Flutter Databases

WHY IT EXISTS: Local Flutter apps often ship with offline datasets that grow into the tens of thousands of rows. Using LIKE with leading and trailing wildcards forces SQLite to inspect every row and perform a substring comparison, which is linear time and visibly lags the UI thread. Full-text search was invented to solve exactly this by preprocessing text into a queryable index, turning multi-second waits into millisecond results.

THE MENTAL MODEL: Think of a book index at the end of a textbook. Instead of reading every page to find a word, you look in the index and jump straight to the pages listed. An FTS table is that index living inside your database. It breaks text into tokens, normalizes case and diacritics, and records which row contains each token. When you search, the engine intersects token lists to find matching rows instantly rather than scanning the whole table.

HOW IT WORKS: In SQLite you create a virtual table with CREATE VIRTUAL TABLE name USING fts5 followed by your text column. The fts5 module builds an inverted index behind the scenes. You query it with SELECT rowid FROM table WHERE table MATCH 'flutter AND sqlite', and SQLite returns only rows containing both words. Because the table is virtual, it does not store full row data in the same way as ordinary tables; it stores the index. You typically keep your main table for structured columns and mirror only the searchable text into the FTS table, then join on rowid to retrieve metadata. Advanced tokenizers like unicode61 handle accents, while the porter tokenizer applies stemming so searching for run also matches running.

WHEN TO USE IT: Use local FTS when your app stores large offline text corpora such as chat histories, note collections, or product catalogs and users expect instant search with relevance ranking. It also shines when you need prefix matching or Boolean logic like requiring multiple terms. If you are using Drift, its built-in FTS support abstracts much of the boilerplate, but the same principles apply under the hood.

WHEN NOT TO USE IT: Do not use FTS for small datasets where a simple LIKE query on an indexed column is fast enough, because the extra virtual table adds maintenance overhead. Avoid it if you rely on foreign key constraints or complex triggers on the searchable text, since virtual tables do not enforce foreign keys the same way ordinary tables do. Also skip it if you only need exact key lookups rather than content search.

ONE CANONICAL EXAMPLE: Imagine a journaling app built with sqflite that stores fifty thousand entries. You create an fts5 virtual table on entry bodies and query by joining entries to entries_fts on rowid, matching the term 'flutter' and ordering by rank. To keep the index fresh, you wrap every insert, update, and delete in your repository layer so it writes to both the main entries table and the entries_fts table in the same transaction. If you forget the dual write, the user searches for a recent entry and gets zero results with no crash, making the bug hard to detect.