Vector Search in SQLite

This content was generated with the assistance of AI and should be verified against the official documentation of each tool and framework before being relied on in production.

This section’s bibliography lists the reference material consulted while preparing these pages.

Examples on this page target Python 3.12, sqlite-vec 0.1.9 and SQLite’s built-in sqlite3 module (SQLite ships FTS5 in its default build on modern Python). SQLite is not a client/server database: it is an embedded, single-file, in-process engine, which makes it a natural fit for a local, personal-scale vector store — a desktop app, a CLI tool, a notebook, or an edge/mobile deployment — where running a separate vector database would be pure operational overhead. The DocsAssistant scenario uses it as file docsassistant.db, with a vec0 table vec_chunks(chunk_id integer primary key, embedding float[768], component text) alongside an FTS5 table chunks_fts, as an alternative to the server-based pgvector and Qdrant paths documented elsewhere in this section.

sqlite-vec: a vec0 virtual table per collection

sqlite-vec is a SQLite extension, written in pure C with no external dependencies, that adds a vec0 virtual table type for storing and searching vectors. It ships as a Python wheel that also bundles the compiled extension, so there is nothing else to install on the host:

# pip install sqlite-vec
import sqlite3
import sqlite_vec

conn = sqlite3.connect("docsassistant.db")
conn.enable_load_extension(True)
sqlite_vec.load(conn)
conn.enable_load_extension(False)

conn.execute("""
    CREATE VIRTUAL TABLE vec_chunks USING vec0(
        chunk_id INTEGER PRIMARY KEY,
        embedding FLOAT[768],
        component TEXT PARTITION KEY
    )
""")

A vec0 column’s type declares both the element type and the fixed dimensionality: float[768] for 32-bit float embeddings (nomic-embed-text’s native output), `int8[768] for pre-quantized 8-bit embeddings, and bit[768] for a binary-quantized representation — see Quantization & Compression for how those narrower representations are produced. component TEXT PARTITION KEY is a partition key: a column that vec0 uses to physically shard storage, so filtering WHERE component = 'vector-rag' alongside a KNN search only scans that partition’s rows instead of the whole table (partition keys only accept = in a KNN query). Two other kinds of column can be declared directly on the vec0 table:

  • Metadata columns (TEXT, INTEGER, FLOAT or BOOLEAN, declared like an ordinary column) are returned by SELECT and can be filtered in a KNN WHERE clause with =, !=, <, ⇐, > and >=.

  • Auxiliary columns are declared with a leading +, for example +content TEXT. They are returned by SELECT like any other column, so a KNN query can read them without a JOIN, but they cannot appear in a KNN query’s WHERE clause. They suit large payloads such as the chunk text:

CREATE VIRTUAL TABLE vec_chunks_with_text USING vec0(
    chunk_id INTEGER PRIMARY KEY,
    embedding FLOAT[768],
    component TEXT PARTITION KEY,
    chunk_index INTEGER,   -- metadata column: filterable in KNN queries
    +content TEXT          -- auxiliary column: returned by SELECT, not filterable
);

This page’s DocsAssistant schema keeps vec_chunks narrow and stores the text in chunks instead (see the two-table pattern below). See sqlite-vec: metadata, partition-key and auxiliary columns.

Embeddings are inserted as raw bytes via serialize_float32, which packs a Python sequence of floats into the little-endian binary layout vec0 expects:

from sqlite_vec import serialize_float32

embedding = [0.012, -0.034, 0.271]  # truncated; real vectors are 768-dim
conn.execute(
    "INSERT INTO vec_chunks (chunk_id, embedding, component) VALUES (?, ?, ?)",
    (1001, serialize_float32(embedding), "vector-rag"),
)
conn.commit()

A KNN query is an ordinary SELECT with a special MATCH predicate: the query vector matched against the embedding column, combined with k to bound the candidate list and ORDER BY distance to get nearest neighbours first. component = 'vector-rag' alongside the MATCH clause is pushed down as a partition-key filter, so it prunes storage rather than post-filtering results:

query = serialize_float32(query_embedding)  # 768 floats from nomic-embed-text
rows = conn.execute(
    """
    SELECT chunk_id, distance
    FROM vec_chunks
    WHERE embedding MATCH ? AND k = 5 AND component = 'vector-rag'
    ORDER BY distance
    """,
    (query,),
).fetchall()

The distance metric is fixed per table at creation time via distance_metric=cosine (the default is L2/Euclidean); DocsAssistant’s nomic-embed-text embeddings are compared with cosine distance, matching the metric used by its pgvector vector_cosine_ops index:

CREATE VIRTUAL TABLE vec_chunks USING vec0(
    chunk_id INTEGER PRIMARY KEY,
    embedding FLOAT[768] distance_metric=cosine,
    component TEXT PARTITION KEY
);

See the official sqlite-vec Python API and vec0 table documentation for the full column-type and clause reference.

The two-table pattern: vec_chunks joined to chunks

A vec0 table is deliberately narrow: it stores an integer rowid (or an explicit PRIMARY KEY alias, as above), the vector, and whatever partition/metadata/auxiliary columns were declared — it is not meant to hold large text content or a rich, evolving schema. The idiomatic pattern is therefore two tables: chunks holds the full row (content, section path, token count, timestamps, …​) with its own auto-incrementing primary key, and vec_chunks stores only chunk_id (matching chunks.id) and the embedding, joined at query time:

CREATE TABLE chunks (
    id INTEGER PRIMARY KEY,
    source_path TEXT NOT NULL,
    section TEXT,
    content TEXT NOT NULL,
    component TEXT NOT NULL,
    chunk_index INTEGER NOT NULL
);

CREATE VIRTUAL TABLE vec_chunks USING vec0(
    chunk_id INTEGER PRIMARY KEY,
    embedding FLOAT[768] distance_metric=cosine,
    component TEXT PARTITION KEY
);
SELECT c.id, c.source_path, c.section, c.content, v.distance
FROM vec_chunks AS v
JOIN chunks AS c ON c.id = v.chunk_id
WHERE v.embedding MATCH :query_embedding AND v.k = 5
ORDER BY v.distance;

Upserts: chunks versus vec_chunks

Re-ingesting a changed document means overwriting rows that already exist, and the two tables need different statements. chunks is an ordinary table, so SQLite’s UPSERT updates the existing row in place, without the delete/insert cycle of INSERT OR REPLACE — which matters here because a delete fires the FTS5 DELETE trigger and any ON DELETE foreign-key action keyed on chunks.id:

INSERT INTO chunks (id, source_path, section, content, component, chunk_index)
VALUES (:id, :source_path, :section, :content, :component, :chunk_index)
ON CONFLICT (id) DO UPDATE SET
    source_path = excluded.source_path,
    section     = excluded.section,
    content     = excluded.content,
    component   = excluded.component,
    chunk_index = excluded.chunk_index;

vec_chunks is a virtual table, and SQLite does not implement UPSERT for virtual tables: the same ON CONFLICT statement against vec_chunks fails with UPSERT not implemented for virtual table. vec0 in sqlite-vec 0.1.9 also has no INSERT OR REPLACE support — a duplicate chunk_id fails with UNIQUE constraint failed on vec_chunks primary key. Use a plain UPDATE to replace the vector, and DELETE plus INSERT in one transaction when the partition key changes, because vec0 rejects an UPDATE of a partition-key column:

# new embedding, same component: update the vector in place
conn.execute(
    "UPDATE vec_chunks SET embedding = ? WHERE chunk_id = ?",
    (serialize_float32(new_embedding), 1001),
)

# component changed: partition keys cannot be updated, so delete and re-insert atomically
with conn:
    conn.execute("DELETE FROM vec_chunks WHERE chunk_id = ?", (1001,))
    conn.execute(
        "INSERT INTO vec_chunks (chunk_id, embedding, component) VALUES (?, ?, ?)",
        (1001, serialize_float32(new_embedding), "spring-ai"),
    )

See SQLite: UPSERT (including its virtual-table limitation) and sqlite-vec: vec0 virtual tables.

Keeping the raw embeddings

A vec0 table can hand its vectors back (SELECT embedding FROM vec_chunks returns the stored blob), but it only holds whatever representation it was created with. Once the vectors live in an int8[768] or bit[768] table, the original float values are gone, and a dropped or corrupted vec0 table takes the only copy with it. Rebuilding a vec0 table — for a new distance_metric, a different quantization, or an upgraded sqlite-vec version — is then a full re-embedding run. Keeping the raw float32 embeddings in an ordinary side table makes every such rebuild a single INSERT …​ SELECT:

CREATE TABLE chunk_vectors (
    chunk_id INTEGER PRIMARY KEY REFERENCES chunks (id) ON DELETE CASCADE,
    model TEXT NOT NULL,           -- e.g. 'nomic-embed-text'
    embedding BLOB NOT NULL        -- serialize_float32() output, 768 x float32
);

-- rebuild a quantized vec0 table from the raw vectors, without calling the embedding model
CREATE VIRTUAL TABLE vec_chunks_int8 USING vec0(
    chunk_id INTEGER PRIMARY KEY,
    embedding INT8[768] distance_metric=cosine,
    component TEXT PARTITION KEY
);

INSERT INTO vec_chunks_int8 (chunk_id, embedding, component)
SELECT cv.chunk_id, vec_quantize_int8(cv.embedding, 'unit'), c.component
FROM chunk_vectors AS cv
JOIN chunks AS c ON c.id = cv.chunk_id;

See sqlite-vec: API reference for vec_quantize_int8 and the other scalar functions.

FTS5 hybrid search on chunks_fts

SQLite’s bundled FTS5 extension provides BM25-ranked full-text search as a second virtual table. Declared as an external-content table (content='chunks'), it stores only the full-text index and reads the text itself from chunks, so it must be kept in sync by triggers on all three write operations. An index that misses an UPDATE or DELETE on chunks keeps stale entries, which return wrong results and can later corrupt the index. External-content tables remove old entries with the special 'delete' command, which must be given the old column values:

CREATE VIRTUAL TABLE chunks_fts USING fts5(content, content='chunks', content_rowid='id');

CREATE TRIGGER chunks_ai AFTER INSERT ON chunks BEGIN
    INSERT INTO chunks_fts(rowid, content) VALUES (new.id, new.content);
END;

CREATE TRIGGER chunks_ad AFTER DELETE ON chunks BEGIN
    INSERT INTO chunks_fts(chunks_fts, rowid, content) VALUES ('delete', old.id, old.content);
END;

CREATE TRIGGER chunks_au AFTER UPDATE ON chunks BEGIN
    INSERT INTO chunks_fts(chunks_fts, rowid, content) VALUES ('delete', old.id, old.content);
    INSERT INTO chunks_fts(rowid, content) VALUES (new.id, new.content);
END;

-- index rows that were already in chunks before the triggers existed
INSERT INTO chunks_fts(chunks_fts) VALUES ('rebuild');

See SQLite FTS5: External content tables for these triggers and the 'rebuild' command.

A hybrid query runs the vector MATCH and the FTS5 MATCH as two separate SELECT`s and fuses their ranked result lists in application code — SQLite has no built-in RRF operator, so the reciprocal rank fusion logic (summing `1 / (60 + rank) over two ranked id lists) is exactly what bridges them:

vector_hits = conn.execute(
    "SELECT chunk_id, distance FROM vec_chunks WHERE embedding MATCH ? AND k = 20 ORDER BY distance",
    (serialize_float32(query_embedding),),
).fetchall()

fts_hits = conn.execute(
    "SELECT rowid, bm25(chunks_fts) AS score FROM chunks_fts WHERE chunks_fts MATCH ? ORDER BY score LIMIT 20",
    (fts_query,),
).fetchall()

# reciprocal rank fusion: both lists are already sorted best-first
rrf_scores: dict[int, float] = {}
for hits in (vector_hits, fts_hits):
    for rank, (chunk_id, _score) in enumerate(hits, start=1):
        rrf_scores[chunk_id] = rrf_scores.get(chunk_id, 0.0) + 1.0 / (60 + rank)

top_ids = sorted(rrf_scores, key=rrf_scores.get, reverse=True)[:5]

See SQLite — FTS5 Extension for the full query-syntax and ranking reference, and Cormack, Clarke & Büttcher (SIGIR 2009) for the RRF formula and its k = 60 constant.

From sqlite-vss to sqlite-vec, and beyond a single file

An older approach to vector search inside SQLite, sqlite-vss, embedded FAISS itself as its search backend. It ships as two loadable extensions that must be loaded in order: vector0, which provides the vector helper functions, and vss0, which provides the FAISS-backed vss0 virtual table (its index type is chosen with a FAISS factory string, Flat,IDMap2 by default). Its author has stopped active development and directs users to sqlite-vec instead. Where sqlite-vss vendored FAISS’s C++ build (heavy, platform-specific, and inheriting FAISS’s non-transactional semantics — what FAISS does not give you), today the answer is sqlite-vec: a small, dependency-free, pure-C reimplementation that plugs directly into SQLite’s own transactional, single-file semantics instead of wrapping an external library. Existing sqlite-vss deployments migrate by re-creating a vec0 table with the equivalent column types and re-inserting every embedding — there is no automatic schema converter between the two extensions.

Turso and its open-source engine libSQL (a SQLite fork) take a different route: native F32_BLOB vector columns and a vector_top_k index function built directly into the engine rather than as a loadable extension, backed by a DiskANN-derived index, plus a hosted, replicated service on top of the same file format — useful when the appeal of SQLite’s embedded model is wanted together with managed multi-region replication that sqlite-vec alone does not provide.

Both remain personal-to-small-scale tools: a single writer and no built-in replication. For sqlite-vec, the indexing options are also limited: in the stable 0.1.9 release every vec0 KNN query is an exhaustive (brute-force) scan, sped up only by partition keys, metadata filters and narrower int8 / bit vectors — there is no ANN index to tune. Approximate indexes (a rescoring index, DiskANN, and an experimental IVF) first appeared in the 0.1.10 alpha pre-releases and are not part of a stable release yet — database/choosing-the-right-database.adoc#_vector_databases frames exactly this trade-off against purpose-built engines. LangChain wraps this store as SQLiteVec, letting the same retriever code target either backend behind its generic vector-store interface (see below).

In LangChain and Spring AI

LangChain

LangChain’s SQLiteVec vector store wraps the two-table pattern shown above — text, metadata and embeddings — behind the same generic construction, similarity-search and retriever-conversion interface documented in Vector stores:

from langchain_community.vectorstores import SQLiteVec
from langchain_ollama import OllamaEmbeddings

embeddings = OllamaEmbeddings(model="nomic-embed-text")
store = SQLiteVec.from_texts(
    texts=["HNSW indexes...", "pgvector basics...", "RAG with Spring AI..."],
    embedding=embeddings,
    table="docsassistant",
    db_file="docsassistant.db",
)
hits = store.similarity_search("how do I speed up similarity search", k=5)

See LangChain — SQLiteVec for the full constructor and connection options.

Spring AI

Spring AI ships no first-party SQLite vector store: the framework’s VectorStore auto-configurations target client/server engines (pgvector, Redis, Qdrant, and others), and an embedded, single-file store with no network protocol does not fit that model any more than FAISS does (see FAISS’s equivalent gap). A Spring application that specifically wants SQLite-backed vector search would implement the JDBC access itself against the vec0 schema shown above rather than through a managed starter. See VectorStore API for the general abstraction and the list of stores Spring AI does support out of the box.

Two-table schema

erDiagram CHUNKS ||--|| VEC_CHUNKS : "id = chunk_id" CHUNKS ||--|| CHUNKS_FTS : "id = rowid" CHUNKS { integer id PK text source_path text section text content text component integer chunk_index } VEC_CHUNKS { integer chunk_id PK float_768 embedding text component } CHUNKS_FTS { integer rowid PK text content }

Related pages

References