Using DBDeploy in Gradle

As you probably know, DBDeploy is a tool for easily and automatically installing database changes. And Gradle is a next-generation build automation tool (like Ant and Maven). The question is, how to use DBDeploy in Gradle scripts? It seems that DBdeploy doesn’t have Gradle plugin yet, nor has Gradle DBDeploy plugin. After some experimenting we found out that the easiest way is to reuse dbdeploy ant task. Let’s see the example below. Assuming that our project has “db” folder with all the sql scripts:

• db

  • create_changelog_table.sql
  • 001_create_customer_table.sql
  • 002_create_address_table.sql
  • 003_etc…

• build.gradle

We can create a Gradle build file containing 3 tasks:

build.gradle
project.ext {
  dbDriver = 'com.mysql.jdbc.Driver'
  dbUrl = 'jdbc:mysql:///codeborne?useUnicode=yes&characterEncoding=UTF-8'
  dbUsername = 'codeborne'
  dbPassword = 'codeborne'
}


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

Now we have 3 gradle tasks:

> gradle createChangelogTable

:createChangelogTable
BUILD SUCCESSFUL

> gradle updateDatabase

[ant:dbdeploy] dbdeploy 3.0M3
[ant:dbdeploy] Reading change scripts from directory /home/andrei/projects/blog-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 must run “gradle createChangelogTable” once and then execute “gradle updateDatabase” so much as you wish, doing “gradle undoLastChange” to rollback the latest changes (until you committed you changes!) The bottom line is: Gradle has a very concise readable syntax for build scripts, DBDeploy is a simple and stable way for applying database changes. They just work fine together.Happy databasing!