How to Automate Database Tasks with DbSchema
For a DbSchema user with a job that comes back every release, who can read Java and would rather write the job down once.
On this page
The schema documentation has to be regenerated after every release, and it is the same walk through the same dialog each time: open the model, pick the diagrams, pick the format, pick the output folder. DbSchema can do that walk from a script instead. Automation Scripts are Java Groovy with the model and the live connection already handed to them, and they run from a menu inside DbSchema or from the command line with no window on the screen.
Write the script in the SQL Editor
The quickest place to start is the SQL Editor, which opens from the Editors menu or from the toolbar.
In its toolbar, switch the mode from SQL to Groovy. The editor then reads what you type as Groovy instead of as statements to send to the database. Groovy is 100% compatible with Java, so everything that works in Java works in the script and there is no new language to learn first.
Start with the smallest script there is, so you can see where the output goes:
println "Hello from Groovy"
Hello from Groovy
The line appears in the Result Pane at the bottom, which is where everything a script prints ends up.
The three variables a script starts with
A script does not have to open a connection or load the model, because DbSchema injects both before it runs.
sql is the physical database connection, as a Groovy SQL object, so a query is one method call. project is the DbSchema project, which is the way into the schemas, tables and columns of the model and is documented in the DbSchema API. out is the print stream of the Result Pane.
Reading rows takes one closure. The script below runs against this table:
CREATE TABLE address (
address_id int PRIMARY KEY,
city varchar(50) NOT NULL
);
INSERT INTO address VALUES (1, 'Berlin'), (2, 'Lisbon');
sql.eachRow("SELECT address_id, city FROM address ORDER BY address_id") { r ->
println "$r.address_id $r.city"
}
1 Berlin
2 Lisbon
What automation scripts can do
Because a script holds the model and the connection at the same time, the jobs it can take over are the ones that would otherwise mean a person driving the dialogs:
- Reverse-engineer a schema.
- Generate the HTML5 documentation.
- Generate a migration script.
- Load a model from a format of your own, such as CSV or XML.
- Save a model to another format.
- Run queries against the database.
Of those six, only the queries reach the live database, because a script sends them through sql to the connection DbSchema opened. Reverse-engineering reads the database and writes what it finds into the model. The other four work on the model and on files on disk. The HTML5 documentation and the migration script are files a script writes, and a migration script changes a database only when someone runs it.
You do not have to start from an empty editor for any of those. Open Tools → Automation Scripts to manage and run saved scripts. That dialog ships with a library of built-in code samples and points at the DbSchema API reference, so the nearest sample is the shortest way in.
Run a script with nobody at the keyboard
A script you have to sit down and start yourself has automated half the job. DbSchema starts without a UI and executes a Groovy file directly:
DbSchema.exe -x path/to/script.groovy
Anything you put after the script path arrives inside the script as a string array called parameters, which is how one script serves a development database and a production one.
For work that has to happen on a schedule, DbSchemaCLI is included in the same installation package and runs .sql and .groovy files from cron. Put the scripts in the folder named by cli.cron.folder and add a crontab entry that calls dbschemacli -cron. The schedule of each script is encoded in its own file name, so usage_report.schedule_minute(each5).sql runs every five minutes. When a script fails, DbSchemaCLI emails the addresses you configured rather than failing quietly.
Take the job you did by hand last week and write that script first. The download is at https://dbschema.com/download.html: open the SQL Editor, switch the mode to Groovy, and start from the sample nearest to that job. The tasks these scripts drive, the HTML5 documentation and schema synchronization among them, are Pro features, and the download includes a 15-day Pro trial.
