sqlite-vec-distance

English | 简体中文

A minimal SQLite extension: vec_distance(BLOB, BLOB) → REAL — a cosine distance UDF (1 - cosine_similarity, accumulated in double precision). 62 lines of C, zero third-party dependencies. Pairs with node:sqlite / better-sqlite3 for brute-force vector KNN.

Installation

npm install sqlite-vec-distance

Prebuilt binaries (darwin-arm64/x64, linux-x64/arm64) work out of the box — no compilation. Other platforms build from source with a single command, see Platform Compatibility.

SELECT id, vec_distance(embedding, ?) AS distance
FROM items
ORDER BY distance
LIMIT 10;

Why

• sqlite-vec is maintained in intermittent sprints, while most projects only use ~1% of its surface (two SQL statements for brute-force cosine KNN);
• The vec0 virtual table requires storing vectors in a separate copy; a regular table + UDF scans the column in your main table directly — half the storage, simpler schema;
• A brute-force scan takes ~10-20ms at the scale of tens of thousands of rows × 1024 dims, on par with vec0 KNN (which is also a linear scan internally);
• The extension only uses the stable sqlite3ext.h API, so the dylib is decoupled from the host SQLite version — prebuilt distribution is safe.

Usage

import { DatabaseSync } from 'node:sqlite';
import { loadVecDistance } from 'sqlite-vec-distance';

// node:sqlite: allowExtension must be set at construction time; it cannot be enabled later
const db = new DatabaseSync('app.db', { allowExtension: true });
loadVecDistance(db);

const rows = db.prepare(`
  SELECT id, vec_distance(embedding, ?) AS distance
  FROM items WHERE embedding IS NOT NULL
  ORDER BY distance LIMIT 10
`).all(queryBlob);

better-sqlite3 works the same way (db.loadExtension(path)) — loadVecDistance(db) drops in directly.

Utilities (0.2.0+)

Zero-option, stateless, pure functions that complement the KNN pattern above:

import {
  f32ToBlob, blobToF32, f32ToBase64, base64ToF32, // Float32 ↔ BLOB/base64 (little-endian)
  txDb,             // SAVEPOINT transactions (nesting-safe; works with node:sqlite / better-sqlite3)
  checkVecDistance, // UDF availability probe → { ok, detail }
} from 'sqlite-vec-distance';

const blob = f32ToBlob(embedding);    // vector → BLOB (accepted directly by run())
const vec = blobToF32(row.embedding); // BLOB → Float32Array (Array.from if you need number[])
txDb(db, () => { /* RELEASE on success; ROLLBACK TO + rethrow on error */ });
if (!checkVecDistance(db).ok) { /* surface detail in your doctor output */ }

The node:sqlite type enhancement is opt-in via an explicit side-effect import: it types StatementSync.all/get return values in the better-sqlite3 assertion style (instead of unknown), replacing your project's hand-rolled node-sqlite.d.ts:

import 'sqlite-vec-distance/enhance-node-sqlite';

Integration Recipes

Copy these into a new project and get it right for free.

1. KNN SQL pattern (sort inside the database, never brute-force in memory):

-- Basic: cosine distance ascending, top-k
SELECT id, vec_distance(embedding, ?) AS dist
FROM items WHERE embedding IS NOT NULL
ORDER BY dist LIMIT ?;
-- score = 1 - dist (cosine distance → similarity, restoring descending order)
-- Max-pool variant (one owner, many vectors — keep the nearest):
SELECT ownerId, MIN(vec_distance(embedding, ?)) AS dist FROM items
WHERE embedding IS NOT NULL GROUP BY ownerId ORDER BY dist LIMIT ?;

2. Connection boilerplate (node:sqlite):

import { DatabaseSync } from 'node:sqlite';
import { loadVecDistance } from 'sqlite-vec-distance';

const db = new DatabaseSync(path, { allowExtension: true }); // construction-time only
db.exec('PRAGMA journal_mode = DELETE'); // or WAL, per project
db.exec('PRAGMA foreign_keys = ON');
loadVecDistance(db);

3. vitest recipe: requires vitest ^4 (the vite bundled in 1.x crashes on node:sqlite); test.deps.external must include /^node:/ and this package:

export default defineConfig({
  test: { deps: { external: [/^node:/, 'sqlite-vec-distance'] } },
});

4. Gotchas:

• allowExtension can only be passed to new DatabaseSync() at construction time — it cannot be enabled afterwards;
• BLOBs returned by node:sqlite are plain Uint8Array (no Buffer methods like readFloatLE); always convert via this package's convert family (DataView-based, handles subarrays with non-zero byteOffset correctly);
• CJS projects require()-ing this package (ESM-only) need TS module: nodenext (node16 fails with TS1479) + Node 22.12+ (require(esm)).

Platform Compatibility

• Prebuilt platforms (darwin-arm64/x64, linux-x64/arm64, built by CI) work out of the box; prebuilds/<platform>-<arch>/ ships with the package;
• Platforms without prebuilds: run bash make.sh build-native from the package root to compile from source (requires cc); the C source and public-domain SQLite headers are fully vendored — clone and build;
• Node versions: node:sqlite requires Node ≥ 22.13 (22.5–22.12 need --experimental-sqlite); the better-sqlite3 path has no such requirement;
• Semantics & precision: self-distance 0, orthogonal 1, opposite 2; within 1e-6 of a manual float32 cosine; mismatched dimensions / byte length not a multiple of 4 throw.

Development

npm install                 # install dev dependencies
bash make.sh build-native   # compile the extension for the current platform
npm test                    # pretest recompiles, then runs all tests

License

MIT (C source and TS loader). The vendored sqlite3.h / sqlite3ext.h are official SQLite public-domain headers.