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.