Short version: have DB1 hold the transactional data (data generated while running the system). Have DB2a have the release-bound data (data about and connected to the code itself-settings, prices, whatever).
Have DB2a have views onto DB1 tables. Version a code only “knows about” DB2a but any transactional CRUD ops hit the tables on DB1.
Now version b of the code just needs to ship/create a DB2b and both a and b can run in parallel.
If you need to change the shape of DB1 tables, those changes need to be backward compatible (can only add nullable columns, no use of "select *", etc).
There’s a few details about how to make it fully practical, but that’s the gist and we ran than for about 12 years on a moderately heavily trafficked e-commerce site.
What ends up happening is we end up separating the two more with "cache this one, not that one" rather than two different databases.
I will explore this idea on my next greenfield project, whenever that happens.
I'll look again later tonight more thoroughly to see if I've posted the mechanisms and restrictions publicly anywhere before. If I haven't, I'll try to dig it out of our old dev doc system and post them here, but I can't make any promises as the docs I recall are now over a decade old, so I'm not fully sure they exist any more. :)
This requires a little discipline, but if you follow a few simple rules it's not really that arduous:
- when adding a new column, it must have a default value set, or be nullable
- don't drop any columns
- don't rename any columns
Now, for those last 2, what I really mean is "don't do it in a single release" - if you want to make destructive changes, do it over the course of 2 releases. - release 1: remove dependencies on the column from the app/API/service
- release 2: performs the database migration with destructive changes
It probably sounds more difficult than it actually is :) In reality, I don't make destructive changes that often though.Same here. A forward only approach works best for us too. if you need to clean up a mess, it is a new migration script. It's too complex to try an work backwards. What if multiple scripts were ran? Then you have to roll back say script number 2 out of 5 and there were destructive operations. It becomes really hairy really quickly. So forward-only is the easiest to reason about.
Please do make sure that you have snapshots for restoring if you really mess up badly. I know its not always feasible to do snapshots before every deploy, but having a daily snapshot can bring you a lot of comfort.
If you built your own migration tool (highly encourage it, its not that hard to build a forward-only migration tool), then you can trigger selective snapshots/table dumps for only the tables that gets changed, and only for specific operations (updating schema, dropping columns, dropping table) before your migration scripts touches the db - that way you have a path to restore. You don't always need a full DB dump (say you have 500+ tables but only changing 2, 1 of which is destructive, thus the backup is tiny and quick). It also helps if different data sets live in isolation to help manage this kind of admin.