Quickstart

This page takes you from an empty directory to a working request against a server you run. You will model three TPC-DS tables, get SQL from the terminal, start the server, and get the same SQL over HTTP. Nothing here connects to a warehouse: 0sql only needs to know what your tables look like.

1. Install zsql

curl -fsSL https://0sql.io/install.sh | sh
zsql --version

zsql is one static binary: the CLI, the planner and the server. See installation for other options.

2. Start a project

zsql init tpcds
cd tpcds

zsql init writes project.yml, datasources.yml, security.yml, empty models/ and tests/ directories, and runs git init on branch main. The project uid is tpcds: it is what the server knows the project as, and it appears in every request URL.

3. Describe the warehouse

0sql needs the adapter so it emits the right dialect. Connection details are optional metadata; secrets are stripped before a deploy.

warehouse:
  name: Warehouse
  adapter: postgres
  tier: hot
  database: tpcds
  schema: public

4. Model three tables

Two dimension tables and one fact. Field names are what your requests will use.

zsql generate "Web Sales" --empty table -d warehouse --domain sales
zsql generate Item --empty table -d warehouse --domain sales
zsql generate Date --empty table -d warehouse --domain sales
zsql generate --relationships sales/sales
name: Web Sales
physical_name: web_sales
datasource: warehouse
cost: 100

fields:
  - type: measure
    name: Web Net Paid
    data_type: decimal
    format: currency:2
    expression:
      sql: sum(ws_net_paid)

  - type: measure
    name: Web Orders
    data_type: integer
    expression:
      sql: count(distinct ws_order_number)
name: Item
physical_name: item
datasource: warehouse
cost: 10

fields:
  - type: dimension
    name: Category
    data_type: string
    synonyms: [department]
    expression:
      sql: i_category

  - type: dimension
    name: Product Name
    data_type: string
    expression:
      sql: i_product_name
name: Date
physical_name: date_dim
datasource: warehouse
cost: 10

fields:
  - type: dimension
    name: Date
    data_type: date
    expression:
      sql: d_date
datasource: warehouse

web_sales_item:
  left: Web Sales
  right: Item
  sql: left.ws_item_sk = right.i_item_sk
  cardinality: many_to_one

web_sales_date:
  left: Web Sales
  right: Date
  sql: left.ws_sold_date_sk = right.d_date_sk
  cardinality: many_to_one

Joins are declared once, with their cardinality. The planner derives every valid route from them. See relationships.

5. Check it and get SQL

$ zsql check
validated tpcds/main: 3 tables, 5 fields, 2 joins, 2 paths, 0 policies, 0 sampled dimensions
catalog: warehouse not checked; `zsql catalog read -d warehouse --schema <name>` reads one
no tests (tests/*.yml)
-- local 6.1 ms

zsql check loads the project from the files here, forms the join routes and runs the tests. Nothing needs to be running. Now ask it for SQL. An expression line is one comma-separated list: fields to project, predicates to filter, formulas to calculate.

zsql sql "web net paid, category starts with super"
-- datasource: warehouse (Warehouse, postgres)
SELECT
	sum(T0."ws_net_paid") AS "Web Net Paid"
FROM
	web_sales T0
	JOIN item T1
		ON T0.ws_item_sk = T1.i_item_sk
WHERE
	LOWER(T1."i_category") LIKE 'super%'
-- planned here in 8.4 ms

The planner joined item because the filter needed it, and compared the string case-insensitively. Try a date grain:

zsql sql "month(date), web net paid"
-- datasource: warehouse (Warehouse, postgres)
SELECT
	DATE_TRUNC('month', T1."d_date")::DATE AS "Month(Date)",
	sum(T0."ws_net_paid") AS "Web Net Paid"
FROM
	web_sales T0
	JOIN date_dim T1
		ON T0.ws_sold_date_sk = T1.d_date_sk
GROUP BY
	DATE_TRUNC('month', T1."d_date")::DATE

zsql explain shows the node tree and timings behind any line, and zsql repl keeps a session open and reloads the model as you edit it. See querying from the CLI.

6. Serve it

Your application wants the same thing over HTTP. In a second terminal:

$ zsql serve
zsqld: 0 deployment(s) loaded from /home/you/.zsql/data; deploys are open to whoever reaches this address
zsqld: listening on http://127.0.0.1:3699
zsql: serving /home/you/tpcds as tpcds/main; redeploying as its files change
deployed tpcds/main: 3 tables, 5 fields, 2 joins, 2 paths, 0 policies, 0 sampled dimensions
no tests (tests/*.yml)

The server deployed the directory as tpcds/main, the checked-out git branch, and will redeploy it whenever a model file changes. There is no key to make: the server has no accounts and answers whoever can reach it, which on your loopback is only you. See running the server for the background form, securing access, the deploy token and production.

7. Get SQL over HTTP

curl -s http://127.0.0.1:3699/projects/tpcds/branches/main/sql \
  -H "Content-Type: application/json" \
  -d '{
    "spec": {
      "projections": [
        {"field": "Date", "decorators": [{"type": "truncate", "grain": "month"}]},
        {"field": "Web Net Paid"}
      ]
    }
  }'
const res = await fetch('http://127.0.0.1:3699/projects/tpcds/branches/main/sql', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    spec: {
      projections: [
        { field: 'Date', decorators: [{ type: 'truncate', grain: 'month' }] },
        { field: 'Web Net Paid' },
      ],
    },
  }),
});
const { sql, adapter } = await res.json();
// run `sql` against your warehouse with your own client
import requests

r = requests.post(
    "http://127.0.0.1:3699/projects/tpcds/branches/main/sql",
    json={"spec": {"projections": [
        {"field": "Date", "decorators": [{"type": "truncate", "grain": "month"}]},
        {"field": "Web Net Paid"},
    ]}},
)
sql = r.json()["sql"]
# run `sql` against your warehouse with your own client
curl -s http://127.0.0.1:3699/projects/tpcds/branches/main/sql \
  -H "Content-Type: application/json" \
  -d '{"expr": "month(date), web net paid"}'

With expr the answer also carries the spec the line was read as.

{
  "sql": "SELECT\n\tDATE_TRUNC('month', T1.\"d_date\")::DATE AS \"Month(Date)\",\n\tsum(T0.\"ws_net_paid\") AS \"Web Net Paid\"\nFROM\n\tweb_sales T0\n\tJOIN date_dim T1\n\t\tON T0.ws_sold_date_sk = T1.d_date_sk\nGROUP BY\n\tDATE_TRUNC('month', T1.\"d_date\")::DATE",
  "datasource": "Warehouse",
  "datasource_uid": "warehouse",
  "adapter": "postgres"
}

Run that SQL with your own warehouse client. 0sql has done its part.

8. Run it against the warehouse

zsql sql prints a statement; zsql run runs it and shows the rows, which is how you find out whether the model answers the question correctly rather than merely plausibly:

$ zsql run 'item category, web net paid'
Item Category  Web Net Paid
─────────────  ────────────
Books              14203.55
Music               8821.40
6 rows (complete) · warehouse (postgres) · 0.4 s · r-20261024-9f2a1c04

It needs a credential in .zsql and runs only from this machine; the rows land under ~/.zsql/results, outside the repository. See Running a query.

9. Add a second fact table

Add a Store Sales table with a Net Paid measure joined to the same Item and Date tables. The server picks the change up by itself. Ask for both measures by month:

zsql sql "month(date), web net paid, net paid"

The SQL now has one aggregation CTE per fact table, stitched with a FULL OUTER JOIN on the month. Neither measure is inflated by the other’s rows. That is grain-safe blending, and it needed no extra modeling. See the cross-fact blend example for the full SQL.

Next steps

  • Running the server: in the background, with accounts and keys, under systemd.
  • The query spec: every key of a request.
  • Request cookbook: real requests and the SQL they produce.
  • Row-level security: policies in the model, context in the request.
  • Tests: assert the SQL a projection produces, on every check and deploy.
  • Chat dashboard demo: the same request inside a working app, where a model writes the spec and a dashboard runs it. Clone it and it runs on a warehouse that ships in the repo.