document updated 15 years ago, on Aug 22, 2009
It's good to have several different servers for deployment: a development server, a staging/pre-production/release candidate server, and a production server.[1] [2]. This helps maintain high-uptime production servers while still giving developers the freedom to make necessary changes.
Pushing code updates from dev⇒staging⇒production is pretty straightforward, it's essentially a copy operation. But how do you handle upgrading/downgading the database attached to each server, when you need to make schema changes? What happens if you push a change to the production server, but something breaks, and you have to quickly revert the schema changes?
The traditional solution to this is:
[3]
[4]
[5]
[6]
[7]
[8]
- whenever a developer needs to modify the schema, they must generate two "database delta" scripts:
- an upgrade script — makes all necessary schema changes, and if any data needs to be modified to accomodate the schema changes, it does that too
- a downgrade script — reverses the schema changes and associated data changes
- when a given server needs to be upgraded or downgraded: A) the desired version of the website code is pushed over. B) run the appropriate database delta script (if jumping several versions at once, run all delta scripts in between the current version and the target version)
There are some tools that assist in this process, particularly in doing "SQL diffs", which automatically generate the ALTER TABLE statements for you.
Some frameworks have this functionality built right in.
More modular/independent versions of the above:
Random articles