How to start DBDeploy in Gradle

In this post I will show how to start DBDeploy from Gradle script.

What is it all about?

You may already have heard of versioned migration of the database structure. This was written on the Habré . DBDeploy is one of the simplest and most well-known tools that makes it easy to install all the latest changes to the database on any instance and any development machine. And Gradle is now a fashionable tool for building a project (like Ant and Maven, only better). They also wrote about him .

And what is the question?

The question is how to run DBDeploy from a Gradle script? DBDeploy has tasks for Ant and a plugin for Maven, but there is no plugin for DBdeploy yet (more precisely, it is in its infancy). Having a little poke, I came to the conclusion that the easiest way is to use the same DBDeploy Ant task from the Gradle script ( it describes how to use any Ant task from the gradle script). Consider an example.

The task is clear, where do we start?

Let's say our project has the following structure. The SQL scripts are stored in the db folder:

• db
  
  • create_changelog_table.sql
  • 001_create_customer_table.sql
  • 002_create_address_table.sql
  • 003_etc ...
  
  
• build.gradle

Then we can create a Gradle script with 3 tasks:

build.gradle

project.ext {
  dbDriver = 'com.mysql.jdbc.Driver'
  dbUrl = 'jdbc:mysql:///habrahabr'
  dbUsername = 'habra'
  dbPassword = 'habr'
}

task updateDatabase

task updateDatabase << {
  ant.taskdef(name: 'dbdeploy', 
              classname: 'com.dbdeploy.AntTarget', 
              classpath: configurations.compile.asPath)
  ant.dbdeploy(driver: dbDriver,
    url: dbUrl,
    userid: dbUsername, 
    password: dbPassword, 
    dir: 'db',
    dbms: 'mysql',
    undooutputfile: 'db/undo_last_change.sql')
}

task createChangelogTable

task createChangelogTable << {
  ant.sql(driver: dbDriver, 
          url: dbUrl, 
          userid: dbUsername,
          password: dbPassword,
          encoding: 'UTF-8',
          classpath: configurations.compile.asPath) {
      fileset(file: 'db/create_changelog_table.sql')
  }
}

task undoLastChange

task undoLastChange << {
  ant.sql(driver: dbDriver,
          url: dbUrl,
          userid: dbUsername,
          password: dbPassword,
          encoding: 'UTF-8',
          classpath: configurations.compile.asPath) {
      fileset(file: 'db/undo_last_change.sql')
  }
}

How to run it?

Thus, we have 3 tasks:

> gradle createChangelogTable

: createChangelogTable
BUILD SUCCESSFUL

> gradle updateDatabase

[ant: dbdeploy] dbdeploy 3.0M3
[ant: dbdeploy] Reading change scripts from directory / tmp / habr / gradle-dbdeploy / db ...
[ant: dbdeploy] Changes currently applied to database:
[ant: dbdeploy] 1..61
[ant: dbdeploy] Scripts available:
[ant: dbdeploy] 62..62
[ant: dbdeploy] To be applied:
[ant: dbdeploy] 62..62
[ant: dbdeploy] Applying # 62: 062_migrate_currency_to_eur.sql ...
[ant: dbdeploy] -> statement 1 of 5 ...
[ant: dbdeploy] -> statement 2 of 5 ...
[ant: dbdeploy] -> statement 3 of 5 ...
[ant: dbdeploy] -> statement 4 of 5 ...
[ant: dbdeploy] -> statement 5 of 5 ...
[ant: dbdeploy] Generating undo scripts ...
BUILD SUCCESSFUL

> gradle undoLastChange

: undoLastChange
BUILD SUCCESSFUL

Now you can run “gradle createChangelogTable” once, run “gradle updateDatabase” every time even after each script change, and use “gradle undoLastChange” to undo the last changes.

To summarize

Gradle provides a very concise and readable syntax for build scripts, DBDeploy offers a simple and reliable way to push changes to the database. And they worked perfectly together.

Good luck!