SQLite FTS5: The Full-Text Search Engine Already in Your App

Most developers reach for Elasticsearch the moment someone says “search.”

Spin up a cluster. Configure shards. Pay the bill. Babysit it on-call.

Meanwhile, SQLite has had full-text search built in since 2015 and almost nobody talks about it.

It’s called FTS5. And for a shocking number of use cases, it’s all you need.

The part that makes me laugh every time I set it up:

sql

CREATE VIRTUAL TABLE email USING fts5(sender, title, body);
SELECT * FROM email WHERE email MATCH 'invoice' ORDER BY rank;

That’s it. That’s the search engine.

No service to run. No network hop. No JVM. Just a file on disk.

And it isn’t a toy. FTS5 ships with the stuff you actually need in production:

• BM25 ranking out of the box (yes, the same scoring Elasticsearch uses)

The one thing worth actually thinking about upfront is how you store content.

By default FTS5 keeps a copy of your rows alongside the index. Fine for small stuff. Wasteful once your data grows. The fix is external content tables — FTS5 indexes the text but reads the originals from your existing table through triggers. Same query interface, roughly half the disk.

For append-heavy workloads there’s a contentless-delete mode that skips the copy entirely and still lets you delete rows cleanly. Honestly I wish I’d known about this one sooner.

Where FTS5 falls short is pretty much where you’d expect:

If you’re building Google, use something else. If you’re building search for a SaaS app with a few million rows, a CLI tool, a local-first app, a Chrome extension, or a side project you don’t want to pay $50/month to host... FTS5 is probably the right answer and you didn’t know it existed.

The whole thing is a few hundred KB. Already compiled into the SQLite that ships with your language’s standard library. You don’t even need to install anything.

Go read the docs. You’ll ship search this weekend.