DEV Community

Cover image for Build a Full-Text Search Engine with SQLite FTS5
Muhammad Saud
Muhammad Saud

Posted on Originally published at logicstack.org

Build a Full-Text Search Engine with SQLite FTS5

FTS5 is the full-text search engine built into SQLite, and it turns a plain database file into a search engine with ranked results. It builds an inverted index of words, so a search reads the index instead of scanning every row. In this tutorial we build a searchable notes library with it in Python, using only the standard library.

Key takeaways

  • FTS5 is a virtual table in SQLite, so Python needs no extra package to use it.

  • An external content FTS5 table stores only the index, and three triggers keep it in step with the real table.

  • Results come back ranked by BM25, and the snippet function shows each match in context.

  • Phrase, prefix, boolean, column filter and NEAR queries all work through one MATCH operator.

  • On 100,000 documents our FTS5 query ran about 2.2 times faster than LIKE, and LIKE cannot rank at all.

What we build

We build a small command line notes library called notes_search. It stores notes in a SQLite table, indexes them with FTS5, and searches them from the terminal. It has four commands: build creates the database and seeds 12 notes, search runs a ranked query, demo shows the five query shapes, and benchmark times FTS5 against LIKE. The whole project is one file, search.py, and it uses only the Python standard library.

Prerequisites

  • Python 3.9 or newer. We tested with Python 3.12.3.

  • SQLite 3.9 or newer with FTS5 compiled in. We tested with SQLite 3.45.1, which ships with Python 3.12.

  • No packages to install and no server to run.

Why does LIKE fail as a search engine?

A search box built on LIKE has three problems. First, a leading wildcard such as LIKE '%word%' cannot use an index, so it reads every row on each search. Second, it matches exact characters only, so searching for "run" misses "running". Third, it returns rows in no useful order. FTS5 fixes all three: it indexes words, it stems them so word forms match, and it ranks results with BM25. The SQLite documentation describes FTS5 as a virtual table module that provides full-text search functionality to database applications, and that is exactly what we use it for.

Step 1: Check that FTS5 is available

FTS5 has shipped with SQLite since version 3.9.0, and Python bundles SQLite, so it is usually already there. We check with one probe before writing anything else.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE VIRTUAL TABLE t USING fts5(a)")
print("FTS5 OK")
print(sqlite3.sqlite_version)
Enter fullscreen mode Exit fullscreen mode

Our run printed:

FTS5 OK
3.45.1
Enter fullscreen mode Exit fullscreen mode

If this raises an OperationalError instead, the SQLite build has FTS5 switched off. The fix is to upgrade Python or install pysqlite3-binary, which bundles a newer SQLite.

Step 2: Create the notes table and the FTS5 index

We keep the notes in a normal table and put the search index in a second table. The FTS5 table uses the external content form, so it stores only the index and reads the text from the notes table. The porter tokenizer stems words, so a search for "running" also finds "run" and "runs".

con.executescript('''
    CREATE TABLE notes (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        body TEXT NOT NULL
    );
    CREATE VIRTUAL TABLE notes_fts USING fts5(
        title, body,
        content='notes', content_rowid='id',
        tokenize='porter unicode61'
    );
''')
Enter fullscreen mode Exit fullscreen mode

The content='notes' option is the key detail. It tells FTS5 that the notes table holds the text and the FTS5 table holds only the index, so the text is stored once and the index can be rebuilt from the notes at any time.

Step 3: Keep the index in step with triggers

An external content table does not update itself, so we add three triggers on the notes table. Each one mirrors the change into the index, and the application code never touches the FTS5 table directly.

CREATE TRIGGER notes_ai AFTER INSERT ON notes BEGIN
    INSERT INTO notes_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;
CREATE TRIGGER notes_ad AFTER DELETE ON notes BEGIN
    INSERT INTO notes_fts(notes_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;
CREATE TRIGGER notes_au AFTER UPDATE ON notes BEGIN
    INSERT INTO notes_fts(notes_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO notes_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;
Enter fullscreen mode Exit fullscreen mode

We verified the triggers work. We inserted a note containing the made up word "quixotic", and the search found 1 match. We deleted the note, and the same search found 0 matches. The index follows the table with no application code involved.

Step 4: Seed notes and run the first search

We seed 12 short notes about SQLite and search, then query them with MATCH and ORDER BY rank. The snippet function returns a short window of the body around each match, with square brackets around the matched words.

def search(query, limit=5):
    con = sqlite3.connect("notes.db")
    rows = con.execute('''
        SELECT n.title,
               snippet(notes_fts, 1, '[', ']', ' ... ', 12) AS preview,
               bm25(notes_fts, 10.0, 1.0) AS score
        FROM notes_fts
        JOIN notes n ON n.id = notes_fts.rowid
        WHERE notes_fts MATCH ?
        ORDER BY score
        LIMIT ?
    ''', (query, limit)).fetchall()
    for title, preview, score in rows:
        print(f"  {title}  (score {score:.2f})")
        print(f"  {preview}\n")
Enter fullscreen mode Exit fullscreen mode

Running python search.py build printed Built notes.db: 12 notes indexed. Then python search.py search "full text search" printed:

2 result(s) for "full text search":

  Why full-text search beats LIKE  (score -4.80)
   ... [Full]-[text] [search] builds an inverted index of words, so it finds ...

  Phrase search in FTS5  (score -2.53)
   ... [Searching] for "[full] [text] [search]" only matches documents where those three words ...
Enter fullscreen mode Exit fullscreen mode

Note the join: we join on rowid, not on a named column, because rowid is the internal key FTS5 uses. That is the pattern the SQLite documentation recommends for external content tables.

How does FTS5 rank results?

FTS5 ranks with BM25, a scoring function that rewards a term appearing often in one document but rarely across the whole collection, with a small boost for short documents. The bm25 function returns a negative number, and a more negative score means a better match, so we order by the score ascending. The two numbers after the table name are column weights: bm25(notes_fts, 10.0, 1.0) counts a match in the title ten times more than a match in the body. That is why a search for "sqlite" puts the note titled "Getting started with SQLite" first, ahead of notes that only mention it in the body.

Step 5: Phrase, prefix, boolean and NEAR queries

One MATCH operator covers five query shapes, and our demo command runs one of each. Double quotes make a phrase, a star makes a prefix, capitals make boolean operators, a column name with a colon limits the search to one column, and NEAR finds words close together.

"full text search"            # phrase, words in exact order
databas*                      # prefix, matches database and dataset
python AND sqlite             # boolean, both words required
title:sqlite                  # column filter, title only
NEAR(running stemming, 5)     # proximity, within 5 tokens
Enter fullscreen mode Exit fullscreen mode

The demo output confirmed each shape works:

--- "full text search" ---  2 results, best: Why full-text search beats LIKE
--- databas* ---            2 results, best: Prefix search with a star
--- python AND sqlite ---   2 results, best: Getting started with SQLite
--- title:sqlite ---        1 result: Getting started with SQLite
--- NEAR(running stemming, 5) ---  1 result: Porter stemming matches word forms
Enter fullscreen mode Exit fullscreen mode

One trap we hit while testing: a hyphen in a query reads as a column filter, so searching for full-text looks for a column named "text" and raises an error. The fix is to wrap user input in double quotes so FTS5 treats it as a phrase.

Step 6: Measure FTS5 against LIKE

We generated documents from a fixed random seed, rebuilt the index, then timed both approaches on the full result set. The benchmark code is part of search.py, so anyone can rerun it.

like_ms, like_n = timed(
    "SELECT id FROM bench WHERE body LIKE ?", ("%ranking%",))
fts_ms, fts_n = timed(
    "SELECT rowid FROM bench_fts WHERE bench_fts MATCH ?", ("ranking",))
Enter fullscreen mode Exit fullscreen mode

Our measured results, Python 3.12.3 and SQLite 3.45.1 on this machine:

Documents: 20000
LIKE '%ranking%':  16.87 ms, 19414 rows (no ranking)
FTS5 MATCH:        6.95 ms, 19414 rows (ranked by BM25)
FTS5 is 2.4x faster here.
Documents: 100000
LIKE '%ranking%':  77.77 ms, 96982 rows (no ranking)
FTS5 MATCH:        35.69 ms, 96982 rows (ranked by BM25)
FTS5 is 2.2x faster here.
Enter fullscreen mode Exit fullscreen mode

The honest reading is that the speed gap is modest at this size, about 2.2 to 2.4 times, because both queries return tens of thousands of rows and fetching them dominates the cost. The real win is what LIKE cannot do at all: ranking, stemming, phrases and snippets. One more honest note: LIKE with a LIMIT clause stops at the first matches, so it can look faster in a quick test, but it still cannot rank, and the rows it returns are the first in the table, not the best.

When should we move to a search service?

FTS5 handles tens of thousands of documents with ease, and our benchmark shows it stays fast at 100,000. We would move to a dedicated search service such as Meilisearch or Elasticsearch when we need typo tolerance, faceted search, or millions of documents under heavy load. FTS5 has no built in typo tolerance, and it matches exact tokens only. For a notes app, a docs site, or a small product catalogue, FTS5 is the simpler answer, and it ships with the database we already have.

What to try next

  • Wrap the search function in a FastAPI endpoint so the notes library becomes a small search API.

  • Add a rebuild command that runs INSERT INTO notes_fts(notes_fts) VALUES('rebuild') and verify the index survives a manual delete from the FTS5 table.

  • Index a real folder of Markdown files, then compare a porter tokenizer index against a trigram tokenizer index for substring search.


Originally published on LogicStack. Run the code examples right in your browser with our free interactive Python and SQL playgrounds — no setup, no sign-up.

Top comments (0)