Managing the migration of breaking database changes to a database shared by old version of the same application

Question

One of my goals is to be able to deploy a new version of a web application that runs side by side the old version. The catch is that everything shares a database. A database that in the new version tends to include significant refactoring to database tables. I would like to be rollout the new version of the application to users over time and to be able to switch them back to the old version if I need to.

Oren had a good post setting up the issue, but it ended with:

"We are still in somewhat muddy water with regards to deploying to production with regards to changes that affects the entire system, to wit, breaking database changes. I am going to discuss that in the next installment, this one got just a tad out of hand, I am afraid."

The follow-on post never came ;-). How would you go about managing the migration of breaking database changes to a database shared by old version of the same application. How would you keep the data synced up?

Answer 1

If the old version has to be maintained, the changes simply can't be breaking. That also helps when deploying a new version of a web app - if you need to roll back, it really helps if you can leave the database as it is.

Obviously this comes with significant architectural handicaps, and you will almost certainly end up with a database which shows its lineage, so to speak - but the deployment benefits are usually worth the headaches, in my experience.

It helps if you have a solid collection of integration tests for each old version involved . You should be able to run them against your migrated test database for every version which is still deemed to be "possibly live" - which may well be "every version ever" in some cases. If you're able to control deployment reasonably strictly you may get away with only having compatibility for three or four versions - in which case you can plan phasing out obsolete tables/columns etc if there's a real need. Just bear in mind the complexity of such planning against the benefits accrued.

Answer 2

Read Scott Ambler's book "Refactoring Databases"; take with a pinch of salt, but there are quite a lot of good ideas in there.

The details of the solutions available depend on the DBMS you use. However, you can do things like:

• create a new table (or several new tables) for the new design
• create a view with the old table name that collects data from the new table(s)
• create 'instead of' triggers on the view to update the new tables instead of the view

In some circumstances, you don't need a new table - you may just need triggers.

Answer 3

Assuming only 2 versions of your client, I'd only keep one copy of the data in the new tables.

You can maintain the contract between the old and new apps behind views on top of the new tables. Use before/instead of triggers to handle writes into the "old" views that actually write into the new tables.

You are maintaining 2 versions of code and must still develop your old app but it is unavoidable.

This way, there are no synchronisation issues, effectively you'd have to deal with replication conflicts between "old" and "new" schemas.

More than 2 versions becomes complicated as mentioned...