Agile Database Deployment Using Phing

Phing allows you to use SQL to define changes to your database schema, making it possible to use a version control system to keep things synchronized with the actual code.

A common way to automate development and deployment tasks is by writing shell scripts, however, Phing provides some advantages over shell scripts for task automation. Most of the shell scripts I created so far help me automate the most tedious tasks, from configuring, building, testing and documenting applications to synchronizing files and migrating database schemas. But, having a large collection of shell scripts can lead to a maintenance nightmare, reason why I decided to port some of them to PHP as Phing tasks. I chose Phing because it’s simple, powerful and very easy to extend.

DbDeployTask

Phing offers an optional task for making revisions to a database, it’s called DbDeployTask. Dave Marshall wrote a nice article about it. After adding this task to my build script and testing it, I came to the conclusion that DbDeployTask is not very effective if you want to roll out incremental changes to the databases. Also, it introduces new problems to the database migration process.

For example, if you use DbDeployTask, the SQL script names must begin with a number that indicates the order in which they should be run:

basedir/
  database/
    deltas/
      1-create_foo_table.sql
      2-alter_foo_table.sql
      3-add_constraint_id_to_foo.sql
    history/
      up_20080622120000.sql
      down_20080622120000.sql
  public/

But, what happens when 2 or more developers are making changes to the same database design? How do they know which scripts have been executed? What if they don’t use a central development database, how do they merge individual changes? To be honest, I don’t know.

Agile Database Deployment

A more Agile way to manage database changes is for the developers to connect to a central development database. After making any changes to the database, the developers will have to:

  1. Update the local database/ directory before modifying any script.
  2. Copy the changes made to the database to the corresponding SQL file (up and down).
  3. Check the files back into the repository.

Finally, the changes can be propagated to other databases by executing a script or setting up a Cron job.

This is how the database directory can be structured:

basedir/
  database/
    down/
      alter/
      constraints/
      create/
      data/
    up/
      alter/
      constraints/
      create/
      data/
  public/

To put your database definition under version control, you need an off-line representation of that database. The directory up/ is used when migrating to a new version, down/ is used to roll back any changes if needed. These directories contain all the changes made to the database:

up/
  alter/
    foo.table.sql
  constraints/
    foo.table.sql
  create/
    foo.table.sql
  data/

This directory structure is based on the one suggested by Mike Hostetler, and it clearly offers more advantages. After the team has made all necessary changes, the next step is to deploy the schema. If you are responsible for performing that task, you can get the most recent version of the database project from version control, build the deployment script, manually update that script as necessary, and then run the script to deploy the schema changes into development, staging or production.

Learn More

Database Schema Deployment (PDF) – By Lukas Smith

About these ads

7 thoughts on “Agile Database Deployment Using Phing

  1. We currently have 5 developers working on the same codebase, we communicate the old fashioned way before commiting new delta files, that way they stay sequential, although a simple pre-commit hook could stop people commiting files with the wrong number.

    The real challenge comes when branching your codebase, when I’ve got that running smoothly enough, I’ll write about a slightly improved method of deploying database changes.

  2. Dave, I’ve been following your blog lately, your posts are very interesting (and inspiring as well). I look forward to reading more about this, thanks.

  3. Pingback: New Wiki and SVN Repository

  4. Hey, nice article. But I’ve a question: Your example shows just one change… up/{create,alter}/foo.table.sql

    but if you’d more than 2 changes and these changes must be executed at at the same order how do you deal with this?

    At my job we have a central database with a few metadata tables where we place all the changes. So, a build task catch all the changes since the last release and put on just one update_db.sql file. That we use to upgrade the live databases of our customers.

    What you think about this approach?

  5. Pingback: Complete PHP Development Environment on OSX – Step 2: Phing

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s