Migrating a database is one of the most sensitive things that can happen to do at some point in our career, sometimes it is easy, sometimes it is not. Let’s see how to use the expand/contract pattern to be able to do it in both cases ~
The key to evolving databases design is evolving schemas alongside the code, we should manage changes to database structure the same way we manage the source code: tested, versioned and incremental. It allows managing changes to the schema and the code incrementally, by handling each as parts of a whole.
Let’s say we have 3 applications that share the same relational database
If one of the applications need to evolve via schema change it could potentially break the other two applications. To handle this situation we can use the expand/contract pattern adding a transition phase into the refactoring.
Using this pattern you can maintain both states during the refactoring allowing backward compatibility.
Let’s see a small real example:
We have a
Product table with
IdCode column, the
IdCode is an identification number formed by 2 things: and
internal code and an
external code. Our mission is to split the
IdCode column and have the new columns to allow us to filter by
InternalCode without breaking anything, we have different options:
No integration points and no legacy data
We don’t have pre-existent data and we don’t have external systems who use this table so the refactoring is pretty straightforward, we can just add both columns
ExternalCode and drop the old
No integration points but legacy data
We have pre-existent data to migrate to new columns but we don’t have external systems. We need to create a function to extract the information from the existing column to migrate the data to the new one, for example
Integration points and legacy data
We have pre-existent data and external systems which are using our table so we need to combine the previous techniques to solve this problem combining them with a new one.
Of course, we need to extract
ExternalCode from the old
IdCode column then we have to create a trigger that during the CREATE/UPDATE operations on our
Product table does these steps:
If the new column
IdCode is empty we can concatenate the
InternalCode and the
ExternalCode values that we extracted using the previous functions in order to fill the new
IdCode column with the old notation and then if the new
IdCode column is filled we can extract the new values from it.
It allows the old systems to find the same data and the new systems to have the new version of the data.
Once all the systems are aligned we can drop the column or just set it as UNUSED if the deleting process is time-consuming.
I discover this pattern reading the book Building Evolutionary Architectures, that I heavily recommend.
The Expand/Contract technique is a subset of a pattern called
parallel change that we can find in Martin Fowler’s blog.