Put your database under version control

Tuesday, 12 January 2010


In this tutorial I want to look at some strategies for including your database as part of your version control strategy.

Over the years that I have been writing web applications the majority of the sites I have worked on include some sort of database back-end. Whether they be large scale web applications or simply a sign-up for a newsletter, the database is an integral part of the system. It may come as a surprise that it has only been in the most recent of applications that I have started including the database as part of our version control strategy.


If you haven't started to include your database as part of your version control I would like to point out some of the benefits realised and convince you of why this should also become your habit and part of your regular release cycle or even if it's a single site, part of your site release / update regime.

Most of you should be are most definitely backing up your database regularly, so I won't be covering that area.

To kick things off I will explain to you how we were previously doing things before our recent changeover to versioning our database. In the not so distant past there were five of us working on different parts of the same app. Each of us were working off of the same database, which meant even if we weren't aware that someone was changing the database, there was a small chance of you finding out incidently that a change was made to the table you were working on (queue scream). We would each work on our separate parts and before Beta release day we would all come together and apply our changes to a clean database that would be used for production once Beta was over. Sounds scary right? It was worse than that; we all kept a text file with aforementioned database changes that could be thousands of lines of stored procedures, triggers and table updates. We did manage to make it work without huge dramas, but it's a terrible way to work.

You can imagine if someone forgot to add their changes or held off adding changes or their changes needed to be reverted. The exact reason that we have version control for all our files, so why the hell not for your database? Your situation may (or may not) be as bad as what is mentioned above but you can see the benefits of including your database as part of your versioning: who changed it, when was it changed, can we roll back to a previous version, what should be included in this release? All of these things were not being answered before and we also couldn't track the most basic problem 'if something had been changed?'. Our CRM has hundreds of tables and stored procedures (not an exaggeration), it is God damn huge, so it's not an easy task to manually verify if something is changed or missing. You can now start to see a big problem requiring an easy solution.

We had discussed the problem several times and could not come up with a suitable solution. We wanted something unobtrusive that would be less trouble than what we already had. With any IT team we are always looking for the solution which requires zero effort, has zero loop-holes in case someone forgets to (or is too damn lazy) to do it and is a complete solution to the problem (partial solution = likely fall back to personal methods = half of us do it this way and half of us do it our own way).

The search for a solution got put onto the shelf (inertia) for some time until I came across a query to generate the CREATE script for any table or stored procedure, after this everything else fell in to place. I'll run you over our steps for keeping our database inside version control:

Components:
* Database table; each row contains the name, type and script to (re)create our stored procedures or tables.
* Stored procedure; empties the above mentioned table and then repopulates with the current state of our database.
* php script; Calls the stored procedure then retrieves all the rows from the table. Loops over each row then stores (or updates) an invdividual file which is a .sql file with the CREATE statement. These files are stored inside /db/stored_procedures or /db/tables folder.

Steps:
* Developer makes a change to a table or stored procedure
* Developer finished making any changes to scripts and any other files that will form part of version control commit
* Developer runs the php script above so that database files stored inside the repository are updated
* Developer commits to version control and writes a lovely message for all to see
* Other developers update their repositories and are aware that changes have been made to the database

All is good, yay! But there is still one small issue, the zero loop-holes, what happens if someone forgets to commit there database changes? This is covered in our release cycle. Whoever is responsible for the release MUST run the php script and if there are any database changes that have not been committed yet then we have a slight problem. This is usually solved by a bit of finger pointing. We also have an agreed rule, if it doesn't belong to anyone and was written by some phantom menace then we just revert back to the previous version (this hasn't happened yet because I work with such lovely people).

Hopefully this gives you a good insight into how you can get your database into version control. It is hard for me to provide working examples as there are so many different combinations of databases and scripting languages. I have provided some links below that I used to get our MSSQL database under version control.

How to generate a CREATE TABLE statement for a given table in SQL server

For your stored procedures you can do something like the following:
protected function export_storedprocs() {
 $sp_dir = $_SERVER['APPL_PHYSICAL_PATH'] . 'dbstored_procs\';

 // Retrieve all SP names
 $strSQL = "SELECT Distinct SO.Name
    FROM sysobjects SO (NOLOCK)
    WHERE SO.Type = 'P'
    ORDER BY SO.Name";
 $sps = $this->fetchrows($strSQL);
 
 foreach ($sps as $sp) {
  $strSQL = "EXEC sp_helptext {$sp['Name']}";
  $splines = $this->fetchrows($strSQL);
  if ($splines) {
   $h = fopen($sp_dir . $sp['Name'] . ".sql", "w");
   foreach ($splines as $line) {
    fwrite($h, $line['Text']);
   }
   fclose($h);
  }
 }
 
 echo "Export complete";
}

Also checkout: worthy reading on the same topic
blog comments powered by Disqus