Liquibase Incremental DB Diff

12Dec/094

Liquibase Incremental DB Diff

After discussing this on the Groovy and Grails Exchange, I finally put together a new version of the db diff script that I've mentioned in an earlier post. I couldn't find the original version that I've written a couple of months ago, so I hacked together a new one. It's far from perfect, efficient or beautiful, but it does what it is supposed to.

So, what's the idea behind this script? Everybody that uses Liquibase (if you are not, check out my blog post about what Liquibase is and how to use it) knows how painful it is to update your changelog.xml. Liquibase already gives you a great tool to start with, the db-diff command. However, it is hardcoded to diff the current environment's database against the test database. This is annoying as you'll have to modify your datasources and keep the database schema updated manually. But fear not, here is my (surprisingly) easy solution.

I've created a simple script (or rather, I took the db-diff script and hacked it), which I've called incremental DB diff (all the other cool names are already taken...) and what it does is the following:

update the schema in the db-diff (or whatever you call it) database using the changelog.xml
diff the database of the current environment against this database and output the diff as Liquibase XML

So, how is this a good thing? Easy: this little script will automatically create a target database to diff against using the changelog.xml which is good, as the changelog.xml represents your last migration status. And as it uses a separate database it doesn't influence the test database.

After running the script just put the output in the changelog.xml and you're done.

All you have to do to use this is to drop the file into your /scripts/ directory, create a new database and an according datasource entry for the environment "dbdiff" like this:

	dbdiff {
		dataSource {
			driverClassName = "com.mysql.jdbc.Driver"
			dbCreate = "create-drop"
			url = "jdbc:mysql://localhost/foo_dbdiff"
			username = "foo"
			password = "bar"
		}
	}

Download DbDiffIncremental.groovy. Drop me a mail or a comment if you have questions.

Tagged as: #ggx, Database, Grails, Groovy & Grails Exchange, LiquiBase, Migration, Schema Leave a comment

Comments (4) Trackbacks (4) ( subscribe to comments on this post )

diffing
January 23rd, 2010 - 04:07

Great stuff. This is exactly what I was hoping for.

However, like the original DbDiff.groovy script this does not seem to work with external configurations — i.e. the expected DataSource.groovy does not contain the environment settings (instead they are in an external file located at grails.config.locations — or in this case it does not exist at all).

line:

def testConfig = testConfigSlurper.parse(classLoader.loadClass(“DataSource”))

changed to lines:

def testConfig
try {
testConfig = testConfigSlurper.parse(classLoader.loadClass(“DataSource”))
}
catch (e) {}

if ( testConfig != null ) {
config.grails.config.locations.each({ testConfig.merge(testConfigSlurper.parse(new File(it.split(“:”)[1]).toURL())) })
} else {
testConfig = config.grails.config.locations.inject(new ConfigObject()) { aNewConfig, item -> aNewConfig.merge(testConfigSlurper.parse(new File(item.split(“:”)[1]).toURL())) }
}

Seems to work.
diffing
January 27th, 2010 - 22:52

previous suggestion won’t work for external configurations specified on classpath (needs to be modified).
Jakob Külzer
January 27th, 2010 - 23:23

Agreed, the script is not very flexible but it could easily be improved.

It still requires the user to manually modify the datasources though, because Liquibase is kind of sensitive to different databases. E.g. diff’ing an HSQLDB database against a MySQL database will not go well.

I like the above approach, injecting the configuration during runtime, but it still leaves the question of how to get the database driver right…

Ideas?
Justin Miranda
August 14th, 2010 - 03:35

Thanks for the great posts — I’ve been extracting a lot of good bits of information from your Grails/Liquibase posts. I’m having some difficulty with the diff process (using the Liquibase db-diff or your script). I want to be able to run the ‘dev’ database using dbCreate = ‘create-drop’ so that I can make changes to the domain and then, every once in a while, run the db-diff script to figure out what’s changed and add that to my changelog. However, what I’ve noticed is that the db-diff creates a huge number of changesets for dropping/creating foreign key constraints. This is obviously due to the fact that the “create-drop” will re-create the foreign key indices (with an auto-generated name) each time. So how do you get a reasonable diff if 90% of the changesets are garbage. Do you just parse through the changesets and grab the ones that you want to include in your changelog?