Command reference
zsql [GLOBAL FLAGS] <COMMAND> [ARGS]. With no command inside a project directory, zsql starts the repl; outside one it prints help. zsql --version prints the version; zsql <command> --help prints that command’s flags.
Global flags
| Flag | Default | Notes |
|---|---|---|
--project DIR | the directory holding project.yml, searched from the current directory upwards | Error without one: no project.yml here or above; run `zsql init` to start a project |
-r, --remote | off | Use the server’s copy of the model rather than the files here |
--branch NAME | the checked-out git branch of the project; main outside a project | The branch on the server. Refused without -r: --branch NAME names a branch on the server; add -r to plan against it, or check it out to plan it here |
--server URL | see server resolution | Implies -r. Trailing / trimmed |
--uid UID | uid from project.yml | Implies -r. Required outside a project directory |
--no-input | off on a terminal; on when stdin is not a terminal or CI is set | Never prompt: a missing answer is a refusal naming the flag that supplies it |
--agent | off | JSON wherever a command has a JSON shape, and never a prompt |
Anything that needs only the model runs here from the project directory: check, test, sql, explain, explore, fields, tables, values, spec, repl. The same commands with -r run against the server’s copy of the branch. deploy, status, deployments, undeploy and auth are always about a server. datasource, catalog, generate, sample, run and results are local only: they open a warehouse connection, and the server has no database driver in it.
list, remove and health remain as the older names for deployments, undeploy and server health.
—agent and —json
Every command that answers with data answers in JSON under --agent, or under its own --json: sql, explain, explore, spec, run, results, catalog read, catalog sync, catalog search, catalog show, check, test, sample, values, fields, tables, datasource list, status and server status. One document per invocation, including when a command answers and then fails — the failure is inside the document and the exit code is unchanged.
Project commands
zsql init [DIR] [--name NAME] [--uid UID]
| Argument | Default | Notes |
|---|---|---|
DIR | . | Created if missing. Refused if it holds a project.yml. |
--name | the directory name | name: in project.yml |
--uid | slug of the name | Lower-cased |
Writes project.yml, datasources.yml, security.yml, AGENTS.md, models/.keep, tests/.keep; appends .zsql, .strata and samples/values.jsonl.gz to .gitignore; runs git init -q -b main if there is no .git. Existing files are kept.
Warehouse commands
These open a connection and have no -r form. See Connecting to a warehouse.
zsql datasource list
Lines of key adapter tier where secret. The last column says whether the secret is at hand, never what it is. --json for the same as a document.
zsql datasource add [--adapter A] [--tier T] [--name N] [--host H] [--port P] [--database D] [--schema S] [--username U] [--file F] [--account A] [--warehouse W] [--catalog C] [--project-id P] [--key-file F] ...
Writes the entry into datasources.yml and the secret into .zsql. Prompts on a terminal; every prompt has the flag form above.
zsql datasource auth [-d DS] [--field F] [--secret-stdin]
| Flag | Default |
|---|---|
-d, --datasource | the only one, when there is one |
--field | the adapter’s usual secret. One of password, personal_access_token, private_key, oauth_client_id, oauth_client_secret, access_key_id, secret_access_key, session_token |
--secret-stdin | off; prompts hidden |
Writes the secret into .zsql under the datasource’s key.
zsql datasource test [-d DS]
Opens the connection, runs a one-row probe, lists the schemas it can see: cs: ok, duckdb (512 ms); 2 schemas: main (20), sim_base (20).
zsql catalog read [TABLES...] [--schema S]... [--all] [-d DS] [--json]
Reads schemas whole (--schema, repeatable; the database on ClickHouse and MySQL) or named tables (schema.table or table) and merges them into catalog/<datasource>.json. A schema picker on a terminal when nothing is named; a refusal naming the three ways otherwise. --all is never the default.
zsql catalog sync [-d DS] [--json]
Re-reads every scope and added table the file records, reports the drift (tables added or gone, columns added, removed or retyped) and drops only what the warehouse says is gone.
zsql catalog search PATTERN [--live] [-d DS] [--json]
Tables whose name holds the pattern, in the file or, with --live, in the warehouse.
zsql catalog show TABLE [-d DS] [--json]
One table’s columns, keys and comment from the file; every datasource’s when -d is unsaid.
zsql generate [TABLES...] [--dir D] [--schema S] [--all] [--columns C] [--new-columns] [--live] [--empty table] [--relationships [D/N]] [--infer] [--physical-name P] [--domain D] [-d DS] [--dry-run]
| Flag | Notes |
|---|---|
TABLES... | schema.table or table; a word ending in / is the directory to draft into |
--dir D | the models/{dir} the files go in; each table’s own schema when unsaid |
--schema S | every table of that schema |
--all | every table in the catalog; never the default |
--columns C | for a table that has a model: fields for these columns, comma-separated |
--new-columns | for a table that has a model: fields for every column it lacks |
--live | a named table the catalog lacks is read from the warehouse first and added to it |
--empty table | an empty model file from the template, no catalog behind it |
--relationships [D/N] | the joins file at models/{domain}/rel.{name}.yml; takes {domain}/{name}, {domain}, or nothing for every domain with tables |
--infer | with --relationships: infer the joins from the table files there. No query is run |
--physical-name P, --domain D | with --empty |
--dry-run | say what would be written and write nothing |
Never rewrites an existing file.
zsql sample [NAMES...] [-d DS] [--dry-run] [--budget N] [-y] [--json]
Collects the values of the dimensions marked enable_sampling into samples/values.jsonl.gz. Two queries per dimension. --budget (100 when unsaid) refuses before running anything when the plan needs more. --json reports counts only; the values go to the file. See Sampling.
zsql run [LINE...] [--spec FILE] [--context FILE] [--limit N] [--all] [--fresh] [--timeout S] [--format F] [--out FILE] [--head N] [--quiet]
| Flag | Default |
|---|---|
--limit N | the 10,000-row cap, pushed into the statement |
--all | off; no limit, held in memory |
--fresh | off; run even when a stored result is within its TTL, and replace it |
--timeout S | the datasource’s query_timeout |
--format F | table; also json, jsonl, csv. Never changes with where stdout goes |
--out FILE | none; the format its extension says |
--head N | 50 for a table on a terminal |
Keeps the rows under ~/.zsql/results/<project>/. See Running a query.
zsql results list | show ID | export ID --out FILE | sql ID | rm ID... | purge [-y]
show takes --format, --head and --offset.
zsql values DIM [--json] [-r]
A dimension’s sampled values, by name or uid, from samples/values.jsonl.gz here or the deployed branch with -r.
Server commands
zsql serve [--bind ADDR] [--data-dir DIR] [--open] [--no-project] [--public-url URL]
Runs the server in this process. Inside a project, deploys that project once the server is up and redeploys it as its files change (unless --no-project). The server has no accounts and no keys: it answers whoever can reach it, and securing access is done in front of it.
| Flag | Default | Does |
|---|---|---|
--bind ADDR | 127.0.0.1:3699, or ZSQL_BIND | Address to listen on; port 0 takes a free one |
--data-dir DIR | $ZSQL_HOME/data, or ZSQL_DATA_DIR | Where deployed models live |
--open | Allow a non-loopback bind with no deploy token, which lets anyone who reaches the port deploy and remove | |
--no-project | Serve only the data directory, even inside a project | |
--public-url URL | the bind address, or ZSQL_PUBLIC_URL | Where the server is reached from outside, for zsql server status to report |
A non-loopback --bind is refused unless ZSQL_DEPLOY_TOKEN is set or --open is given. The deploy token is a variable only, never a flag, so it stays out of the process list; with it set, deploy, validate, test and remove need Authorization: Bearer <token> and every read stays open. Every other setting is a ZSQL_* variable; see Running the server.
zsql server start|stop|status|logs [--lines N]
start takes the flags of serve and runs it detached, with its output in $ZSQL_HOME/server.log and its pid in $ZSQL_HOME/server.pid. stop sends SIGTERM and waits (SIGKILL after 15 s). status says whether the server it started holds its data directory’s lock and whether it answers /healthz. logs prints the last N lines (default 100).
zsql auth --api-key TOKEN [--server URL]
Writes api_key: and optionally server: into .zsql in the project directory (mode 0600). Prints saved to <path>. The value is the server’s deploy token (its ZSQL_DEPLOY_TOKEN); a server without one needs none.
zsql check
Loads the project from its files, forms the join routes, checks the model against the catalog and runs the tests here. Connects to nothing. Prints validated <uid>/<branch>: N tables, N fields, N joins, N paths, N policies, N sampled dimensions, then a line per datasource saying what the catalog checked or how to read one, then the warnings, the test results and -- local N ms. With -r, the same as zsql deploy --dry-run.
N sampled dimensions counts dimensions with values in samples/values.jsonl.gz, not dimensions with enable_sampling set.
zsql deploy [--dry-run] [--watch]
| Flag | Does |
|---|---|
--dry-run | POST to the validate route instead; the server keeps nothing |
--watch | Deploy, then redeploy whenever a .yml or .yaml under the project changes (poll 500 ms, debounce 200 ms) |
Sends the model files, tests/, datasources.yml with its secret keys stripped, and samples/values.jsonl.gz when there is one. Never .zsql, .strata or .git. Exits non-zero on a rejected archive or any failed test.
zsql test
Runs tests/*.yml here against the model loaded from the files; with -r, the deployed branch’s tests on the server. Exits non-zero with tests failed.
zsql status
Prints the deployed branch’s summary as JSON.
zsql deployments
One line per deployment on the server: project branch N tables, N fields, N policies, N tests. Works outside a project. zsql list is the older name.
zsql undeploy --yes
Removes the deployed branch. Without --yes: this removes <uid>/<branch> from <server>; add --yes to confirm. zsql remove is the older name.
zsql server health
Prints <server> ok. No key needed. zsql health is the older name.
Shell completion
zsql completions bash | zsh | fish
Prints a completion script: source <(zsql completions bash).
Query commands
The three planning commands share the query arguments:
| Flag | Notes |
|---|---|
LINE... | An expression line, parsed locally into a spec. Several arguments are joined with spaces, so quoting is optional. zsql spec --help has every form. |
--spec FILE | A JSON file; a top-level spec key is unwrapped. Conflicts with a line. |
--context FILE | A JSON security context |
One of a line or --spec is required. --expr LINE is a hidden back-compat alias for giving the line bare, and still works.
zsql sql (LINE... | --spec FILE) [--context FILE] [--explain] [--json]
| Flag | Does |
|---|---|
--explain | Same output as zsql explain |
--json | Print the answer as JSON |
Prints corrections (stderr), -- datasource: <uid> (<name>, <adapter>), the SQL, and when planned here -- planned here in N ms (stderr).
zsql explain (LINE... | --spec FILE) [--context FILE] [--json]
Corrections, -- datasource:, SQL, blank line, -- N nodes, the node tree, -- local N us: parser N us, plan N us (server with -r), -- phases (us): ....
zsql explore (LINE... | --spec FILE) [--context FILE] [-q TERM] [--json]
| Flag | Does |
|---|---|
-q TERM | Keep fields whose name, uid or synonyms contain the term, or come close |
Lines of uid dim|msr name tables [~score], then -- can add N dimensions, N measures · local N us (server with -r).
zsql spec LINE...
Prints how each item was read, then the spec JSON an expression line makes. Several arguments are joined with spaces, so quoting is optional. Needs no model at all. --help prints the language’s summary and every form.
zsql repl [--context FILE]
| Flag | Does |
|---|---|
--context FILE | Security context applied to every query in the session |
Holds the model loaded from the files here and reloads it when any of them changes, printing reloaded in N ms; a broken edit keeps the old model and prints the error. Dot-commands: .help .? .tables .fields [q] .reload .branch [name] (with -r) .spec <line> .json {...} .explain <line|json> .explore <line|json> [? term] .quit .exit .q. History in ~/.zsql/history. The line editor completes fields, functions, keywords, projected dimensions, hints tables, extract parts and calc types by the grammar, hints the agreed-on rest of a word, and holds a line open while a parenthesis, bracket, brace or quote is unclosed.
zsql fields [Q] [--hidden]
| Argument | Does |
|---|---|
Q | Filter text; containing matches first, then fuzzy with a score |
--hidden | Include hidden fields |
Lines of uid dim|msr name [(hidden)] [~score].
zsql tables
Lines of uid physical_name datasource cost N.
Environment variables
| Variable | Used for |
|---|---|
ZSQL_API_KEY | The server’s deploy token, as the CLI sends it. Wins over every file. |
ZSQL_SERVER | The server URL. Second after --server; does not imply -r. |
ZSQL_HOME | Moves ~/.zsql: the config, the repl history, the server’s data directory, pidfile and log |
ZSQL_BIND, ZSQL_DATA_DIR, ZSQL_DEPLOY_TOKEN, ZSQL_PUBLIC_URL, ZSQL_ADMIT_PLAN, ZSQL_ADMIT_EXPLORE, ZSQL_ADMIT_HEAVY | The server’s settings, read by zsql serve; see Running the server |
ZSQL_DS_<KEY>_<FIELD> | A datasource’s secret, checked before any file. <KEY> is the datasource key upper-cased with non-alphanumerics as _; <FIELD> is one of PASSWORD, PERSONAL_ACCESS_TOKEN, PRIVATE_KEY, OAUTH_CLIENT_ID, OAUTH_CLIENT_SECRET, ACCESS_KEY_ID, SECRET_ACCESS_KEY, SESSION_TOKEN |
CI | When set, implies --no-input |
GOOGLE_APPLICATION_CREDENTIALS, AWS_REGION, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_PROFILE | Read by the BigQuery and Athena readers, as those SDKs read them |
HOME | Locates ~/.zsql when ZSQL_HOME is unset |
Server resolution: --server, ZSQL_SERVER, server: in .zsql (or .strata), server: in ~/.zsql/config, server: in project.yml, then http://127.0.0.1:3699.
Deploy token resolution: ZSQL_API_KEY, api_key: in .zsql (or .strata), api_key: in ~/.zsql/config.
Datasource secret resolution: ZSQL_DS_<KEY>_<FIELD>, then .zsql (or .strata) in the project — nested under the datasource key, or flat as <key>_<field> — then ~/.zsql/config.
Config files
| File | Holds | Notes |
|---|---|---|
project.yml | name, uid, production_branch, optional server | Committed. Found by walking up from the current directory. |
.zsql | api_key, server | Project directory. Written by zsql auth, mode 0600, gitignored. YAML. |
.strata | Same keys | Read only when .zsql is absent. |
~/.zsql/config | api_key, server | Machine-wide fallback. YAML. |
~/.zsql/history | repl history | |
~/.zsql/data | the server’s deployed models, as <project>/<branch>/ | --data-dir or ZSQL_DATA_DIR moves it |
~/.zsql/server.log, ~/.zsql/server.pid | the background server’s log and pid | written by zsql server start |
datasources.yml | Warehouses | Shipped with secret keys removed |
AGENTS.md | How to work on this model, for an agent | Committed. Written by zsql init |
catalog/<ds>.json | What the warehouse holds | Committed. Written by zsql catalog |
samples/values.jsonl.gz | Sampled dimension values | Gitignored by default; deployed with the model. Written by zsql sample |
~/.zsql/results/<project>/ | What zsql run kept | Outside the repository, never deployed |
Exit codes and errors
zsql exits 0 on success and non-zero on any error. Errors print as Error: <message>. A refusal, planned here or on the server, prints its class and message, <class>: <message>; with --json it is {"error": {"class", "message"}} on stdout, exit 1:
Unauthorized: this deploy token is not valid
NotFound: no deployment for project tpcds branch main
ContextRequired: this branch has security policies; a context is required to plan
DeployError: Error in models/store/tbl.store_sales.yml:
Expression errors for Store Net Paid: Sql measure should have an aggregation function
zsql deploy and zsql test exit non-zero after a successful HTTP call when any test failed (tests failed). The complete error class table is on Query errors and in the API reference.