Skip to content

SQLite connector

SQLite files are readable as a Dosi datasource: point a profile at the file and metric queries run against it.

SQLite is a storage format rather than a warehouse dialect, so there is no SQLite SQL generator. Today the file is opened through DuckDB's sqlite extension — Dosi compiles DuckDB SQL and DuckDB reads the SQLite file. That is an implementation detail, and a temporary one: native SQLite support is planned, and when it lands the profile below keeps working unchanged.

The connection is read-only, including batch execution. Seed or update the database before pointing Dosi at it. Gated by exec-duckdb, which is on by default.

Connection profile

datasources:
  fixture:
    type: sqlite
    uri: sqlite:////absolute/path/fixture.sqlite

Parameters

Key Type Required Default Notes
type string yes — sqlite
uri string yes — sqlite:////abs/path, sqlite:///rel/path, sqlite:/path and a bare filesystem path are all accepted. Unlike DuckDB there is no in-memory form — the file must exist.
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.

Limitations

  • Read-only. execute_batch is refused outright, so Dosi cannot seed or migrate a SQLite database — do that with the sqlite3 CLI first.
  • The first open may need network access. DuckDB installs its sqlite extension on demand; in a network-isolated environment, preinstall or cache that extension.
  • SQLite typing is dynamic, so a date-like column can reach DuckDB as text. Cast explicitly (CAST(order_date AS DATE)) before typed functions such as DATE_TRUNC when the stored type is not what the model assumes.

Verify the connection

$ dosi query --model model.yaml --metrics revenue --execute --connection fixture

Troubleshooting

Message Cause and fix
datasource "x" (sqlite) is missing required field "uri" Unlike DuckDB, the path is mandatory: uri: sqlite:////absolute/path/database.sqlite.
datasource "x" (sqlite) has an empty database path The uri: parsed to nothing — check for a stray sqlite:// with no path.
sqlite connections are read-only; batch execution is disabled Seed or update the SQLite file before running Dosi.
cannot open sqlite database through duckdb: <e> The file is missing, unreadable, or DuckDB could not install/load the matching sqlite extension.

Reference