DbSchemaCLI

DbSchemaCLI is the command-line client of DbSchema. It connects to databases through JDBC drivers, runs SQL and Groovy on one or several databases at once, and runs script files, for example in a scheduled task. It also hosts the Floating License Server for teams.

DbSchemaCLI comes with every DbSchema installation, as DbSchemaCLI.exe on Windows, in the DbSchema folder. The examples on this page come from DbSchemaCLI 10.5.2, connected to a PostgreSQL database shopdb.

Start DbSchemaCLI

Open a terminal.
Run DbSchemaCLI, such as "C:\Program Files\DbSchema\DbSchemaCLI.exe" on Windows.
Type help to list every command.

DbSchemaCLI 10.5.2 #261006.405
Not Connected > help
Define connections in 'C:\Users\you\.DbSchema\config\cli\init.sql' file.:
...
Connectivity Commands
  register driver <rdbms> <className> <url> [<key=defaultValue>...] Register JDBC Driver. ...
  download driver <db_name>      Download JDBC driver from repository for a given database. ...
  connection <alias> <rdbms> "<key=value>..." Create a database connection. ...
  connection group <alias> <con1>,<con2>... Create a group of connections. ...
  connect [-q] <con1>,<group1>,... [exclude <con3>,...] Connect to the defined connection or connection group. ...
  show connections               Shows currently connected databases.
  check network <url>            Check whether a URL is reachable with the configured proxy and certificate trust. ...
...
All other commands will be executed in the database.

The prompt shows the connection you work with, or Not Connected. Type exit to quit.

Define a connection

A connection has a name and the details that DbSchemaCLI needs to reach the database. The connection command takes the name first, then options:

connection <name> -d <dbms> -h <host> [-P <port>] [-D <database>] [-u <user>] [-p <password>]

The usage line in help still shows an older form, connection <alias> <rdbms> "<key=value>...". DbSchemaCLI 10.5.2 rejects that form; use the options.

Option	Meaning
`-d`, `--dbms`	The database type, as DbSchema names it: `PostgreSQL`, `MySql`, `SqlServer`, `Oracle`, `Sqlite`. The name is case-sensitive. Required.
`-h`, `--host`	The database host. Required, also when you give `-url`.
`-P`, `--port`	The port.
`-D`, `--db`	The database.
`-u`, `--user`	The user.
`-p`, `--password`	The password.
`-url`, `--url`	A full JDBC URL, instead of host, port and database.

Not Connected > connection shop -d PostgreSQL -h 127.0.0.1 -P 55432 -D shopdb -u postgres -p secret
Ok

With a JDBC URL:

Not Connected > connection shop -d PostgreSQL -h 127.0.0.1 -url jdbc:postgresql://127.0.0.1:55432/shopdb -u postgres -p secret
Ok

A connection that you define this way lasts until DbSchemaCLI quits. show connections also lists the connections that you saved in DbSchema.

Keep connections in init.sql

DbSchemaCLI runs the file init.sql each time it starts. Put the connection commands there, and every session has them. The first line of help gives the path of the file, such as C:\Users\you\.DbSchema\config\cli\init.sql on Windows.

connection shop -d PostgreSQL -h 127.0.0.1 -P 55432 -D shopdb -u postgres -p secret
connection shop2 -d PostgreSQL -h 10.0.0.12 -P 5432 -D shopdb -u postgres -p secret

The file holds the passwords as plain text, so keep it readable by you alone.

Connect and run SQL

Type connect and the connection name.
Type a query. A query ends with ;, and may span several lines.

Not Connected > connect shop
Connected
shop > select customer_id, first_name, last_name from shop.customers order by customer_id limit 3;
customer_id  | first_name  | last_name
-------------+-------------+-----------
          1  | Leo         | Dubois
          2  | Ioana       | Garcia
          3  | Liam        | Miller
Selected 3 rows.

show connections lists the connection in use and every connection that you can connect to.

Run SQL on several databases at once

Name several connections after connect, separated by commas. Each query then runs in every database, and DbSchemaCLI prints the result of each one under its name.

Not Connected > connect shop,shop2
Connected 2
shop,shop2 > select count(*) as customers from shop.customers;
shop
customers
----------
      200
Selected 1 rows.

shop2
customers
----------
      200
Selected 1 rows.

Run Groovy

Groovy is a language that reads like Java. Use it where a task needs more logic than SQL. Start a Groovy block with groovy and end it with a line that holds only /. The block receives the variable sql, the connection, as a Groovy Sql object.

shop > groovy
sql.eachRow("select status, count(*) as n from shop.orders group by status order by status") { r ->
    println "${r.status}: ${r.n}"
}
/
com.wisecoders.dbschema.schema.Connector@4602f874 > CANCELLED: 130
DELIVERED: 748
NEW: 128
PAID: 247
SHIPPED: 247
Ok

The first output line starts with the connection, as DbSchemaCLI 10.5.2 prints it. While several connections are active, the block runs once for each of them.

Run a script file

execute runs a file of commands, such as SQL queries and groovy blocks.

shop > execute C:/scripts/top-customers.sql

To run a file and quit, give its path when you start DbSchemaCLI. End the file with exit; without it, DbSchemaCLI waits for more commands.

"C:\Program Files\DbSchema\DbSchemaCLI.exe" C:/scripts/report.sql

Here report.sql defines its connection, runs a query and a Groovy block, and quits:

connection shop -d PostgreSQL -h 127.0.0.1 -P 55432 -D shopdb -u postgres -p secret
connect shop
select status, count(*) as orders from shop.orders group by status order by status;
groovy
sql.eachRow("select count(*) as n from shop.customers") { r ->
    println "Customers: ${r.n}"
}
/
exit

Check the network

When a driver download or a connection fails, check network shows the proxy and the certificate trust that DbSchemaCLI uses, and requests the URL with them.

Not Connected > check network https://dbschema.com
Network settings in use:
  Proxy: detected from the operating system and the JVM settings
  TLS trust: the JVM trust store, the operating system trust store

Reached [https://dbschema.com]: 200

Firewall, proxy and SSH explains how to change these settings.

Change the DbSchemaCLI settings

DbSchemaCLI reads its settings from the DbSchema settings. Open them in DbSchema with Edit → Settings, then DbSchemaCLI in the tree. The groups are Settings, Mail, Cron and Spool.

The DbSchema Settings dialog with DbSchemaCLI, Settings selected: Number of threads to use during transfer, Initialization Script, Replace Variables, Ignore Errors and other options

Inside DbSchemaCLI, vset changes a text setting for the session:

shop > vset [email protected]
Property Set

Host the Floating License Server

A team with a floating license can run the license server inside DbSchemaCLI. Licensing shows the commands, register and license server.

Common problems

"Missing required options: d, h"

The connection command lacks -d or -h. This happens with the older form connection pos1 PostgreSql "user=postgres host=localhost". Write the details as options, as Define a connection shows.

"Enter a full JDBC URL (jdbc:..)" or "Please choose a Connection String URL"

DbSchemaCLI could not build a JDBC URL for the connection. Older versions said "Please choose a Connection String URL". Check that -d is the database type exactly as DbSchema names it: PostgreSQL, not PostgreSql. Or give the whole URL with -url.

A Groovy file run with execute fails on sql.eachRow

In DbSchemaCLI 10.5.2, a .groovy file run with execute receives sql as an object that has no eachRow. Put the Groovy code in a groovy block, ending with /, inside a .sql file, as report.sql above shows.

DbSchemaCLI does not exit after the script

End the script with exit.

Cookie preferences