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, accounts, keys 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; accounts off; rate limits off; mail off, links to the journal
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
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, or a teammate, 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"}'

Accounts are off, so there is no key and no Authorization header. The CLI’s other commands look at the same address by default, so zsql -r sql --expr "..." 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 on 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.

Accounts and keys

With accounts off there is one tenant and no authentication, which is right for a laptop and wrong for anything reachable by other people. --accounts (or ZSQL_ACCOUNTS=on in the environment) turns on tenancy: accounts, users, personal keys, read-only query keys with per-project grants, project access levels and an audit log, all in accounts.db under the data directory.

$ zsql serve --accounts
zsqld: 0 deployment(s) loaded from /home/you/.zsql/data; accounts on, sign-up open; rate limits on; mail off, links to the journal
zsqld: branch caps: 5 per project, 20 per account
zsqld: listening on http://127.0.0.1:3699
zsql: accounts are on, so /home/you/tpcds is not deployed by itself; sign up, make a key, and `zsql deploy --server http://127.0.0.1:3699`

Open http://127.0.0.1:3699/app/ in a browser and sign up with an account name, your name, an email address and a password. That creates the account, makes you its first user with the admin role, and shows your first personal key once. Store it in the project and deploy:

zsql auth --api-key zsk_… --server http://127.0.0.1:3699
zsql deploy

From here every request carries Authorization: Bearer <key>: a personal key (zsk_) for people and CI, a query key (zqk_) granted the project for an application. Members, roles, grants, rotation and the web app’s pages are on Accounts, keys and access. ZSQL_SIGNUP=closed stops new accounts once yours exist; admins still invite members.

With accounts on, the rate limits are on too: planning, exploring, deploying and account changes each have a per-account allowance, and 429 says when to come back. With accounts off only the admission caps and the body limits apply.

Beyond the loopback

--bind 0.0.0.0:3699 listens on every interface. With accounts off that is refused, because anyone who can reach the port could deploy and plan; add --accounts, or --open to say you mean it (a trusted private network, a container with its own ingress).

Put TLS in front with the reverse proxy you already run, and tell the server where it is reached:

ZSQL_PUBLIC_URL=https://0sql.example.com ZSQL_TRUSTED_PROXY=1 zsql serve --accounts --bind 127.0.0.1:3699

ZSQL_PUBLIC_URL (or --public-url) is the base of the links the server emails and what marks session cookies Secure when it is https. ZSQL_TRUSTED_PROXY=1 makes the rate limits read the caller’s address from the proxy’s header instead of seeing the proxy for everyone.

Mail

Password reset and email verification are emailed links. The server has no SMTP client: it writes the message to ZSQL_MAIL_COMMAND on stdin (msmtp -t, sendmail -t, a script of your own), with ZSQL_MAIL_FROM as the sender. With no command configured the links are written to the server’s log instead and never into a response, so a server without mail still works: an admin reads the link from the journal and passes it on.

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.

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_ACCOUNTS=on
ZSQL_PUBLIC_URL=https://0sql.example.com
ZSQL_TRUSTED_PROXY=1
ZSQL_MAIL_COMMAND=msmtp -t
ZSQL_MAIL_FROM=0sql <0sql@example.com>

Install the binary, create the zsqld user, systemctl enable --now zsqld, and put your reverse proxy in front of 127.0.0.1:3699. journalctl -u zsqld -n 10 shows the startup line: how many deployments loaded, whether accounts and sign-up are on, the rate limit mode, and whether mail is configured. A restart reloads deployments from disk in well under a second; sessions survive because they are in SQLite.

Back up the data directory. It holds every deployed branch and, with accounts on, the accounts database with its hashed keys and sessions. Nothing about anyone’s queries is in it: specs and contexts are planned in memory and never written anywhere.

MCP

The same server speaks MCP at /mcp: an agent gets the reads as tools under the same keys and limits. See MCP.

Next steps

• Quickstart: a project, a server and the first SQL
• Accounts, keys and access: everything --accounts turns on
• Deploying: zsql deploy, branches, status, list, remove
• CI/CD: deploying to your server from a pipeline