Build a searchable code index with SQLite FTS5 in an afternoon

You can index a repository with SQLite's FTS5 extension in a few dozen lines: create a virtual table, insert one row per file or function, and query with MATCH ordered by bm25. It needs no server and no model, and it gives you a baseline to beat later.

Why start here

Before embeddings, rerankers or local models, you want a baseline. A keyword index tells you how far plain search gets you, and it is a component you will keep, since exact matches matter for code. See keyword or vector search.

The SQLite documentation describes FTS5 as a virtual table module that provides full text search to database applications, included since version 3.9.0, or buildable as a loadable extension.

Step 1: create the table

CREATE VIRTUAL TABLE code USING fts5(path, symbol, body);

Three columns: the file path, the symbol name (function or class), and the text. Keeping the symbol separate lets you weight it higher.

Step 2: load chunks

Walk the repository, skip build output and vendored directories, and insert one row per chunk. A chunk can be a whole file to begin with, or a function if you have a parser. See chunking source code.

import sqlite3
con = sqlite3.connect("code.db")
con.execute("INSERT INTO code VALUES (?, ?, ?)", (path, symbol, text))
con.commit()

Step 3: query with ranking

SELECT path, symbol FROM code WHERE code MATCH ? ORDER BY bm25(code, 1.0, 10.0, 1.0) LIMIT 10;

The documentation says bm25 returns a real value where a better match is numerically smaller, so ascending order puts the best first. Column weights can be passed as extra arguments, so the example above counts a match in the symbol column ten times as much as one in the path or body. You can also order by the hidden rank column, which the documentation describes as faster for sorting.

Step 4: use the query syntax

FTS5 supports phrase queries in double quotes, boolean operators, and prefix queries with a trailing star. The documentation warns that a star inside quotes is handed to the tokenizer, so write prefix terms outside the quotes. It also offers a trigram tokenizer for substring matching, which helps when searching for part of an identifier.

Code identifiers like parseHeader or parse_header tokenise differently under the default tokenizer, so test a few of your real names. You may want to preprocess identifiers by splitting on underscores and case changes and indexing both forms.

Step 5: keep it fresh

Store a content hash per file. On each run, hash the files, compare, and update only rows that changed. Delete rows for removed files. This takes seconds even on large repositories and avoids stale answers.

Step 6: measure

Write twenty real questions and the file you would expect for each. Check how often the expected file lands in the top ten. The method is in how to evaluate a code search system.

Limits

FTS5 finds words, not meaning. "Throttle requests" will not find RateLimiter. That gap is exactly what embeddings fill. Treat this index as stage one.

Quick check

Sources

• SQLite FTS5 documentation

Last reviewed: 19 October 2026.