DuckDB connector¶
DuckDB is the default execution engine and needs nothing
installed in the default build: DuckDB's official library ships with the
download, sits next to the executable, and runs in process. (The
lean build is the exception — see Limitations.) One
connection is opened lazily per executor, results are decoded once as Arrow
RecordBatches (query_arrow) and rows are derived from those batches, so
DuckDB is Arrow-native end to end. Sessions are pinned to UTC
(SET GLOBAL TimeZone='UTC') so timestamps come back zone-less and comparable
with every other engine.
DuckDB is also the reference the other connectors are compared against, which
is why it is the fallback for dosi query --execute when no profile is named.
Gated by the exec-duckdb cargo feature, which is on by default — the lean
artifact is what a build without it looks like.
Connection profile¶
Omit uri: entirely for an in-memory database. execute_batch works there too —
state persists on the connection for the life of the executor.
A database file is opened read-only: dosi never writes to it. Other
processes can read the file at the same time (duckdb -readonly, a notebook,
another dosi), but none can open it for writing while dosi has it open, and
dosi cannot open a file another process holds for writing. The file must
exist; dosi does not create an empty one.
Parameters¶
| Key | Type | Required | Default | Notes |
|---|---|---|---|---|
type |
string | yes | — | duckdb |
uri |
string | no | in-memory | duckdb:////abs/path is absolute, duckdb:///rel/path is relative to the working directory, and a bare filesystem path is accepted as-is. |
default |
bool | no | false |
See connection profiles. |
Parsed and ignored on this connector: host, port, username,
password, database, schema, sslmode, sslrootcert, arrow_flight_port,
compat_mode. They are accepted so one file can describe several warehouses,
but the DuckDB executor never reads them.
The CLI's --db <file> is the same thing without a profile: it points
--execute at a DuckDB file directly.
Arrow-native results¶
DuckDB needs no configuration to stream Arrow — it is the native path, and the row shape is derived from the batches rather than the other way round. See Arrow for what that buys over the REST and CLI surfaces.
Limitations¶
None declared. DuckDB runs the whole corpus as the cross-engine reference, so where an expression is refused on other engines it is a limitation of that engine, not of the model.
The one caveat is the shape you downloaded, not a build-time detail: the lean
artifact has no libduckdb and shells out to a duckdb CLI on your PATH
instead (JSON mode, a fresh process per query). Consequences there:
- a
duckdbCLI must be installed —dosireportscannot start duckdb CLIotherwise; - the shell-out is verified against the DuckDB v1.4.x series, and
dosiwarns once if the CLI it finds is outside it. Results come back through JSON on that path, so type fidelity (large integers, DECIMAL, TIMESTAMPTZ) is version-sensitive in a way the in-process Arrow path is not; - batch execution against an in-memory database is a
configerror, because nothing persists between processes.
Verify the connection¶
Seed the bundled sample with duckdb orders.duckdb < $DOSI_EXAMPLES/orders/seed.sql,
and point local_duckdb at that file before running this query.
$ dosi query --model $DOSI_EXAMPLES/orders/model.yaml \
--metrics revenue --group-by orders.status --order status --execute --connection local_duckdb
status revenue
cancelled 10
completed 23825.49
pending 0
3 rows
Troubleshooting¶
| Message | Cause and fix |
|---|---|
cannot open duckdb database: <e> |
The file in uri: does not exist (dosi opens it read-only and never creates one), another process has it open for writing (Conflicting lock is held), or it is not a DuckDB database. A DuckDB file written by a newer DuckDB version also lands here. |
duckdb file connections are read-only; batch execution is disabled |
A batch script writes, and a file is opened read-only: seed the file with DuckDB before pointing dosi at it. |
duckdb rejected SQL: <e> |
The compiled SQL reached DuckDB and DuckDB refused it; the message is DuckDB's own. |
cannot clone duckdb connection: <e> |
The underlying connection was closed — usually the database file disappeared mid-run. |
cannot start duckdb CLI: <e> |
Only in the lean build: install the DuckDB CLI or switch to the default download. |
in-memory duckdb cannot persist a batch script across calls |
Same build: point --db at a file instead of using an in-memory database. |
Reference¶
- Official site: https://duckdb.org/
- Connecting and client configuration: DuckDB Rust client, CLI installation
- SQLite — local SQLite files are read through this same connector
- Connection profiles · Arrow