MySQL and SVN

This question comes from Dan S.

As from the subject line, you may be gathering I have a question. My current job has me doing PHP development, so you may be getting an increased flux of questions from me. The question I have right now is regarding MySQL and version control. Is there any smooth way of integrating the two? Right now, when I make structural changes to my database, I export it out. However, today I ran into a situation where I made changes at home, but forgot to export the DB and got to work to discover my mistake. Any hints or tricks for this one?

-Dan

Excellent question. So a few things that come to mind are:

• Short term easy, long term hard: Keep your structure and data in separate sql documents
• Short term hard, long term easy: Use migrations

Plain 'ol SQL

So for the short term easy solution, if you separate your data and structure in separate SQL documents. This makes quick structural changes a bit easier to maintain and deploy. Any ALTER's you do, you can save them as separate .sql files and if you have a good naming convention (incrementing numbers or YYYYMMDDHHMMSS prefixes are nice) you can run them all sequentially.

The problem with this method is that it requires manual effort and is pretty error prone as it involves lots of copy/pasting from your shell or sql admin tool. But, it does work and its pragmatic enough to where you could develop a small build process around it.

Side note: Build processes are a good thing. I recommend that you look into automating your tasks as much as possible. Things like DB updates, environment setups, app deployments are all candidates for build processes. SVN's pretty good at this too: consider post-commit hooks. You can use these to execute shell scripts which your build process needs to build, deploy or whatever.

Side note 2: Since your using mysql, use mysqldump. It's wicked powerful and allows you to export data or structure in a bunch of different ways. When you export your structure, make sure you use the option which adds a DROP TABLE IF EXISTS ... prefix to the CREATE calls. PHPMyAdmin is painful to use, but its export options do support this as well.

Migrations

The Ruby community has given us the wonderful gift of migrations (AFAIK it's a ruby idea, I could be wrong though). You can read about them here. All examples are in Ruby, but several PHP projects exist. I haven't used any of them, but this one seems to be quite nice. For a good breakdown, go to the wiki and read the Big Picture Overview.

A third option (which I didn't bother including because it's a bad solution to this problem) is to master/slave your db's. This is really convenient in the sense that any change, no matter how small, will be automatically be propagated to all appropriate DBs. This is nice because it's a truly effortless solution. It sucks for what you're trying to do because it doesn't maintain a history of structural changes -- which you should be doing.

Granted, you could work around this by having a (hourly|daily|weekly) structural snapshot get generated and committed by a cron job -- but there are portability concerns there too.

Readers: It's your turn.

Am I right? Wrong? Agree? Disagree?