Sqlite Vec

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.

Gates

When wiring storage and KNN queries, run these steps in order; each step has an objective pass before you rely on results in production or in review comments.

1. Dimension lock — The N in float[N], int8[N], or bit[N] matches the embedding model’s output length (and any Matryoshka slice you apply). Pass: the same N appears in the CREATE VIRTUAL TABLE DDL and in the serialized vector length (len(vector) or the model’s documented dimension).
2. Serialization match — Column type and Python (or other) helpers align: serialize_float32 for float[N], serialize_int8 for int8[N], binary rules for bit[N]. Pass: at least one round-trip insert of a known test vector and a MATCH query using the same serializer returns the expected row (e.g. k = 1 returns that row).
3. Doc-backed edge claims — Distance metric choice, metadata filter operators, partition-key rules, or version-specific behavior are only asserted if they appear in this skill or in the official docs linked under Resources. Pass: the relevant URL or doc section is cited in the artifact (issue, PR, or note) before the claim is treated as settled.

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:

1. 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

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

   • Range: -128 to 127
   • Used for quantized embeddings

3. 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:

1. Vector columns - Store embeddings (float[N], int8[N], bit[N])
2. Metadata columns - Indexed, filterable in KNN queries
3. Partition key columns - Internal sharding for faster filtered queries
4. 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

1. Use partition keys for multi-tenant or temporally-filtered queries
2. Keep k reasonable (10-100 for most use cases)
3. Filter with metadata columns when possible
4. Choose appropriate distance metric for your embeddings
5. Batch operations in transactions
6. Use auxiliary columns for large data not needed in filtering
7. 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