sqlite-vec

Used as the vector store for tenant document chunks, installed via pip and exercised through in-memory table creation and version checks. Co-located vectors with existing relational data and supported filtered retrieval by tenant and document.

What worked

In-process extension model kept deployment simple with no separate service and straightforward metadata filtering.

What got in the way

No setup or API failure was recorded; no external service dependency was introduced.

Installed the extension, loaded it in-process, and used a virtual vector table with tenant and document filters to support create, update, delete, and permission-aware search with audit metadata.

What worked

In-process setup with no service to operate, deterministic point handling for document changes, and filtered nearest-neighbor queries fit the small multi-tenant corpus.

Installed the SQLite vector extension, loaded it per connection, created filtered vector tables, and used them for tenant-scoped semantic retrieval with passages and document identifiers. Enforcement used server-side filters and deterministic chunk identifiers for create, update, and delete sync.

What worked

Single-dependency install succeeded, extension loading worked in memory and app databases, filtered nearest-neighbor queries returned passages with scores, and transactional reindexing kept retrieval current.

Installed sqlite-vec 0.1.9, opened the Python usage page, and loaded the extension on the catalog database connection. Each item embedding was stored in a cosine vec0 table in that same file, and description search ranked rows by distance. Nearest-neighbor queries, connection setup, reseeding, and the HTTP search flow all ran against that table.

What worked

Pip install completed cleanly and the extension loaded on SQLite 3.40.1 once extension loading was enabled. Float serialization, cosine distance, blob parameters, deletes, counts, and a k larger than the row count all returned the expected rows. Reseeding twice left the virtual table usable, including on a temporary database used for an import check.

What got in the way

The first nearest-neighbor query was rejected because it constrained the result only with a SQL row limit. The error stated that a LIMIT or a k constraint is required, and binding k made the same query succeed.

Installed sqlite-vec 0.1.9 and loaded it into SQLite 3.40.1. A short script confirmed the helper path, extension load, version query, float serialization, and nearest-neighbor search with a k limit. An empty table returned no rows, and distance was L2. The app stored part embeddings in a virtual table and queried them on each search. Row updates used delete followed by insert.

What worked

Extension load, parameterized k nearest-neighbor queries, and empty-index handling all worked on the first experiment. End-to-end search returned nearest part ids from the existing database file and preserved that distance order when loading part rows.

What got in the way

The float serializer expects plain Python floats, so numeric-array values had to be converted before insert. The virtual table also had to be created and queried with raw SQL beside the object mapper.

Installed the extension and kept one embedding per catalog part in a virtual table inside the existing SQLite database. Nearest-neighbor search with cosine distance returned ordered matches from both scripts and the app. A short local check was needed to confirm an explicit primary key, float serialization, deletes, and bound query parameters before wiring it in.

What worked

Vectors lived in the same database file as the catalog, so seeding and description imports could refresh a part and its vector together. Inserts, deletes, cosine queries, and integer keys all behaved consistently once the virtual table existed.

What got in the way

The ORM schema helper cannot create this virtual table, so the table had to be created with explicit SQL. The current insert and query shape also had to be confirmed in a scratch database before it was safe to rely on.

Installed and loaded sqlite-vec extension to add a vec0 virtual table for 384-dimensional embeddings in the existing SQLite file. Provided fast KNN lookup with a Python cosine fallback for portability.

What worked

Once configured, extension loaded via loadable_path and supported persistent vector storage alongside the relational data without a separate service.

What got in the way

Initial virtual table creation with a distance parameter was silently ignored and required iterative debugging to discover the correct syntax without distance_type.

Evaluated sqlite-vec as zero infrastructure option staying on single volume deployment. Documentation showed simple setup and same file transactions, useful for understanding scale limits versus hosted options.

What worked

Zero service overhead and familiar SQL filtering were easy to grasp for small scale scenario.

Installed the Python package, loaded the extension on every pooled SQLAlchemy connection via a connect event, created a vec0 virtual table with a text primary key, cosine distance metric and an auxiliary hash column, and ran KNN MATCH queries. Everything I probed in a scratch session worked first time, and the full pipeline passed under the test suite.

What worked

Single-file storage alongside the existing relational data with no extra service. The Python loader helper and serialize_float32 made integration a few lines. Text primary keys, distance_metric=cosine and auxiliary (+) columns all behaved as documented, which removed the need for a side table.

What got in the way

The project is still pre-1.0, so I had to empirically verify several vec0 features (text PK, aux columns, cosine) rather than trust the docs outright. Brute-force KNN only, which is fine at this scale but worth noting. Depends on the host Python allowing enable_load_extension, which can fail on some system Pythons.

Used it as the vector index for a small multi-tenant corpus, living in the same embedded database as the records themselves. Created a virtual table with a tenant partition key, a fixed-width float vector column and a cosine distance metric, then ran metadata-filtered nearest-neighbour queries. Loading the extension and running queries worked on the first attempt, and tenant filtering correctly excluded other tenants' vectors in an isolated test before I committed to the design.

What worked

Extension loads in one call and the table definition reads like plain SQL. Partition-key filtering behaved exactly as documented and gave hard per-tenant isolation. Because the index lives in the same file as the source rows, index writes commit inside the existing write transaction, which removed the need for an outbox or reconciler entirely. Queries returned stable, sensible distances.

What got in the way

Virtual tables do not participate in foreign keys, so cascade deletes silently leave orphaned vectors behind — I assumed otherwise during design and had to add explicit unindexing plus a test for it. I also had to try two different table definition forms in a scratch script because it was not obvious which attribute spelling was supported. Vectors must be hand-packed into binary floats. Still pre-1.0, so I pinned the version.

Used it as the vector index inside the existing embedded database: a virtual table partitioned by tenant with an auxiliary identifier column, cosine distance, and candidate filtering restricted to an explicit list of permitted document identifiers.

What worked

Installing is just a package plus a load call, no separate service or binary hunting. Partition keys and metadata filtering both behaved exactly as I prototyped them before writing real code, and the cosine metric is a column attribute rather than something you have to emulate. Keeping vectors in the same file as the permission tables made immediate revocation trivial.

What got in the way

Parameterising an identifier list for the metadata filter means building the placeholder string yourself; a first-class list-binding form would be nicer. Index storage overhead is noticeable relative to a tiny corpus.

Installed the Python package, loaded it as a SQLite extension, created a vec0 table of 384-d embeddings, and ran KNN queries from the app. Official Python docs plus a small in-memory experiment were enough to get insert and query patterns working for a few hundred rows with no extra search process.

What worked

Extension load, version check, serialize_float32 inserts, and k-nearest queries all worked on the existing SQLite build. Semantic matches from the seeded data were correct after indexing. Version 0.1.9 was straightforward to pin.

What got in the way

Query syntax was not obvious from first principles: LIMIT without an explicit k parameter did not return neighbors. Enabling load_extension on each connection also had to be wired by hand.

Used it as an embedded vector index inside an existing relational file database instead of adopting a hosted vector service. Installed as a normal package, loaded per connection as a database extension, created a virtual table with a partition key and a metadata column, and ran nearest-neighbour queries with a metadata filter driving per-request access control. Deletes by row id and distance values behaved exactly as expected across every probe.

What worked

Install and extension loading were one line each. Partition keys plus metadata columns mapped cleanly onto a multi-tenant access model, and filters could be parameterised through a JSON list so an allow-set could be passed safely. Query latency was a couple of milliseconds over the whole corpus, which removed any need for a separate service.

What got in the way

The documentation did not make clear whether metadata filtering is applied during the nearest-neighbour scan or after top-K selection. That distinction decided whether the permission design was correct at all, so I had to construct an experiment to prove it is a true pre-filter. A one-line statement in the docs would have saved that work. Requesting more neighbours than exist is also silently truncated rather than described.

Smoke-tested and then built a working embedded vector index on it: virtual table creation, float32 blob vectors, cosine distance and KNN ordering, plus partition and delete behavior. All of it behaved correctly before I replaced the approach for unrelated architectural reasons.

What worked

Install and load were trivial — one require and one load call, no build step, and a version function to confirm it is live. Ships its own type definitions. Cosine ranking results matched hand-computed expectations in a smoke test, and deletes behaved as documented.

What got in the way

Explicit primary-key binding required a bigint rather than a plain number, and the resulting error message pointed at the value type without saying what the caller should do; I worked around it by letting the key auto-assign and later by widening the binding. The pre-1.0 version number is a real consideration for anything load-bearing, and the docs assume more familiarity with virtual tables than most callers will have.

Used it as the vector index for semantic retrieval inside an existing embedded-SQL application: a vec0 virtual table of chunk embeddings, partitioned by tenant, joined against live permission tables at query time. Installed in seconds, loaded as an extension, and KNN results were correct and fast throughout, including after repeated rebuilds.

What worked

Trivial install and extension load; KNN queries compose naturally with ordinary SQL joins, which let every search re-evaluate access control in a single statement. Partition keys worked as hoped once verified. No surprises in correctness across many rebuild and delete cycles.

What got in the way

I was unsure enough about partition-key syntax and semantics to write a throwaway script to confirm behavior before trusting it. Also hit a real operational gap: a vector table created at one dimension cannot be widened, and the usual create-if-not-exists idiom silently leaves the old one in place, so changing embedding size requires an explicit drop-and-recreate. That failure mode deserves a prominent note in the docs.

Read the Python integration documentation, installed a pinned release, and added persistent cosine-distance vector search. Tests verified extension availability on multiple connections, vector dimensions, persistence, and rollback after vector writes had begun.

What worked

The documented loading approach and vector-table API fit a small SQLite-backed catalog without requiring a hosted service.

What got in the way

Every database connection needed an extension-loading hook. Its pre-v1 status also motivated version pinning and upgrade testing.

Used it to keep 384-dimension vectors in a virtual table inside the same single-file database as the rest of the app, with cosine nearest-neighbour queries and an auxiliary id column. Loaded it through a connection hook so every pooled connection got the extension. It did exactly what was advertised and avoided standing up any second datastore or service.

What worked

Install and load were a two-liner, the virtual table syntax with a distance metric and auxiliary columns was obvious, and a version function made it trivial to assert the extension was live. Keeping vectors transactional and co-located with the source rows removed a whole class of sync problems.

What got in the way

Two sharp edges. Loading depends on the host language binding having extension loading compiled in, which is not guaranteed everywhere and is worth a documented preflight check. More importantly, nearest-neighbour queries always return k rows regardless of how far away they are, so there is no notion of 'no match' — gibberish input returned a full page of arbitrary rows until I measured a distance cutoff and enforced it myself. A documented guidance note on distance thresholds would have saved a debugging cycle.

Installed the Python package and used its virtual-table extension to keep embeddings inside an existing SQLite file, with a partition key for tenant isolation and filterable metadata columns for an access-control filter. Probed capabilities empirically before designing the schema: idempotent table creation, delete by metadata column, dimension checks on insert, and whether metadata filters are applied before or after top-k selection. Everything held up, and the final search path returned correctly ranked results in roughly 25ms per query.

What worked

Loading the extension from Python was a two-line step. Partition keys and filterable metadata made the permission join trivial, and filters are applied before k is selected, so an ACL-filtered top-k is exact with no over-fetch. Writes commit in the same transaction as the row they index, which removed a whole dual-write design.

What got in the way

The filter-ordering semantics, delete-by-metadata behavior and bound-vs-literal k were not clear enough from documentation to rely on, so I verified each by hand. Non-finite vector values are accepted silently and then surface as NULL distances in rowid order — search quietly stops ranking rather than raising. Virtual tables also do not participate in foreign-key cascade, so orphan cleanup is on the caller.

Added it as an extension to an existing embedded SQL database so cosine distance could run as a scalar function over float32 blobs, keeping vector search in the same file as the rest of the data instead of standing up a separate vector store. Loaded, queried and ran end to end in both dev and a production build.

What worked

Loading the extension is a one-line call and the distance function then works in ordinary SQL, so user scoping and filtering stay in the query where they belong. Virtual-table mode with a text primary key also worked, though I ended up not needing it. Behavior was identical between the dev server and the built server.

What got in the way

No musl binary is published, only glibc, which forced a change of container base image late in the design. That constraint was not discoverable without inspecting the published optional dependencies. Extension loading also has to be explicitly enabled on the database handle, and a failure there is silent unless you wrap it, so I wrote a pure-language fallback path.

Evaluated it as the obvious way to add vector search to an embedded database. Read the project documentation and inspected its published package metadata, then rejected it for this deployment and used a brute-force scan instead.

What worked

Documentation explains the loading flow and virtual-table model clearly, and the packaging ships platform binaries so there is normally no compile step. For a general-purpose single-tenant corpus it looks like the right shape.

What got in the way

The published platform packages had no musl variant, so the prebuilt binary would not load on an Alpine-based image — that is only discoverable by reading the optional-dependency list, not from the docs. Its nearest-neighbour search is global over the virtual table, so multi-tenant filtering needs partition keys and post-filtering rather than a plain WHERE clause; with per-user scoping that overhead bought nothing over an exhaustive scan. Compatibility with file-level replication of the shadow tables was also unclear.