Handling database schema changes when pushing new versions

Question

During times of heavy development, the database schema changes both rapidly and continuously, and by the time our weekly push to the beta build comes around, the schema has changed so much that the only sensible option is to nuke all of the tables I can and copy the new versions from my dev database. Obviously, this isn't going to work once we launch, since nuking production data is a recipe for disaster, so I was wondering what strategies were out there for managing database schema changes from one version/revision to another?

Some I've found or experienced:

1. Straight nuke-and-dump from one database to another (what I am doing now)
2. Maintaining an UPDATE.sql file with SQL statements that get run either via script or by hand.
3. Maintaining an update.php file with a corresponding "db-schema-version" value in the active database

The third option seems to be the most sensible, but there still exists the possibility of a badly constructed SQL query failing mid-script, leaving the database in a half-updated state, necessitating a restoration of a backup.

It seems like a non-issue, but it does happen, since we as a team, we use phpMyAdmin, and I can't seem to even rely on myself remember copying the executed SQL statement to paste into the update.php file. Once you navigate to another page, I have to re-write the SQL statement out by hand, or reverse my change and do it again.

I guess what I am hoping for is a solution that doesn't impact our established development workflow?

Answer 1

Automate. Automate. Automate.

You're on the right track with an explicit DB version number, but I would go a step further and make the code explicitly aware of exactly what schema it expects to work against (e.g. by committing the actual DDL script and having the updater parse it); then at update time you only have to discover the existing scheme via database metadata and INSERT/DROP/ALTER as necessary, no matter from what version to what version you are updating. (You could also keep an explicit version number in the database itself and deliver the entire schema history with the installer so that you don't even need schema discovery.)

Potential syntax errors in the update SQL script are a problem, but you can solve that by verifying that the updater can only produce correct DDL statements. (Formal proofs are almost never worthwhile in enterprise software development - too much effort for too little assurance - but I feel that database integrity is one of the few exceptions: basic SQL isn't a particularly hard language to capture formally, and the benefit of protecting production data is so great that almost any amount of one-time up-front effort is justified, particularly if it has to work for unattended installs,)

Answer 2

The database schema versioning is the way to go - each version has only one change to the database, or at least changes that can be reverted as whole. DBDeploy is a great tool to automate that.

Some things I have learned useful are:

1. Always test your change locally, and test your code with it, just the ALTER passing is not enough
2. You need to synchronize which get to do their change first - a simple wiki page where you can "take a number" worked great for my team.
3. Don't try to fix broken change, add a new counter-change to negate it, it's much painless
4. Your code depends on that changes - be sure to link the issues in your bug-tracking system with the DB changes. That is very helpful at deploy time.
5. Include DB changes to your CI - do apply your changes to a CI database on commit. Also, if you have any tests that use a database, run them on commit.

Answer 3

Since you're using phpMyAdmin, then I assume you are also using MySQL.

Take a look at EER Model diagrams in MySQL Workbench. They have been a great help to me in maintaining and updating schemas.

First, you can synchronize the model with a database source. So that changes in the diagram are pushed as ALTER TABLE commands. This allows you to perform your schema changes on the diagram, keep it always up to date while also pushing updates to your development database when needed.

Secondly, you can reverse engineer an EER diagram from a database source. Which can be handy by taking changes on a development database, and updating a production database since it will calculate the differences.

Thirdly, it can be used to assist in created the SQL that should go into your "update.sql" file.

CONS:

Database triggers and constraints are a problem with the synchronize updater. It doesn't seem to know the right order of things. Foreign key constraints often generate errors, but this can be resolved by editing the SQL it generates.

This is not a data migration tool. Any changes that are beyond purely schema will still need custom SQL.

Answer 4

Take a look at http://south.aeracode.org — it's a DB migrations library for Django (a Python framework) but:

1. you can get great ideas from it (and perhaps even find a PHP clone)
2. you can actually use it independently from the rest of Django to manage your PHP/MySQL app's tables.

It can also generate schema update scripts; it also handles change reversals automatically most of the time.