SQLite § full-text-search

Full-text search (FTS5)

Full-text search — the retrieval of documents containing a query of words or phrases, ranked by relevance — is implemented in SQLite by the FTS5 virtual-table module, the fifth-generation full-text-search extension. FTS5 is bundled with the standard SQLite distribution and provides a complete inverted-index implementation: tokenisation, prefix matching, phrase queries, boolean operators, ranking, snippet and highlight extraction, and a custom-tokeniser API for application-specific text.

This page covers the syntax of CREATE VIRTUAL TABLE … USING fts5, the MATCH operator and its query-string grammar, the bundled tokenisers and their trade-offs, the bm25 ranking function, the highlight and snippet helpers, the contentless and external-content table forms for storage efficiency, and the maintenance commands.

CREATE VIRTUAL TABLE … USING fts5

The simplest form creates a full-text-indexed table with one or more text columns:

CREATE VIRTUAL TABLE article_fts USING fts5(title, body);

Each column accumulates an inverted index — a mapping from token to the row(s) containing the token. The columns are unconstrained TEXT; FTS5 does not enforce types beyond storing the text.

Rows are inserted, updated, and deleted as they would be in an ordinary table:

INSERT INTO article_fts (title, body) VALUES
    ('SQLite', 'A small, embedded, public-domain database engine.'),
    ('Lua',    'A small, embeddable, dynamically typed scripting language.');

UPDATE article_fts SET body = body || ' Now in version 3.46.' WHERE rowid = 1;
DELETE FROM article_fts WHERE rowid = 2;

The internal index is maintained automatically; the application sees an ordinary-looking table.

The MATCH operator

The MATCH operator is the entry point for FTS5 queries:

SELECT rowid, title FROM article_fts WHERE article_fts MATCH 'sqlite';

The left operand of MATCH is the FTS5 table itself (or a column of it), and the right operand is the query string — a small DSL that FTS5 interprets to produce the match set.

A query of a single word matches any row containing the word in any indexed column:

WHERE article_fts MATCH 'sqlite'           -- 'sqlite' anywhere
WHERE article_fts MATCH 'database'         -- 'database' anywhere

A column-qualified query restricts the match to a specific column:

WHERE article_fts MATCH 'title:sqlite'     -- 'sqlite' only in title
WHERE article_fts MATCH 'body:database'    -- 'database' only in body

Multiple terms separated by spaces require all terms to appear:

WHERE article_fts MATCH 'sqlite database'  -- both 'sqlite' AND 'database'

Boolean operators express disjunction and negation:

WHERE article_fts MATCH 'sqlite OR mysql'      -- either
WHERE article_fts MATCH 'sqlite NOT mysql'     -- 'sqlite' but not 'mysql'
WHERE article_fts MATCH '(sqlite OR mysql) AND embedded'

A phrase query, in double quotes, requires the words in sequence:

WHERE article_fts MATCH '"public domain"'      -- exact phrase

A prefix query with a trailing asterisk matches any word starting with the prefix:

WHERE article_fts MATCH 'embed*'               -- embed, embedded, embeddable, …

A near query restricts the matches to terms within n tokens of each other:

WHERE article_fts MATCH 'NEAR(sqlite database, 5)'

The full grammar is documented at sqlite.org/fts5.html#full_text_query_syntax.

Ranking with bm25

A query that returns multiple rows is typically ordered by relevance. FTS5 ships with the BM25 ranking function — a widely used probabilistic ranking algorithm — accessible through the bm25() function:

SELECT rowid, title, bm25(article_fts) AS score
FROM article_fts
WHERE article_fts MATCH 'sqlite database'
ORDER BY score;

bm25 returns a smaller (more-negative) value for more-relevant rows; the conventional ORDER BY is ascending. The function may be invoked with weights for each column, providing per-column relevance tuning:

-- title weighted twice as heavily as body
ORDER BY bm25(article_fts, 2.0, 1.0)

For applications that need a different ranking (TF-IDF, custom tuning), FTS5 exposes the underlying token positions and document statistics through the xColumnSize, xPhraseCount, and related auxiliary functions; a custom ranking function can be implemented through the C API.

Highlight and snippet

Two helper functions extract presentation-ready text from matched rows:

SELECT
    rowid,
    highlight(article_fts, 1, '<b>', '</b>') AS highlighted_body,
    snippet(article_fts, 1, '<b>', '</b>', '…', 16) AS snippet
FROM article_fts
WHERE article_fts MATCH 'sqlite';

highlight(table, column, open, close) returns the value of the named column with each matching token wrapped in the open/close strings.

snippet(table, column, open, close, ellipsis, n) returns a short excerpt from the column — at most n tokens — centred on the match, with omitted text replaced by ellipsis. The function is the canonical mechanism for search-result previews.

Tokenisers

A tokeniser breaks an input string into a sequence of tokens that are then indexed. FTS5 ships four built-in tokenisers:

Tokeniser	Behaviour
`unicode61`	Default. Splits on Unicode word boundaries, case-folds, removes diacritics.
`ascii`	Splits on ASCII word boundaries; case-folds; non-ASCII passes through.
`porter`	The unicode61 output, then Porter stemming for English.
`trigram`	Three-character overlapping windows; useful for substring search.

The tokeniser is selected at table creation:

CREATE VIRTUAL TABLE article_fts USING fts5(
    title, body,
    tokenize = 'porter unicode61'
);

The tokenisation is applied at index time (when rows are inserted) and at query time (when the MATCH string is parsed). The same tokeniser must be used for both; FTS5 applies the table’s tokeniser automatically.

porter is the conventional choice for English-language search — it stems running and runs to run, increasing recall. unicode61 is the language-agnostic default. trigram enables substring search (MATCH 'cli' matches client, recline, acclimate); the trade-off is a substantially larger index.

A custom tokeniser — a C function that produces tokens — can be registered through the FTS5 API. The conventional uses are language-specific stemming, lemmatisation through an external library (Apache Lucene’s stemmers, the snowball-stemmer family), and domain-specific token extraction (chemical formulae, code identifiers).

Contentless and external-content tables

By default, FTS5 stores the original text alongside the index, doubling the storage. Two storage variants reduce the cost.

A contentless table stores only the index; the original text must be retrieved from elsewhere (or reconstructed through highlight and snippet):

CREATE VIRTUAL TABLE article_fts USING fts5(title, body, content='');

The contentless form halves storage but forbids SELECT title FROM article_fts; only MATCH queries and the auxiliary functions work. The pattern is appropriate when the original text lives in another table and FTS5 is purely a search index.

An external-content table stores the index and references rows of an external table:

CREATE TABLE article (
    id    INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body  TEXT NOT NULL
);

CREATE VIRTUAL TABLE article_fts USING fts5(
    title, body,
    content='article',
    content_rowid='id'
);

-- Application is responsible for keeping the index in sync:
CREATE TRIGGER article_ai AFTER INSERT ON article BEGIN
    INSERT INTO article_fts(rowid, title, body) VALUES (NEW.id, NEW.title, NEW.body);
END;
CREATE TRIGGER article_ad AFTER DELETE ON article BEGIN
    INSERT INTO article_fts(article_fts, rowid, title, body)
    VALUES('delete', OLD.id, OLD.title, OLD.body);
END;
CREATE TRIGGER article_au AFTER UPDATE ON article BEGIN
    INSERT INTO article_fts(article_fts, rowid, title, body)
    VALUES('delete', OLD.id, OLD.title, OLD.body);
    INSERT INTO article_fts(rowid, title, body) VALUES (NEW.id, NEW.title, NEW.body);
END;

The external-content form keeps the original text in a normal table, where it can be queried, joined, and modified normally; FTS5 maintains an index keyed on the rowid. The triggers above are the conventional pattern; the 'delete' command is the FTS5-specific incantation for removing a row from the index.

Maintenance

Two FTS5 commands are issued through INSERT-style invocations on the table itself:

-- Rebuild the index from scratch (slow but exhaustive)
INSERT INTO article_fts(article_fts) VALUES ('rebuild');

-- Optimise the index (compact, defragment)
INSERT INTO article_fts(article_fts) VALUES ('optimize');

-- Integrity check
INSERT INTO article_fts(article_fts) VALUES ('integrity-check');

The commands accept a setting name in single quotes and a value (where applicable). The full set is documented at sqlite.org/fts5.html#the_fts5_table_function.

The conventional maintenance schedule for a high-traffic FTS5 table:

optimize after substantial bulk updates (typically as a nightly background job).
rebuild only when a tokeniser change or schema migration invalidates the existing index.
integrity-check occasionally as a sanity check; the cost is roughly equivalent to a scan.

A worked example

A small full-text-search application — a journal in which entries are searched by free text:

CREATE TABLE entry (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    body      TEXT NOT NULL,
    tags      TEXT NOT NULL DEFAULT '',
    written_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
) STRICT;

CREATE VIRTUAL TABLE entry_fts USING fts5(
    title, body, tags,
    content='entry',
    content_rowid='id',
    tokenize = 'porter unicode61'
);

CREATE TRIGGER entry_ai AFTER INSERT ON entry BEGIN
    INSERT INTO entry_fts(rowid, title, body, tags)
    VALUES (NEW.id, NEW.title, NEW.body, NEW.tags);
END;
CREATE TRIGGER entry_ad AFTER DELETE ON entry BEGIN
    INSERT INTO entry_fts(entry_fts, rowid, title, body, tags)
    VALUES ('delete', OLD.id, OLD.title, OLD.body, OLD.tags);
END;
CREATE TRIGGER entry_au AFTER UPDATE ON entry BEGIN
    INSERT INTO entry_fts(entry_fts, rowid, title, body, tags)
    VALUES ('delete', OLD.id, OLD.title, OLD.body, OLD.tags);
    INSERT INTO entry_fts(rowid, title, body, tags)
    VALUES (NEW.id, NEW.title, NEW.body, NEW.tags);
END;

-- Search:
SELECT
    e.id,
    e.title,
    e.written_at,
    snippet(entry_fts, 1, '<b>', '</b>', '…', 16) AS snippet,
    bm25(entry_fts) AS score
FROM entry AS e
JOIN entry_fts AS f ON f.rowid = e.id
WHERE entry_fts MATCH ?
ORDER BY score
LIMIT 20;

The schema is conventional. The triggers keep the index synchronised; the search query joins the FTS5 table to the original entry table for the columns FTS5 does not expose (here, written_at); the bm25 ranking and snippet helper produce the search-result page. The pattern transfers directly to documentation, mail-archive search, code search, and any other text-heavy retrieval domain.