SQLite Adapter
Generate SQLite SQL from your model, for demos and small embedded datasets.
What the adapter emits
- Identifiers in
"double quotes"; bare column names in expressions are lowercased. - Date parts with
strftime:CAST(strftime('%Y', x) AS INTEGER),CAST(strftime('%w', x) AS INTEGER)and so on. - CTEs for the per-fact subqueries and a
FULL OUTER JOINwhen a query blends measures from two facts (SQLite 3.39 or later).
Configuration
sqlite_local:
adapter: sqlite
name: Local SQLite
tier: hot
file: ./data/warehouse.db
Connection fields
The server never opens the file: it has no database driver in it, and the deployed model keeps only adapter, tier and the name. zsql on this machine does open it, in process, for zsql catalog, zsql sample and zsql run — which makes SQLite the quickest way to see the whole flow working, since there is no credential at all. A relative file path resolves against the project directory, and there is nothing secret to keep out of git.
Notes
- Expressions are SQLite SQL.
total(),group_concatandstrftimeare fine inexpression.sql; there is nopercentile_cont. - Prefer DuckDB for analytics. For anything beyond a demo, DuckDB has the analytical SQL (date truncation, window functions, quantiles) that the generated statements lean on.