Migrating from SQLite FTS5 to Whoosh

SQLite's FTS5 is a fine full-text engine, but it's a compiled C extension: it lives inside SQLite, its ranking is BM25-only, and once your search logic outgrows MATCH — you want faceting, spell-correction, configurable analyzers, or highlighting that isn't a bolt-on — you start fighting it. I've written about the trade-offs in detail; this post is the other half: if you've decided to move, here's exactly how, with working code.

Whoosh is pure Python — no compiler, no server — so the migration is just Python reading from one store and writing to another. Install it:

pip install whoosh3

Step 0: what we're starting from

Assume a typical FTS5 table with a title and body column, using the porter stemmer so searches match word variants:

import sqlite3

con = sqlite3.connect("notes.db")
con.execute("""
CREATE VIRTUAL TABLE docs USING fts5(
    title,
    body,
    tokenize = 'porter unicode61'
);
""")
# ... rows inserted ...

# A typical FTS5 query:
for rowid, title in con.execute(
        "SELECT rowid, title FROM docs WHERE docs MATCH 'search server' ORDER BY rank"):
    print(rowid, "|", title)

Running that against a small sample corpus prints:

== FTS5 search: 'search server' ==
1 | Deploying Flask on a single server

Our goal: reproduce this in Whoosh, then unlock the features FTS5 can't easily give you.

Step 1: mirror the FTS5 columns as a Whoosh schema

Whoosh indexes are typed. Each FTS5 text column becomes a TEXT field; carry the SQLite rowid across as a unique ID so re-runs update rows instead of duplicating them (that's what makes the migration resumable).

from whoosh.fields import Schema, ID, TEXT

schema = Schema(
    rowid=ID(stored=True, unique=True),
    title=TEXT(stored=True),
    body=TEXT(stored=True),
)

Two field decisions worth knowing:

• stored=True keeps the original value in the index so you can show it in results (FTS5 always stores the columns; Whoosh makes it opt-in).
• unique=True on rowid means update_document() replaces an existing row with the same id — handy for an incremental sync, not just a one-shot dump.

Step 2: stream rows out of FTS5 and index them

An FTS5 virtual table is just a table — plain SELECT reads it. Pull the columns and hand each row to a Whoosh writer:

import os, shutil
from whoosh.index import create_in

INDEX_DIR = "whoosh_index"
if os.path.exists(INDEX_DIR):
    shutil.rmtree(INDEX_DIR)
os.mkdir(INDEX_DIR)
ix = create_in(INDEX_DIR, schema)

con = sqlite3.connect("notes.db")
writer = ix.writer()
count = 0
for rowid, title, body in con.execute("SELECT rowid, title, body FROM docs"):
    writer.add_document(rowid=str(rowid), title=title, body=body)
    count += 1
writer.commit()
con.close()
print(f"Migrated {count} documents into Whoosh.")

For a large table, iterate the cursor in batches and call writer.commit() periodically, or use ix.writer(limitmb=256) to give the writer more memory. The cursor itself streams, so you never load the whole table into RAM.

Step 3: run the same query in Whoosh

FTS5's MATCH searches every column at once. The Whoosh equivalent is a MultifieldParser over the fields you want:

from whoosh.qparser import MultifieldParser

with ix.searcher() as s:
    parser = MultifieldParser(["title", "body"], schema=ix.schema)
    q = parser.parse("search server")
    for hit in s.search(q):
        print(hit["rowid"], "|", hit["title"])

Output — the same document FTS5 returned:

Migrated 4 documents into Whoosh.
== Whoosh search: 'search server' ==
1 | Deploying Flask on a single server

Whoosh ranks with BM25F by default, so relevance ordering is in the same spirit as FTS5's default rank.

The one real gotcha: stemming

This is where a naïve migration quietly changes behavior. Our FTS5 table used tokenize = 'porter', which stems words — so a search for ranked matches a document containing rank. Whoosh's default TEXT analyzer does not stem. To preserve FTS5's behavior, attach a StemmingAnalyzer to the fields:

from whoosh.fields import Schema, ID, TEXT
from whoosh.analysis import StemmingAnalyzer

schema = Schema(
    rowid=ID(stored=True, unique=True),
    title=TEXT(stored=True, analyzer=StemmingAnalyzer()),
    body=TEXT(stored=True, analyzer=StemmingAnalyzer()),
)

With that in place, stemmed queries match word variants exactly as FTS5's porter tokenizer did:

query 'ranked'     -> rowids ['1']
query 'deploy'     -> rowids ['2']
query 'matching'   -> rowids ['1']

ranked finds "rank", deploy finds "Deploying" and "deployment", matching finds "matches". If your FTS5 table used the default unicode61 tokenizer (no stemming), keep Whoosh's default analyzer and you'll match its behavior instead. Pick the analyzer that mirrors how your existing index was built — that's the single most important decision in the whole migration.

What you get once you're on Whoosh

The migration isn't just lateral. Features that are awkward or absent in FTS5 come built in. Highlighting, for example — ask a hit for a snippet with the matched terms marked up:

from whoosh import highlight

with ix.searcher() as s:
    q = MultifieldParser(["title", "body"], schema=ix.schema).parse("relevant matches")
    r = s.search(q)
    r.fragmenter = highlight.ContextFragmenter(maxchars=120, surround=40)
    for hit in r:
        print(hit.highlights("body"))

against document length to rank the most <b class="match term0">relevant</b> <b class="match term1">matches</b> first

From here you also get spell-correction and "did you mean" suggestions, faceting and grouped results, pluggable analyzers per field, and a real query language (ranges, wildcards, boosts) — all in pure Python, all covered in the docs and other tutorials.

A migration checklist

1. List your FTS5 columns and note the tokenize option.
2. Build a Whoosh Schema: one field per column, plus a unique id carried from rowid.
3. Choose the analyzer that matches your tokenizer — StemmingAnalyzer for porter, the default for plain unicode61.
4. SELECT rows from the virtual table and add_document() (or update_document()) each one.
5. Replace WHERE ... MATCH queries with a MultifieldParser over the same columns.
6. Spot-check a handful of real queries against both engines before you cut over.

Still deciding? The companion post, Whoosh vs SQLite FTS5, weighs speed, index size, and deployment with real benchmark numbers.