Vector search

c3 can store embeddings and rank rows by how close they are to a query vector, in plain SQL. The vector column type, the distance functions and an approximate nearest-neighbour index are built into the database engine, so there is no extension to load and nothing to set up beyond your schema.

c3 doesn't make the embeddings. Your Cell gets them from a model provider (OpenAI, Voyage, Cohere, a model you host) with a fetch, keeps the API key in a vault binding, and stores the numbers it gets back.

A vector column

Declare the column as F32_BLOB(n), where n is the number of dimensions your embedding model returns:

await env.DB.exec(`
  CREATE TABLE IF NOT EXISTS docs (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    body      TEXT NOT NULL,
    embedding F32_BLOB(1536)
  )
`);

Each value is stored as n 32-bit floats, so a 1536-dimension embedding takes 6 KiB per row. Pick n to match the model: text-embedding-3-small returns 1536, many open models return 384 or 768. Inserting a vector of the wrong length into an indexed column fails with dimensions are different, and comparing two vectors of different lengths fails with vectors must have the same length.

Inserting vectors

Wrap the parameter in vector32(?) and bind the embedding as a plain JavaScript array. Parameters travel as JSON, so the array arrives as the text [0.012,-0.034,...], which is the format vector32() reads:

const embedding = await embed(env, `${title}\n\n${body}`);   // number[]

await env.DB.prepare(
  "INSERT INTO docs (title, body, embedding) VALUES (?, ?, vector32(?))"
).bind(title, body, embedding).run();

JSON.stringify(embedding) works the same way if you'd rather be explicit. A Float32Array does not: it serializes as an object ({"0":0.012,...}) and the insert fails with vector: must start with '['. Convert it first with Array.from(floats).

To load many rows, send them in one batch(). It's a single round trip and a single transaction:

await env.DB.batch(items.map((item, i) =>
  env.DB.prepare(
    "INSERT INTO docs (title, body, embedding) VALUES (?, ?, vector32(?))"
  ).bind(item.title, item.body, embeddings[i])
));

Most providers embed a list of inputs in one request, so embed the whole batch at once too.

Searching

The simplest search compares the query vector against every row and sorts by distance. Smaller is closer:

const q = await embed(env, searchText);

const { results } = await env.DB.prepare(`
  SELECT id, title, vector_distance_cos(embedding, vector32(?)) AS distance
  FROM docs
  WHERE embedding IS NOT NULL
  ORDER BY distance
  LIMIT 10
`).bind(q).all();

Keep the WHERE embedding IS NOT NULL. A row with no embedding yet makes the whole query fail with unexpected value type: got NULL, rather than sorting to the end.

Because it's ordinary SQL, filters go in the same WHERE clause and apply before the ranking:

const { results } = await env.DB.prepare(`
  SELECT id, title, vector_distance_cos(embedding, vector32(?)) AS distance
  FROM docs
  WHERE embedding IS NOT NULL AND account_id = ? AND created_at >= ?
  ORDER BY distance
  LIMIT 10
`).bind(q, accountId, since).all();

A full scan is exact, and it's fast at the sizes most Cells hold. Scanning 10,000 rows of 1536 dimensions takes a few tens of milliseconds, and the time grows in proportion to rows × dimensions. Check meta.duration_ms on the result to see what your own query costs.

Reading a vector back

Selecting the column directly returns the raw bytes as an array of numbers, which is rarely what you want. vector_extract returns it as text:

const row = await env.DB.prepare(
  "SELECT vector_extract(embedding) AS embedding FROM docs WHERE id = ?"
).bind(id).first();
const vector = JSON.parse(row.embedding);   // number[]

Functions

Use cosine distance unless your embedding model's documentation says otherwise.

The approximate index

For tables too large to scan on every query, c3 can keep an approximate nearest-neighbour index on a vector column. Queries through it read a small part of the table instead of all of it. The trade-offs are real, so start with the scan above and add the index when meta.duration_ms tells you to.

Create it on an empty table

await env.DB.exec(`
  CREATE INDEX IF NOT EXISTS docs_embedding_idx
  ON docs (libsql_vector_idx(embedding, 'compress_neighbors=float8', 'max_neighbors=16'))
`);

Create the index in your schema setup, before any rows go in. From then on every insert, update and delete keeps it current.

Building the index over rows that are already in the table is slow, and a statement that runs past c3's 30-second limit is cancelled. 2,000 rows of 1536 dimensions is already enough to hit it. See adding an index to a full table below.

Querying through it

vector_top_k(index, query, k) returns the rowids of the k nearest rows. Join it back to the table to get the columns:

const { results } = await env.DB.prepare(`
  SELECT d.id, d.title
  FROM vector_top_k('docs_embedding_idx', vector32(?), 10) AS t
  JOIN docs d ON d.rowid = t.id
`).bind(q).all();

The results come back nearest first. If you also want the distance, compute it in the same query: vector_distance_cos(d.embedding, vector32(?1)) with the query bound once as ?1.

Two things differ from the scan:

• It's approximate. The index can miss some of the true nearest rows, so the ten it returns may not be exactly the ten a full scan would return.
• Filters apply after the index. A WHERE on the joined table drops rows from the k the index already picked, so asking for 10 and filtering can leave you with 3. Ask for more than you need and add a LIMIT, or use the full scan when the filter narrows the table a lot.

Options

Options are extra string arguments to libsql_vector_idx:

The index stores copies of neighbouring vectors for every row, and with the defaults it is far larger than the table itself. In our test, 2,000 rows of 1536 dimensions took 16 MB with no index, 82 MB with the options shown above, and over 600 MB with the defaults, and inserts were around eight times slower with the defaults too. Pass compress_neighbors=float8 and a max_neighbors value unless you have measured a reason not to.

An unknown option fails at CREATE INDEX time with invalid vector index parameter.

Adding an index to a full table

Create a new table with the index already on it, copy the rows across in chunks small enough to finish well inside 30 seconds, then swap the names:

await env.DB.exec(`
  CREATE TABLE docs_new (id INTEGER PRIMARY KEY, title TEXT NOT NULL, body TEXT NOT NULL, embedding F32_BLOB(1536));
  CREATE INDEX docs_new_embedding_idx
    ON docs_new (libsql_vector_idx(embedding, 'compress_neighbors=float8', 'max_neighbors=16'));
`);

let last = 0;
for (;;) {
  const { meta } = await env.DB.prepare(`
    INSERT INTO docs_new SELECT * FROM docs WHERE id > ? ORDER BY id LIMIT 200
  `).bind(last).run();
  if (meta.rows_affected === 0) break;
  last = meta.last_insert_rowid;
}

Run the copy from a script or one request at a time rather than one long Cell invocation. Once it finishes, DROP TABLE docs and ALTER TABLE docs_new RENAME TO docs. The index keeps its name, so queries use vector_top_k('docs_new_embedding_idx', ...) after the swap.

If a CREATE INDEX on a full table does run out of time, c3 removes the half-built index, and the error says the index was not created. Nothing is left behind, so use the copy above and the same name works.

Scan or index

For tens of thousands of rows, the scan is usually the right answer.

Moving the data elsewhere

The vector columns and functions are part of libSQL, the SQLite fork c3 runs on. A database snapshot from an explant opens in the stock sqlite3 tool and ordinary columns read normally, but the vector functions aren't there, and a table with a vector index can't be written to: inserts fail with unknown function: libsql_vector_idx(). Drop the vector index before taking the file to plain SQLite, or open it with a libSQL build.

A complete Cell

js-vec-search in tissue-examples is a working semantic search Cell: it embeds documents with OpenAI, stores them in c3 and serves a search page. For keyword search instead, see full-text search with FTS5.

See also

• c3 API reference: prepare, bind, batch
• c3 limitations: statement timeout, result caps
• ribo.toml reference: c3 and vault bindings