Continuous Integration and Database Management

Question

When working on a project with several other people it's common to have several people with differente areas, such as the database.

My challenge is how to let several people edit the database model in a continuous integration environment.

A developer has suggested writing a "versioning script" where each edit was entered into a .sql script, with a version-number that the database would be able to detect. A new addition to the model would in this file be tagged with a version, and the database would be updated once the script had been submitted and a build had been run.

I've also heard about Publisher/Subscriber... and read a bit about it.

How do you manage this situation in your daily work, and what suggestions can you give me to make the database-changes run as seamless as possible ?

** Edit **

Migration frameworks and migration-scripts have been mentioned. If you have some practical experience and would suggest a framework, that would also be appreciated.

Answer 1

Check out migration frameworks. AFAIK, the idea came from rails, but people have built frameworks for pretty much everything else at this point.

Answer 2

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

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

Answer 3

It depends.

If this is a released product you're talking about, schema edits would need to be carefully tracked so you can plan your upgrade process. It would be good to start thinking about that now, so the "versioning script" makes some level of sense. But backward/forward compatibility is usually only a user-visible requirement, not a "between compiles" requirement. Between releases, it would make sense to maintain an upgrade script that modifies the database tables to bring it up to the new schema.

If this is a new/unreleased product, what do you care if someone changes the schema? And why would you even want to keep the database between continuous integration builds? You should be able to regenerate any test data with an automated test, anyway. Anyone who changes the schema should update the tests as well, though.

For a released product, you'd probably want to have a set of tests that can deal with a "version 1.0" database, to make sure it can upgrade successfully to "version 1.1" (for example).

Answer 4

We script everything in Subversion and check it into the project just like any other code. All database deployments are done from scripts pulled from the source control system. If two people happen to be working onthe same script (which is fairly rare) Subversion will let you merge the two scripts.

Answer 5

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