Search documents by meaning

Ask a question in plain language and get the closest documents back — even when they share no keywords with it. Install dirsql-plugin-embeddings and semantic search becomes plain SQL: the plugin's embed() function turns text into vectors and loads sqlite-vec, which does the distance math. No API keys, no services — the model runs locally.

Suppose short notes live in notes/*.md:

notes/pasta.md      # boiling spaghetti, olive oil, garlic
notes/branches.md   # git feature branches and pull requests
notes/tomatoes.md   # planting tomato seedlings after the last frost

The one-liner

The plugin package is also its own command. Give it a corpus glob and a question, and it prints the closest paths, ranked by distance:

uvx dirsql-plugin-embeddings 'notes/*.md' "how do I cook pasta?" -k 3

• The corpus glob is required, and always first: the plugin never picks a default corpus, so you always say exactly which files are in scope. A bare glob is fine — the command normalizes it to the ./-relative form the SQL layer requires.
• The question is the second positional.
• -k / --limit (both spellings, default 10) is the number of results — it is exactly the SQL LIMIT of the generated query; there is no other cutoff.
• --model <id> switches the embedding model (model story).

The first-ever run downloads the model (on the order of a hundred megabytes, with progress on stderr); after that it loads from the local cache. Results print one path<TAB>distance line per match, closest first.

Which shape

The one-liner embeds every matched file on every run. That is the right trade for a question you ask once, and the wrong one for a corpus you search repeatedly — where a stored vec0 index embeds each file once, at ingest, and a query embeds only the question:

	This page: path-table	Vector index
Setup	none	a [[table]] with a ddl batch
Freshness	always current — the walk is the read	watcher-maintained; survives restarts under --persist
Per query	one embed() round trip per matched file, plus a walk that reads every file's content	one embed() round trip total, plus an in-process KNN scan
Top-k	ORDER BY … LIMIT k	MATCH … AND k = …
Good for	a one-off question, a small corpus, an ad-hoc glob	a corpus you query repeatedly

The index only pays off when the table outlives the query — under --persist, or inside a long-running dirsql server. A one-shot dirsql query against an ephemeral index rebuilds the table, and therefore re-embeds the corpus, before it answers; that is strictly more work than the subquery below. The full recipe — the width probe, the ddl batch, both triggers, and what a model-id edit costs — is Add a search index to a table.

The rest of this page is the zero-setup shape.

The SQL behind the one-liner

The one-liner generates and runs ordinary dirsql SQL, and you can write it yourself when you want more than ranked paths — a different projection, a join, a WHERE clause, a subset of a JSON file's content:

uvx --with dirsql-plugin-embeddings dirsql "
  SELECT path,
         vec_distance_cosine(emb, embed('how do I cook pasta?')) AS distance
  FROM (SELECT path, embed(content) AS emb FROM './notes/*.md')
  WHERE emb IS NOT NULL
  ORDER BY distance
  LIMIT 3"

[{"path":"notes/pasta.md","distance":0.315},{"path":"notes/tomatoes.md","distance":0.881},{"path":"notes/branches.md","distance":0.92}]

Neither "cook" nor any other keyword needs to appear in pasta.md — the distance ranking is doing the work.

Reading the query inside-out:

1. The subquery scans the path-table'./notes/*.md' and embeds each file's content — only the files the glob matches are ever read or embedded. In hand-written SQL the ./ prefix is required; only the one-liner normalizes a bare glob for you.
2. embed('how do I cook pasta?') embeds the question once (the function is deterministic, so SQLite reuses the value across rows).
3. WHERE emb IS NOT NULL drops the files that could not be embedded. A file that is unreadable or not valid UTF-8 has NULL content, so its embedding and its distance are NULL too — and SQLite sorts NULLs first ascending, so without this line the unrankable files take the top-k slots.
4. vec_distance_cosine(...) computes cosine distance between the two vectors; ORDER BY distance LIMIT 3 keeps the three nearest. There is no vec0 table here, so sqlite-vec's MATCH … AND k = … does not apply — for a plain expression, ORDER BY … LIMIT k is its documented top-k.

Structured files compose with SQL's JSON operators — embed one field instead of the whole file:

SELECT path
FROM (SELECT path, embed(content ->> 'abstract') AS emb
      FROM './papers/**/metadata.json')
WHERE emb IS NOT NULL
ORDER BY vec_distance_cosine(emb, embed('local private models'))
LIMIT 10

The same projection works as an on-file hook feeding the indexed shape: parse the field in the hook, store it as a column, and the trigger embeds it once instead of on every query.

Repeat runs are cheap

Computed vectors are cached on disk, keyed on content and model (vector cache) — re-running a search over unchanged files skips the model entirely and re-embeds only what changed. That takes the inference out of the shape above, but not the walk or the per-file round trip; only a stored index removes those. And the plugin costs nothing when idle: a query that never calls embed() spawns no worker and loads no model (zero cost when unused).

How embed() gets into SQL

The plugin ships a config fragment declaring embed() via [[dirsql.function]], which the uvx/pip launcher discovers automatically. The same mechanism is open to your own configs and plugins — any external command that speaks the worker protocol can back a SQL function. To build one, see Write a plugin.