Grails Liquibase plugin: dbDiff tool workaround

12 / Oct / 2010 by Amit Jain 0 comments

The current project I am working on, is going through QA. At the same time development of the new features and bug fixing is on and we couldn’t afford to loose the test data. So synchronizing the state of the QA database with the development was becoming a pain. So we decided to use grails liquibase plugin.

I found one great article by Jackob Kulzer. As explained by Jackob Kulzer, dbDiff tool in the plugin is hard coded to compare development and test environment databases. The other option available was to write every changeset manually. But I was afraid, what if I forget to update even a single changeset.

I decided to write the script, which would overwrite my test database with the schema of the QA database. QA database was on another server. So I had to write multiple scripts to make it work. But once done it made my job really easy.

Following are the scripts to be created:

1. qaSchemaDump.sh (To be created on the development machine):


#SSH to the server and execute generateSchema shell script.
#Note: I had created password less login to the server.
ssh amit@qa.myserver.net /home/amit/Scripts/generateSchema.sh

#Copy QA Database schema to the development machine
scp apg@qa.myserver.net:/home/amit/Scripts/qaDBSchema.sql /home/amit/Scripts/liquibase/qaDBSchema.sql

#Import QA Database schema to test database using createTestDBFromQASchema.sql
mysql -u root --password=myPassword < /home/amit/Scripts/liquibase/createTestDBFromQASchema.sql
exit

2. generateSchema.sh (To be created on the Server):


/* Creates QA database schema */
mysqldump --no-data --tables -u root --password=myPassword myQADatabase > /home/amit/Scripts/qaDBSchema.sql

3. createTestDBFromQASchema.sql (To be created on the development machine):

/* drop, create and then use the test database.*/
drop database myTestDB; create database myTestDB;
use myTestDB;

/*Import the QA database schema to the newly created test database*/
source /home/amit/Scripts/liquibase/qaDBSchema.sql
exit

Once all the above mentioned scripts are created, we just need to run ‘qaSchemaDump.sh’ and followed by ‘grails dbDiff’ and the changeset would be available to us on the console. Then copy this changeset, append it to changeLog.xml and commit it to the repository.

With all this done, login to your QA/production server, take database backup and now we can run grails migrate-sql to confirm the sql that would be generated and then finally run ‘grails migrate’ to update QA/Production database.

Thanks to Jackob Kulzer and Nathan Voxland (author of the plugin)

Hope this helped!

~~Amit Jain~~
amit@intelligrape.com

http://www.tothenew.com/blog/

FOUND THIS USEFUL? SHARE IT

Leave a comment -