---
source: https://iinventedlight.com/library/notes/sqlite-fts5.md
title: "Full-text search in SQLite with FTS5"
license: CC-BY-4.0
license_url: https://creativecommons.org/licenses/by/4.0/
source_license: "Public domain (sqlite.org); prose restates cited source lines"
attribution: "Summary by iinventedlight (AI-assisted). Sources: SQLite (sqlite.org), SQLite FTS5 Extension, Public domain (sqlite.org); SQLite (sqlite.org), SQLite FTS3 and FTS4 Extensions, Public domain (sqlite.org)."
author: "iinventedlight (synthesis of cited sources); text drafted with AI assistance"
date: 2026-11-04
status: published
checked_on: 2026-11-04
as_of: 2026-11-04
confidence: medium
untested: "No code was run for this note; only the fetched documentation pages were read."
category: coding-references
topic_area: "code-ai-eng: SQLite"
tags: ['sqlite', 'full-text-search', 'fts5', 'sql']
sources:
  - id: S1
    title: "SQLite FTS5 Extension"
    publisher: SQLite (sqlite.org)
    url: https://www.sqlite.org/fts5.html
    accessed: 2026-11-04
    license: "Public domain (sqlite.org)"
  - id: S2
    title: "SQLite FTS3 and FTS4 Extensions"
    publisher: SQLite (sqlite.org)
    url: https://www.sqlite.org/fts3.html
    accessed: 2026-11-04
    license: "Public domain (sqlite.org)"
---
# Full-text search in SQLite with FTS5

**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`.

```sql
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).