sqlite-vec local vector search with no server: a Python walkthrough

You can do sqlite-vec local vector search from a single .db file: no Docker, no Postgres, no vector database account. Load the extension into SQLite, create a vec0 virtual table, insert float vectors, and ask for the k nearest. I ran the whole thing below on 2026-10-31 and pasted the real output.

It suits a desktop tool, a CLI, a personal notes search or a small app where one process owns the data. It does not suit a multi-user web app. That comparison is at the end.

How do you install sqlite-vec for Python?

python3 -m venv .venv
.venv/bin/pip install sqlite-vec numpy

I installed version 0.1.9 (select vec_version() printed v0.1.9). The project README says sqlite-vec is pre-v1 and to expect breaking changes, so pin the version in your requirements file.

The macOS trap

This is where most first attempts die. Python's sqlite3 can only load extensions if it was built with that enabled. On my Mac:

• python.org Python 3.14.3 (SQLite 3.50.4): hasattr(conn, "enable_load_extension") was False.
• Homebrew Python (SQLite 3.54.1): True.

If you see AttributeError: 'sqlite3.Connection' object has no attribute 'enable_load_extension', that is the cause. The sqlite-vec Python docs say the same and recommend Homebrew's Python on macOS. On Linux, distro Pythons generally work, but check with the one-liner:

python3 -c "import sqlite3; print(hasattr(sqlite3.connect(':memory:'), 'enable_load_extension'))"

How do you create a table and run a nearest-neighbour query?

Here is the core of the script I ran. The embed() function is a toy: it hashes words into 256 buckets. It is not semantic, and it only exists so you can run the demo without an embedding API. Swap in a real model for anything useful; the embedding models comparison helps you choose.

import hashlib, math, re, sqlite3, struct
import sqlite_vec

DIM = 256

def embed(text):  # toy stand-in, replace with a real embedding model
    v = [0.0] * DIM
    for tok in re.findall(r"[a-z0-9]+", text.lower()):
        h = int(hashlib.md5(tok.encode()).hexdigest(), 16)
        v[h % DIM] += 1.0 if (h >> 8) & 1 else -1.0
    n = math.sqrt(sum(x * x for x in v)) or 1.0
    return [x / n for x in v]

def blob(v):
    return struct.pack(f"{len(v)}f", *v)

db = sqlite3.connect("kb.db")
db.enable_load_extension(True)
sqlite_vec.load(db)
db.enable_load_extension(False)   # turn it back off once loaded

db.execute("create table chunks(id integer primary key, source text, body text)")
db.execute(f"create virtual table chunk_vec using vec0(embedding float[{DIM}])")

for src, body in docs:
    cur = db.execute("insert into chunks(source, body) values (?, ?)", (src, body))
    db.execute("insert into chunk_vec(rowid, embedding) values (?, ?)",
               (cur.lastrowid, blob(embed(body))))
db.commit()

rows = db.execute("""
    select c.source, round(v.distance, 4), c.body
    from chunk_vec v join chunks c on c.id = v.rowid
    where v.embedding match ? and k = 3
    order by v.distance
""", (blob(embed(question)),)).fetchall()

Three details matter:

1. The vector table and the text table are separate, joined on rowid. vec0 stores vectors. Keep your chunk text, source and timestamps in an ordinary table and use the same integer id as the vector's rowid.
2. Nearest-neighbour syntax is embedding match ? and k = N. You need the k = N constraint; it is not a limit.
3. Disable extension loading after you load it. enable_load_extension(False) shrinks what a SQL-injection bug could do.

I seeded five tiny policy sentences (refunds, shipping, warranty, privacy, returns) and asked: "how many days until I get my refund on the original payment method". Real output:

sqlite 3.54.1 | vec v0.1.9
('refunds.md', 1.0952, 'Refunds are issued to the original payment method within 5 business days.')
('warranty.md', 1.2842, 'The warranty covers manufacturing defects for twelve months from purchase.')
('shipping.md', 1.2905, 'Standard shipping takes 3 to 7 business days inside the country.')

The right chunk wins, but only because my question shares words with it ("refund", "original", "payment", "method"). Rows two and three are noise from hash collisions. This is exactly what a bag-of-words toy does, and it is why you need real embeddings to match a question like "when do I get my money back".

The distance column is L2 distance by default. If you want cosine, either normalise vectors before inserting, or declare the column with distance_metric=cosine. I did not test that option in this session, so check the current sqlite-vec docs before relying on it.

How fast is it at 10,000 and 50,000 vectors?

I filled a second vec0 table with random unit vectors of 384 dimensions (a common embedding size) and timed k = 5 queries in an in-memory database, 5 runs each:

10000 vectors x 384 dims: KNN k=5 median 0.6 ms (5 runs)
50000 vectors x 384 dims: KNN k=5 median 3.0 ms (5 runs)

Five times the vectors, about five times the time. That linear shape is what you would expect from a scan over every vector, though I am inferring that from the numbers rather than from the project's documentation. For a notes app or a project's docs, a few milliseconds is invisible next to the embedding API call you make for the query. Past a few hundred thousand vectors, or when your latency budget is tight, test again with your own data and consider Postgres with an HNSW index (see Supabase pgvector RAG step by step).

Caveats: random vectors, one laptop, memory-resident database. A cold file on a slow disk will be slower.

What breaks, and how do you handle it?

Wrong dimension. My test inserted a 10-element vector into a 256-dimension table:

wrong-dim error: OperationalError Dimension mismatch for inserted vector for the "embedding" column. Expected 256 dimensions but received 10.

Good, it fails loudly. Store the model name and dimension in your own metadata table, and refuse to open a database built with a different model.

Deleting. delete from chunk_vec where rowid = 1 worked and the count dropped from 5 to 4. Delete the matching row in chunks in the same transaction so the two tables cannot drift.

Concurrency. SQLite allows one writer at a time. Fine for one app process; painful for a web server with many workers writing.

Re-embedding. Changing models means a new table. Build chunk_vec_v2 alongside, backfill, swap names. Never mix vectors from two models in one column.

Should you use sqlite-vec, FAISS, Chroma or Postgres?

The vector store guide compares the in-memory and hosted options. If you pair sqlite-vec with SQLite's built-in full-text search you can approximate the hybrid approach in the hybrid search guide. I did not test that combination here.

For the retrieval half of a RAG pipeline, how RAG works explains where this fits.

What I did not verify

• Cosine distance option and metadata or partition columns: not run; check the sqlite-vec docs.
• Behaviour on Windows and Linux: not tested.
• Timings are random vectors, in memory, one machine.
• The embedding function here is a toy. No real embedding model or API was used, so retrieval quality is not demonstrated.