Grails database-migration-plugin: DB independent diff files

If you are using Grails database-migration-plugin and your application has to support MySQL as well as Oracle, you have 2 choices currently. As the underlying Liquibase library is currently unable to create real database-agnostic migration files when performing a diff, you can:

• create 2 different sets of migration files, one for MySQL one for Oracle. Drawback of this is, that this is error prone and anything else than DRY.
• Convert the created migration files automatically so they are real database agnostic.

Thanks to the Grails database-migration-plugin hooks (when using database-migration plugin version >= 1.3), we can do that automatically on initial start after creating a new migration file. Migration files are only migrated once, and migrated files will be marked with a special comment to indicate conversion.

In changelog.groovy, define all types you want to use for Oracle and MySQL (you can extend that to support other db types, easily):

databaseChangeLog = {
  
  /*
    DATABASE SPECIFIC TYPE PROPERTIES
   */
  property name: "text.type", value: "varchar(50)", dbms: "mysql"
  property name: "text.type", value: "varchar2(500)", dbms: "oracle"

  property name: "string.type", value: "varchar", dbms: "mysql"
  property name: "string.type", value: "varchar2", dbms: "oracle"

  property name: "boolean.type", value: "bit", dbms: "mysql"
  property name: "boolean.type", value: "number(1,0)", dbms: "oracle"

  property name: "int.type", value: "bigint", dbms: "mysql"
  property name: "int.type", value: "number(19,0)", dbms: "oracle"

  property name: "clob.type", value: "longtext", dbms: "mysql"
  property name: "clob.type", value: "clob", dbms: "oracle"

  property name: "blob.type", value: "longblob", dbms: "mysql"
  property name: "blob.type", value: "blob", dbms: "oracle"

  /* DATABASE SPECIFIC FEATURES */
  property name: "autoIncrement", value: "true", dbms: "mysql"
  property name: "autoIncrement", value: "false", dbms: "oracle"


  /* Database specific prerequisite patches */
  changeSet(author: "changelog", id: "ORACLE-PRE-1", dbms: "oracle") {
    createSequence(sequenceName: "hibernate_sequence")
  }

  /* Patch files */  
  include file: 'initial.groovy'

}

Then create a Callback Bean class for database-migration-plugin and register it in resources.groovy:

migrationCallbacks(DbmCallbacks)

Bean:

import liquibase.Liquibase
import liquibase.database.Database
import org.codehaus.groovy.grails.plugins.support.aware.GrailsApplicationAware;
import org.codehaus.groovy.grails.commons.GrailsApplication

class DbmCallbacks implements GrailsApplicationAware {
  private static final String MIGRATION_KEY = "AUTO_REWORKED_MIGRATION_KEY"
  private static final String MIGRATION_HEADER = "*/ ${MIGRATION_KEY} */"
  // DB-Specific types to liquibase properties mapping
  // see changelog.groovy for defined liquibase properties
  Map<String,String> liquibaseTypesMapping = [
          // start with specific ones, then unspecific ones.
          'type: "varchar(50)"': "type: '\\\${text.type}'",
          'type: "varchar2(500)"': "type: '\\\${text.type}'",
          'type: "varchar"': "type: '\\\${string.type}'",
          'type: "varchar2"': "type: '\\\${string.type}'",
          'type: "bit"': "type: '\\\${boolean.type}\'",
          'type: "number(1,0)"': "type: '\\\${boolean.type}'",
          'type: "bigint"': "type: '\\\${int.type}'",
          'type: "number(19,0)"': "type: '\\\${int.type}'",
          'type: "longtext"': "type: '\\\${clob.type}\'",
          'type: "clob"': "type: '\\\${clob.type}\'",
          'type: "longblob"': "type: '\\\${blob.type}\'",
          'type: "blob"': "type: '\\\${blob.type}\'",
          // regEx (e.g. "varchar(2)" to ${string.type}(2)'. Do not add trailing "'", here!
          '/.*(type: "varchar\\((.*)\\)").*/': "type: '\\\${string.type}",
          '/.*(type: "varchar2\\((.*)\\)").*/': "type: '\\\${string.type}",
          // db features
          'autoIncrement: "true"': "autoIncrement: '\\\${autoIncrement}'"
  ]

 void beforeStartMigration(Database database) {
   reworkMigrationFiles()
 }
 private void reworkMigrationFiles() {
    def config = grailsApplication.config.grails.plugin.databasemigration
    def changelogLocation = config.changelogLocation ?: 'grails-app/migrations'
    new File(changelogLocation)?.listFiles().each { File it ->
      List updateOnStartFileNames = config.updateOnStartFileNames
      if (updateOnStartFileNames?.contains(it.name)) {
        // do not convert updateOnStart files.
        return
      }
      convertMigrationFile(it)
    }
  }
 private void convertMigrationFile(File migrationFile) {
    def content = migrationFile.text
    if (content.contains(MIGRATION_KEY)) return
    liquibaseTypesMapping.each {
      String pattern = it.key
      String replace = it.value
      if (pattern.startsWith('/')) {
        // Handle regex pattern.
        def newContent = new StringBuffer()
        content.eachLine { String line ->
          def regEx = pattern[1..-2] // remove leading and trailing "/"
          def matcher = (line =~ regEx)
          if (matcher.matches() && matcher.groupCount() == 2) {
              String replaceFind = matcher[0][1] // this is the found string, e.g. 'type: "varchar(22)"'
              String replacement = "${replace}(${matcher[0][2]})\'"  // new string, e.g. "type: '${string.type}(22)' "
              line = line.replace(replaceFind, replacement)
          }
          newContent += "${line}\n"
        }
        content = newContent
      } else {
        // non-regEx, so replace all in one go.
        content = content.replaceAll(pattern, replace)
      }
    }
    // mark file as already migrated
    content = "${MIGRATION_HEADER} +"\n"+ content
    migrationFile.write(content, 'UTF-8')
    log.warn "*** Converted database migration file ${migrationFile.name} to be database independent"
  }

This for sure can be optimized (e.g. use only regEx definitions in the map and handle if no matcher groups are found, but it does it's job. 

Tested with MySQL and Oracle 11.0.2 XE.