Full-Text Search in SQLite with FTS5

A LIKE '%term%' query scans every row and can't rank results, match word stems, or handle phrase queries. For real search over text columns, SQLite ships a full-text search engine called FTS5. It builds an inverted index, supports boolean and phrase queries through the MATCH operator, and ranks results with BM25 out of the box.

FTS5 is an extension that is compiled into most SQLite builds by default (it has been the recommended FTS module since SQLite 3.9.0, 2015). The older FTS3 and FTS4 modules still exist for backward compatibility, but FTS5 has better query syntax, built-in ranking, and a cleaner external-content design. Use FTS5 unless you're maintaining an old schema.

Creating an FTS5 table

FTS5 tables are virtual tables. You declare the columns you want to index, and nothing else: FTS5 rejects type names, constraints, and PRIMARY KEY declarations.

CREATE VIRTUAL TABLE articles_fts USING fts5(title, body);
 
INSERT INTO articles_fts (title, body) VALUES
  ('SQLite FTS5', 'Full-text search using an inverted index.'),
  ('Window Functions', 'Running totals and rankings without self-joins.'),
  ('Indexes', 'B-tree indexes speed up equality and range queries.');

Every FTS5 table has a hidden rowid column, just like an ordinary table. You query it with the MATCH operator:

SELECT rowid, title
FROM articles_fts
WHERE articles_fts MATCH 'search';

The match expression is matched against all indexed columns by default. To restrict it to one column, qualify the term:

SELECT title FROM articles_fts WHERE articles_fts MATCH 'title:functions';

Query syntax

FTS5's query language covers most of what people expect from a search box:

-- AND is implicit between terms
SELECT * FROM articles_fts WHERE articles_fts MATCH 'index search';
 
-- explicit boolean operators (must be uppercase)
SELECT * FROM articles_fts WHERE articles_fts MATCH 'index OR ranking';
SELECT * FROM articles_fts WHERE articles_fts MATCH 'search NOT phrase';
 
-- phrase query (exact sequence)
SELECT * FROM articles_fts WHERE articles_fts MATCH '"inverted index"';
 
-- prefix query
SELECT * FROM articles_fts WHERE articles_fts MATCH 'rank*';
 
-- NEAR: terms within N tokens of each other (default 10)
SELECT * FROM articles_fts WHERE articles_fts MATCH 'NEAR(full text, 5)';

One gotcha: if user input contains characters FTS5 treats as syntax (quotes, parentheses, the * wildcard), an unescaped query can throw a syntax error. For untrusted input, wrap each token in double quotes and double any internal quotes, or build the match string yourself rather than passing raw text straight through.

Ranking with bm25

The reason to use FTS5 over LIKE is ranking. When a query uses MATCH, FTS5 exposes a special rank column that is the BM25 score: lower (more negative) is a better match. Order by it ascending to get the most relevant rows first.

SELECT title, rank
FROM articles_fts
WHERE articles_fts MATCH 'index'
ORDER BY rank;

You can weight columns so a hit in the title counts more than one in the body. The bm25() function takes one weight per column:

SELECT title, bm25(articles_fts, 10.0, 1.0) AS score
FROM articles_fts
WHERE articles_fts MATCH 'functions'
ORDER BY score;

Here the title column is weighted 10x relative to the body.

Highlighting matches

FTS5 includes two auxiliary functions for showing matches in context. highlight() wraps matched terms in markers; snippet() extracts a short window of text around the match.

-- wrap matches in <b>...</b>, column index 1 = body
SELECT highlight(articles_fts, 1, '<b>', '</b>')
FROM articles_fts
WHERE articles_fts MATCH 'index';
 
-- snippet: column 1, same markers, '...' ellipsis, max 10 tokens
SELECT snippet(articles_fts, 1, '<b>', '</b>', '...', 10)
FROM articles_fts
WHERE articles_fts MATCH 'index';

External content tables

By default an FTS5 table stores its own copy of the indexed text, which doubles storage if you already keep the data in a normal table. External content tables fix this: the FTS5 index points at an existing table and stores only the index, not the source text.

CREATE TABLE articles (
  id    INTEGER PRIMARY KEY,
  title TEXT,
  body  TEXT
);
 
CREATE VIRTUAL TABLE articles_fts USING fts5(
  title, body,
  content='articles',
  content_rowid='id'
);

The catch is that FTS5 no longer sees writes to the base table automatically. You keep the index in sync with triggers:

CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts (rowid, title, body)
  VALUES (new.id, new.title, new.body);
END;
 
CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
  INSERT INTO articles_fts (articles_fts, rowid, title, body)
  VALUES ('delete', old.id, old.title, old.body);
END;
 
CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
  INSERT INTO articles_fts (articles_fts, rowid, title, body)
  VALUES ('delete', old.id, old.title, old.body);
  INSERT INTO articles_fts (rowid, title, body)
  VALUES (new.id, new.title, new.body);
END;

The 'delete' command-row syntax is how FTS5 removes entries from an external-content index: you must supply the old values so it can locate the index records. To index data that already exists, run INSERT INTO articles_fts(articles_fts) VALUES('rebuild');.

If you don't need to retrieve the original text at all (your app fetches it elsewhere by rowid), a fully contentless table with content='' stores the index only and skips even the base-table dependency.

Tokenizers

A tokenizer decides how text is split into searchable terms. FTS5 ships several:

• unicode61 (default): splits on Unicode separators, case-insensitive, optional diacritic folding.
• ascii: like unicode61 but only folds ASCII.
• porter: wraps another tokenizer and applies the Porter stemming algorithm, so running matches run.
• trigram (added 3.34.0, 2020): indexes three-character sequences, which makes substring and LIKE-style matching fast, at the cost of a larger index.

-- stemming: a search for 'connect' also matches 'connecting', 'connected'
CREATE VIRTUAL TABLE docs_fts USING fts5(body, tokenize='porter unicode61');
 
-- trigram: supports substring search the others can't do
CREATE VIRTUAL TABLE code_fts USING fts5(snippet, tokenize='trigram');

Common mistakes

• Using LIKE because FTS5 "isn't available." It almost always is. Check with SELECT sqlite_compileoption_used('ENABLE_FTS5');.
• Forgetting that rank only exists inside a MATCH query. Selecting rank without a MATCH clause is an error.
• Passing raw user input to MATCH. Special characters cause syntax errors. Sanitize or quote tokens.
• Expecting external-content tables to auto-sync. They don't. Without triggers (or manual maintenance), the index drifts from the base table.
• Reaching for stemming when you need substrings. The porter tokenizer stems whole words; it won't match a fragment in the middle of a word. Use the trigram tokenizer for that.

For more on plain B-tree indexes and when each kind helps, see our SQLite indexes guide.

When you're iterating on a search query and tuning column weights or NEAR distances, Mako's AI autocomplete can scaffold the MATCH expression and bm25() weights so you can adjust them against live results.

Mako connects to SQLite and eight other databases with AI-powered autocomplete. Try it free at mako.ai.