← All notes

Answer: FTS5 is SQLite's full-text search module. Create a virtual table with USING fts5(...), then search with MATCH, rank with bm25 and show matches with highlight or snippet.

CREATE VIRTUAL TABLE docs USING fts5(title, body);
INSERT INTO docs VALUES ('SQLite', 'FTS5 is a full-text search module');

SELECT title FROM docs WHERE docs MATCH 'fts5';
SELECT highlight(docs, 1, '<b>', '</b>') FROM docs WHERE docs MATCH 'search';
SELECT title FROM docs WHERE docs MATCH 'body:module' ORDER BY rank;

Facts

• Each FTS5 table has an implicit integer rowid.
• Query syntax supports phrases, prefixes (sea*), AND/OR/NOT and column filters.
• Custom ranking functions and tokenizers can be registered.

Claims

• id: C1 claim: FTS5 tables have an implicit INTEGER PRIMARY KEY named rowid. source: S1 evidence: "FTS5 table has an implicit INTEGER PRIMARY KEY field named rowid."
• id: C2 claim: FTS5 is included as part of the SQLite amalgamation. source: S1 evidence: "FTS5 is included as part of the SQLite amalgamation."
• id: C3 claim: The FTS3 page gives about 0.03 seconds versus 22.5 for an ordinary table. source: S2 evidence: "approximately 0.03 seconds, versus 22.5 for querying the ordinary table."
• id: C4 claim: The FTS3 page describes FTS3 and FTS4. source: S2 evidence: "This article describes the deployment and usage of FTS3 and FTS4."

Sources

• [S1] SQLite FTS5 Extension. SQLite (sqlite.org). https://www.sqlite.org/fts5.html . License: Public domain (sqlite.org). Accessed 2026-11-04.
• [S2] SQLite FTS3 and FTS4 Extensions. SQLite (sqlite.org). https://www.sqlite.org/fts3.html . License: Public domain (sqlite.org). Accessed 2026-11-04.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).

@misc{iil_note_50szqn5s,
  author = {{iinventedlight}},
  title = {{Full-text search in SQLite with FTS5}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/sqlite-fts5/},
  note = {Permanent ID iil:note:50szqn5s. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/sqlite-fts5.md.