Connecting to a warehouse
Two different programs are described by datasources.yml, and keeping them apart is the whole of this page.
The server never connects to your warehouse. It has no database driver linked into it: it takes a query spec and gives back SQL text. The only keys it reads are adapter, which picks the dialect to generate, and tier, which ranks datasources when more than one could answer a query.
zsql on your own machine does connect, read-only, when you ask it to — zsql catalog to read what the warehouse holds, zsql sample to collect a dimension’s values, zsql run to run a planned query. Everything else in datasources.yml is for that, and for your own tools.
The two keys the planner reads
warehouse:
name: Warehouse
adapter: postgres # the dialect to generate
tier: hot # hot | warm | cold; the planner prefers hotter tiers
Those three travel in the deployed model. See Datasources for what tier does to routing and Adapters for the dialects.
The keys zsql connects with
A deploy uploads datasources.yml with the secrets stripped, but the server keeps none of these: they are read here, on this machine.
| key | adapters | meaning |
|---|---|---|
host, port | the wire and HTTP adapters | where the server is |
database | most | the database (ClickHouse and MySQL call it the database that --schema names) |
schema | most | the default schema; a catalog read without --schema reads every one it can see. BigQuery spells it dataset, and either key is read |
username | the wire adapters, Snowflake key pair | who connects |
url | ClickHouse, Trino | the base URL when host and port cannot say it — a scheme, or TLS on a port that does not imply it |
file | SQLite, DuckDB | the file to open |
query_timeout | all | seconds; also the statement timeout |
sslmode, ca_file | the wire and HTTP adapters | see TLS |
account, warehouse, role, private_key_file | Snowflake | the account identifier, the compute to run on, the role to assume, a PKCS#8 PEM |
warehouse, catalog, client_id | Databricks | the SQL warehouse id, the Unity catalog, a service principal’s application id |
project, location, key_file | BigQuery | the project, the region, a service-account or ADC key file |
region, catalog, workgroup, output_location, profile | Athena | the AWS region, the Glue data catalog, the workgroup, the S3 result location, a named profile |
cache_ttl | all | seconds a stored zsql run result answers for; 0 keeps nothing |
A path is not a secret, so private_key_file, key_file and ca_file may live in the committed file.
Secrets
Secrets never leave this machine. They go in .zsql, which zsql init gitignores and no deploy ever sends, or in the environment. Nothing reads them but the connection, and a resolved secret lives no longer than the connection does.
# .zsql — gitignored
warehouse:
password: hunter2
snow:
personal_access_token: ...
A flat warehouse_password: hunter2 works too, and ~/.zsql/config is read after the project’s file. The environment is checked first, so a shell can override a checked-out project without editing it:
export ZSQL_DS_WAREHOUSE_PASSWORD=hunter2
export ZSQL_DS_SNOW_PERSONAL_ACCESS_TOKEN=...
The variable is ZSQL_DS_<KEY>_<FIELD>, the datasource key upper-cased with anything that is not a letter or digit becoming an underscore. The eight fields are password, personal_access_token, private_key, oauth_client_id, oauth_client_secret, access_key_id, secret_access_key and session_token.
A missing secret is named rather than guessed at:
datasource warehouse needs a password: put it in .zsql under warehouse, or in ZSQL_DS_WAREHOUSE_PASSWORD
zsql datasource auth
zsql datasource auth -d warehouse # prompts, hidden
zsql datasource auth -d snow --field personal_access_token
echo "$SECRET" | zsql datasource auth -d warehouse --secret-stdin
Writes the secret into .zsql under the datasource’s key. --field names which one; without it, the adapter’s usual secret. --secret-stdin is the form for a script.
TLS
sslmode is libpq’s five rungs with libpq’s meanings, and the default is verify-full:
sslmode | means |
|---|---|
disable | plaintext, for a container on loopback |
prefer | TLS if the server offers it, plaintext if not, never verified; a connection that ends up plaintext says so |
require | TLS or nothing, certificate not checked — the rung a self-signed development server needs |
verify-ca | TLS, the chain checked against the trust store, the host name not |
verify-full | TLS, chain and host name both checked. The default. |
MySQL’s spellings of the same five are accepted. ca_file is a PEM of certificates to trust besides the platform’s, for a private CA — libpq’s sslrootcert, MySQL’s --ssl-ca. An HTTP adapter whose url is plain http while sslmode insists on TLS is refused, naming both.
Which adapters zsql can reach
Twelve adapter names in eleven implementations, over four transports. Redshift rides the Postgres wire with its own catalog queries.
| transport | adapters |
|---|---|
| native wire | Postgres, Redshift, MySQL, SQL Server |
| HTTP | ClickHouse, Trino |
| in-process | SQLite, DuckDB |
| cloud REST | Snowflake, Databricks, BigQuery, Athena |
Postgres, MySQL, SQL Server, ClickHouse and Trino are tested against real servers in containers on every push, and Redshift through the Postgres one. The four cloud adapters are tested against a fake of each endpoint, because none can be reached without an account. Druid is a dialect with no reader: 0sql generates Druid SQL, but zsql catalog and zsql run cannot open it.
Credentials, by adapter:
| adapter | takes |
|---|---|
| Postgres, Redshift, MySQL, SQL Server, ClickHouse, Trino | username + password |
| SQLite, DuckDB | nothing; a file |
| Snowflake | personal_access_token, or username + an RS256 key pair (private_key_file, or private_key in .zsql) |
| Databricks | personal_access_token, or client_id + oauth_client_secret |
| BigQuery | key_file, or GOOGLE_APPLICATION_CREDENTIALS, or gcloud auth application-default login |
| Athena | access_key_id + secret_access_key (+ session_token), or AWS_ACCESS_KEY_ID and friends, or a profile in ~/.aws/credentials |
datasource list, add and test
$ zsql datasource list
key adapter tier where secret
warehouse postgres hot db.internal:5432/analytics yes
snow snowflake warm myorg-myaccount missing: .zsql or ZSQL_DS_*
cs duckdb hot /data/cs_warehouse.duckdb none needed
The last column says whether the secret is at hand — yes, missing: .zsql or ZSQL_DS_*, or none needed for an adapter that takes none — never what it is.
zsql datasource add # asks on a terminal
zsql datasource add --adapter postgres --host db.internal --database analytics --username zsql
add writes the entry into datasources.yml and the secret into .zsql. Every prompt has a flag form, so it works unattended; --no-input turns a missing answer into a refusal that names the flag.
$ zsql datasource test -d cs
cs: ok, duckdb (512 ms); 2 schemas: main (20), sim_base (20)
next: zsql catalog read --schema <name>
test opens the connection, runs a one-row probe and lists the schemas the credential can see, which is the list catalog read --schema takes. It is the one command whose job is the connection, so a wrong host, a missing grant or a TLS mismatch is diagnosed here rather than halfway through a catalog read. A driver’s own message is reported with its cause chained onto it, because the outer message is often “error performing TLS handshake” while the reason underneath is “server does not support TLS”.
zsql check connects to nothing. It validates the model against the committed catalog instead.
Next steps
- The catalog: read what the warehouse holds into
catalog/<datasource>.json - Datasources:
adapter,tierand how the planner routes - Server and deploy token: the server and its deploy token,
.zsqland the environment