Automation Scripts

DbSchema can automate database tasks using Java Groovy scripts with direct access to the DbSchema API. Groovy is 100% compatible with Java — everything that works in Java works in Groovy.

Groovy scripts are used in two places in DbSchema:

  • Automation Scripts — full scripts accessed from Tools → Automation Scripts or run directly in the SQL Editor by switching the language to Groovy
  • Groovy Templates — inline expressions used in SQL statement templates configured under Model → Settings → Database Specific → SQL
Automation Scripts Dialog

Writing Groovy Scripts

Groovy scripts run against the connected database. The following variables are injected automatically:

VariableDescription
sqlPhysical database connection (Groovy SQL object)
projectThe DbSchema project — schemas, tables, columns, etc. (API docs)
outThe Result Pane print stream

Example — iterate over query results:

sql.eachRow("select * from address") { r ->
    println "Address id: ${r.address_id}"
}

String multiline = """I am a multiline
text"""

Scripts can be executed directly in the SQL Editor by switching the mode from SQL to Groovy. You can also manage and run saved scripts from Tools → Automation Scripts, which includes a library of built-in code samples.

Groovy Templates

Groovy templates are text strings where ${...} expressions are evaluated at runtime. They are used to customise the SQL statements DbSchema generates, configured under Model → Settings → Database Specific → SQL.

Example template:

ALTER TABLE ${table} DROP COLUMN ${column}

Expressions can contain conditional logic:

${ name == 'test' ? 'AAA' : 'BBB' }

To embed executable code in a template, use <% ... %> blocks, for example <% println 'Test' %>.

Running Scripts in Headless Mode

DbSchema can start without a UI and execute a Groovy script directly from the command line:

DbSchema.exe -x path/to/script.groovy

Or using the Java launcher:

java -cp "lib/*" com.wisecoders.dbs.DbSchema -x path/to/script.groovy

Any additional arguments after the script path are passed to the script as a string array in the variable parameters.