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