sqlite-vec: Vector Search in SQLite (Tested Python Example)

sqlite-vec is a SQLite extension for vector search. It stores vectors in vec0 virtual tables and finds the nearest ones with a MATCH query. It is “Written in pure C, no dependencies, runs anywhere SQLite runs” (sqlite-vec README), and because it’s a loadable extension, it works with the stock SQLite already in your language. Its README warns: “sqlite-vec is a pre-v1, so expect breaking changes!” Pin the version you test with.

pip install sqlite-vec, load it into a connection with sqlite_vec.load(db), create CREATE VIRTUAL TABLE vec_notes USING vec0(embedding float[3]), insert vectors as JSON arrays, and query with WHERE embedding MATCH ? AND k = 2 ORDER BY distance.

The example was run on 6 October 2026 with Python 3.13.14 with SQLite 3.53.4 and sqlite-vec 0.1.9, plus a compatibility check in the stock sqlite3 shell 3.51.0. Outputs are pasted from the run.

Store and search vectors from Python

Keep normal columns in an ordinary table and the vectors in a vec0 table that shares its rowid:

import sqlite3
import sqlite_vec

db = sqlite3.connect("notes.db")
db.enable_load_extension(True)
sqlite_vec.load(db)                # loads the vec0 extension into this connection
db.enable_load_extension(False)
print("vec_version:", db.execute("SELECT vec_version()").fetchone()[0])

db.execute("CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT NOT NULL)")
db.execute("CREATE VIRTUAL TABLE vec_notes USING vec0(embedding float[3])")
rows = [(1, "sqlite tips", "[0.9, 0.1, 0.0]"), (2, "postgres tips", "[0.1, 0.9, 0.0]"), (3, "sqlite wal", "[0.8, 0.2, 0.1]")]
for id_, body, vec in rows:
    db.execute("INSERT INTO notes (id, body) VALUES (?, ?)", (id_, body))
    db.execute("INSERT INTO vec_notes (rowid, embedding) VALUES (?, ?)", (id_, vec))
db.commit()

knn = db.execute("""
    SELECT notes.body, round(v.distance, 3) AS distance
    FROM vec_notes AS v JOIN notes ON notes.id = v.rowid
    WHERE v.embedding MATCH ? AND k = 2
    ORDER BY v.distance
""", ("[1, 0, 0]",)).fetchall()
print("knn:", knn)

cos = db.execute("""
    SELECT notes.body, round(vec_distance_cosine(v.embedding, ?), 3) AS cosine
    FROM vec_notes AS v JOIN notes ON notes.id = v.rowid ORDER BY cosine
""", ("[1, 0, 0]",)).fetchall()
print("cosine:", cos)
db.close()

$ python vec_demo.py
vec_version: v0.1.9
knn: [('sqlite tips', 0.141), ('sqlite wal', 0.3)]
cosine: [('sqlite tips', 0.006), ('sqlite wal', 0.037), ('postgres tips', 0.89)]

The KNN distance here is Euclidean: 0.141 is √(0.1² + 0.1²), the distance from [1, 0, 0] to [0.9, 0.1, 0.0]. For cosine distance, vec_distance_cosine() computes it directly. That scans every row, which is fine for small tables. Each vector here has 3 dimensions; real embeddings have hundreds.

Python’s sqlite3 module can only load extensions if it was built with extension support (enable_load_extension must exist). The Homebrew Python used here has it.

Compatibility with stock SQLite

The database stays a normal SQLite file. Without the extension loaded, ordinary tables work and the vec0 table fails with a clear error, rather than returning wrong results:

$ sqlite3 notes.db "SELECT body FROM notes;"
sqlite tips
postgres tips
sqlite wal
$ sqlite3 notes.db "SELECT rowid FROM vec_notes;"
Error: in prepare, no such module: vec0

Compare libSQL’s vector index, which makes stock SQLite miscount that table’s rows and refuse inserts and deletes in it.

Related

libSQL, Python sqlite3, SQLite JSON, and the project: sqlite-vec README.