0sql documentation
0sql turns a query spec into one SQL statement for your warehouse. You describe tables, dimensions, measures, joins and row-level security policies in YAML, serve the project with zsql serve, and your application sends requests like the one below. 0sql plans the SQL in microseconds and returns it. It never connects to your warehouse, never executes anything, and stores nothing about your queries. It is open source under the MIT licence, one static binary, and runs wherever you put it.
POST http://127.0.0.1:3699/projects/tpcds/branches/main/sql
Content-Type: application/json
{"spec": {"projections": [{"field": "Category"}, {"field": "Web Net Paid"}],
"filters": [{"field": "Category", "predicate": "starts_with", "value": "super"}]}}
{"sql": "SELECT\n\tT1.\"i_category\" AS \"Category\",\n\tsum(T0.\"ws_net_paid\") AS \"Web Net Paid\"\nFROM\n\tweb_sales T0\n\tJOIN item T1\n\t\tON T0.ws_item_sk = T1.i_item_sk\nWHERE\n\tLOWER(T1.\"i_category\") LIKE 'super%'\nGROUP BY\n\tT1.\"i_category\"",
"datasource": "Warehouse", "datasource_uid": "warehouse", "adapter": "postgres"}
What 0sql does
- Resolves names to SQL. A request names fields, not columns. The planner finds the tables that hold them, the join route between them, and the aggregation each measure needs.
- Blends across fact tables safely. Measures from different facts at different grains are aggregated separately and stitched on the conformed dimension. No fan-out, no double counting. See cross-fact blends.
- Applies your security policies. Each request carries a security context. Policies in the model turn it into
WHEREfilters orCASEmasks in the SQL. See security context. - Speaks 13 dialects. One model, one request shape, SQL for Postgres, Snowflake, BigQuery, Databricks, Redshift, Athena, Trino, ClickHouse, Druid, DuckDB, MySQL, SQL Server or SQLite. See adapters.
- Refuses before it is wrong. A request that cannot be resolved returns an error naming what could not be found, with the nearest fields. See errors and corrections.
- Keeps your data where it is. The server is given the shape of your warehouse, not its contents: it has no database driver in it, holds no credential and reads no row. See what the server sees.
Two halves
The server plans. It answers a query spec with a SQL statement and nothing else: no query execution, no result cache, no end-user UI, no dashboards, no saved queries. Views, reports and interactive filters belong to the caller; filters in a spec are plain values your application has already resolved. Your application runs the statement, caches it, renders it, charts it.
The zsql command line reads and runs. On your own machine it opens a read-only connection to the warehouse so you can draft a model from it and check the answers: zsql catalog reads the schema, zsql generate drafts model files from it, zsql sample collects a dimension’s values, and zsql run runs a planned query and keeps the rows under ~/.zsql/results, outside the repository. None of that goes through the server.
What the server sees
The two are separate programs. The server has no database driver linked into it: it never holds a warehouse credential, never opens a connection and never reads a row. Its view is narrow by design.
| The server holds | The server never sees |
|---|---|
| The semantic model you deploy: table and column names, SQL expressions, join conditions, security policies, tests | Any row of any table |
| The sampled values of the dimensions you turned sampling on for, if you turned any on | Any result of any statement it planned |
| The query spec and security context of each request, in memory, while it plans | Your warehouse credentials |
Only the model is kept. A spec and its context are planned in memory and are never stored or logged, so there is no query history anywhere. The statement comes back to you, and you execute it yourself, against your own database, with your own credentials.
The one thing on that list that is customer data is the sampled values, and it is there because you asked for it: sampling is off on every dimension until a model turns it on, so a deployment holds no values at all unless someone decided it should. What to enable is the page for that decision.
The practical consequence: a reviewer assessing 0sql is assessing a process that reads schema metadata, not data. See what is stored for the exact list.
The journey
- Install. One command puts
zsqlon your machine: the CLI, the planner and the server. See installation. - Model.
zsql init, describe your datasources, add tables and relations in YAML, andzsql sqlanswers from the files at hand. See the semantic model reference. - Serve.
zsql serveruns the server and deploys the project as the checked-out git branch, redeploying as you edit;zsql deploysends a project to a server elsewhere. See running the server. - Request. POST a spec with a security context and get SQL. See the Query API.
- Run it. Against your own warehouse, with your own credentials, in your own code.
The quickstart does all five in about ten minutes.
Where to go
| You want to | Read |
|---|---|
| Send your first request | Quickstart |
| Run the server, with accounts and keys, in production | Running the server |
| Construct a request: projections, filters, calculations, segments | Query API and the spec |
| See real requests and the SQL they produce | Request cookbook |
| Write the YAML model | Semantic model |
| Build complex measures | Compound measures and calculations |
| Lock rows down per user | Row-level security |
| Drive it from a terminal or CI | zsql CLI |
| Look up an endpoint | API reference |
| Put the API behind an agent | Chat dashboard tutorial, with a working repo |
| Point an agent at the docs | /docs/llms.txt |