Traditionally, “vector database” meant a hosted service, a Docker container, or at minimum a Postgres extension. The sqlite-vec extension is challenging that meaning. The plain SQLite is a file-based database already bundled into your language’s standard library. It can perform the k-nearest-neighbor vector search directly in SQL, alongside the full-text search.
This is a high-level walkthrough of what that stack looks like: semantic search, keyword search, hybrid retrieval, and the multi-tenant pattern that makes it production-shaped for small scales.
Why this is worth your attention
The pitch for a hosted vector database is usually scale. Billions of vectors, sub-10ms search, global replication. Most applications never get there. They have thousands to low millions of vectors, one region, and a small team. While catering to this set of real-world cases, sqlite-vec gives you:
- Vector search as a normal SQL query.
- Zero infrastructure with data isolation; DB as a file.
- Hybrid search built in; SQLite’s FTS5 paired with vector search.
- Majorly Available: The extension compiles to WASM and runs on the same syntax across Python, Node, Rust, Go, and Elixir bindings.
Setting up vector search
Install the extension and its Python bindings, then load it into a normal SQLite connection:
pip install sqlite-vec
import sqlite3
import sqlite_vec
import struct
def serialize(vector: list[float]) -> bytes:
return struct.pack(f"{len(vector)}f", *vector)
db = sqlite3.connect("knowledge.db")
db.enable_load_extension(True)
sqlite_vec.load(db)
db.enable_load_extension(False)
# A vec0 virtual table for 384-dim embeddings (e.g. all-MiniLM-L6-v2)
db.execute("""
CREATE VIRTUAL TABLE IF NOT EXISTS doc_vectors USING vec0(
embedding float[384]
)
""")
Vectors go in as packed binary floats, and a k-nearest-neighbour query is just a MATCH clause with an ORDER BY distance:
db.execute(
"INSERT INTO doc_vectors(rowid, embedding) VALUES (?, ?)",
(doc_id, serialize(embedding)),
)
rows = db.execute(
"""
SELECT rowid, distance
FROM doc_vectors
WHERE embedding MATCH ?
ORDER BY distance
LIMIT 5
""",
[serialize(query_embedding)],
).fetchall()
That’s a working KNN vector index. No index-building step to manage separately, no service to keep warm.
Adding keyword search: FTS5 alongside vec0
Semantic search misses exact terms, for example, product SKUs, error codes, acronyms etc. Keyword search finds these easily. SQLite has had a full-text search module, FTS5, built in:
db.execute("""
CREATE VIRTUAL TABLE IF NOT EXISTS doc_text USING fts5(
content
)
""")
db.execute(
"INSERT INTO doc_text(rowid, content) VALUES (?, ?)",
(doc_id, text),
)
keyword_rows = db.execute(
"""
SELECT rowid, rank
FROM doc_text
WHERE content MATCH ?
ORDER BY rank
LIMIT 5
""",
[query_text],
).fetchall()
You now have two independent rankings of the same rowid space: one by semantic distance and the other by BM25-style keyword rank.
Combining them: Reciprocal Rank Fusion
The standard way to merge two rankings without needing to calibrate their scores against each other is Reciprocal Rank Fusion (RRF): each result gets 1 / (k + rank) from each list it appears in, summed across lists.
def reciprocal_rank_fusion(vector_rows, keyword_rows, k=60):
scores: dict[int, float] = {}
for rank, (rowid, _distance) in enumerate(vector_rows):
scores[rowid] = scores.get(rowid, 0) + 1 / (k + rank + 1)
for rank, (rowid, _bm25) in enumerate(keyword_rows):
scores[rowid] = scores.get(rowid, 0) + 1 / (k + rank + 1)
return sorted(scores.items(), key=lambda kv: kv[1], reverse=True)
fused = reciprocal_rank_fusion(rows, keyword_rows)
This fusion implements a hybrid search for your application.
Pattern for data isolation in small multi-tenant apps: one file, not one table
The instinctive design for multi-tenancy is a shared database with a tenant_id column and a WHERE filter on every query. Resist that instinct if you can. SQLite makes a much stronger pattern nearly free: one database file per tenant.
/data/
tenant-client1.sqlite
tenant-client2.sqlite
tenant-client3.sqlite
A request resolves its tenant server-side and opens exactly that file. There’s no shared table to filter. Isolation becomes a property of the filesystem. As a bonus, this also reduces search space for a single query; a tenant’s queries only ever scan that tenant’s file, not the entire corpus.
Where SQLite hits a ceiling
A few signals it’s time to grow up: heavy concurrent writes to the same file serialize, since SQLite allows one writer at a time (WAL mode helps reads proceed alongside writes, but doesn’t remove that constraint); brute-force KNN in vec0 starts to slow down once a single file reaches tens of millions of vectors, where ANN-indexed systems (HNSW, IVF) pull ahead; and if your product needs first-class search across all tenants at once, the one-file-per-tenant pattern works against you. None of these rules out SQLite upfront. They are just the point where a specific hot tenant or table graduates to something heavier.
Pushing the index to the edge
The whole index is a single file with no server process to stand up. Instead of keeping it centralized behind an API call, it can be moved to wherever the query is happening e.g. a phone, a browser-app etc. That’s a meaningfully different latency shape: a round trip to a hosted vector database is a network hop plus queueing plus a service’s own query time; a local function call. For anything latency-sensitive or intermittently connected an in-store lookup device, a mobile app doing on-device semantic search, an offline field-service tool, it’s the difference between “instant” and “spinner.”
The same property that makes this good for isolation makes it good for the edge: the file travels as a unit. Sync a fresh copy down to a device on deploy or on a schedule, and it works fully offline between syncs. This is also the use case the newer sqlite-ai / sqlite-vector extensions are explicitly built around, including running small embedding and inference models on-device so a query never needs the network at all.
A few scenarios where SQLite beats a “proper” vector database
- Offline-first mobile or desktop apps. An agentic field-service or note-taking app requires semantic RAG to function on a plane or in a basement and has no real alternative to an embedded index.
- Regulated, single-tenant on-prem deployments. Healthcare, legal, and government customers that require data to never leave a specific machine or network segment are often better served by a file they fully control than by any hosted service, however compliant.
- Edge and IoT devices with intermittent connectivity. Kiosks, vehicles, and field sensors that need to answer semantic queries locally and sync opportunistically are a poor fit for anything that assumes an always-on connection.
A managed vector database still earns its keep when you need massive concurrent write throughput or cross-tenant search as a first-class feature.
The wider ecosystem, briefly
sqlite-vec is the most widely adopted option. Further sqlite-lembed generates embeddings directly via a SQL function. A newer sqlite-ai / sqlite-vector family targets on-device and edge deployments specifically mobile apps, IoT, fully offline scenarios for if your deployment target is offline or resource-constrained.
Closing thought
The interesting shift here isn’t that SQLite got vector search bolted on as a gimmick. It’s what that unlocks for a specific, common situation: a small team that can’t or won’t ship its data to a third-party vector service. Because the whole index lives in a file on your own infrastructure, sensitive or regulated data never has to leave the machine it’s running on just to be searched.
There’s no external service in the loop that could retain it, log it, or get breached independently of you. And because there’s no managed database tier, no per-vector storage fee, and no per-embedding API call to a hosted provider, the cost of adding semantic search stays close to the cost of the compute you are already paying for.
Smaller teams running on-premises or in a single tenant they fully control, can now employ a managed vector database, at a fraction of the cost and without a new place for the data to be live.
