There are quite a lot of different options. On some very small / trivial applications developed by just one person, it might be acceptable to use ad-hoc queries (maybe with a GUI) to manage the schema - but this runs the risk of development and production schemas becoming out of sync (or rather, out of sync with their respective versions of code).
Requirements are typically
- Schema changes are held with the rest of the software in a SCM system
- Schema changes can be made in one development environment and will be correctly propogated with code to other development and production environments as required
- Schema changes can be rolled back (if for example, a deployment contains a serious bug)
There doesn't seem to be an easy answer for this one.
Our teams do something like:
- Have a base create.sql which generates the original schema
- In each revision where a schema change is required, write an apply.sql and remove.sql script to apply / roll back the change
- These scripts need to be hand written as there are often special cases - sometimes several ALTER statements are required to make a single change, sometimes in combination with UPDATEs etc. Rollback scripts are even more tricky and sometimes need to restore from a backup table.
- Have a system of scripts which works out which apply.sql / remove.sql is/are required (when deploying a new version of the code) and runs them, in order to update the schema
The problem is compounded by having multiple teams working on different code branches, not knowing necessarily which order their releases will go out in (one team would ultimately be responsible for merging the other's changes into their branch prior to release).
Ideas on a postcard please :)
Mark
