SQLite Full-Text Search (FTS5) Cheatsheet

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

# Store searchable text in an FTS5 index.

INSERT INTO docs (title, body)
VALUES ('SQLite WAL guide', 'Write-ahead logging improves concurrency for many app workloads.');

# Add rows to an FTS5 table.

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

# Separate storage from the FTS index when needed.

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

# Find rows containing a term.

SELECT rowid, title FROM docs WHERE docs MATCH '"write ahead logging"';

# Search for words appearing together in order.

SELECT rowid, title FROM docs WHERE docs MATCH 'concurr*';

# Match terms by prefix.

SELECT rowid, title, bm25(docs) AS rank
FROM docs
WHERE docs MATCH 'sqlite wal'
ORDER BY rank;

# Sort by relevance score.

SELECT title,
       highlight(docs, 1, '<mark>', '</mark>') AS highlighted_body,
       snippet(docs, 1, '<b>', '</b>', '…', 12) AS excerpt
FROM docs
WHERE docs MATCH 'sqlite';

# Decorate matched terms in search output.