DbPrompt Command Line Client

Free universal multi-database client for executing queries on multiple databases, crontab scripts, data transfer and files upload.

DbPrompt Multi-Database SQL Client


Register Database Connections

First time you start DbPrompt will create a file init.sql in the user home directory .DbPrompt folder. The exact location will show in DbPrompt console. Edit this file to create database connections using this two commands:

  • register driver <rdbms> <driverClass> <URL> <"default_parameters">
  • connection <name> <rdbms> <"parameters">


register driver PostgreSql org.postgresql.Driver jdbc:postgresql://<host>:<port>/<db> "port=5432"

connection pos1 PostgreSql "user=postgres password=secret host=localhost db=sakila"

For each used Rdbms (MySql,Postgres,Oracle...) the register driver command defines the Java driver class and the JDBC URL with parameters as <...> tags. In the example above the default port value is passed in the end. This way of defining connections give more flexibility in managing large number of connections. In the advanced section you will see the benefits.

The connection command defines the connection by setting the real values for each URL parameter. The rdbms from the register driver command should match the rdbms from the connection command.

DbPrompt already include JDBC drivers for MySql, MariaDb, PostgreSQL, Oracle, SqlServer, Sqlite, Sybase, Teradata, Vertica, Derby, Exasol, Firebird, H2, HSql and Ingres. For the other databases you can download the driver using the command (rdbms is case sensitive):

download driver <rdbms>

The complete list of known drivers is the same as for DbSchema. Alternative download the JDBC driver .jar files by yourself and place them in the /home/users/.dbprompt/drivers/<rdbms>

Execute Query on Multiple Databases

This operation assumes that the same table exists on each database you connect to. Also the connections pos1 and pos2 should be already defined.

connect pos1,pos2
SELECT * FROM sakila.city;

Group connections into groups.

connection group production pos1,pos2
connect production
SELECT * FROM sakila.city;
Spool result to a file.
connect production exclude my3
spool /tmp/result.csv
SELECT * FROM sakila.city;
spool off

Upload Reports on [s]ftp Server

Produced .csv report files can be uploaded on ftp or sftp servers. The command is upload [-d] ftpURL fileName. -d states for removing the local file. We are using Apache Commons VFS and all protocols used there are supported. Example:
upload sftp://ftpusser@ftp.ftphost.com/intoFolder report.csv

Use Variables

Variables can be defined using command vset. Few variables are system variables, used by DbPrompt. Any other user-defined variable will be replaced in SQL text if the system variable replace_variables=true: Use help to list all system variables. Execute show variables to list the already defined variables.
vset spool.separator=|
vset format.date=YYYY-MM-dd'T'hh:mm:ss'Z'
vset format.timestamp=YYYY-MM-dd'T'hh:mm:ss'Z'
vset replace_variables=true

vset myid=6230
connect db4
execute export.sql
Where the export.sql script is :
spool /tmp/export_&myid.csv
select * from some_table where id=&myid;

Transfer Data Between Databases

Next command transfers data into the currently connected database from the specified databases using a reader query (executed on the from databases). The column names should match between the source and the target. There can be multiple source databases.
connect pos1
transfer into_table from pos2,pos3 using
select id, firstname, lastname from persons;

The transfer is running on configurable number of threads.

vset transfer.threads=4

Execute 'help' to see all configurable parameters.

There is one reader thread and multiple writer threads. Each writer thread is writing a bunch of rows, then it does COMMIT the transaction. The writer threads are writing between 500 and 100.000 records, depending on the number of columns which are transferred ( 500 for more then 50 columns, 100.000 for 1- 2 columns ).

Write Complex Scripts using Java Groovy

Many database tasks require more logic than simple SQLs. We do this using Groovy, a pure Java scripting language with closures. Groovy code can be started with the keyword groovy and ends with the first line with only a slash '/'.

Everything which works in Java works also in Groovy, with this three major improvements:

  • Multiline Strings (also called GString, started and end with """ )
  • Variables in GStrings, ${...} are replaced
  • Closures sql.eachRow(...){r->...} are similar with Java Lambda
Here is an example with Groovy:
connect pos1

int days = 5
sql.eachRow( """
    SELECT personid, firstname, lastname FROM persons p WHERE p.created < sysdate-${days} AND
      EXISTS ( SELECT 1 FROM sport_club s WHERE s.personid=p.personid )
      ORDER BY personid ASC
    """.toString() ) { r ->
    println "${r.personid} ${r.firstname} ${r.lastname}"
Groovy scripts receive two objects :

  • sql is the database connection as Sql object.
  • connector is the DbPrompt object for a defined connection

Write Cronjob Scripts

Using DbPrompt you can execute database scripts on a regular basis with a minimal resolution of one hour. If a script is failing, an email will be sent to the configured emails.
vset email.server.host=internal.mailserver
vset email.server.user=admin
vset email.server.password=secret
vset email.from=DbPrompt
vset email.alert=admin@dbschema.com
vset cron.folder=/dbprompt/cronscripts
On the machine where DbPrompt is running, add in /etc/crontab this line, fixing fist the path to DbPrompt
01 * * * * /usr/local/bin/dbprompt -cron
And in /dbprompt/cronscripts/ save files like
DbPrompt is checking in the file names for this pattern in the end of the file name:
  • .<daily|weekly|monthly|yearly>[<number_or_range>hour].
  • .<number_or_range><day_of_week|day_of_month|day_of_year|month_of_year>[<number_or_range>hour]
If the scripts are getting any error, an email will be sent to the configured email server. Files can also have the .groovy extension. In this case the file content is interpreted as Groovy script.

Advanced Features and Scripts

Need more scripts and features ? Check the advanced features page.

Technical Support

For any issue or feature request please write us. Please write us also about any database-specific scripts you wrote, this will help us to improve the database specific management commands.