sqlite-vec

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

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.

sqlite-vec是一款用于向量相似度搜索的轻量级SQLite扩展。它支持直接在SQLite数据库中存储和查询向量嵌入，无需依赖外部向量数据库。

Quick Reference

快速参考

Load Extension

加载扩展

python

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)

python

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

基础KNN查询

sql

-- 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;

sql

-- 创建表
CREATE VIRTUAL TABLE vec_items USING vec0(
  embedding float[4]
);

-- 插入向量（在Python中使用serialize_float32()）
INSERT INTO vec_items(rowid, embedding)
VALUES (1, X'CDCCCC3DCDCC4C3E9A99993E00008040');

-- KNN查询
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

sqlite-vec支持三种向量元素类型：

float[N] - 32位浮点数（每个元素占4字节）
- 是嵌入向量最常用的类型（如OpenAI、Cohere等模型的输出）
- 示例：
```
float[1536]
```
  对应text-embedding-3-small模型的输出
int8[N] - 8位有符号整数（每个元素占1字节）
- 取值范围：-128 至 127
- 用于量化后的嵌入向量
bit[N] - 二进制向量（每个元素占1位，打包为字节存储）
- 存储密度最高
- 用于二进制量化的向量

Binary Serialization Format

二进制序列化格式

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

python

from sqlite_vec import serialize_float32, serialize_int8
import struct

向量必须以二进制BLOB或JSON字符串的形式提供。Python辅助函数示例：

python

from sqlite_vec import serialize_float32, serialize_int8
import struct

Float32 vectors

Float32向量

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

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

等价于：struct.pack("%sf" % len(vector), *vector)

Int8 vectors

Int8向量

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

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

等价于：struct.pack("%sb" % len(int_vector), *int_vector)


NumPy arrays can be passed directly (must cast to float32):
```python
import numpy as np
embedding = np.array([0.1, 0.2, 0.3, 0.4]).astype(np.float32)
db.execute("SELECT vec_length(?)", [embedding])


NumPy数组可直接传入（需转换为float32类型）：
```python
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

vec0虚拟表

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

vec0虚拟表是向量搜索的核心数据结构。

Basic Table Creation

基础表创建

sql

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

sql

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

Distance Metrics

距离度量

sql

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

Supported metrics:

l2

(default),

cosine

hamming

(bit vectors only)

sql

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

支持的度量方式：

l2

（默认）、

cosine

、

hamming

（仅适用于bit向量）

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:

sql

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
);

vec0表支持四种列类型：

向量列 - 存储嵌入向量（float[N], int8[N], bit[N]）
元数据列 - 已索引，可在KNN查询中过滤
分区键列 - 用于内部分片，加速带过滤条件的查询
辅助列 - 未索引存储（前缀为+）

包含所有列类型的示例：

sql

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

  -- 分区键（分片）
  organization_id integer partition key,
  created_month text partition key,

 -- 向量列
  content_embedding float[768] distance_metric=cosine,

  -- 元数据列（可在KNN查询中过滤）
  document_type text,
  language text,
  word_count integer,
  is_public boolean,

  -- 辅助列（不可过滤）
  +title text,
  +full_content text,
  +url text
);

KNN Queries

KNN查询

Standard Query Syntax

标准查询语法

sql

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

sql

SELECT rowid, distance
FROM vec_items
WHERE embedding MATCH ?
  AND k = 10
ORDER BY distance;

核心组成部分：

```
WHERE embedding MATCH ?
```
- 触发KNN查询
```
AND k = 10
```
- 限制返回10个最近邻
```
ORDER BY distance
```
- 按相似度排序结果

Metadata Filtering

元数据过滤

sql

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

sql

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;

元数据支持的操作符：

、

!=

、

>=

、

<=

、

BETWEEN

不支持的操作：

IS NULL

、

LIKE

、

GLOB

、

REGEXP

、标量函数

Partition Key Filtering

分区键过滤

sql

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)

sql

SELECT document_id, distance
FROM vec_documents
WHERE contents_embedding MATCH ?
  AND k = 20
  AND user_id = 123  -- 分区键预过滤
ORDER BY distance;

分区键支持多租户或时间维度的分片。最佳实践：

每个唯一分区值应包含100个以上的向量
最多使用1-2个分区键
避免过度分片（唯一值过多）

Joining with Source Tables

与源表关联查询

sql

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;

sql

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):

sql

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

用于手动计算距离（非vec0表场景）：

sql

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

-- 余弦距离
SELECT vec_distance_cosine('[1, 1]', '[2, 2]');
-- ~0.0

-- 汉明距离（仅bit向量）
SELECT vec_distance_hamming(vec_bit(X'F0'), vec_bit(X'0F'));
-- 8

Vector Operations

向量操作

Constructors

构造函数

sql

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

sql

-- Float32向量
SELECT vec_f32('[.1, .2, .3, 4]');  -- 子类型223

-- Int8向量
SELECT vec_int8('[1, 2, 3, 4]');  -- 子类型225

-- 二进制向量
SELECT vec_bit(X'F0');  -- 子类型224

Metadata Functions

元数据函数

sql

-- 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]'

sql

-- 获取向量长度
SELECT vec_length('[1, 2, 3]');  -- 3

-- 获取向量类型
SELECT vec_type(vec_int8('[1, 2]'));  -- 'int8'

-- 转换为JSON
SELECT vec_to_json(vec_f32('[1, 2]'));  -- '[1.000000,2.000000]'

Arithmetic

算术运算

sql

-- 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]'

sql

-- 向量相加
SELECT vec_to_json(
  vec_add('[.1, .2, .3]', '[.4, .5, .6]')
);
-- '[0.500000,0.700000,0.900000]'

-- 向量相减
SELECT vec_to_json(
  vec_sub('[.1, .2, .3]', '[.4, .5, .6]')
);
-- '[-0.300000,-0.300000,-0.300000]'

Transformations

转换操作

sql

-- 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;

sql

-- 归一化（L2范数）
SELECT vec_to_json(
  vec_normalize('[2, 3, 1, -4]')
);
-- '[0.365148,0.547723,0.182574,-0.730297]'

-- 切片（适用于Matryoshka嵌入）
SELECT vec_to_json(
  vec_slice('[1, 2, 3, 4]', 0, 2)
);
-- '[1.000000,2.000000]'

-- Matryoshka模式：先切片再归一化
SELECT vec_normalize(vec_slice(embedding, 0, 256))
FROM vec_items;

Quantization

量化操作

sql

-- 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]'

sql

-- 二进制量化（正数→1，负数→0）
SELECT vec_quantize_binary('[1, 2, 3, 4, -5, -6, -7, -8]');
-- X'0F'

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

Iteration

迭代操作

sql

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

sql

-- 遍历向量元素
SELECT rowid, value
FROM vec_each('[1, 2, 3, 4]');
/*
┌───────┬───────┐
│ rowid │ value │
├───────┼───────┤
│ 0     │ 1     │
│ 1     │ 2     │
│ 2     │ 3     │
│ 3     │ 4     │
└───────┴───────┘
*/

Python Integration

Python集成

Complete Example

完整示例

python

import sqlite3
import sqlite_vec
from sqlite_vec import serialize_float32

python

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)] )

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}")

undefined

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}")

undefined

Embedding API Integration

与嵌入API集成

python

from openai import OpenAI
from sqlite_vec import serialize_float32

client = OpenAI()

python

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

存储到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()

undefined

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()

undefined

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

使用分区键 优化多租户或带时间过滤条件的查询
合理设置k值（大多数场景下10-100即可）
尽可能使用元数据列过滤
为嵌入向量选择合适的距离度量
在事务中批量操作
使用辅助列存储无需过滤的大尺寸数据
确保每个分区键的唯一值对应100个以上的向量

Common Patterns

常见模式

Multi-tenant Search

多租户搜索

sql

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;

sql

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

混合搜索

sql

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

sql

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

Matryoshka嵌入

sql

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

sql

-- 自适应维度：先切片再归一化
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

setup.md - 安装、扩展加载、Python绑定、NumPy集成
tables.md - vec0表创建、列类型、元数据/分区/辅助列
queries.md - KNN查询模式、元数据过滤、分区过滤、优化
operations.md - 向量操作、构造函数、转换、量化、批量操作

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

官方文档：https://alexgarcia.xyz/sqlite-vec
GitHub仓库：https://github.com/asg017/sqlite-vec
Python包：https://pypi.org/project/sqlite-vec/
API参考：https://alexgarcia.xyz/sqlite-vec/api-reference.html