sql
CREATE VIRTUAL TABLE docs USING fts5(title, body);Notes
FTS5 is the go-to SQLite feature for efficient full-text search.
SQLite Full-Text Search (FTS5) Cheatsheet
FTS5 virtual tables, MATCH queries, prefix search, phrase search, bm25 ranking, snippets, and highlighting patterns for SQLite search features.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
Create FTS5 tables
Set up virtual tables for full-text search.
sql
INSERT INTO docs (title, body)
VALUES ('SQLite WAL guide', 'Write-ahead logging improves concurrency for many app workloads.');Notes
You can index plain text fields directly or sync from content tables.
Use an external content table pattern
Separate storage from the FTS index when needed.
sqlANYsqlitefts5content table
sql
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'
);Notes
Common when the app needs a normal table plus a search index over the same content.
Search, rank, and highlight
Run practical search queries and return better results.
sql
SELECT rowid, title FROM docs WHERE docs MATCH 'sqlite';Notes
MATCH is the core FTS operator.
sql
SELECT rowid, title FROM docs WHERE docs MATCH '"write ahead logging"';Notes
Phrase queries are useful for documentation and knowledge-base search.
sql
SELECT rowid, title FROM docs WHERE docs MATCH 'concurr*';Notes
Useful for search-as-you-type and partial matching.
sql
SELECT rowid, title, bm25(docs) AS rank
FROM docs
WHERE docs MATCH 'sqlite wal'
ORDER BY rank;Notes
bm25() is the usual relevance-ranking helper for FTS5 results.
Highlight matches or return snippets
Decorate matched terms in search output.
sqlANYsqlitehighlightsnippet
sql
SELECT title,
highlight(docs, 1, '<mark>', '</mark>') AS highlighted_body,
snippet(docs, 1, '<b>', '</b>', '…', 12) AS excerpt
FROM docs
WHERE docs MATCH 'sqlite';Notes
Great for search result pages where you want visible context around the match.
More in SQLite
SQLite Triggers, Views, and Generated Columns Cheatsheet
CREATE VIEW, CREATE TRIGGER, audit logging, derived values, generated columns, and automation patterns in SQLite.
SQLite PRAGMA and Administration Cheatsheet
High-signal PRAGMA commands for foreign keys, cache, synchronous settings, page size, temp storage, health checks, and general SQLite administration.
SQLite JSON Functions Cheatsheet
json_extract, json_set, json_each, JSON operators, generated columns, and practical JSON query/update patterns in SQLite.
SQLite Import, Export, and Backup Cheatsheet
CSV import/export, SQL dumps, VACUUM INTO, backup copies, and repeatable data movement patterns for SQLite databases.
SQLite Transactions, WAL, and Locking Cheatsheet
BEGIN modes, COMMIT and ROLLBACK, savepoints, WAL mode, busy timeouts, and concurrency patterns for SQLite applications.
SQLite Indexes and Query Planner Cheatsheet
CREATE INDEX patterns, covering and partial indexes, EXPLAIN QUERY PLAN, ANALYZE, and practical tuning tips for SQLite queries.