Migrating database changes from development to live

Question

Perhaps the biggest risk in pushing new functionality to live lies with the database modifications required by the new code. In Rails, I believe they have 'migrations', in which you can programmatically make changes to your development host, and then make the same changes live along with the code that uses the revised schema. And roll both backs if needs be, in a synchronized fashion.

Has anyone come across a similar toolset for PHP/MySQL? Would love to hear about it, or any programmatic or process solutions to help make this less risky...

Answer 1

I've never come across a tool that would do the job. Instead I've used individual files, numbered so that I know which order to run them: essentially, a manual version of Rails migrations, but without the rollback.

Here's the sort of thing I'm talking about:

000-clean.sql         # wipe out everything in the DB
001-schema.sql        # create the initial DB objects
002-fk.sql            # apply referential integrity (simple if kept separate)
003-reference-pop.sql # populate reference data
004-release-pop.sql   # populate release data
005-add-new-table.sql # modification
006-rename-table.sql  # another modification...

I've never actually run into any problems doing this, but it's not very elegant. It's up to you to track which scripts need to run for a given update (a smarter numbering scheme could help). It also works fine with source control.

Dealing with surrogate key values (from autonumber columns) can be a pain, since the production database will likely have different values than the development DB. So, I try never to reference a literal surrogate key value in any of my modification scripts if at all possible.

Answer 2

I don't trust programmatic migrations. If it's a simple change, such as adding a NULLable column, I'll just add it directly to the live server. If it's more complex or requires data changes, I'll write a pair of SQL migration files and test them against a replica database.

When using migrations, always test the rollback migration. It is your emergency "oh shit" button.