The database can do that part

Two features this time, and they turn out to be the same idea wearing different clothes: something you were doing by hand, the database can just do.

The chore nobody wants

If you've built anything with vector search, you know the shape of it. You add an embedding column. You call an embeddings API when you insert a row. Then you remember that rows also get updated, so you hook that too. Then you discover the provider was down for four minutes last Tuesday and eleven rows have stale vectors, so you write a reconciliation job. Then the reconciliation job needs retry logic. Then the retry logic needs a dead-letter list, because one row has input the provider will never accept and you're paying to retry it every five minutes forever.

None of that is your application. It's plumbing that leaked upward because the database wouldn't do it.

Now it will:

CREATE EMBEDDING INDEX body_ix ON articles(body) INTO embedding
    USING MODEL 'text-embedding-3-small';

And that's the feature. Write to body; the embedding column fills in.

INSERT INTO articles (body) VALUES ('data protection and personal privacy law');
-- no second statement, no API call, no background worker of yours

Edit the text later and it re-embeds, because it notices. Change nothing and it costs nothing.

How it knows

The obvious design is a hook on INSERT and UPDATE: catch the write, queue the row. We didn't do that, and the reason is worth a paragraph.

A hook only sees writes that go through the hook. It doesn't see a bulk load. It doesn't see a restore from backup. It doesn't see binlog replay, or a replica applying changes from its primary. Every one of those leaves your embeddings quietly out of date, and quietly is the bad part.

So instead the sweep works it out from the data itself. Each row's vector is recorded alongside a hash of the (model, text) that produced it. Anything where those disagree needs work. That's it — and it's true no matter how the row got there.

It also means the feature costs nothing on the write path, which matters when the write path is the thing you tuned.

The hash is SHA-256 rather than Rust's default hasher, for a reason that only shows up later: that value is stored. DefaultHasher makes no promise of stability across compiler versions, and a hash that quietly shifted under a toolchain upgrade would re-embed every row in your database. That's an expensive way to change nothing.

Writing the vector back is transactional, too. An embedding call takes a real second, and a row can be edited inside that window. The sweep only stores its answer if the row is still exactly as it read it — so your edit is never overwritten by a stale copy that was in flight. The row just stays pending and gets picked up next time.

When the provider misbehaves

It will. Failures back off — one second, two, four, eight, sixteen — and after five tries a row is set aside rather than retried forever. Not forgotten, though:

+---------+----------+--------+-----------+------------+-----------+----------+--------+
| Name    | Table    | Source | Target    | Model      | Dimension | Retrying | Failed |
+---------+----------+--------+-----------+------------+-----------+----------+--------+
| body_ix | articles | body   | embedding | all-minilm |       384 |        0 |      2 |
+---------+----------+--------+-----------+------------+-----------+----------+--------+

Fix the text and the hash changes, which clears the state and puts the row back in the queue. That's the natural way to un-stick one, and it falls out of the design rather than being bolted on.

And a row waiting for its vector is still findable. HYBRID() fuses a full-text ranking with a vector ranking over the union of both, so a just-inserted row still matches on its words and gains the semantic half when the sweep reaches it. Search degrades instead of hiding the row — which matters, because a row that silently doesn't appear in results is indistinguishable from data loss.

The bug that ranked a cat above a qubit

Here's the part we're glad we checked.

Every unit test for this feature injects a fake embedder — fast, deterministic, no network. Twenty of them, all green. Then we pointed it at a real Ollama running all-minilm, loaded five documents, and asked a question about quantum physics.

It returned, in order: a cat on a windowsill, some sourdough bread, and a privacy law. The document about superconducting qubits didn't make the top three.

The vectors were there. The column was full. But ElyraSQL's HNSW index only rebuilds when a table's write counter moves, and the sweep was writing rows straight to storage without advancing it. So every vector the feature produced was invisible to vector search — which is, of course, the entire point of producing them. The column filled in beautifully and search kept answering from an index that had never seen any of it.

No unit test could have caught it. They all assert the vector reaches the row, and it did. The defect lived in the step after.

The fix advances the counter in the same transaction as the row write, so it's atomic and two concurrent sweeps conflict rather than losing one. Afterwards, three queries that share no words at all with their target:

┌────────────────────────────────────┬─────────────────────────────────────────────────────┐
│               Query                │                       Top hit                       │
├────────────────────────────────────┼─────────────────────────────────────────────────────┤
│ rules for handling citizen records │ GDPR compliance for processing personal information │
├────────────────────────────────────┼─────────────────────────────────────────────────────┤
│ superposition of trapped ions      │ quantum entanglement in superconducting qubits      │
├────────────────────────────────────┼─────────────────────────────────────────────────────┤
│ baking with wild yeast             │ sourdough bread needs a long slow rise              │
└────────────────────────────────────┴─────────────────────────────────────────────────────┘

There's a lesson in there that isn't about embeddings. Mocks test the thing you built; only the real thing tests the thing you assumed.

Handing the keys to an agent

The other feature is smaller and took an afternoon:

elyrasql mcp --data app.edb

That's a Model Context Protocol server, speaking JSON-RPC over stdin and stdout. Point an AI agent at it and the agent gets three tools — list_tables, describe_table, query — with no server running, no port open and no driver installed. Register it the usual way:

{
  "mcpServers": {
    "app-database": {
      "command": "elyrasql",
      "args": ["mcp", "--data", "/srv/app/app.edb"]
    }
  }
}

Database MCP servers aren't novel. What we spent our thinking on was the part that usually gets waved through: what is the agent allowed to do?

It's read-only by default, and refusals come from the engine's own privilege check — not from scanning the SQL for scary keywords, which is a game you lose eventually. --allow-writes raises the session to Write, deliberately not Admin:

┌─────────────────────────────┬─────────┬────────────────┐
│          Statement          │ default │ --allow-writes │
├─────────────────────────────┼─────────┼────────────────┤
│ SELECT                      │   ✅    │       ✅       │
├─────────────────────────────┼─────────┼────────────────┤
│ INSERT / UPDATE / DELETE    │   ❌    │       ✅       │
├─────────────────────────────┼─────────┼────────────────┤
│ CREATE / ALTER / DROP TABLE │   ❌    │       ❌       │
├─────────────────────────────┼─────────┼────────────────┤
│ GRANT / CREATE USER         │   ❌    │       ❌       │
└─────────────────────────────┴─────────┴────────────────┘

An agent that can be talked into deleting rows should not also be able to drop the table they lived in.

We wrote that table by running the statements, not by reading the privilege code — and it came out better than the comment we'd drafted. We'd written that such an agent "at least" couldn't reach the grant table. Turns out DDL is out of reach too.

Two smaller choices, since they're the kind that quietly matter. Results come back as JSON, with DECIMAL rendered as a string — "24.50", not 24.5 — because a model reading prices shouldn't have the scale washed out through a float. And truncation always announces itself: 200 rows by default, with a truncated: true and a note. A model that can't tell a partial answer from a complete one will confidently reason from the partial one.

One more thing we caught before it was a bug: tracing_subscriber writes to stdout by default. On an stdio protocol, the first log line would have corrupted the stream. The CLI now parses arguments before setting up logging, so this subcommand can send its logs to stderr where they belong.

Sixty-five

A smaller fix, found by accident and worth telling because of how it was found.

A throwaway script built one SELECT COUNT(*) branch per table to snapshot a database. It worked fine on a database with 25 tables. A month later the database had 90, and it stopped:

ERROR 1105 (HY000): query error: query nesting exceeds 64 levels

Set operations parse left-associatively, so A UNION B UNION C is really SetOp(SetOp(A, B), C). Execution walked into that structure, spending one level of recursion per branch — and hit a guard at 64. MySQL 8.4 runs a thousand branches without blinking, and generated SQL produces those all the time.

A UNION chain is now gathered as a flat list first. Uniform chains only: the walk stops at any branch whose operator or quantifier differs, because (A UNION B) UNION ALL C genuinely isn't the same as a three-way anything, and INTERSECT/EXCEPT aren't associative the way UNION is. Those keep the path they had.

Flattening alone wasn't enough, and the measurement is the interesting bit. With execution no longer recursing, the server started dying at 5,000 branches instead — and the culprit was in the same function, cloning the entire chain once per branch:

┌────────────────────────────────┬──────────┐
│ Operation on an N-branch chain │ Survives │
├────────────────────────────────┼──────────┤
│ parse + drop                   │ 10,000   │
├────────────────────────────────┼──────────┤
│ parse + clone                  │ 3,000    │
└────────────────────────────────┴──────────┘

Cloning was the binding constraint by a factor of three. Branch queries are now built field by field and never copy the chain at all. The result: 2,000 branches, up from 64, and a 1.1 MB fifty-thousand-branch statement returns a clean error with the process still standing.

Fifteen set-operation shapes were compared against real MySQL 8.4 before and after — mixed operators, mixed quantifiers, parenthesised sub-chains, ORDER BY, NULLs, collation. All identical. That's what says the flattening preserved meaning, rather than us saying so.

We also tried wrapping the whole statement in a stack-growing shim and it didn't work, because it only checks once per poll and these are plain recursive walks. We left it out rather than ship a guard that guards nothing.

The quiet one

Last, the fix nobody will notice, which is the goal.

If you use ElyraSQL as a library, dropping a database handle now waits for the file to actually be released. It used to return while a background thread still held the lock, so reopening the same file a moment later was a race — which is precisely what a test suite does, over and over. It's deterministic now, and a held file reports as its own error kind instead of an opaque string you had to pattern-match.

Getting it

docker pull ghcr.io/kwhorne/elyrasql:1.11.0

No on-disk format change and no upgrade steps: a 1.9.x or 1.10.0 database opens in 1.11.0 unchanged, and a 1.11.0 database still opens in either.

One honest caveat. The pre-parse depth budget is now shared between expression depth and set-operation branches, because both really do deepen the same tree. A statement combining thousands of operators and thousands of UNION branches can be refused where only one of the two used to count. It's the single shape this release accepts less of than the last one, and we'd rather you read it here than find it.

511 tests, green, and the whole thing measured against MySQL 8.4 on every pull request.

Go let the database do that part.