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 firstname.lastname@example.org /home/amit/Scripts/generateSchema.sh
#Copy QA Database schema to the development machine
scp email@example.com:/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
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;
/*Import the QA database schema to the newly created test database*/
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!