duckdb-mcp-ocarina

A working environment that tests a DuckDB database through its MCP server, with no LLM. Clone it, run one command, and watch deterministic data checks run against a real server.

The database is driven entirely from YAML scripts called rondos, using Ocarina. Each rondo runs SQL through the DuckDB MCP server, captures values out of the JSON results, and asserts on them. The data lives in this repo as CSV files, so a fresh clone runs the same way on any machine, with no signup and no API key.

Prerequisites

• Ocarina: go install github.com/msradam/ocarina@latest
• uv for uvx: curl -LsSf https://astral.sh/uv/install.sh | sh

uvx fetches the DuckDB MCP server on first run. There is nothing else to install.

Quick start

git clone https://github.com/msradam/duckdb-mcp-ocarina
cd duckdb-mcp-ocarina
make test

make test runs every rondo in order. Each exits non-zero if an assertion fails, so the same commands work as a CI gate. Run them one at a time with make smoke, make audit, make migrate, make regression, or directly:

ocarina play rondos/audit.yaml

What each rondo does

Rondo	What it proves
rondos/smoke.yaml	The server starts, answers a query, and writes survive across steps on one connection. The 30-second "is it alive" gate.
rondos/audit.yaml	Data integrity over the shipped CSVs: primary keys are unique, no required field is null, and every order points at a real customer.
rondos/migrate.yaml	A schema migration preserves every row and fully populates the new derived column.
rondos/regression.yaml	Business metrics (order count, distinct customers, paid revenue, refunds) match pinned golden values. Drift fails the build.
rondos/transactional.yaml	block / rescue / always: stage rows, validate them, and always drop the staging table, so the teardown runs even when a step fails.
rondos/report.yaml	Served as a composite MCP tool. params: become the tool input, several queries run, and one summary line comes back.

The data is in data/customers.csv and data/orders.csv. DuckDB reads them in place with read_csv_auto, so the checks run against the files exactly as committed.

All four rondos share one server, declared once in .mcp.json and referenced by name as server: duckdb. Ocarina reads .mcp.json from the working directory, so the rondos stay short and the connection details live in one place.

Serve a composite tool

rondos/report.yaml is not played, it is served. ocarina serve exposes the whole rondo as a single MCP tool named sales_summary. The rondo's params: become the tool input schema, the body runs three queries (load the orders, load the customers, aggregate), and return: names the line handed back. It is a stored procedure for the CSVs in this repo: an agent calls one tool instead of issuing the queries itself.

ocarina serve rondos/report.yaml
# call it, here with ocarina itself as the client:
ocarina hum ocarina serve rondos/report.yaml -- sales_summary status=paid

Transactions and teardown

rondos/transactional.yaml uses block:, rescue:, and always:, the same error handling as an Ansible playbook. The block stages rows and validates them, rescue: runs only on failure, and always: drops the staging table whether the block passed or failed. A clean run still exits 0, and the table never leaks.

ocarina play rondos/transactional.yaml

Make it fail

The tests catch real problems, not just pass on clean data. data/orders_broken.csv has a duplicate primary key and an order whose customer does not exist. Point the audit at it and watch it fail:

sed 's/orders.csv/orders_broken.csv/' rondos/audit.yaml > /tmp/audit-broken.yaml
ocarina play /tmp/audit-broken.yaml
# the "order id is unique" and "every order points at a real customer"
# checks fail, and ocarina exits non-zero

In CI

.github/workflows/ci.yml installs Ocarina and uv, then plays every rondo on each push. A failing assertion fails the job. No database service, no credentials, no fixtures to host.

How it works

There is no model in the loop. A rondo is an ordered list of steps; each calls the execute_query tool with SQL, grabs a value out of the JSON result with a gjson path, and checks it with expect. Run twice, get the same result. See the Ocarina docs for the full rondo format.

License

MIT.