Super Simple Database Migrations with Subversion and Bash Scripting

Recently I’ve decided that I really need to get some kind of solid database schema versioning system in place for my PHP apps. Rails has its lovely migrations system, but sadly there’s no equivalent functionality built into the PHP Zend_Db component at the moment. This is probably due to the fact that Zend Framework is a decoupled framework, instead of being a full stack like Rails, so migrations may well never see the light of day.

In my quest for a good PHP solution, I came across Doctrine. It’s an open source PHP ORM (Object Relational Mapper) and DBAL (Database Abstraction Layer), so they tell me, but the part that interests me the most is the migrations. These are scripted in a very similar way to Rails migrations, with up and down methods to facilitate the creation/removal of tables, indexes and fields, and postUp and postDown methods to insert/delete data.

I’ll need to learn Doctrine’s API fully before I can integrate it seamlessly into my apps, but being impatient as I am, I haven’t done that yet. Instead I’ve hacked together a quick and dirty solution that uses Subversion and Bash scripting for the legwork. I thought I’d share it here as it may well be handy for other developers in need of a quick solution.

So, we’ll assume that the following code is a part of a Bash script that you have on your server to deploy new releases…

# Run the latest database migration if it exists
  db_migrations="./working-copy/trunk/migrations"
  db_migration_latest="$db_migrations/latest.sql"
  if [ -e "$db_migration_latest" ]
  then
    current_date=`date +%Y-%m-%d_%H-%M-%S`
    new_filename="migration_$current_date.sql"
    mysql -uYOURMYSQLUSER -hlocalhost -pYOURMYSQLPASSWORD YOURDATABASENAME < $db_migration_latest
    svn mv $db_migration_latest $db_migrations/$new_filename
    svn commit -m "DEPLOY - Ran latest database migration and saved for reference as $new_filename"
  fi

A nice simple way of doing releases is to have a working copy of your Subversion repository checked out on the live server (svn checkout). When you want to deploy a new release, all you have to do is run svn update (you can of course use svn export if you wish to export a clean, full release of your app without the .svn meta-data). In the trunk of your repository you could set it up to have two directories: application and migrations. The application directory would contain your application (website or blog etc.). To add a migration to be applied when you next deploy to the live server, just create a file in migrations called latest.sql. As you change the database schema for new features to be deployed in the next release, you can log the SQL you’ve run to make development database changes in latest.sql. Commit the latest.sql file along with all your other code, then when you deploy the latest release to your live server with the Bash script, it will check to see if latest.sql exists, import it into MySQL if it does, rename latest.sql with the current date and time, and then commit it back to the Subversion repository for archival purposes.

I like this solution as it piggybacks on a version control system (Subversion in this case) and uses raw SQL, removing the need to write custom migration scripts. It’s also language agnostic – always a bonus. The example I’ve given is pretty basic, but it could easily be expanded to support reverting to previous migrations if you also log SQL to revert database changes.

Go forth and migrate!

Discuss

Why not join the discussion?