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:
- Straight nuke-and-dump from one database to another (what I am doing now)
- Maintaining an UPDATE.sql file with SQL statements that get run either via script or by hand.
- 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?