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 projectyour application or proxy
The security context a plan runs underthe caller sends it: context in the request body, the Zsql-Context header on /mcp
Rate limitsyour proxy or application
TLSyour proxy
Who may deploythe deploy token

What reaching the port gives a caller:

  • Every deployed model. GET /projects lists 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 503 with Retry-After when 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 fromHow it reaches the server
the same private network or VPNthe server’s private address
anywhere elsea tailnet, or ssh -L 3699:127.0.0.1:3699 <host> and the default address
hosted CIa 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.

FlagVariableDefaultMeaning
--bind ADDRZSQL_BIND127.0.0.1:3699Address to listen on; port 0 takes a free one and logs it
--data-dir DIRZSQL_DATA_DIR$ZSQL_HOME/dataWhere deployed models live
ZSQL_DEPLOY_TOKENnoneWhat a deploy, a validate, a test and a removal must present. A variable only
--openAllow a non-loopback bind with no deploy token
--no-projectServe only the data directory, even inside a project
--public-url URLZSQL_PUBLIC_URLthe bind addressWhere the server is reached from outside, for zsql server status
ZSQL_ADMIT_PLAN, ZSQL_ADMIT_EXPLORE, ZSQL_ADMIT_HEAVYcores × 4, cores, 2How much of each kind of work runs at once
ZSQL_HOME~/.zsqlWhere 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 serve refuses to start while ZSQL_ACCOUNTS is on, so a server that authenticated every request does not come back up answering anyone. Remove it from the environment, along with ZSQL_SIGNUP, ZSQL_LIMITS, ZSQL_MAIL_*, ZSQL_TRUSTED_PROXY and ZSQL_MAX_BRANCHES_*, which are no longer read. Set ZSQL_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.db is not read; the server says so at startup while the file is there.
  • zsk_ and zqk_ 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}/access answer 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