Full-Text Search

Creating an FTS5 table

FTS5 is a virtual table module built into the standard amalgamation. Every column is indexed text, and there are no types, so do not declare any.

CREATE VIRTUAL TABLE docs_fts USING fts5(title, body);
INSERT INTO docs_fts(title, body)
VALUES('SQLite indexes','A covering index answers the query from the index alone.');
SELECT rowid, title FROM docs_fts WHERE docs_fts MATCH 'index';

CREATE VIRTUAL TABLE docs_fts USING fts5(
  title,
  body,
  author UNINDEXED,
  tokenize = 'porter unicode61 remove_diacritics 2',
  prefix = '2 3'
);

Notes that save time. Every FTS5 column has a hidden rowid, and that rowid is how you join back to real data. SELECT * FROM docs_fts returns the text columns, not the rank. There are no constraints, no NOT NULL, no UNIQUE, and no secondary indexes on an FTS5 table.

Query syntax

The query string on the right of MATCH has its own grammar, unrelated to SQL.

SELECT rowid FROM docs_fts WHERE docs_fts MATCH 'quer*';
SELECT rowid FROM docs_fts WHERE docs_fts MATCH 'title:search';
SELECT rowid FROM docs_fts WHERE docs_fts MATCH 'NEAR(readers writer, 5)';
SELECT rowid FROM docs_fts WHERE docs_fts MATCH '"covering index"';
SELECT rowid FROM docs_fts WHERE docs_fts MATCH 'index NOT covering';
SELECT rowid FROM docs_fts('index');          -- the table-function form, identical
SELECT rowid FROM docs_fts WHERE docs_fts = 'index';   -- also identical

AND, OR, NOT, and NEAR must be uppercase, otherwise they are treated as ordinary search tokens. Precedence is NOT then AND then OR, and there is no short-circuit, so parenthesize anything mixed.

Never interpolate user input into a MATCH string. Bind it, and if the user may type punctuation, quote each token so a stray " or * cannot become syntax.

-- Safe: bind the whole query
SELECT rowid FROM docs_fts WHERE docs_fts MATCH ?;
-- Safer for arbitrary user text: build a quoted phrase list in the application
--   'quick brown' becomes  "quick" "brown"
--   and add a trailing * on the last token for as-you-type search

A double quote inside a quoted phrase is escaped by doubling it. Anything the tokenizer discards, punctuation included, is simply dropped from the query.

Error: fts5: syntax error near ""

That is what an unbalanced quote or a bare * produces, which is exactly why the tokens get quoted in the application layer.

Ranking with rank and bm25

The hidden rank column is the BM25 score, and it is negative, with more negative meaning more relevant. So ORDER BY rank sorts best first, with no DESC.

SELECT rowid, title, rank
FROM docs_fts
WHERE docs_fts MATCH 'index'
ORDER BY rank
LIMIT 20;
-- 1|SQLite indexes|-0.792733540616102

bm25() is the same function with explicit per-column weights, one float per indexed column in declaration order. A weight of 0 excludes a column from scoring while still letting it match.

-- Weight the title ten times the body
SELECT rowid, bm25(docs_fts, 10.0, 1.0) AS score
FROM docs_fts WHERE docs_fts MATCH 'search'
ORDER BY score LIMIT 20;
-- 2|-1.01298400471093

Set a default ranking for the table once, and every later ORDER BY rank uses it.

INSERT INTO docs_fts(docs_fts, rank) VALUES('rank','bm25(10.0, 1.0)');
SELECT rowid, rank FROM docs_fts WHERE docs_fts MATCH 'search' ORDER BY rank;

Two things to know. rank is only available in a query that has a MATCH, and referencing it elsewhere is an error. And ORDER BY rank DESC gives you the worst results, which is the most common mistake with FTS5.

Combining a text score with a business signal is a plain arithmetic expression.

SELECT d.id, d.title,
       bm25(f, 10.0, 1.0) + (-0.5 * min(d.views, 1000) / 1000.0) AS score
FROM docs_fts f JOIN docs d ON d.id = f.rowid
WHERE f MATCH ?
ORDER BY score
LIMIT 20;

highlight and snippet

Both take the FTS5 table name, a 0-based column index, and markers.

SELECT highlight(docs_fts, 1, '[', ']')
FROM docs_fts WHERE docs_fts MATCH 'index';
-- A covering [index] answers the query from the [index] alone.

SELECT snippet(docs_fts, 1, '<b>', '</b>', '...', 8)
FROM docs_fts WHERE docs_fts MATCH 'index';
-- A covering <b>index</b> answers the query from the...

The markers are inserted as raw text, so escape for HTML in your template layer rather than trusting '<b>' here. highlight on a long body returns the entire body, which is why snippet exists. Both need position information, so they do not work with detail=none.

Tokenizers

The tokenizer decides what counts as a word, and changing it requires rebuilding the index.

-- Stemming: 'running' and 'runs' both match 'run'
tokenize = 'porter unicode61'
-- Fold accents: 'café' matches 'cafe'.  1 = legacy, 2 = full Unicode
tokenize = 'unicode61 remove_diacritics 2'
-- Treat hyphen and underscore as part of a word
tokenize = "unicode61 tokenchars '-_'"
-- Treat a period as a separator even though unicode61 would keep it
tokenize = "unicode61 separators '.'"

porter must be listed first, as a wrapper around the real tokenizer. Stemming is English-only and it is aggressive, so universal and universe collide. It also means a phrase query matches the stemmed forms, which is usually what a user wants.

trigram (3.34+) is the one that enables substring search, including making LIKE and GLOB use the index.

CREATE VIRTUAL TABLE tri USING fts5(t, tokenize='trigram');
INSERT INTO tri VALUES('hello world');
SELECT rowid FROM tri WHERE tri MATCH '"ell"';       -- 1
SELECT rowid FROM tri WHERE t LIKE '%llo wo%';       -- 1, uses the trigram index

Trigram costs: the index is several times larger, the search term must be at least 3 characters, and case_sensitive and remove_diacritics are its only options. Use it for code search, identifiers, and infix matching, not for prose.

External content tables

By default FTS5 stores a second copy of your text. content= points it at a real table instead, so the text lives once. The FTS5 rowid must map to that table's rowid via content_rowid=.

CREATE TABLE docs(id INTEGER PRIMARY KEY, title TEXT, body TEXT);

CREATE VIRTUAL TABLE docs_fts USING fts5(
  title, body,
  content = 'docs',
  content_rowid = 'id',
  tokenize = 'porter unicode61'
);

-- Backfill an existing table
INSERT INTO docs_fts(rowid, title, body) SELECT id, title, body FROM docs;

FTS5 does not keep itself in sync. Three triggers do that, and all three are required.

CREATE TRIGGER docs_ai AFTER INSERT ON docs BEGIN
  INSERT INTO docs_fts(rowid, title, body)
  VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER docs_ad AFTER DELETE ON docs BEGIN
  INSERT INTO docs_fts(docs_fts, rowid, title, body)
  VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER docs_au AFTER UPDATE ON docs BEGIN
  INSERT INTO docs_fts(docs_fts, rowid, title, body)
  VALUES ('delete', old.id, old.title, old.body);
  INSERT INTO docs_fts(rowid, title, body)
  VALUES (new.id, new.title, new.body);
END;

The 'delete' command row is the part everyone gets wrong. With content= set, a plain DELETE FROM docs_fts is not allowed, and you must instead insert a special row whose first value is 'delete' and whose other values exactly reproduce what was indexed. Pass the wrong old values and the index is silently corrupted, with no error until a later query returns wrong results.

If the content table and the index drift apart, a query raises rather than lying:

Error: fts5: missing row 1 from content table 'main'.'docs'

That happens whenever a row is deleted from docs without the matching 'delete' going into docs_fts. The fix is a rebuild.

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

content='' is the contentless variant. It stores no text at all, so highlight and snippet do not work and SELECT title FROM docs_fts returns NULL, but the index is small and matching plus ranking still work. From 3.47 a contentless table can be declared contentless_delete=1 so ordinary DELETE works on it.

A typical search query joins the index back to the real table.

SELECT d.id, d.title, snippet(f, 1, '<mark>', '</mark>', '…', 12) AS excerpt
FROM docs_fts f
JOIN docs d ON d.id = f.rowid
WHERE f MATCH ?
ORDER BY f.rank
LIMIT 20 OFFSET ?;

Maintenance and special commands

FTS5 is configured and maintained by inserting command rows whose first column is the table's own name.

INSERT INTO docs_fts(docs_fts) VALUES('optimize');
INSERT INTO docs_fts(docs_fts) VALUES('integrity-check');
INSERT INTO docs_fts(docs_fts) VALUES('rebuild');
INSERT INTO docs_fts(docs_fts, rank) VALUES('merge', 500);   -- incremental

Run 'optimize' after a bulk load and then occasionally, for example nightly. It rewrites the whole index in one transaction, so it blocks writers for as long as it takes. Prefer 'merge' with a page budget on a live database.

The fts5vocab module exposes the vocabulary, which is how you build autocomplete or inspect what the tokenizer actually produced.

CREATE VIRTUAL TABLE docs_vocab USING fts5vocab('docs_fts', 'row');
SELECT term, doc, cnt FROM docs_vocab ORDER BY cnt DESC LIMIT 20;
-- 'col' and 'instance' are the other two modes

FTS5 versus FTS4, and versus LIKE

FTS3 and FTS4 still exist for compatibility. They are not the same module and their query syntax differs.

Use FTS5 for anything new. Read a legacy schema carefully, because MATCH 'a NEAR/5 b' is valid FTS4 and a plain three-token AND search in FTS5.

When not to reach for FTS5 at all:

- A prefix search on a single column is just an index. WHERE name LIKE 'ada%' with COLLATE NOCASE on the column is an index seek and needs no extra table. - An exact-token lookup, for example tags, belongs in a normalized join table. - A tiny table, under a few thousand short rows, scans faster than the join costs.

Reach for FTS5 when you need relevance ranking, phrase and proximity queries, stemming, or snippets over a body of prose. Reach for the trigram tokenizer when you need real substring search that an index can serve.