At The Economist, we've been struggling some to keep our database changes 100% in code. We're required to automate everything using hook_update and hook_install. We don't want to be pushing database imports around, and we want our build scripts to create a current database for testing purposes. Also, we want to see how the database is changing over time. How we do it? The answer might not surprise you--the Rake of Drupal is Drush.

The relative difficultly of this highlights an aspect of Drupal that's lacking in Rails: anyone can build a site without knowing PHP, and many devs (me included, on smaller projects) are perfectly happy clicking around the admin area, building views, creating blocks, and learning modules rather than writing code. Because there's no true site management area in Rails, if you want to make changes like these, you can either manipulate the database in SQL (and lose versioning) or write migrate scripts in Ruby. As a result, they have a much more robust and proven toolkit for these tasks--the drawback is that non-developers are shut out of application development.

Bit of background--Rake is a build tool for Ruby, which is used by Rails to execute schema changes by way of the command rake migrate. For each change, you write an "up" and "down" method. The up method progresses the system one version forward, the down one version back, so you have to write code that installs a feature and can also uninstall it. Our approach is a bit simpler due to API limitations for things like this; we just write upgrade functions.

The use of the install and update functions for contrib modules and your own functions will almost certainly differ. Contrib modules need an install function that will configure the module entirely on a clean system plus update functions that will move things around when you install a new version. Frankly, I'd like it if Drupal executed every update function on module installation and skipped hook_install altogether; the premise being that your lowest numbered update function (6001 for Drupal 6 modules) should install the lowest version of your schema. The only situation in which that would be inopportune is a module with hundreds of complex update functions that constantly back out of prior changes, and it'd only be really poor if it took so long that an unwitting site admin would cancel the module's installation using the browser's stop button because it took so long. Probably unlikely for most modules. We use our update functions like this; they're run progressively on installation. It saves us considerable development time versus building a purely clean install function.

The one potential pitfall to running update functions in this way is if the environment changes, a function might go missing or accept different parameters, which would cause our installation to break--this would be especially obnoxious on our Hudson Continuous Integration environment. So in this way we might find ourselves spending time refactoring old update functions, but this hasn't happened yet. This fact makes our approach much less tenable for contrib modules, which might depend throughout its update hooks on a certain environment configuration (e.g. one version of a function should exist in hook_update_6001 and another in hook_update_6010, but obviously only the most recent version actually exists). And contrib modules generally can't refactor their previous update functions either.

These problems aren't so severe in Rails because the migration system is generally simpler and on many applications would just involve creating database tables, for which there is seriously beautiful syntax--typical of Ruby. Managing your site's changes over time is critical to any framework--these tools aren't shipped with Drupal, like rake migrate is with Rails, but with a little bit of work, you can get very close!

Key Concepts in Automating Upgrades in Drupal

• A current and widely discussed limitation of Drupal is its lack of API functions. There's sometimes no way around directly manipulating the database, but the Install Profile API is a great start.
• Build your views and CCK types in the admin UI, then export them to code and install the exports using hook_update and the Install Profile API.
• Download, install, and get to know Drush really well. Knowing and using Drush will make you efficient anyways.
• Write build scripts in bash, make, or ant to install a ready-to-go version of your site with no database import required. This will make automating your unit tests much easier.
• One bootstrap module with update functions that install other modules is effective. We use a Google spreadsheet to claim update numbers for our bootstrap module and have placed the URL to this document at the bottom of install file. You can and should install contrib modules this way too, but drupal_install_modules() will not fail if one or more dependencies listed in the install file are unmet, so be careful!
• Get in the habit of writing your updates in code first, then running drush updatedb from the command line rather than making the changes in the admin area and then putting them in code to run later or on a CI server. The exceptions I make are for Views and CCK, which need to be exported to array structures.

Code snippets

Install function which iterates over hook_update functions

function ec_channel_install() {
  // Execute our defined updates.
  $version = 6001;
  while (function_exists('ec_channel_update_' . $version)) {
    call_user_func('ec_channel_update_' . $version++);
  }
  // hook_install has no return value
}

Create a content type

function ec_channel_update_6001() {
 
  install_include(array('node'));
 
  // Create the Channel Page content type.
  $props = array(
    'description' => t('Channel page, contains news packages, stories and blocks.'),
    'has_title' => TRUE,
    'title_label' => t('Channel Name'),
    'has_body' => FALSE,
  );
  install_create_content_type('channel', 'Channel Page', $props);
  return array();
}

Install Some Modules

function ec_channel_update_6003() {
  drupal_install_modules(array('content_multigroup', 'fieldgroup', 'link'));
  return array();
}

Import CCK fields

function ec_channel_update_6002() {
  $ret = array();
 
  // Economist Story node reference field.
  install_include(array('content', 'content_copy'));
  install_content_copy_import_from_file(drupal_get_path('module', 'ec_channel') .'/'.'freeform_story_link_econ_story.type', 'freeform_story_link');
  return $ret;
}

Related

• Rails Migration API
• Another way to do this in Drupal
• Discussion on how to implement this in Django