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 WHERE filters or CASE masks 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 holdsThe server never sees
The semantic model you deploy: table and column names, SQL expressions, join conditions, security policies, testsAny row of any table
The sampled values of the dimensions you turned sampling on for, if you turned any onAny result of any statement it planned
The query spec and security context of each request, in memory, while it plansYour 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

  1. Install. One command puts zsql on your machine: the CLI, the planner and the server. See installation.
  2. Model. zsql init, describe your datasources, add tables and relations in YAML, and zsql sql answers from the files at hand. See the semantic model reference.
  3. Serve. zsql serve runs the server and deploys the project as the checked-out git branch, redeploying as you edit; zsql deploy sends a project to a server elsewhere. See running the server.
  4. Request. POST a spec with a security context and get SQL. See the Query API.
  5. 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 toRead
Send your first requestQuickstart
Run the server, with accounts and keys, in productionRunning the server
Construct a request: projections, filters, calculations, segmentsQuery API and the spec
See real requests and the SQL they produceRequest cookbook
Write the YAML modelSemantic model
Build complex measuresCompound measures and calculations
Lock rows down per userRow-level security
Drive it from a terminal or CIzsql CLI
Look up an endpointAPI reference
Put the API behind an agentChat dashboard tutorial, with a working repo
Point an agent at the docs/docs/llms.txt