GS Design Just another developer blog

IF you just got into versioning your code then you are probably really happy with your acomplishment.
But do you have your database versioned ? This is the questioned asked in this here by Jeff Atwood and also asked by a lot of users on stackoverflow.

After reading a lot on this topic i decided on a strategy on how to keep my database under version control.
As a warning it isn’t the most easy strategy and it really takes some discipline from all the developers.

The strategy

The main ideea is to have a folder with this structure in your project base path
/__DB
—-/changesets
——–/1123
—-/data
—-/tables

Now who the whole thing works is that you have 3 folders:
Tables
Holds the table create query. I recommend using the naming “table_name.sql”.

Data
Holds the table insert data query. I recommend using the same naming “table_name.sql”.
Note: Not all tables need a data file, you would only add the ones that need this initial data on project install.

Changesets
This is the main folder you will work with.
This holds the change sets made to the initial structure. This holds actually folders with changesets.
For example i added a folder 1123 wich will contain the modifications made in revision 1123 ( the number is from your code source control ) and may contain one or more sql files.

I like to add them grouped into tables with the naming xx_tablename.sql – the xx is a number that tells the order they need to be runned, since sometimes you need the modification runned in a certain order.

Note:
When you modify a table, you also add those modifications to table and data files … since those are the file s that will be used to do a fresh install.

This is the main ideea.

Improvements you can do

This is the basic ideea of how i version my database structure. I am sure this is not the perfect way of versioning your database but this is the own that works for me.
If you want you can modify the structure and add 2 subfolder for each changeset : up & down … and this way you could have a way of upgrading and downgrading your database.

Automation

Since running those queries manually isn’t so enjoyable i wrote a small class to automate the process a little.
The class is posted on phpclasses.org and is available for download here.
Warning: The class is still in beta, so be careful when first using it. and always backup )

The main idea is that the class saves the last changeset runned in mysql and always checks if there are any new change sets. When you see any commits with database change sets you access /__DB/index.php and the class does the upgrade for you.

I await your comments
Cheers