Global options and environment

Global options go before the command name and apply to every command. This page also covers what all commands share: how a target is named, where passwords come from, the TLS and SSH options, confirmations, and the environment variables the CLI reads.

Usage

Usage: querybara [options] [command]

Test connections, run SQL, and compare or sync the structure and data of
databases.
The Querybara desktop engine on the command line; shares the app's saved
connections.

Options:
  -V, --version                             print the version
  --store <path>                            local store file (env QUERYBARA_STORE; default: the desktop app store)
  -v, --verbose                             debug output on stderr (never includes secrets)
  -q, --quiet                               only results, warnings and errors
  --no-color                                plain output without colours
  -h, --help                                show help for a command

Commands:
  test [options] <target>                   test a connection step by step: DNS, TCP, SSH, TLS, auth, ping, version
  query [options] <target>                  run SQL statements one by one from -e, a file, or stdin
  compare [options] <source> <target>       compare the structure of two databases and optionally sync the target
  data-compare [options] <source> <target>  compare the rows of a table in two databases and optionally sync the target
  ddl [options] <target>                    print the schema as a DDL script in dependency order
  import [options] <target>                 import a CSV, TSV, JSON, JSON Lines, Excel, XML or Parquet file into a table
  export [options] <target>                 export tables or a query result to CSV, TSV, JSON, JSON Lines, Excel, XML, Parquet, SQL, HTML or Markdown
  run-file [options] <target> <file>        run a .sql file statement by statement with progress and an error log
  transfer [options] <source> <target>      copy tables, collections or keys from one database to another
  backup [options] <target>                 back up a database to a Querybara archive (.qbak), SQL, or with pg_dump/mysqldump
  restore [options] <target> <file>         restore a backup (.qbak, .sql, .sql.gz or a pg_dump archive) into a database
  profiles                                  manage saved connection profiles (shared with the desktop app)
  help [command]                            display help for command

Targets:
  Every <target>, <source> and <profile> argument is a saved profile (name or id) or a
  connection URI: postgres://user:pass@host:5432/db, mysql://user@host/db, mariadb://...
  URI passwords are used for that run only and never stored. Otherwise the password comes
  from the profile's saved secret, QUERYBARA_PASSWORD_<PROFILE> or QUERYBARA_PASSWORD, or a
  hidden prompt. TLS is off unless the URI says otherwise (?sslmode=..., rediss://,
  https://, mongodb+srv://) or --tls is given.

  An http:// or https:// URL is an Elasticsearch node: https://[email protected]:9200. It logs in with the URL's user and password, or
  with the API key in QUERYBARA_API_KEY; the scheme decides TLS (--tls sets the https mode).

SSH tunnels and proxies:
  A saved profile connects through its own SSH tunnel and proxy. A URI target takes them
  from --ssh user@host[:port] (repeat it for jump hosts, in the order to connect), with
  --ssh-key <path>, --ssh-agent or an SSH password (--ssh-password-env <VAR>, else
  QUERYBARA_SSH_PASSWORD, else a hidden prompt), and --proxy socks5://host:port or
  http://host:port. Host keys are checked against the desktop app's known_hosts (next to
  the store; --known-hosts to use another file): a new key is asked about in a terminal
  and refused otherwise unless --ssh-accept-new is given; a changed key is always refused.
  A MongoDB replica set (host list, mongodb+srv or ?replicaSet=), Redis Sentinel or
  Cluster reaches every node through the tunnel or proxy, by the name the node announces;
  SRV records are still looked up on this computer.

Environment:
  QUERYBARA_STORE              local store file (default: the desktop app's querybara.db)
  QUERYBARA_PASSWORD           password for targets without one
  QUERYBARA_PASSWORD_<NAME>    password for one profile (name upper-cased, other chars as _)
  QUERYBARA_SSH_PASSWORD       SSH password for --ssh hops and profiles that ask for it
  QUERYBARA_SSH_KEY_PASSPHRASE passphrase of an encrypted SSH key
  QUERYBARA_PROXY_PASSWORD     proxy password (a --proxy URL may carry it too)
  QUERYBARA_API_KEY            Elasticsearch API key (URL targets and profiles)
  QUERYBARA_BEARER_TOKEN       bearer token for profiles that log in with one
  QUERYBARA_PASSPHRASE         seals passwords the CLI saves (the OS keychain is app-only)
  QUERYBARA_EXPORT_PASSPHRASE  passphrase for profiles export/import files
  QUERYBARA_BACKUP_PASSPHRASE  passphrase of encrypted backups (backup --encrypt, restore)
  NO_COLOR                   turn colours off

Exit codes:
  0 success / no differences, 1 differences found, a failed connection test, or an
  import or SQL file that skipped rows or failed statements, 2 error, 130 interrupted
  (Ctrl+C).

Global options

Option	What it does
`--store <path>`	The local store file. Same as `QUERYBARA_STORE`. Default: the desktop app’s store
`-v, --verbose`	Debug output on stderr, such as the store in use. Never includes secrets
`-q, --quiet`	Only results, warnings and errors
`--no-color`	Plain output without colours. Same as setting `NO_COLOR`
`-V, --version`	Print the version
`-h, --help`	Show help for the program or a command

querybara --store ./ci-store.db --quiet query shop-ci -f seed.sql
querybara -v test shop-prod

Results go to stdout and nothing else does, so you can pipe them. Progress, status lines, warnings and errors go to stderr. An error prints as error: <message>, followed by a detail: and a hint: line when there are any, and the SQLSTATE or engine error code. Stack traces appear only with --verbose.

Targets

Every <target>, <source> and <profile> argument is either a saved profile or a connection URI.

A saved profile is found by id, then by exact name, then by name ignoring case. A name that matches more than one profile is an error. The profile brings its own TLS settings, SSH tunnel, proxy and safety settings (read-only, confirm writes, production).
A connection URI starts with postgres://, postgresql://, mysql://, mariadb://, mongodb://, mongodb+srv://, redis://, rediss://, http:// or https://. An http:// or https:// URL is an Elasticsearch node.

querybara test shop-prod
querybara test "postgres://[email protected]:5432/shop"
querybara test "mysql://[email protected]/shop"
querybara test "https://[email protected]:9200"

A mysql:// target that turns out to be a MariaDB server is used with MariaDB’s rules.

Passwords and secrets

A password in a URI is used for that run only and never stored. Otherwise the password comes from the profile’s saved secret, then QUERYBARA_PASSWORD_<NAME>, then QUERYBARA_PASSWORD, then a hidden prompt when a terminal is attached.

For QUERYBARA_PASSWORD_<NAME>, the profile name is upper-cased and every run of other characters becomes _. The password of the profile shop-prod is QUERYBARA_PASSWORD_SHOP_PROD.

QUERYBARA_PASSWORD_SHOP_PROD="$DB_PASSWORD" querybara test shop-prod

The desktop app seals saved passwords with the OS keychain, which the CLI cannot read. When a login without a password is refused and a terminal is attached, the CLI asks for the password and tries once more.

Connection options

Commands that connect take these options. They apply to the targets of that command.

Option	What it does
`--tls <mode>`	TLS mode for this run: `disable`, `require`, `verify-ca` or `verify-full`
`--ssh <user@host[:port]>`	Reach URI targets through this SSH server. Repeat it for jump hosts, in the order to connect
`--ssh-key <path>`	SSH private key file: OpenSSH, PEM or PuTTY `.ppk`
`--ssh-agent`	Log in with the keys of ssh-agent (`SSH_AUTH_SOCK`) or Pageant
`--ssh-password-env <VAR>`	Take the SSH password from this variable. Default `QUERYBARA_SSH_PASSWORD`, else a prompt
`--proxy <url>`	Reach URI targets, or their first SSH server, through `socks5://host:port` or `http://host:port`
`--ssh-accept-new`	Trust and remember an SSH host key not seen before
`--known-hosts <path>`	The known hosts file to use instead of the desktop app’s

TLS is off for a URI target unless --tls is given or the URI says otherwise: with sslmode, ssl-mode, ssl or tls in its query string, or with the rediss://, https:// or mongodb+srv:// scheme. A saved profile uses its own TLS settings unless --tls is given.

querybara test "postgres://[email protected]/shop?sslmode=verify-full"
querybara test "postgres://[email protected]/shop" --tls verify-full

A saved profile connects through its own SSH tunnel and proxy. A URI target uses the --ssh and --proxy options. A MongoDB replica set (a host list, mongodb+srv or ?replicaSet=), Redis Sentinel or Redis Cluster reaches every node through the tunnel or proxy, by the name the node announces. SRV records are still looked up on this computer.

querybara query "postgres://[email protected]/shop" --ssh [email protected] --ssh-agent -e "select 1"
querybara query "mysql://[email protected]/shop" --ssh [email protected] --ssh [email protected] --ssh-key ~/.ssh/id_ed25519 -e "select 1"
querybara test "postgres://[email protected]/shop" --proxy socks5://proxy.example.com:1080

SSH host keys

Host keys are checked against the desktop app’s known_hosts file, next to the store, or the file --known-hosts names.

A remembered key is trusted.
A new key is asked about in a terminal. Without a terminal it is refused, unless you pass --ssh-accept-new, which trusts it and remembers it.
A changed key is always refused.

Confirmations

Statements and operations that need confirmation are asked about in a terminal, with [y]es, [N]o, [a]ll for statements in a script. Without a terminal, -y, --yes is the only way to run them; otherwise the command stops with an error and exit code 2. When querybara query reads its script from stdin, it cannot ask, so it needs --yes too.

What needs confirmation:

UPDATE or DELETE without WHERE, DROP and TRUNCATE.
Every write to a profile whose environment is production, or that is set to confirm writes.
Operations that drop, empty or overwrite data, such as a restore over existing objects. Each command page lists its own.

A read-only profile refuses writes outright, and so does any target when you pass --read-only to a command that has it.

Environment variables

Variable	What it does
`QUERYBARA_STORE`	The local store file. Default: the desktop app’s `querybara.db`
`QUERYBARA_USER_DATA_DIR`	The desktop app’s data folder; the CLI uses `querybara.db` inside it
`QUERYBARA_PASSWORD`	Password for targets without one
`QUERYBARA_PASSWORD_<NAME>`	Password for one profile
`QUERYBARA_SSH_PASSWORD`	SSH password for `--ssh` hops and for profiles that ask for it
`QUERYBARA_SSH_KEY_PASSPHRASE`	Passphrase of an encrypted SSH key
`QUERYBARA_PROXY_PASSWORD`	Proxy password. A `--proxy` URL may carry it too
`QUERYBARA_TLS_KEY_PASSPHRASE`	Passphrase of a profile’s TLS client key
`QUERYBARA_API_KEY`	Elasticsearch API key, for URL targets and profiles
`QUERYBARA_BEARER_TOKEN`	Bearer token for profiles that log in with one
`QUERYBARA_PASSPHRASE`	Seals the passwords the CLI saves in the store
`QUERYBARA_EXPORT_PASSPHRASE`	Passphrase for `profiles export` and `profiles import` files
`QUERYBARA_BACKUP_PASSPHRASE`	Passphrase of encrypted backups (`backup --encrypt`, `restore`)
`NO_COLOR`	Turns colours off

Pass secrets in the environment rather than as arguments. The backup passphrase is never read from the command line, which other users of the machine can see.

Documents Querybara 0.1.1 · built frombc9f5aa