How to do Full Text Search with SQLite

SQLite has a powerful way to add new functionality via loadable extensions. The first-party ones include fts5, json1 and a couple others.

When building applications it is common to add searching features based on data coming from tables and you may already have queries for fuzzy searching with LIKE. You may be excited to hear that SQLite can easily add fully query capabilities over a dataset all with just a simple MATCH keyword. 👀

Creating your first search index 

Full text search in SQLite requires storing the index in VIRTUAL tables, which allow for optimized storage of the index based on the queries we will execute against it.

You can create the virtual table for the index making sure to include the USING directive for the fts5 target.