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.
- 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