sqlite-vec

sqlite-vec is a lightweight SQLite extension for vector similarity search. It enables storing and querying vector embeddings directly in SQLite databases without external vector databases.

Quick Reference Load Extension import sqlite3 import sqlite_vec from sqlite_vec import serialize_float32

db = sqlite3.connect(":memory:") db.enable_load_extension(True) sqlite_vec.load(db) db.enable_load_extension(False)

Basic KNN Query -- Create table CREATE VIRTUAL TABLE vec_items USING vec0( embedding float[4] );

-- Insert vectors (use serialize_float32() in Python) INSERT INTO vec_items(rowid, embedding) VALUES (1, X'CDCCCC3DCDCC4C3E9A99993E00008040');

-- KNN query SELECT rowid, distance FROM vec_items WHERE embedding MATCH '[0.3, 0.3, 0.3, 0.3]' AND k = 10 ORDER BY distance;

Core Concepts Vector Types

sqlite-vec supports three vector element types:

float[N] - 32-bit floating point (4 bytes per element)

Most common for embeddings (OpenAI, Cohere, etc.) Example: float[1536] for text-embedding-3-small

int8[N] - 8-bit signed integers (1 byte per element)

Range: -128 to 127 Used for quantized embeddings

bit[N] - Binary vectors (1 bit per element, packed into bytes)

Most compact storage Used for binary quantization Binary Serialization Format

Vectors must be provided as binary BLOBs or JSON strings. Python helper functions:

from sqlite_vec import serialize_float32, serialize_int8 import struct

Float32 vectors

vector = [0.1, 0.2, 0.3, 0.4] blob = serialize_float32(vector)

Equivalent to: struct.pack("%sf" % len(vector), *vector)

Int8 vectors

int_vector = [1, 2, 3, 4] blob = serialize_int8(int_vector)

Equivalent to: struct.pack("%sb" % len(int_vector), *int_vector)

NumPy arrays can be passed directly (must cast to float32):

import numpy as np embedding = np.array([0.1, 0.2, 0.3, 0.4]).astype(np.float32) db.execute("SELECT vec_length(?)", [embedding])

vec0 Virtual Tables

The vec0 virtual table is the primary data structure for vector search.

Basic Table Creation CREATE VIRTUAL TABLE vec_documents USING vec0( document_id integer primary key, contents_embedding float[768] );

Distance Metrics CREATE VIRTUAL TABLE vec_items USING vec0( embedding float[768] distance_metric=cosine );

Supported metrics: l2 (default), cosine, hamming (bit vectors only)

Column Types

vec0 tables support four column types:

Vector columns - Store embeddings (float[N], int8[N], bit[N]) Metadata columns - Indexed, filterable in KNN queries Partition key columns - Internal sharding for faster filtered queries Auxiliary columns - Unindexed storage (prefix with +)

Example with all column types:

CREATE VIRTUAL TABLE vec_knowledge_base USING vec0( document_id integer primary key,

-- Partition keys (sharding) organization_id integer partition key, created_month text partition key,

-- Vector column content_embedding float[768] distance_metric=cosine,

-- Metadata columns (filterable in KNN) document_type text, language text, word_count integer, is_public boolean,

-- Auxiliary columns (not filterable) +title text, +full_content text, +url text );

KNN Queries Standard Query Syntax SELECT rowid, distance FROM vec_items WHERE embedding MATCH ? AND k = 10 ORDER BY distance;

Key components:

WHERE embedding MATCH ? - Triggers KNN query AND k = 10 - Limit to 10 nearest neighbors ORDER BY distance - Sort results by proximity Metadata Filtering SELECT document_id, distance FROM vec_movies WHERE synopsis_embedding MATCH ? AND k = 5 AND genre = 'scifi' AND num_reviews BETWEEN 100 AND 500 AND mean_rating > 3.5 AND contains_violence = false ORDER BY distance;

Supported operators on metadata: =, !=, >, >=, <, <=, BETWEEN

Not supported: IS NULL, LIKE, GLOB, REGEXP, scalar functions

Partition Key Filtering SELECT document_id, distance FROM vec_documents WHERE contents_embedding MATCH ? AND k = 20 AND user_id = 123 -- Partition key pre-filters ORDER BY distance;

Partition keys enable multi-tenant or temporal sharding. Best practices:

Each unique partition value should have 100+ vectors Use 1-2 partition keys maximum Avoid over-sharding (too many unique values) Joining with Source Tables WITH knn_matches AS ( SELECT document_id, distance FROM vec_documents WHERE contents_embedding MATCH ? AND k = 10 ) SELECT documents.id, documents.title, knn_matches.distance FROM knn_matches LEFT JOIN documents ON documents.id = knn_matches.document_id ORDER BY knn_matches.distance;

Distance Functions

For manual distance calculations (non-vec0 tables):

-- L2 distance SELECT vec_distance_l2('[1, 2]', '[3, 4]'); -- 2.8284...

-- Cosine distance SELECT vec_distance_cosine('[1, 1]', '[2, 2]'); -- ~0.0

-- Hamming distance (bit vectors) SELECT vec_distance_hamming(vec_bit(X'F0'), vec_bit(X'0F')); -- 8

Vector Operations Constructors -- Float32 SELECT vec_f32('[.1, .2, .3, 4]'); -- Subtype 223

-- Int8 SELECT vec_int8('[1, 2, 3, 4]'); -- Subtype 225

-- Bit SELECT vec_bit(X'F0'); -- Subtype 224

Metadata Functions -- Get length SELECT vec_length('[1, 2, 3]'); -- 3

-- Get type SELECT vec_type(vec_int8('[1, 2]')); -- 'int8'

-- Convert to JSON SELECT vec_to_json(vec_f32('[1, 2]')); -- '[1.000000,2.000000]'

Arithmetic -- Add vectors SELECT vec_to_json( vec_add('[.1, .2, .3]', '[.4, .5, .6]') ); -- '[0.500000,0.700000,0.900000]'

-- Subtract vectors SELECT vec_to_json( vec_sub('[.1, .2, .3]', '[.4, .5, .6]') ); -- '[-0.300000,-0.300000,-0.300000]'

Transformations -- Normalize (L2 norm) SELECT vec_to_json( vec_normalize('[2, 3, 1, -4]') ); -- '[0.365148,0.547723,0.182574,-0.730297]'

-- Slice (for Matryoshka embeddings) SELECT vec_to_json( vec_slice('[1, 2, 3, 4]', 0, 2) ); -- '[1.000000,2.000000]'

-- Matryoshka pattern: slice then normalize SELECT vec_normalize(vec_slice(embedding, 0, 256)) FROM vec_items;

Quantization -- Binary quantization (positive→1, negative→0) SELECT vec_quantize_binary('[1, 2, 3, 4, -5, -6, -7, -8]'); -- X'0F'

-- Visualize SELECT vec_to_json( vec_quantize_binary('[1, 2, -3, 4, -5, 6, -7, 8]') ); -- '[0,1,0,0,1,0,1,0]'

Iteration -- Iterate through elements SELECT rowid, value FROM vec_each('[1, 2, 3, 4]'); / ┌───────┬───────┐ │ rowid │ value │ ├───────┼───────┤ │ 0 │ 1 │ │ 1 │ 2 │ │ 2 │ 3 │ │ 3 │ 4 │ └───────┴───────┘ /

Python Integration Complete Example import sqlite3 import sqlite_vec from sqlite_vec import serialize_float32

Setup

db = sqlite3.connect(":memory:") db.enable_load_extension(True) sqlite_vec.load(db) db.enable_load_extension(False)

Create table

db.execute(""" CREATE VIRTUAL TABLE vec_items USING vec0( embedding float[4] ) """)

Insert vectors

items = [ (1, [0.1, 0.1, 0.1, 0.1]), (2, [0.2, 0.2, 0.2, 0.2]), (3, [0.3, 0.3, 0.3, 0.3]) ]

with db: for rowid, vector in items: db.execute( "INSERT INTO vec_items(rowid, embedding) VALUES (?, ?)", [rowid, serialize_float32(vector)] )

Query

query = [0.25, 0.25, 0.25, 0.25] results = db.execute( """ SELECT rowid, distance FROM vec_items WHERE embedding MATCH ? AND k = 2 ORDER BY distance """, [serialize_float32(query)] ).fetchall()

for rowid, distance in results: print(f"rowid={rowid}, distance={distance}")

Embedding API Integration from openai import OpenAI from sqlite_vec import serialize_float32

client = OpenAI()

Generate embedding

response = client.embeddings.create( input="your text here", model="text-embedding-3-small" ) embedding = response.data[0].embedding

Store in sqlite-vec

db.execute( "INSERT INTO vec_documents(id, embedding) VALUES(?, ?)", [doc_id, serialize_float32(embedding)] )

Query

query_embedding = client.embeddings.create( input="search query", model="text-embedding-3-small" ).data[0].embedding

results = db.execute( """ SELECT id, distance FROM vec_documents WHERE embedding MATCH ? AND k = 10 """, [serialize_float32(query_embedding)] ).fetchall()

Performance Tips Use partition keys for multi-tenant or temporally-filtered queries Keep k reasonable (10-100 for most use cases) Filter with metadata columns when possible Choose appropriate distance metric for your embeddings Batch operations in transactions Use auxiliary columns for large data not needed in filtering Ensure partition keys have 100+ vectors per unique value Common Patterns Multi-tenant Search CREATE VIRTUAL TABLE vec_docs USING vec0( doc_id integer primary key, user_id integer partition key, embedding float[768] );

SELECT doc_id, distance FROM vec_docs WHERE embedding MATCH ? AND k = 10 AND user_id = 123;

Hybrid Search SELECT product_id, distance FROM vec_products WHERE embedding MATCH ? AND k = 20 AND category = 'electronics' AND price < 1000.0 ORDER BY distance;

Matryoshka Embeddings -- Adaptive dimensions: slice then normalize SELECT vec_normalize(vec_slice(embedding, 0, 256)) FROM vec_items;

Reference Files setup.md - Installation, extension loading, Python bindings, NumPy integration tables.md - vec0 table creation, column types, metadata/partition/auxiliary columns queries.md - KNN query patterns, metadata filtering, partition filtering, optimization operations.md - Vector operations, constructors, transformations, quantization, batch operations Resources Official documentation: https://alexgarcia.xyz/sqlite-vec GitHub repository: https://github.com/asg017/sqlite-vec Python package: https://pypi.org/project/sqlite-vec/ API reference: https://alexgarcia.xyz/sqlite-vec/api-reference.html