How to Automate Database Tasks Using DbSchema

Design • 10-Mar-2021

How to Automate Database Tasks using DbSchema

DbSchema is a desktop application for database design and management.
It offers a visual interface for designing the schema, generating documentation, running queries, etc.
But how about automating these operations, and executing them without human interaction?

First, let’s have a closer look at the SQL Editor.
We can open it from the SQL menu.

Open SQL Editor

In the SQL Editor toolbar, we can choose between SQL and Java Groovy.
Groovy is pure Java with closures.
Anybody how knows Java or JavaScript can write Groovy Code.

Groovy

Let’s practice first a simple command: print line.

Println

The Groovy code will get the sql variable as the database connection and project as the DbSchema model object.

Groovy Variables

Then let’s have a look at the DbSchema Help / Code Samples.
Here we can see a link to the DbSchema API, as well as different code samples.

Code Samples

What can be the automation scripts used for?

  1. Schema reverse engineer
  2. Generate HTML5 documentation
  3. Generate migration scripts
  4. Load the model from a custom format, like CSV, XML, etc.
  5. Save the model to another format
  6. Execute database queries.