Tuesday, May 15, 2012

Automated Database Update Or Rollback

One of the important step during release is doing database update and rollback in case something goes wrong, usually people perform this operation manually. In this blog I'll talk about how we can automate this process by following some convention.

Here I'm taking mysql database as an example we can have same conventions for other databases also

Convention to manage rollback/updates of a release
  • Each project codebase at it's root will have a folder database_scripts
  • The database_scripts folder will contain folder for each release i.e Release1_1, Release2_0...
  • The database scripts release folder will in turn contains two folders update & rollback which will contain updates & rollbacks scripts for a release.
Automating the rollback/update
  • The update folder will have a source input file FileSequencer.txt. This file will point to all the update scripts in correct order that needs to be executed for the release
  • In the similar manner rollback folder will have a source input file FileSequencer.txt. This file will point to all the rollback scripts in correct order that needs to be executed for the release
  • At last we will have a utility shell script, this script will take db details and execute all the scripts referred in FileSequencer.txt using mysql command

4 comments

Why do this much of work when there are solutions available to make the SQL scripts part of code deployment. Liquidbase is one of them: http://www.liquibase.org/. We are using it in our projects and it integrates quite well.

Your question is your answer :) if you have to create such a system it will not be more then 20 lines of code in shell scripting

This comment has been removed by the author.

But it will add to the maintenance. Using Liquibase you get the luxury to put that responsibility on each developer and it provide very good logs of old scripts and stops it from running a particular script twice.


EmotionsEmoticon