In this post I will describe probably the easiest one of the database version control schemes, which uses backups in order to keep the different versions of the database along with the code.
When making a change to a database, you need to follow these steps:
- Get the latest backup from the source control.
- Restore the database.
- Apply the changes.
- Update the version of the database in a “Version” table. (this step is optional but recommended)
- Perform a backup.
- Commit the new backup overriding the previous file.
The “Version” table to which I made reference in step 4, it’s a table that keeps either the history of the different versions, or the current version of the database in one record. In case you want to have the history of the changes made, you could have columns such as: version, date created, created by and description.
Note: In case you are using an ORM framework for your data access, then you should include the tasks necessary to update the ORM code in order to commit everything together.
- Easy. It is pretty straight forward and very easy to use and learn.
- Easy to automate. In most database engines you can perform backups and restores from a command line, making this process even more easy and automated.
- Clean ups in every update. Because you have to perform a restore you will lose all the data (testing data probably) every time you want to update your database.
- If somebody updated the database while you were making a change yourself, then you will have to merge the two databases.
- Data migration problems. This is a common problem to most database versioning mechanisms. It can happen in two different situations, first developers merging their databases with the latest version may require migrating data from one table to another in order not to lose what they have locally. Second it will most likely happen upon every Release.