Automation Scripts

An automation script is a small program that does a task for you, such as adding the same column to many tables. DbSchema runs scripts in Groovy, a language that reads like Java and runs every Java statement. A script can read and change the model, run SQL in the database, and generate documentation.

Open the Automation Scripts dialog

The dialog keeps your own scripts and the examples that come with DbSchema.

1. Choose Query → Automation Scripts.
2. Your scripts are under My Scripts.
3. The examples are under Examples, in the folders Basic, Design, Documentation, Export, Import, Schema Synchronization and Tests.

The About tab lists the variables that a script receives. The Examples are read-only. To change one, copy its text into a script under My Scripts. DbSchema API at the bottom opens the API reference.

Create and run a script

1. Right-click My Scripts and choose Create Groovy Script.
2. Type a file name and save. DbSchema creates the empty script and opens it in a tab.
3. Type the script.
4. Press Run.

The output shows under the script, after the message Execution Succeed. DbSchema saves the scripts of My Scripts when you close the dialog.

You can also run Groovy in the SQL Editor: switch its language button from SQL to Java Groovy.

What a script can use

A script in DbSchema receives these variables:

Variable	What it holds
project	The model that is open: its schemas, tables, columns and diagrams.
sql	The connection to the database, as a Groovy Sql object. It exists only while the model is connected.
out	The output under the script. println writes there too.

The classes of the model are in the package com.wisecoders.dbschema.schema: Project, Schema, Table, Column and the rest. The API reference describes each of them.

List the columns of a table

project.getSchema() finds a schema by name, and getTable() finds a table in it.

// Prints the columns of the customers table, with their data types
def customers = project.getSchema('shop').getTable('customers')
for (column in customers.columns) {
    println "${column.name} ${column.typeString}"
}

Add a column to the model

createColumn() takes the column name and the data type, as the database writes it.

// Adds the column birth_date to the customers table in the model
def customers = project.getSchema('shop').getTable('customers')
def birthDate = customers.createColumn('birth_date', 'date')
birthDate.comment = 'Used for birthday offers'
println "Added ${birthDate.name} ${birthDate.typeString} to ${customers.name}"

A script changes the model only. The new column reaches the database when you apply the model to the database.

Drop a column

Dropping a column takes two steps: markForDeletion() marks it, and project.refresh() removes everything that is marked.

// Drops the column phone from the customers table in the model
def customers = project.getSchema('shop').getTable('customers')
def phone = customers.columns.getByName('phone')
phone.markForDeletion()
project.refresh()
println "Dropped ${phone.name} from ${customers.name}"

This script drops the column from the model only. Database → Create or Upgrade Model in the Database adds what the model has and the database lacks, but it does not drop the column. To drop the column in the database too, run the statement from the script:

sql.execute('ALTER TABLE shop.customers DROP COLUMN phone')
sql.commit()

The script tab does not commit by itself, so sql.commit() saves the change. You can also press Commit in the tab's toolbar.

Build the model from a CSV file

A script can read any file with plain Groovy. This one reads lines of the form table,column,data type and creates the tables and columns that are missing.

suppliers,supplier_id,integer
suppliers,name,varchar
suppliers,email,varchar

// Creates tables and columns in the model from a CSV file: table,column,data type
def shop = project.getSchema('shop')
new File('C:/data/columns.csv').eachLine { String line ->
    def (tableName, columnName, dataType) = line.split(',')
    def table = shop.getTable(tableName) ?: shop.createTable(tableName)
    table.createColumn(columnName, dataType)
    println "${tableName}.${columnName} ${dataType}"
}

The new table is in the model, but on no diagram yet. For CSV files with quotes or commas inside values, the Import examples use a CSV reader that handles both.

Apply the changes to the database

1. Choose Database → Create or Upgrade Model in the Database.
2. Select the schemas and press OK.
3. The Execute Script In Database Dialog shows one statement at a time. Press Execute to run it, Skip to leave it out, or Close to stop.

Schema Synchronization explains this dialog and the comparison with the database.

Run SQL from a script

sql runs statements in the database that the model is connected to. eachRow() reads a query row by row.

// Counts the orders in the database, by status
sql.eachRow('SELECT status, count(*) AS orders FROM shop.orders GROUP BY status ORDER BY status') { row ->
    println "${row.status}: ${row.orders}"
}

Run a script from the command line

DbSchema can run a script without opening its window, for example in a scheduled task. Start DbSchema.exe with -x and the full path of the script. The words after the script path go to the script.

"C:\Program Files\DbSchema\DbSchema.exe" -x C:/scripts/list-tables.groovy C:/models/shop.dbs > list-tables.log

A script started this way has no project and no sql. It receives out and parameters: parameters[0] is the script path, and your own words start at parameters[1]. To work with a model, the script loads the model file itself:

import com.wisecoders.dbschema.schema.store.ProjectLoader

def loader = new ProjectLoader()
new File(parameters[1]).withInputStream { input -> loader.parse(input) }
for (schema in loader.project.schemas) {
    for (table in schema.tables) {
        println "${schema.name}.${table.name}"
    }
}

DbSchema writes the output to the standard output, together with its own log lines. The > list-tables.log at the end of the command saves all of it to a file. The lines of the script are marked STDOUT and come last:

11:25:01.613 [INFO ] STDOUT                         - DbSchema 10.5.2 build 261006.405
11:25:01.617 [INFO ] STDOUT                         -
11:25:01.620 [INFO ] STDOUT                         - Execute Script 'C:/scripts/list-tables.groovy' as Java Groovy.
11:25:01.621 [INFO ] STDOUT                         -
11:25:04.129 [INFO ] STDOUT                         - shop.addresses
11:25:04.130 [INFO ] STDOUT                         - shop.categories
11:25:04.131 [INFO ] STDOUT                         - shop.countries
11:25:04.132 [INFO ] STDOUT                         - shop.customers
...
11:25:04.138 [INFO ] STDOUT                         - shop.wishlists

DbSchema ends with exit code 0 even when the script fails, so check the output for an error.

To run scripts that connect to a database from the command line, you can also use DbSchemaCLI.

Common problems

What is the syntax to run a script on macOS?

The About tab of the dialog gives this command for macOS:

/Applications/DbSchema/DbSchema.app/Contents/MacOS/JavaApplicationStub -x /path/to/script.groovy

Give the full path of the script, as on Windows.

DbSchema stops with a NullPointerException when I run -x

Give the full path of the script, such as C:/scripts/list-tables.groovy. A path relative to the current folder fails in DbSchema 10.5.2.

The script fails with "No such property: sql"

The script has no database connection. Either the model is offline, or the script runs with -x, which never receives sql. Connect the model, or open the connection in the script with groovy.sql.Sql.newInstance(...), as the example Create Database Connection shows.

The script fails with "unable to resolve class com.wisecoders.dbs..."

The package of the DbSchema classes is com.wisecoders.dbschema. Change the imports of older scripts from com.wisecoders.dbs to com.wisecoders.dbschema.

Next steps

• Run scripts and SQL on many databases with DbSchemaCLI
• Read the DbSchema API reference
• Compare the model with the database
• Write SQL in the SQL Editor