querybara compare

CLI
PostgreSQL
MySQL
MariaDB

querybara compare compares the structure of two databases: the source has the structure you want, the target is the database to change. It lists the create, alter and drop operations, and can write them as a dependency-ordered script, write an HTML report, or apply them to the target and compare again. It pairs PostgreSQL with PostgreSQL, and MySQL or MariaDB with MySQL or MariaDB.

Usage

Usage: querybara compare [options] <source> <target>

compare the structure of two databases and optionally sync the target

Arguments:
  source                       the desired structure: profile or URI
  target                       the database to change: profile or URI

Options:
  --schema <name>              PostgreSQL schema to compare; repeatable
                               (default: all)
  --ignore <list>              also ignore: comments, collation, auto-increment,
                               definer, ownership, privileges, partitions,
                               column-order, name-case, names,
                               extension-versions
  --no-default-ignores         compare auto-increment, definer, ownership and
                               privileges too (ignored by default)
  --rename <kind>:<from>=<to>  map a renamed object instead of drop + create;
                               repeatable. kind: table, view, column, index,
                               constraint (e.g. table:old_users=users,
                               column:users.mail=email)
  --no-detect-renames          do not turn identical drop + create pairs into
                               renames
  --include-destructive        select destructive operations too (they start
                               unselected)
  --out <file>                 write the deployment script ("-" for stdout)
  --html <file>                write an HTML report
  --json                       print the diff as JSON
  --apply                      run the script on the target, then re-compare
  -y, --yes                    confirm --apply (required for MySQL and MariaDB)
  --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
                               for jump hosts, in order
  --ssh-key <path>             SSH private key file (OpenSSH, PEM or PuTTY .ppk)
  --ssh-password-env <VAR>     take the SSH password from this variable (default
                               QUERYBARA_SSH_PASSWORD, else a prompt)
  --ssh-agent                  log in with the keys of ssh-agent (SSH_AUTH_SOCK)
                               or Pageant
  --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 (a changed key is always refused)
  --known-hosts <path>         SSH known hosts file (default: the desktop app's)
  -h, --help                   show help for a command

Exit codes follow diff(1): 0 no differences, 1 differences found (or remaining after
--apply), 2 error.

--apply runs the selected operations in dependency order with progress and stops at the
first error. PostgreSQL scripts run in one transaction and roll back on failure. MySQL and
MariaDB DDL is not transactional, so --apply warns and needs --yes. After applying, both
sides are compared again and the command fails unless no differences remain.

Examples:
  querybara compare staging prod --out deploy.sql --html report.html
  querybara compare dev "postgres://app@localhost/app_test" --schema public --json
  querybara compare model-db test-db --include-destructive --apply --yes

The --tls and SSH options are described in Global options.

Examples

Write the deployment script and an HTML report:

querybara compare shop-staging shop-prod --out deploy.sql --html report.html

Compare one PostgreSQL schema and print the diff as JSON:

querybara compare shop-dev "postgres://[email protected]/shop_test" --schema shop --json

Write the script to stdout:

querybara compare postgres://[email protected]/shop postgres://[email protected]/shop --out -

Map a renamed table and column instead of a drop and a create:

querybara compare shop-dev shop-staging --rename table:clients=customers --rename column:customers.mail=email --out sync.sql

Apply the changes, destructive ones included:

querybara compare shop-model shop-test --include-destructive --apply --yes

What is compared

On PostgreSQL, --schema limits the comparison to the schemas you name; by default every schema other than the system schemas is compared. MySQL and MariaDB compare the connected database.

By default, auto-increment values, definers, ownership and privileges are ignored. --no-default-ignores compares them too, and --ignore adds more to ignore: comments, collation, auto-increment, definer, ownership, privileges, partitions, column-order, name-case, names, extension-versions.

Identical drop and create pairs are reported as renames unless you pass --no-detect-renames. --rename <kind>:<from>=<to> maps a rename yourself; the kind is table, view, column, index or constraint.

Destructive operations start unselected, so they are not in the script or applied unless you pass --include-destructive.

Applying

--apply runs the selected operations in dependency order with progress and stops at the first error. After applying, both sides are compared again, and the command fails unless no differences remain.

PostgreSQL scripts run in one transaction and roll back on failure. The CLI asks before applying when the script has destructive operations or the target is a production or confirm-writes profile.
MySQL and MariaDB run DDL outside transactions: if a statement fails, the statements before it stay applied. The CLI warns and always needs --yes, even in a terminal.
A read-only target refuses --apply.

Exit codes

Exit codes follow diff(1):

Code	Meaning
0	No differences, or `--apply` left none
1	Differences found, or some remain after `--apply`
2	Error, including a failed or unconfirmed apply
130	Interrupted

Documents Querybara 0.1.1 · built frombc9f5aa