Bytes, SQLite, Hybrid search with FTS5 and sqlite-vec

What?

A complete local search index in one SQLite file:

Piece	Gives you	Ships with
FTS5	Keyword search ranked by BM25	SQLite itself, compiled in almost everywhere
sqlite-vec	Nearest-neighbour search over embeddings (`vec0` virtual table)	A loadable extension, installed with `pip`, `npm`, `cargo` or `gem`
Reciprocal rank fusion (RRF)	One ranking from both	A few lines of SQL

Embeddings come from a local Ollama model (nomic-embed-text), so nothing leaves the machine. It’s the same approach qmd takes.

Why?

Keyword search and vector search fail in different places:

BM25 nails exact tokens like FR-012, but finds nothing when the question uses different words from the document.
Vectors match meaning, but blur identifiers and rare terms.

Running both and fusing them covers each one’s blind spot. Doing it inside SQLite means no search server. The index is a file you can copy, back up, ship inside an app, and query in a transaction alongside the rest of your data.

How?

1. Prerequisites

brew install uv ollama           # or your platform's equivalent
ollama pull nomic-embed-text:v1.5

macOS: the system Python and sqlite3 CLI can't load extensions

Apple’s builds disable load_extension. Use the Python that uv installs (or Homebrew’s), and Homebrew’s sqlite for the CLI.

2. The script

Save as sqlite_search.py. The header lets uv run install sqlite-vec on the fly.

# /// script
# dependencies = ["sqlite-vec==0.1.9"]
# ///
import json, re, sqlite3, sys, urllib.request
import sqlite_vec
 
DOCS = [
    ("FR-012", "Retries", "A failed request is retried three times with exponential backoff, then the job is marked failed."),
    ("FR-013", "Timeouts", "Each request times out after 30 seconds."),
    ("FR-020", "Citations", "Every answer must cite the document it came from."),
    ("FR-021", "Search index", "The search index is never the source of record; it can be rebuilt from the documents."),
    ("FR-030", "Retrieval threshold", "Retrieval is not built until the corpus passes 200,000 tokens."),
    ("FR-031", "Stale records", "Withdrawn and superseded records stay in the archive but are excluded from answers."),
    ("FR-040", "Backups", "The database is backed up nightly and copies are kept for 30 days."),
    ("FR-041", "Logging", "Errors are logged with the request id so a failure can be traced."),
]
MODEL, DIMS = "nomic-embed-text:v1.5", 768
 
 
def embed(texts):
    req = urllib.request.Request(
        "http://localhost:11434/api/embed",
        data=json.dumps({"model": MODEL, "input": texts}).encode(),
        headers={"Content-Type": "application/json"},
    )
    return json.load(urllib.request.urlopen(req))["embeddings"]
 
 
STOP = set("a an and are as at be before by can do does for from how in is it many of on or the to up what when which who why with".split())
 
 
def fts_query(q):
    # FTS5 has no stopword list, so drop them here. Quote every token so FTS5 syntax
    # ("-", ":", "OR") can't leak in, then OR them: FTS5's default is AND.
    terms = [t for t in re.findall(r"\w[\w-]*", q) if t.lower() not in STOP]
    return " OR ".join('"%s"' % t for t in terms) or '""'
 
 
 
db = sqlite3.connect("search.db")
db.enable_load_extension(True)
sqlite_vec.load(db)
db.enable_load_extension(False)
 
db.executescript(f"""
DROP TABLE IF EXISTS docs; DROP TABLE IF EXISTS docs_fts; DROP TABLE IF EXISTS docs_vec;
 
CREATE TABLE docs (id INTEGER PRIMARY KEY, code TEXT, title TEXT, body TEXT);
 
-- Keyword index over docs, kept in step by triggers (external content: no second copy of the text)
CREATE VIRTUAL TABLE docs_fts USING fts5(code, title, body, content='docs', content_rowid='id');
CREATE TRIGGER docs_ai AFTER INSERT ON docs BEGIN
  INSERT INTO docs_fts(rowid, code, title, body) VALUES (new.id, new.code, new.title, new.body);
END;
CREATE TRIGGER docs_ad AFTER DELETE ON docs BEGIN
  INSERT INTO docs_fts(docs_fts, rowid, code, title, body) VALUES ('delete', old.id, old.code, old.title, old.body);
END;
CREATE TRIGGER docs_au AFTER UPDATE ON docs BEGIN
  INSERT INTO docs_fts(docs_fts, rowid, code, title, body) VALUES ('delete', old.id, old.code, old.title, old.body);
  INSERT INTO docs_fts(rowid, code, title, body) VALUES (new.id, new.code, new.title, new.body);
END;
 
-- Vector index: one {DIMS}-float embedding per doc, same rowid
CREATE VIRTUAL TABLE docs_vec USING vec0(embedding float[{DIMS}] distance_metric=cosine);
""")
 
vectors = embed(["search_document: " + f"{t}. {b}" for _, t, b in DOCS])
with db:
    for i, ((code, title, body), v) in enumerate(zip(DOCS, vectors), start=1):
        db.execute("INSERT INTO docs VALUES (?, ?, ?, ?)", (i, code, title, body))
        db.execute("INSERT INTO docs_vec(rowid, embedding) VALUES (?, ?)", (i, sqlite_vec.serialize_float32(v)))
 
HYBRID = """
WITH
fts AS (
  -- bm25() can't run inside a window function, so score first, then number the rows
  SELECT id, row_number() OVER (ORDER BY score) AS r
  FROM (SELECT rowid AS id, bm25(docs_fts, 10.0, 5.0, 1.0) AS score  -- code, title, body
        FROM docs_fts WHERE docs_fts MATCH :q ORDER BY score LIMIT :k)
),
vec AS (
  SELECT rowid AS id, row_number() OVER (ORDER BY distance) AS r
  FROM docs_vec WHERE embedding MATCH :v AND k = :k
)
SELECT d.code, d.title,
       fts.r AS bm25_rank, vec.r AS vec_rank,
       coalesce(1.0 / (60 + fts.r), 0) + coalesce(1.0 / (60 + vec.r), 0) AS rrf
FROM docs d
LEFT JOIN fts ON fts.id = d.id
LEFT JOIN vec ON vec.id = d.id
WHERE fts.id IS NOT NULL OR vec.id IS NOT NULL
ORDER BY rrf DESC
LIMIT 4
"""
 
for q in sys.argv[1:]:
    v = sqlite_vec.serialize_float32(embed(["search_query: " + q])[0])
    print(f"\n{q!r}")
    print(f"  {'code':8} {'title':20} {'bm25':>4} {'vec':>4} {'rrf':>7}")
    for code, title, br, vr, s in db.execute(HYBRID, {"q": fts_query(q), "v": v, "k": 10}):
        print(f"  {code:8} {title:20} {br or '-':>4} {vr or '-':>4} {s:7.4f}")

The schema has three tables that share one rowid:

docs is the source of truth.
docs_fts is an external content FTS5 table. It indexes docs without storing a second copy of the text, and the three triggers keep it in step on insert, update and delete.
docs_vec holds one 768-float embedding per row. Triggers can’t fill it, because embeddings come from a model outside the database, so the app writes it alongside each row.

The hybrid query has three parts:

fts: the BM25 top 10. The column weights 10.0, 5.0, 1.0 (code, title, body) make an id match count most. bm25() is lower-is-better.
vec: the 10 nearest embeddings by cosine distance. k = :k is how vec0 takes the limit.
Fusion: each document scores $\frac{1}{60 + rank}$ for every list it appears in. Fusing ranks rather than scores means BM25 scores and cosine distances never need putting on the same scale.

Two different k's

The k = :k in the vector query is the search depth: how many neighbours each list returns. The 60 in RRF is a different constant, the usual default, which damps how much the very top ranks dominate.

3. Run it

uv run sqlite_search.py "FR-012" "how many attempts before giving up" "can the index be rebuilt"

'FR-012'
  code     title                bm25  vec     rrf
  FR-012   Retries                 1    2  0.0325
  FR-030   Retrieval threshold     -    1  0.0164
  FR-021   Search index            -    3  0.0159
  FR-031   Stale records           -    4  0.0156
 
'how many attempts before giving up'
  code     title                bm25  vec     rrf
  FR-012   Retries                 -    1  0.0164
  FR-013   Timeouts                -    2  0.0161
  FR-030   Retrieval threshold     -    3  0.0159
  FR-040   Backups                 -    4  0.0156
 
'can the index be rebuilt'
  code     title                bm25  vec     rrf
  FR-021   Search index            1    1  0.0328
  FR-030   Retrieval threshold     -    2  0.0161
  FR-012   Retries                 -    3  0.0159
  FR-031   Stale records           -    4  0.0156

The three queries show the split:

FR-012: vectors alone put Retrieval threshold first. BM25 finds the exact id, and fusion puts it on top.
”how many attempts before giving up”: shares no keywords with “retried three times… then marked failed”. BM25 has nothing, and vectors carry it.
”can the index be rebuilt”: both lists agree, so the score is highest.

4. Gotchas

FTS5 has no stopwords. Before STOP was added to fts_query, the second query matched “backed up” in Backups. A single shallow BM25 hit was enough for fusion to rank it first, ahead of the right answer:

'how many attempts before giving up'
  code     title                bm25  vec     rrf
  FR-040   Backups                 1    4  0.0320
  FR-012   Retries                 -    1  0.0164

RRF rewards appearing in both lists, so a junk keyword match beats a strong vector match that’s alone. Strip stopwords, or add your own list.

Quote every term. -, :, AND, OR, NOT and * are FTS5 query syntax. Unquoted, MATCH 'FR-012' fails with no such column: 012. The unicode61 tokenizer splits it into fr + 012, and quoting turns that into a phrase that must match in order.

OR the terms. FTS5’s default is AND, so one extra word in the question drops every document that lacks it.

bm25() can’t run inside a window function. Score in a subquery, then row_number() outside it, as the fts CTE does.

vec0 is brute force. sqlite-vec scans every vector. That’s fast to tens or hundreds of thousands of rows, especially with int8 or bit quantisation, but there’s no approximate index (ANN) yet. For millions of rows, see the DuckDB version with HNSW, or a dedicated vector store.

Use the model’s prefixes. nomic-embed-text expects search_document: on indexed text and search_query: on questions. Mixing them up quietly lowers similarity.

Bytes, DuckDB, Hybrid search with FTS and VSS: the same setup in DuckDB
Algos, Information Retrieval, BM25: what FTS5’s bm25() computes
Algos, Information Retrieval, Hit@K, Algos, Information Retrieval, MRR: measuring whether hybrid beats either list alone
Bytes, AI, QMD, Semantic search over an Obsidian vault: a ready-made tool built the same way
SQLite FTS5, sqlite-vec

Bytes, SQLite, Hybrid search with FTS5 and sqlite-vec

What?

Why?

How?

1. Prerequisites

2. The script

3. Run it

4. Gotchas

Related