Quickstart

This page takes you from an empty directory to a working request against the hosted service. You will model three TPC-DS tables, deploy them, and get SQL back from the terminal and from curl. Nothing here connects to a warehouse: 0sql only needs to know what your tables look like.

1. Get an API key

Sign up at app.0sql.io. The console creates an account and shows your first personal key once. It starts with zsk_. Personal keys deploy projects and run queries; later you will create read-only query keys (zqk_) for your application. See accounts and keys.

2. Install zsql

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

zsql is a single binary that talks to the service. It generates no SQL itself. See installation for other options.

3. Start a project

zsql init tpcds
cd tpcds
zsql auth --api-key zsk_… --server https://app.0sql.io

zsql init writes project.yml, datasources.yml, security.yml, empty models/ and tests/ directories, and runs git init on branch main. zsql auth stores the key and the server in .zsql, which is gitignored, so later commands need neither flag.

The project uid is tpcds. It is what the service knows the project as, and it appears in every request URL.

4. Describe the warehouse

0sql needs the adapter so it emits the right dialect. Connection details are optional metadata; secrets never leave your machine.

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

5. Model three tables

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

zsql new table "Web Sales" --datasource warehouse --domain sales
zsql new table Item --datasource warehouse --domain sales
zsql new table Date --datasource warehouse --domain sales
zsql new relation --datasource warehouse --domain 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.

6. Check and deploy

zsql check

zsql check validates the project on the service and keeps nothing. Then:

zsql deploy
deploy tpcds (2184 bytes) as tpcds/main (the production branch) to https://app.0sql.io
deployed tpcds/main: 3 tables, 5 fields, 2 joins, 2 paths, 0 policies
no tests deployed (tests/*.yml)

The first deploy creates the project in your account. The checked-out git branch, main, is the deployed branch. See deploying.

7. Get SQL from the terminal

The shorthand is one comma-separated line: fields to project, predicates to filter, formulas to calculate.

zsql sql --expr "web net paid, category starts with super"
-- datasource: Warehouse
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%'

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

zsql sql --expr "month(date), web net paid"
-- datasource: Warehouse
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. See querying from the CLI.

8. Get SQL over HTTP

Your application sends the same thing as JSON. Create a query key in the console, grant it the tpcds project, then:

curl -s https://app.0sql.io/projects/tpcds/branches/main/sql \
  -H "Authorization: Bearer zqk_…" \
  -H "Content-Type: application/json" \
  -d '{
    "spec": {
      "projections": [
        {"field": "Date", "decorators": [{"type": "truncate", "grain": "month"}]},
        {"field": "Web Net Paid"}
      ]
    }
  }'
const res = await fetch('https://app.0sql.io/projects/tpcds/branches/main/sql', {
  method: 'POST',
  headers: { Authorization: `Bearer ${process.env.ZSQL_QUERY_KEY}`, '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 os, requests

r = requests.post(
    "https://app.0sql.io/projects/tpcds/branches/main/sql",
    headers={"Authorization": f"Bearer {os.environ['ZSQL_QUERY_KEY']}"},
    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 https://app.0sql.io/projects/tpcds/branches/main/sql \
  -H "Authorization: Bearer zqk_…" \
  -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.

9. Add a second fact table

Add a Store Sales table with a Net Paid measure joined to the same Item and Date tables, deploy, and ask for both measures by month:

zsql sql --expr "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