Full-text search

In the query optimization lesson, we saw that LIKE '%something%' always forces a full table scan. That was the end of the story for regular indexes. No index can help, because the pattern can start anywhere in the text. But searching text is such a common feature (think of every search box you have ever used) that we need a proper answer. SQLite ships one built in. It is called FTS5, and this lesson is about wiring it into our schema.

Let’s start with the obvious attempt. Say we want to search books by title:

SELECT * FROM books WHERE title LIKE '%old man%';

This works, but it has two problems. First, it scans every row. No index can help when the pattern starts with %. With 100,000 books, it is slow. Second, it only finds exact substrings. Searching “old men” returns nothing, even though “old man” is close.

SQLite has a better tool for this: FTS5.

FTS5: full-text search

FTS5 is a full-text search engine built into SQLite. It creates a special table that indexes text for fast, flexible searching. The table itself is created with CREATE VIRTUAL TABLE, and like every other schema change in this course, we add it through a migration.

-- migrations/016_add_books_search.sql
BEGIN;

CREATE VIRTUAL TABLE books_fts USING fts5(title, description, content=books);

CREATE TRIGGER books_fts_insert AFTER INSERT ON books BEGIN
  INSERT INTO books_fts(rowid, title, description) VALUES (NEW.rowid, NEW.title, NEW.description);
END;

CREATE TRIGGER books_fts_update AFTER UPDATE ON books BEGIN
  INSERT INTO books_fts(books_fts, rowid, title, description) VALUES('delete', OLD.rowid, OLD.title, OLD.description);
  INSERT INTO books_fts(rowid, title, description) VALUES (NEW.rowid, NEW.title, NEW.description);
END;

CREATE TRIGGER books_fts_delete AFTER DELETE ON books BEGIN
  INSERT INTO books_fts(books_fts, rowid, title, description) VALUES('delete', OLD.rowid, OLD.title, OLD.description);
END;

INSERT INTO schema_migrations (version, name)
VALUES ('016', 'add_books_search');

COMMIT;

There is a lot in that migration, so let’s walk through it one piece at a time.

The virtual table is the first line:

CREATE VIRTUAL TABLE books_fts USING fts5(title, description, content=books);

This creates a search index over the title and description columns. content=books tells FTS5 that the source data lives in the books table.

[!NOTE] FTS5 virtual tables are not regular tables. They are a special data structure optimized for text search. You query them with the MATCH operator instead of LIKE. FTS5 uses SQLite’s internal rowid to link back to the source table.

The three triggers keep the FTS index in sync with the books table automatically. One fires after an INSERT (index the new row), one fires after an UPDATE (remove the old indexed copy, insert the new one), and one fires after a DELETE (remove the indexed copy). Once this migration runs, we do not have to think about the index again. It updates itself.

Populating the index for existing rows

The triggers only fire on future changes. If our books table already has rows, we need to populate the FTS index for those too:

INSERT INTO books_fts(books_fts) VALUES('rebuild');

The rebuild command reads all rows from the books table and indexes them. You would typically run this once, right after applying the migration that creates the FTS table, to backfill any data that existed before the triggers were in place.

Searching

Once the index exists and is populated, we can search it:

SELECT books.*
FROM books_fts
JOIN books ON books.rowid = books_fts.rowid
WHERE books_fts MATCH 'old man';

The MATCH operator searches the FTS index. It finds words, not substrings. “old man” matches “The Old Man and the Sea” because both “old” and “man” appear as individual words in the title.

Ranking results

FTS5 provides a rank column that scores results by relevance:

SELECT books.title, books_fts.rank
FROM books_fts
JOIN books ON books.rowid = books_fts.rowid
WHERE books_fts MATCH 'old man'
ORDER BY books_fts.rank;  -- Lower rank = more relevant

Results with more matches and closer word positions score higher.

Search operators

FTS5 supports several operators for more precise searching:

-- All words must appear
WHERE books_fts MATCH 'old AND man'

-- Either word can appear
WHERE books_fts MATCH 'old OR man'

-- Exact phrase
WHERE books_fts MATCH '"old man"'

-- Word prefix (matches "sea", "search", "season")
WHERE books_fts MATCH 'sea*'

-- Exclude a word
WHERE books_fts MATCH 'man NOT spider'

This is exactly how production search boxes work. A user types a query, you pass it straight through to MATCH, and FTS5 handles the parsing and ranking.

Highlighting matches

SELECT highlight(books_fts, 0, '<b>', '</b>') AS highlighted_title
FROM books_fts
WHERE books_fts MATCH 'old man';
-- Result: "The <b>Old</b> <b>Man</b> and the Sea"

The highlight function wraps matched words in the tags you specify. The second argument (0) is the column index (0 = title, 1 = description).

When to use FTS5

Use FTS5 for search boxes, autocomplete, searching long text fields (descriptions, articles, messages). Any “find items matching this query” feature.

Do not use FTS5 for exact lookups (WHERE email = ?), numeric comparisons (WHERE price > 10), or structured filtering (WHERE genre = 'fiction'). Regular indexes handle those.

Combine both when you need text search with structured filtering:

SELECT books.*
FROM books_fts
JOIN books ON books.rowid = books_fts.rowid
WHERE books_fts MATCH 'sea'
AND books.genre = 'fiction';

That last query is a good one to sit with for a moment. FTS5 for the text part, regular indexes for the structured part, all joined through the rowid. This is how real search features are built.

Exercises

Exercise 1: Write a migration that creates an FTS5 table for books along with the three sync triggers. After running it, populate the index with rebuild. Search for a word and verify results appear.

Exercise 2: Search for a phrase with quotes. Search with a prefix. Search with NOT. Verify each operator works as expected.

Exercise 3: Add ranking to your search. Search for a common word. Verify that the most relevant results appear first.

Exercise 4: Use highlight to display search results with matching words wrapped in bold tags.

That is the last SQL-side tool in the toolbox. We have designed schemas, related tables, moved changes through migrations, indexed columns, optimized queries, wrapped writes in transactions, configured our connections, and added full-text search. Up to this point though, we have been talking about SQLite as if it lives on your laptop. For a real app, we need to wire it into application code and plan for disk failure. In the next section, we will look at using the built-in node:sqlite client, and then at keeping the database durable with Litestream.