SQLite Full-Text Search: FTS5 Virtual Tables and MATCH

LIKE Doesn't Scale

If you've searched text in SQLite before, you've probably reached for LIKE '%word%'. It works on small tables and falls apart on big ones. There's no index that can help — SQLite has to scan every row, lowercase it, and check for the substring. Word boundaries, ranking, multi-word queries, and prefix matching are all on you to implement.

FTS5 is the built-in answer. It's a virtual table type that maintains an inverted index over your text columns, understands a small query language, and ranks results with BM25. It ships with SQLite by default — no extension to install.

Creating an FTS5 Table

You create an FTS5 table with CREATE VIRTUAL TABLE ... USING fts5(...), listing the text columns you want indexed:

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

INSERT INTO posts (title, body) VALUES
  ('SQLite full-text search', 'FTS5 is fast and built in.'),
  ('Indexes in SQLite', 'B-trees power most lookups in SQLite.'),
  ('Why use SQLite', 'It runs in-process and stores data in one file.');

SELECT title FROM posts WHERE posts MATCH 'sqlite';

Three things worth noticing. The columns have no types — FTS5 treats everything as text. The MATCH operator goes against the table name (posts MATCH ...), not a column. And the query is case-insensitive and tokenized, so 'sqlite' finds SQLite in any of the rows.

The MATCH Query Language

MATCH accepts more than a single word. The query string has its own small grammar:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Full-text search', 'FTS5 supports phrase and prefix queries.'),
  ('Indexes', 'A B-tree index speeds up equality lookups.'),
  ('Triggers', 'Use triggers to keep FTS in sync with the source table.');

SELECT title FROM posts WHERE posts MATCH 'fts5 AND prefix';
SELECT title FROM posts WHERE posts MATCH '"keep fts"';
SELECT title FROM posts WHERE posts MATCH 'trig*';
SELECT title FROM posts WHERE posts MATCH 'index NOT trigger';

What each one does:

• 'fts5 AND prefix' — both tokens must appear (in any order, anywhere in the row).
• '"keep fts"' — exact phrase, in that order.
• 'trig*' — prefix search, matches trigger, triggers, trigonometry...
• 'index NOT trigger' — has index, doesn't have trigger.

You can also target a single column with column:term, like 'title:sqlite'. The full grammar covers parentheses for grouping and OR for alternatives — same shape you'd expect from a search engine.

Ranking With BM25

By default, FTS5 attaches a hidden rank column to every row. It's the BM25 relevance score — lower numbers are better matches. Order by it to get the most relevant results first:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite tutorial', 'Learn SQLite from scratch.'),
  ('SQLite vs Postgres', 'Comparing SQLite to Postgres for small apps.'),
  ('SQLite SQLite SQLite', 'A post that mentions SQLite many times.');

SELECT title, rank
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY rank;

Want to weight some columns more heavily than others? Call bm25() with weights — one per column in declaration order:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite indexes', 'A short note about B-trees.'),
  ('Postgres notes', 'Includes a paragraph about sqlite for comparison.');

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

The first post wins because sqlite shows up in title (weighted 10×) instead of just body (weighted 1×). Pick weights that match how your app actually wants to rank.

Keeping the Index in Sync

The simplest FTS5 table stores its own copy of the text. That's fine for log-style data you only insert into, but most apps already have a real table and want FTS to track it. The clean pattern is an external content FTS table plus three triggers.

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'
);

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;

INSERT INTO articles (title, body) VALUES
  ('SQLite triggers', 'How to use AFTER INSERT triggers.'),
  ('FTS5 patterns', 'External content keeps the data in one place.');

SELECT a.id, a.title
FROM articles a
JOIN articles_fts f ON f.rowid = a.id
WHERE articles_fts MATCH 'triggers';

content='articles' tells FTS5 not to store the text itself — it'll fetch it from the articles table when needed. The triggers mirror writes into the FTS index. Now articles is the source of truth and articles_fts is just the search structure beside it.

The odd-looking INSERT INTO articles_fts(articles_fts, ...) VALUES ('delete', ...) is FTS5's command syntax for telling the index to remove a row.

Snippets and Highlighting

Search results usually want a preview with the matching terms emphasized. FTS5 has two functions for that:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Getting started', 'SQLite is a small, fast, embedded database engine that stores data in a single file on disk.');

SELECT
    highlight(posts, 0, '<b>', '</b>') AS title,
    snippet(posts, 1, '<mark>', '</mark>', '...', 10) AS preview
FROM posts
WHERE posts MATCH 'sqlite database';

• highlight(table, column_index, open, close) returns the column's full text with matched tokens wrapped.
• snippet(table, column_index, open, close, ellipsis, token_count) returns a short excerpt centered on the match.

Column indexes are zero-based in declaration order. These are the building blocks for the "matched terms in yellow" behavior every search UI needs.

Pitfalls Worth Knowing

A few things that catch people:

• MATCH only works on FTS tables. You can't MATCH a regular column. If you need search over an existing table, use the external-content pattern above.
• Don't forget to order by rank. Without it, FTS5 returns rows in storage order, which has nothing to do with relevance.
• Tokenizers matter. The default tokenizer (unicode61) splits on Unicode word boundaries and lowercases. For stemming (run matches running), use the porter tokenizer: USING fts5(body, tokenize='porter').
• FTS5 is not a typo-tolerant engine. It does prefix matching, not fuzzy matching. If you need "did you mean..." behavior, that's a layer above FTS5.
• Contentless tables (content='') are smaller but lossy. You can search them but can't retrieve the original text — only the rowid. Useful when you store the text elsewhere.

Next: Window Functions

FTS5 covers searching for text. The next page covers a different shape of advanced query — window functions, which let you compute running totals, rankings, and per-group analytics without collapsing your rows into aggregates.