Continuous Integration and Database Management

Quoting Jeff Atwood in the excellent Get Your Database Under Version Control post:

...

I was thinking about this again because my friend and co-author K. Scott Allen just wrote a brilliant five part series on the philosophy and practice of database version control:

1. Three rules for database work
2. The Baseline
3. Change Scripts
4. Views, Stored Procedures and the Like
5. Branching and Merging

...

Really, the whole series is worth the read even if many of you seems to be specially interested by the 3rd part. And BTW, have a look at the Bulletproof Sql Change Scripts Using INFORMATION_SCHEMA Views article mentioned in the 3rd part too. You may already be aware of that but it explains amongst other good practices why writing idempotent change scripts is important.

Regarding tooling, you might want to check out UpToDater (code centric), LiquiBase (xml based) or... dbdeploy, a little gem based on real-world experiences of software development in ThoughtWorks. It's not that the 2 first one are not good but this one is my preferred (and is available for Java, PHP or .NET).

I tend to function best with 'migration' scripts, which are the next stage up from a simple versioned script. With a migration, you specify the changes to the database (additions, removals, etc) and also how to undo the changes your migration is performing. This is then tagged with a version of some form that won't clash with other developers. A particularly good version number is the current time (in either YYYYMMDDHHMMSS format or just as seconds from the epoch). This is a good choice because you are very unlikely to get version clashes and it is still very easy to see if new versions exists due to the strictly increasing nature of such timestamps.

Note: This is very much influenced by the migration system in Rails. For more details and ideas I would highly recommend looking into that system.

Rails migration:

class CreateGroups < ActiveRecord::Migration  def self.up    create_table :groups do |t|      t.string :name      t.references :owner      t.timestamps    end  end  def self.down    drop_table :groups  endend

Doctrine migration:

class CreateGroups extends Doctrine_Migration{    public function up()    {      // Create new author table      $columns = array('id'   => array('type'          => 'integer',                                       'length'        => 4,                                       'autoincrement' => true),                       'name' => array('type'          => 'string',                                       'length'        => 255),                       'owner_id' => array('type' => 'integer',                                            'length' => 4));    $this->createTable('groups', $columns, array('primary' => array('id')));    }    public function down()    {    $this->dropTable('groups');    }}

(sorry for lack of timestamps in doctrine... in rails the timestamps call adds in created_at and updated_at fields to the table that are automatically managed for you. I'm not sure of similar behaviour in doctrine so I left them out).

The technology stack (including the database used) wasn't described in the question, which is very relevant to which solution might be most suited.

A highly popular Java-centric migrations solution is Flyway.DBUp is very similar but focuses on the .NET stack.

Here at Redgate we offerReadyRoll, which integrates tightly into Visual Studio and works exclusively against SQL Server.