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 JOIN when 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

0sql never opens the file. file is carried as metadata for your own application, which runs the SQL it gets back. 0sql never uses it. There is nothing secret to keep out of git.

Notes

  • Expressions are SQLite SQL. total(), group_concat and strftime are fine in expression.sql; there is no percentile_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.

Next Steps