sqlANYsqlitefts5virtual table
sql
CREATE VIRTUAL TABLE docs USING fts5(title, body);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.
sqlANYsqlitefts5insert
sql
INSERT INTO docs (title, body)
VALUES ('SQLite WAL guide', 'Write-ahead logging improves concurrency for many app workloads.');You can index plain text fields directly or sync from content tables.
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'
);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.
sqlANYsqlitematchfts5
sql
SELECT rowid, title FROM docs WHERE docs MATCH 'sqlite';MATCH is the core FTS operator.
sqlANYsqlitephrase search
sql
SELECT rowid, title FROM docs WHERE docs MATCH '"write ahead logging"';Phrase queries are useful for documentation and knowledge-base search.
sqlANYsqliteprefixsearch
sql
SELECT rowid, title FROM docs WHERE docs MATCH 'concurr*';Useful for search-as-you-type and partial matching.
sqlANYsqlitebm25ranking
sql
SELECT rowid, title, bm25(docs) AS rank
FROM docs
WHERE docs MATCH 'sqlite wal'
ORDER BY rank;bm25() is the usual relevance-ranking helper for FTS5 results.
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';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.