How many organization use version control for development? Probably almost every single one.
How many store the database schema under version control? Alas, not as many.
Coupling one’s application with table schema is essential. Organization who actively support multiple versions of the product understand that well. Smaller organizations not always have this realization.
How is it done?
Ideally
Ideally one would have:
- The schema, generated by hand
- Essential data (INSERT INTO statements for those lookup tables without which you cannot have an application)
- Sample data: sufficient real-life data on which to act. This would include customers data, logs, etc.
If you can work this way, then creating a staging environment consists of re-creating the entire schema from out schema code, and filling in the essential & sample data.
The thing with this method is that one does not (usually?) apply it on one’s live system. Say we were to add a column. On our live servers we would issue an ALTER TABLE t ADD COLUMN.
But this means we’re using different methods on our staging server and on our production server.
Incremental
Another kind of solution would be to hold:
- The static schema, as before
- Essential data, as before
- Sample data, as before
- A migration script, which is the concatenation of all ALTERs, CREATEs etc. as of the static schema.
Once in a while one can do a “reset”, and update the static schema with the existing design.
As you go along
This solution simply means “we apply the changes on staging; test + version them; then apply on production”.
A side effect of this solution is that the database generates the schema, but the schema does not generate the database as in previous cases. This makes for an uglier solution, where you first apply the changes to the database, and then, based on what the database report, enter data into the version control.
How to do that? Easiest would be to use mysqldump –routines –no-data. Some further parsing should be done to strip out the AUTO_INCREMENT values, which tend to change, as well as the surrounding variables settings (strip out the character set settings etc.).
Summary
However you do it, make sure you have some kind of version control on your schema. It pays off just as with doing version control for your code. You get to compare, understand the incremental changes, understand the change in design, etc.
