SQLite Full-Text Search with FTS5: A Tested Tutorial

Ninad Pathak

October 7, 2026

A search box built on LIKE '%word%' only matches exact characters, so it won’t treat search and searching as the same word, and it reads every row on each search. SQLite has a real full-text search engine built in, called FTS5. In this article I’ll walk you through building full-text search with it, from the index to ranked results with highlighted snippets.

TLDR

Create an index with CREATE VIRTUAL TABLE docs_fts USING fts5(title, body); and query it with WHERE docs_fts MATCH 'word'.
Sort by relevance with ORDER BY rank, which uses the BM25 algorithm.

Keep the index in step with a normal table with content='table' plus three triggers.
Search phrases with quotes ("full text") and prefixes with a star (inde*).
Boolean operators work in capitals only: AND, OR, NOT.
Use tokenize='porter unicode61' to match word forms, and tokenize='trigram' for substring matches.

What is FTS5 and what will we build?

FTS5 is “an SQLite virtual table module that provides full-text search functionality to database applications” (SQLite FTS5). It splits text into words (tokens) and stores an index of which rows contain each word, so a search reads the index instead of every row.

To show you how it works, I will build search for a small articles table. The FTS5 index stays in sync with the table, and a Python function at the end returns ranked titles with highlighted excerpts.

Prerequisites

What	Version used here
SQLite with FTS5 compiled in	3.51.0 (the `sqlite3` shell on macOS)
Python (for the last step)	3.9.6, whose `sqlite3` module uses the same SQLite

The SQLite builds used here include FTS5. Check that yours does:

sqlite> SELECT sqlite_compileoption_used('ENABLE_FTS5') AS fts5;
fts5
----
1

Building full-text search for articles

Each step below adds one piece, and the outputs come from running them in order.

Step 1: Create the table, the FTS5 index and the sync triggers

content='articles' makes the FTS5 table an index over articles instead of a second copy of the text. The triggers update the index whenever a row changes. tokenize='porter unicode61' adds Porter stemming, so “searching” and “searches” both match “search”:

-- The real table that holds the articles
CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL
);

-- The full-text index, kept in step with articles by the triggers below
CREATE VIRTUAL TABLE articles_fts USING fts5(
  title, body,
  content='articles', content_rowid='id',
  tokenize='porter unicode61'
);

CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;
CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
END;
CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
  INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

An FTS5 table takes column names only: “it is an error to add types, constraints or PRIMARY KEY declarations” (SQLite FTS5). It also creates a few shadow tables to store the index:

schema created
sqlite> .tables
articles              articles_fts_config   articles_fts_docsize
articles_fts          articles_fts_data     articles_fts_idx

Step 2: Load the articles

The insert trigger indexes every row as it’s added:

INSERT INTO articles (title, body) VALUES
  ('Getting started with SQLite', 'SQLite stores a whole database in one file. Install it and create your first table.'),
  ('Full-text search in SQLite', 'FTS5 builds an index of words so searching large amounts of text stays fast.'),
  ('Indexing tips', 'An index speeds up searches on a column. Full-text indexes work on words, not whole values.'),
  ('Backing up a database', 'Copy the database file while nothing writes to it, or use VACUUM INTO.'),
  ('Searching logs', 'Search error messages in application logs with full text queries.');
INSERT INTO articles (title, body) VALUES
  ('Dates and times', 'Store timestamps as ISO-8601 strings and use the date functions.'),
  ('Foreign keys', 'Turn on foreign key enforcement with a pragma on each connection.'),
  ('JSON columns', 'Keep settings as JSON and query fields with json_extract.'),
  ('Upserts', 'Insert a row or update the existing one with ON CONFLICT.'),
  ('WAL mode', 'Write-ahead logging lets readers and a writer work at the same time.');

sqlite> SELECT count(*) AS articles, (SELECT count(*) FROM articles_fts) AS indexed FROM articles;
articles  indexed
--------  -------
10        10

Step 3: Search with MATCH

MATCH takes a search query and returns the rows that contain the words:

sqlite> SELECT rowid, title FROM articles_fts WHERE articles_fts MATCH 'database';
rowid  title
-----  ---------------------------
1      Getting started with SQLite
4      Backing up a database

Thanks to the Porter tokenizer, “search” also finds “searching” and “searches”:

sqlite> SELECT rowid, title FROM articles_fts WHERE articles_fts MATCH 'search';
rowid  title
-----  --------------------------
2      Full-text search in SQLite
3      Indexing tips
5      Searching logs

Step 4: Rank results by relevance

ORDER BY rank sorts by the BM25 score, best match first. With bm25(), “the better the match, the numerically smaller the value returned” (SQLite FTS5):

sqlite> SELECT title, round(bm25(articles_fts), 3) AS score FROM articles_fts WHERE articles_fts MATCH 'full text' ORDER BY rank;
title                       score
--------------------------  ------
Full-text search in SQLite  -1.671
Searching logs              -1.668
Indexing tips               -1.383

Step 5: Write more precise queries

Quotes make a phrase. The hyphen in “Full-text” separates two tokens, so the phrase “full text” matches it:

sqlite> SELECT title FROM articles_fts WHERE articles_fts MATCH '"full text"';
title
--------------------------
Full-text search in SQLite
Indexing tips
Searching logs

A trailing * matches a prefix:

sqlite> SELECT title FROM articles_fts WHERE articles_fts MATCH 'inde*';
title
--------------------------
Full-text search in SQLite
Indexing tips

The boolean operators must be in capitals. In lowercase, not is another word to find, so the last query below matches nothing:

sqlite> SELECT title FROM articles_fts WHERE articles_fts MATCH 'database OR logs';
title
---------------------------
Getting started with SQLite
Backing up a database
Searching logs
WAL mode
sqlite> SELECT title FROM articles_fts WHERE articles_fts MATCH 'database NOT backing';
title
---------------------------
Getting started with SQLite
sqlite> SELECT title FROM articles_fts WHERE articles_fts MATCH 'database not backing';

column : word searches one column, and NEAR(a b, n) finds words within n tokens of each other:

sqlite> SELECT title FROM articles_fts WHERE articles_fts MATCH 'title : sqlite';
title
---------------------------
Getting started with SQLite
Full-text search in SQLite

sqlite> SELECT title FROM articles_fts WHERE articles_fts MATCH 'NEAR(index words, 5)';
title
--------------------------
Full-text search in SQLite
Indexing tips

Step 6: Highlight the matched words

highlight() wraps each match in the markers you choose, and snippet() returns a short excerpt around it:

sqlite> SELECT highlight(articles_fts, 0, '<b>', '</b>') AS title FROM articles_fts WHERE articles_fts MATCH 'sqlite';
title
----------------------------------
Getting started with <b>SQLite</b>
Full-text search in <b>SQLite</b>

Step 7: Check that the index stays in sync

Updating and deleting rows in articles fires the triggers, so the index follows:

sqlite> UPDATE articles SET title = 'Backing up an SQLite database' WHERE id = 4;
sqlite> SELECT rowid, title FROM articles_fts WHERE articles_fts MATCH 'title : sqlite';
rowid  title
-----  -----------------------------
1      Getting started with SQLite
2      Full-text search in SQLite
4      Backing up an SQLite database
sqlite> DELETE FROM articles WHERE id = 5;
sqlite> SELECT count(*) AS log_matches FROM articles_fts WHERE articles_fts MATCH 'logs';
log_matches
-----------
1

Running it from Python

This function joins the index back to articles and returns the best matches with an excerpt:

import sqlite3

con = sqlite3.connect("search.db")


def search(query, limit=3):
    sql = """
        SELECT a.title,
               snippet(articles_fts, 1, '[', ']', '…', 8) AS excerpt
        FROM articles_fts
        JOIN articles AS a ON a.id = articles_fts.rowid
        WHERE articles_fts MATCH ?
        ORDER BY rank
        LIMIT ?
    """
    return con.execute(sql, (query, limit)).fetchall()


for title, excerpt in search("search*"):
    print(f"{title}: {excerpt}")

try:
    search("full-text")
except sqlite3.OperationalError as e:
    print("OperationalError:", e)
print(search('"full-text"'))

$ python3 search.py
Full-text search in SQLite: FTS5 builds an index of words so [searching]…
Indexing tips: An index speeds up [searches] on a column…
OperationalError: no such column: text
[('Full-text search in SQLite', 'FTS5 builds an index of words so searching…'), ('Indexing tips', '…on a column. [Full-text] indexes work on…')]

The OperationalError is a real trap. In the FTS5 query language, a hyphen after a word reads as a column filter, so full-text looks for a column named text. Wrap user input in double quotes to search it as a phrase.

Common problems

no such column: text (or another word): the query contains - or : that FTS5 parses as syntax. Quote the user’s input: '"' || replace(input, '"', '""') || '"'.

database not backing finds nothing: the operator is lowercase. Write NOT in capitals.
A search for part of a word finds nothing: the default tokenizer matches whole tokens. Use a prefix (inde*) or the trigram tokenizer below.
Substring and fuzzy-ish search: the trigram tokenizer “treats each contiguous sequence of three characters as a token, allowing FTS5 to support more general substring matching” (SQLite FTS5). On a trigram table, even LIKE '%gres%' uses the index. FTS5 has no typo-tolerant fuzzy matching:

sqlite> CREATE VIRTUAL TABLE names USING fts5(name, tokenize='trigram'); INSERT INTO names VALUES ('PostgreSQL'), ('SQLite'), ('MySQL');
sqlite> SELECT name FROM names WHERE name MATCH 'sql';
name
----------
PostgreSQL
SQLite
MySQL
sqlite> SELECT name FROM names WHERE name LIKE '%gres%';
name
----------
PostgreSQL
sqlite> EXPLAIN QUERY PLAN SELECT name FROM names WHERE name LIKE '%gres%';
QUERY PLAN
`--SCAN names VIRTUAL TABLE INDEX 0:L0

Search is slow on a plain table: LIKE '%word%' scans every row, while MATCH uses the FTS5 index. The query plans show the difference:

sqlite> EXPLAIN QUERY PLAN SELECT title FROM articles WHERE body LIKE '%index%';
QUERY PLAN
`--SCAN articles
sqlite> EXPLAIN QUERY PLAN SELECT title FROM articles_fts WHERE articles_fts MATCH 'index';
QUERY PLAN
`--SCAN articles_fts VIRTUAL TABLE INDEX 0:M2

Conclusion

For anything beyond a quick filter, use FTS5 instead of LIKE. Build it as an external-content index with sync triggers, and sort matches with ORDER BY rank. Quote user input before you pass it to MATCH.