Running the server
The zsql binary is the CLI and the server. zsql serve runs the server in the terminal; zsql server start keeps it running in the background. Everything the docs describe, the query API, deploys and the MCP endpoint, is served by that one process, on your machine or on a host you run. Nothing phones home.
Serve the project you are editing
Inside a project directory:
$ 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 comes up on 127.0.0.1:3699, deploys the directory as the checked-out git branch, and redeploys every time a .yml or .yaml under the project changes. One command gives you a live endpoint over the model you are editing:
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"}'
There is no key and no Authorization header. The CLI’s other commands look at the same address by default, so zsql -r sql "..." plans against the server’s copy with no configuration. Ctrl-C stops it; a client mid-request is answered first.
Deployed models live under the data directory, ~/.zsql/data unless --data-dir or ZSQL_DATA_DIR says otherwise, so a restart reloads them. Two servers cannot share a data directory; the second is told which process has it.
In the background
$ zsql server start
started process 41213 on 127.0.0.1:3699; log at /home/you/.zsql/server.log
$ zsql server status
running as process 41213 at http://127.0.0.1:3699; it answers
$ zsql server logs --lines 20
$ zsql server stop
stopped process 41213
start takes every flag serve takes and runs the same thing detached, with its output in $ZSQL_HOME/server.log. status asks the data directory’s lock whether the server it started is still the one running, then calls its liveness route. stop sends SIGTERM and waits, escalating after 15 seconds.
Outside a project
zsql serve --no-project, or zsql serve from a directory with no project.yml, serves whatever the data directory holds and deploys nothing by itself. Projects reach it with zsql deploy:
cd ~/projects/tpcds
zsql deploy --server http://127.0.0.1:3699
The CLI defaults to that address, so inside a project zsql deploy alone is enough. This is the shape a shared server takes: one process, several projects, each deployed from its own repository by its own zsql deploy, or from CI.
Securing access
The server is a component, used the way a database is. It does not know who is calling, so everything about who may call is done in front of it:
| Who does it | |
|---|---|
| Who may ask, and for which project | your application or proxy |
| The security context a plan runs under | the caller sends it: context in the request body, the Zsql-Context header on /mcp |
| Rate limits | your proxy or application |
| TLS | your proxy |
| Who may deploy | the deploy token |
What reaching the port gives a caller:
- Every deployed model.
GET /projectslists every project and branch, and the catalogue of each (tables, fields, descriptions, SQL expressions, policies as compiled) is readable. There is no per-project access. - SQL under any context. The context is whatever the request says. A caller who can reach the server can claim
system_admin, or any group, and get the statement a policy would have filtered for someone else. Row-level policies protect data through the application that sets the context and runs the SQL; they are not a defence against the network. - Deploys and removals, unless a deploy token is set.
It never gives rows: the server has no warehouse connection, so what leaks from an exposed server is the shape of your warehouse and SQL text, not data.
So, in practice:
- Bind the loopback or a private address, never a public interface. The default is
127.0.0.1:3699. - Let only your application reach it. The browser, the agent and the end user talk to your application; your application authenticates them, decides which project they may ask about, builds the context from its own session, and calls 0sql. Never pass a context through from the client.
- If something outside must reach it directly (an MCP client, a teammate’s CLI), put an authenticating reverse proxy in front: nginx or Caddy with your SSO, Cloudflare Access, a tailnet. Have it forward the read routes only and refuse the four write routes.
- Rate limit there too. The server caps how much work runs at once and answers
503withRetry-Afterwhen it is full, which keeps the process alive under a flood. It does not keep one caller from taking every seat. - Set a deploy token on anything that is not your own laptop.
The deploy token
ZSQL_DEPLOY_TOKEN in the server’s environment is the one credential there is. With it set, four routes need Authorization: Bearer <token>: deploy, validate, test and remove. Every read stays open.
$ ZSQL_DEPLOY_TOKEN=$(openssl rand -hex 32) zsql serve --no-project
zsqld: 0 deployment(s) loaded from /home/you/.zsql/data; deploys need the deploy token
zsqld: listening on http://127.0.0.1:3699
It is a variable and never a flag, so it does not show in the process list. Keep it in the environment file at mode 0600. It is compared in constant time and never logged. To rotate it, change the variable and restart; there is one token at a time.
The CLI sends it from .zsql or ZSQL_API_KEY:
zsql auth --api-key <token> --server http://10.0.0.5:3699
zsql deploy
Without it, a deploy to a server that has one answers:
Unauthorized: this server takes a deploy token for deploys, tests and removals: Authorization: Bearer <token> (from the CLI: `zsql auth --api-key <token>`, or ZSQL_API_KEY)
A server serving its own project (zsql serve inside one) deploys it with its own token.
Treat the token like warehouse credentials. A model carries the SQL expressions your application will run with its own credentials, and the row policies over them; whoever can deploy can drop the policies or choose that SQL. It is not scoped: its holder may overwrite or remove any branch of any project, and the log line for a deploy names the project and branch, not who sent it.
Beyond the loopback
--bind with an address that is not the loopback is refused unless a deploy token is set, or --open says the exposure is meant:
$ zsql serve --bind 0.0.0.0:3699
error: 0.0.0.0:3699 is not a loopback address, and with no deploy token anyone who reaches it may deploy and remove; set ZSQL_DEPLOY_TOKEN, or add --open to mean it
The token only guards the writes. A server bound beyond the loopback still answers every read to whoever reaches the address, so bind the private interface your application is on, not 0.0.0.0, on any host that also has a public one. The usual arrangement keeps the loopback bind and puts a proxy on the same host in front:
ZSQL_PUBLIC_URL=https://0sql.internal.example.com zsql serve --bind 127.0.0.1:3699
ZSQL_PUBLIC_URL (or --public-url) is only what zsql server status reports as the address the server is reached at.
Deploying to a server elsewhere
Deploy to the server directly, the way migrations are run against a database: on a path of its own, not through whatever the application’s queries pass through.
| Where you deploy from | How it reaches the server |
|---|---|
| the same private network or VPN | the server’s private address |
| anywhere else | a tailnet, or ssh -L 3699:127.0.0.1:3699 <host> and the default address |
| hosted CI | a tailnet in the workflow, or the proxy |
The direct path is plain HTTP. A tailnet or an SSH tunnel encrypts it; a flat VPN or VPC may not, and then the token crosses it in the clear on every deploy.
With deploys on their own path, the proxy in front carries reads only and refuses POST .../deploy, .../validate, .../test and DELETE /projects/{uid}/branches/{branch}. A proxy that forwards a fixed list of read routes does this without trying. If the proxy does carry deploys, it either passes Authorization through on those routes, or adds the token itself for callers it has decided may deploy. A proxy that adds the token to every request it forwards gives a deploy to everyone who can read.
Settings
Flags configure the process at hand; ZSQL_* variables are the same settings as an environment file a deployment loads. A flag wins over its variable.
| Flag | Variable | Default | Meaning |
|---|---|---|---|
--bind ADDR | ZSQL_BIND | 127.0.0.1:3699 | Address to listen on; port 0 takes a free one and logs it |
--data-dir DIR | ZSQL_DATA_DIR | $ZSQL_HOME/data | Where deployed models live |
ZSQL_DEPLOY_TOKEN | none | What a deploy, a validate, a test and a removal must present. A variable only | |
--open | Allow a non-loopback bind with no deploy token | ||
--no-project | Serve only the data directory, even inside a project | ||
--public-url URL | ZSQL_PUBLIC_URL | the bind address | Where the server is reached from outside, for zsql server status |
ZSQL_ADMIT_PLAN, ZSQL_ADMIT_EXPLORE, ZSQL_ADMIT_HEAVY | cores × 4, cores, 2 | How much of each kind of work runs at once | |
ZSQL_HOME | ~/.zsql | Where the data directory, the pidfile, the log and the repl history live |
Work is admitted by cost: planning, exploring and field search, and the heavy routes (deploy, validate, test) each have a cap. A request that finds no seat within its wait (one second, five for heavy work) is answered 503 Busy with Retry-After; one that outlives its deadline (30 seconds, 90 for heavy work) is answered 503 Timeout without it. zsql waits and retries a 503 that names a delay.
As a system service
On a host, run the same binary under systemd with the settings in an environment file. The unit below is the one the project ships in ops/zsqld.service, trimmed to what matters; the full one adds the usual hardening (no capabilities, read-only filesystem outside the state directory, a system call filter).
[Unit]
Description=0sql server
After=network-online.target
Wants=network-online.target
[Service]
User=zsqld
Group=zsqld
EnvironmentFile=/etc/zsqld.env
Environment=ZSQL_DATA_DIR=/var/lib/zsqld
Environment=ZSQL_HOME=/var/lib/zsqld
ExecStart=/usr/local/bin/zsql serve
StateDirectory=zsqld
Restart=on-failure
RestartSec=2
[Install]
WantedBy=multi-user.target
ZSQL_BIND=127.0.0.1:3699
ZSQL_DEPLOY_TOKEN=<openssl rand -hex 32>
ZSQL_PUBLIC_URL=https://0sql.internal.example.com
Install the binary, create the zsqld user, systemctl enable --now zsqld, and put your application or proxy in front of 127.0.0.1:3699. journalctl -u zsqld -n 10 shows the startup line: how many deployments loaded, and whether deploys need the deploy token or are open to whoever reaches this address. Read it after every restart. A branch the build cannot load is logged by name with its reason and left out; the fix is to deploy that branch again from its repository. A restart reloads deployments from disk in well under a second.
Back up the data directory if you like: it holds nothing that cannot be rebuilt by deploying each branch again from its repository.
What is stored
Stored, per deployed branch, under <data dir>/<project>/<branch>/:
- the compiled model (tables, fields, joins, universes, policies)
- the branch’s tests
- its sampled values, when the project deployed any
Never stored:
- query specs and expressions
- security contexts
- the SQL that comes back
sql, explain and explore plan in memory and write nothing. Each deploy and each removal writes one log line naming the project and branch.
Never received at all:
- the rows in your warehouse
- the results of any statement
- your warehouse credentials
The server has no database driver linked into it and never opens a socket to a warehouse. The compiled model describes your warehouse (names, SQL expressions, join conditions, policies) and that description is what the planner reads. zsql deploy strips every secret from datasources.yml before upload. The statement you get back is executed by you, with your credentials. (The zsql command line does read the warehouse, on your own machine, for catalog, generate, sample and run; none of that passes through the server.)
Coming from a release with accounts
Releases before 0.6.10 could run with --accounts: users, personal and query keys, sign-up, a web app with a playground, rate limits. All of it is gone.
zsql serverefuses to start whileZSQL_ACCOUNTSis on, so a server that authenticated every request does not come back up answering anyone. Remove it from the environment, along withZSQL_SIGNUP,ZSQL_LIMITS,ZSQL_MAIL_*,ZSQL_TRUSTED_PROXYandZSQL_MAX_BRANCHES_*, which are no longer read. SetZSQL_DEPLOY_TOKEN.- Deployments lived under
<account>/<project>/<branch>/and are now looked for at<project>/<branch>/. Deploy each again, or move each project directory up one level. accounts.dbis not read; the server says so at startup while the file is there.zsk_andzqk_keys stop being checked. An application that sent a query key keeps working, and so does anyone else who can reach the port. A context that was bound to a query key’s grant must now be sent by the caller./app,/login,/signup,/me,/account/...and/projects/{uid}/accessanswer 404.
MCP
The same server speaks MCP at /mcp: an agent gets the reads as tools. It is as open as the rest of the server, so the same rule applies: on the loopback for your own agent, behind your service for anyone else. See MCP.
Next steps
- Quickstart: a project, a server and the first SQL
- Deploying:
zsql deploy, branches,status,deployments,undeploy - Security policies and the context: what your application sends with each plan
- CI/CD: deploying to your server from a pipeline