Capistrano and Drupal

I've been messing around with Capistrano over the last few days. The system is best known for deploying Rails applications, but at its heart it's really just a Ruby-based way of making it easier to manage repetitive server tasks over SSH.

There's one task in particular that I've used it to automate, and which you might find useful, too. Here at EchoDitto we're spread out over a number of offices — DC, Boston, New York, and often who-knows-where-else. This presents some development challenges when collaborating on sites built on a system that, like Drupal, puts alot of site configuration in the database, where version control systems can't be used to manage it.

Of course we do development locally when possible, but we also run a remote dev server to let us collaborate off a single database (and to allow clients to see our work). The obvious solution is to tunnel our local server's database traffic to the central server, allowing different developers' filesystems to connect to the same datastore. But this is slooow.

So other options: setting up MySQL replication? Well, that seems like overkill, and precludes offline development. The solution I've come up with is automating a simple database dump from the dev server. When I need to make config changes I do it on the dev site; when I'm working in the filesystem and need fresh data, I simply invoke a command to pull down the dev DB. For most sites this only takes a few seconds to run, and invoking it is as simple as running `cap get_db` from anywhere within the dev site directory structure.

I used to do this with a somewhat horrible bash script. But it made various assumptions about filesystem and had to be modified for each new local project. Capistrano has let me make this a bit more abstract and reusable. The following script still makes a few assumptions about our servers and configuration, but you might find it useful, too.

Among those assumptions:

• There's no password on the local database. This could be easily changed, but I just don't bother -- if they've got access to my laptop, security has failed badly enough that I'm already in trouble.
• Your dev site directories follow a common naming standard -- for us it's xxx.dev.echoditto.com
• The master database credentials are stores in sites/default/settings.php. Presumably you'll override these with other entries in the sites/ directory for local development.

Without further ado, here's my .caprc. Like pretty much all Capistrano stuff, it assumes you have public key access set up for your dev server.

SSH_CREDENTIALS = "yourusername@your.dev.server.com"

role :dev, SSH_CREDENTIALS

require 'Time'

desc "Dump the database and load it locally"
task :get_db, :roles => :dev do
  
  # edit this line to match your dev server directory scheme
  # this code assumes that your dev site configs are in 
  # directories like:
  # /Users/yourusername/Sites/someclient.your.dev.server.com/sites/default/settings.php
  site_root = `pwd`.strip.sub(/^(.*?)\.your\.dev\.server\.com\/.*$/i,'\1.your.dev.server.com')
  
  # assumes that dev DB settings will be specified in sites/default/settings.php and overridden for local development
  mysql_hits = `grep -hi "mysql://" #{site_root}/sites/default/settings.php`
  
  db_info = mysql_hits.split("\n")[-1].scan(/mysql:\/\/([\w_\-\.]*):([\w_\-\.]*)@([\w_\-\.]*)\/([\w_\-\.]*)/)

  if(db_info.length>0)
    DB_USER = db_info[0][0]
    DB_PASSWORD = db_info[0][1]
    DB_HOST = db_info[0][2]
    DB_NAME = db_info[0][3]
    MYSQL = '/usr/bin/mysql'
    
    # create a one-time filename
    now = Time.now.to_i
    outfile = "#{DB_NAME}-#{now}.sql"

    # dump the database, zip it up, transfer it, uncompress it
    run "mysqldump -p#{DB_PASSWORD} -h#{DB_HOST} -u#{DB_USER} #{DB_NAME} > /tmp/#{outfile}"
    run "nice gzip -9 /tmp/#{outfile}" # we zip it this way rather than during scp for better compression and to control the CPU hit
    `scp #{SSH_CREDENTIALS}:/tmp/#{outfile}.gz /tmp/#{outfile}.gz`
    `gunzip /tmp/#{outfile}.gz`

    # assumes that the local database runs as root with no password
    `echo "DROP DATABASE IF EXISTS #{DB_NAME};" | #{MYSQL} -u root`
    `echo "CREATE DATABASE #{DB_NAME};" | #{MYSQL} -u root`
    `#{MYSQL} -u root -D#{DB_NAME} < /tmp/#{outfile}`

    # clean up the temporary files
    `rm /tmp/#{outfile}`
    run "rm /tmp/#{outfile}.gz"
  else
    STDOUT.puts('Could not find dev database information! Is it in sites/default/settings.php?')
  end
   
end

Some might note that Capistrano is ordinarily used for deploying to a server rather than pulling from it. This is true. It's just that we don't have a use for cap in that role, as we've got a separate, custom deployment system built around SVN triggers and a Ruby daemon. Capistrano's still awfully useful for other tasks, though.