SQLite Basics: Search Documents with FTS5

A notes application often needs to find words across both a title and a body. SQLite’s FTS5 extension provides a searchable virtual table for that job. These examples build a small index, distinguish phrase and prefix searches, and keep search results current after edits.

FTS5 has been included in SQLite’s source distribution since 3.9.0, but the runtime must have FTS5 enabled. Creating the first virtual table below is a practical availability check: an error saying no such module: fts5 means that runtime lacks the module. The tested runtime had it enabled. FTS5 availability and syntax.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation starts with a fresh in-memory main database and prints pipe-separated rows, showing missing values as NULL. It does not escape embedded pipes or newlines, so it is a teaching runner, not a general SQL shell or CSV exporter. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

The toy database disappears when each invocation closes its connection. The examples are independent and can be run in any order.

1. Find a word across two columns

Save as search-note-words.sql:

CREATE VIRTUAL TABLE notes USING fts5(title, body);
INSERT INTO notes(rowid, title, body) VALUES
    (1, 'SQLite basics', 'Store rows safely.'),
    (2, 'Garden notes', 'Plant seeds in spring.'),
    (3, 'Query checklist', 'Compare SQLite indexes.');
SELECT rowid, title FROM notes
WHERE notes MATCH 'sqlite'
ORDER BY rowid;

Run:

python3 run-sql.py search-note-words.sql

Output:

1|SQLite basics
3|Query checklist

The first result matches the title; the second matches the body. The lowercase search also finds SQLite with an uppercase S in this fixture. We order by rowid to make the example repeatable, not to claim that the first row is more relevant.

The columns in CREATE VIRTUAL TABLE are search fields, not ordinary declarations such as title TEXT NOT NULL. This is a normal content-storing FTS5 table, so the inserted documents are held in the table as well as indexed. Keep authoritative business constraints in a suitable ordinary table when the application needs them.

2. Separate a phrase from a token prefix

Save as search-phrases-prefixes.sql:

CREATE VIRTUAL TABLE guides USING fts5(title, body);
INSERT INTO guides(rowid, title, body) VALUES
    (1, 'Quick start', 'Install the tools.'),
    (2, 'Quickly searching', 'Find a saved note.'),
    (3, 'Start quick', 'Review the checklist.');
SELECT 'phrase', rowid, title FROM guides
WHERE guides MATCH 'title : "quick start"'
ORDER BY rowid;
SELECT 'prefix', rowid, title FROM guides
WHERE guides MATCH 'title : quick*'
ORDER BY rowid;

Run:

python3 run-sql.py search-phrases-prefixes.sql

Output:

phrase|1|Quick start
prefix|1|Quick start
prefix|2|Quickly searching
prefix|3|Start quick

Only the first title contains the two words in the requested order. The prefix query includes both quick and quickly, so all three titles appear. The title filter prevents a word in the body from satisfying these particular searches.

Notice the two layers of quoting: SQL’s single quotes surround the search expression, while the double quotes inside identify a phrase. Put the prefix asterisk outside a quoted token or phrase. MATCH uses its own query language; it is not the same pattern syntax as LIKE. In an application, bind the expression as a parameter and decide which search operators users may enter. Binding prevents SQL injection but does not turn an arbitrary expression into a literal phrase.

3. Show matches and refresh them after edits

Save as highlight-edited-help.sql:

CREATE VIRTUAL TABLE help USING fts5(title, body);
INSERT INTO help(rowid, title, body) VALUES
    (1, 'Save a copy', 'Make a backup before editing.'),
    (2, 'Daily routine', 'Check the logs.'),
    (3, 'Old advice', 'Keep a backup nearby.');
SELECT 'before', rowid, highlight(help, 1, '[', ']') FROM help
WHERE help MATCH 'body : backup' ORDER BY rowid;
UPDATE help SET body = 'Verify the backup each morning.'
WHERE rowid = 2;
DELETE FROM help WHERE rowid = 3;
SELECT 'after', rowid, highlight(help, 1, '[', ']') FROM help
WHERE help MATCH 'body : backup' ORDER BY rowid;

Run:

python3 run-sql.py highlight-edited-help.sql

Output:

before|1|Make a [backup] before editing.
before|3|Keep a [backup] nearby.
after|1|Make a [backup] before editing.
after|2|Verify the [backup] each morning.

The second query includes the edited routine and excludes the deleted advice. No separate index-refresh statement was needed for this ordinary FTS5 table. External-content configurations have different synchronization requirements; this example deliberately does not use one.

The column number 1 selects body because numbering starts at zero. Square brackets make the hit visible in a terminal. A website should not treat highlighted document text as trusted HTML: preserve escaping for original content and render match markers safely. The markers here are presentation, not sanitized web output.

The FTS5 reference covers MATCH expressions, tokenizers, virtual-table updates, and highlight(). Choose a tokenizer and search policy against real example documents before exposing this as a user-facing search box.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py search-note-words.sql search-phrases-prefixes.sql highlight-edited-help.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.