Database as Code – The Good, the Bad and the Ugly

Can you offer an overview of the database as code landscape?

CLI tools like Flyway still play a critical role nowadays. However, they show their ages facing the arising infra complexity and demanding collaboration requirements.

Teams need GitLab / GitHub to collaborate instead of relying on Git merely.

It's not about pretty UI and more about UX / DX around dealing with database migrations like how GitLab / GitHub deals with code changes.

Pretty much all the migration management tools and approaches work well on a new project. But you only have a new project once, and briefly.

In practice they often do take a significant amount of discipline at multiple different stages. And they need to account for schema changes outside of perfectly defined and reviewed migrations, because those do happen.

Or you can so "no those can't happen" but that discipline comes at a cost too. Outages and incidents happen, and sometimes the resolution of them will be an on the fly index or other schema change. Does the migration tool give you anything to manage that? Did anyone remember to run it to generate the schema diff and apply it to the other dbs?

And sure you can say "don't resolve incidents with out-of-migration schema changes" but that has costs too. You're going to add process, and time, both when you do it and before in defining and documenting these processes.

ORMs and/or their migration tools is the solution to this, but it isn't a perfect, easy solution. It tends to be fragile over time in the face of day to day changing requirements, as you generally have in a small startup-type company. It's only a solved problem from a purely technical standpoint. In practice every company I've seen has had at least some problems with this, or mountains of careful process to avoid it. Tradeoffs either way.

That works alright in development / non-prod, especially for a smaller team or small scale application. Not in prod though. In my opinion as a database specialist, tying database schema changes to application deployments is an outright anti-pattern in production. (assuming you are using a networked database server like Postgres or MySQL, not an embedded database like SQLite)

Generally you always want to ensure your application logic works properly with both the current schema and next schema. This way, you can roll out the schema change independently first, and then only if/when it succeeds, you roll out new application code (and/or enable new feature flags etc) which actually uses the new columns, tables, procs, etc.

If you tie the schema change to the app deployment instead, you have to do the opposite: ensure that the new version of the application is backwards-compatible with the old schema. That's necessary because you probably have multiple copies of an application running (for HA), and some schema changes may take a long time to run.

So upon deploying a new version of your application, even if your modern ORM correctly handles migration locking (to ensure only one app copy runs the migration), you would still have a window where other updated app servers are running updated code but the schema hasn't been changed yet. So if you tie schema changes to app deployments, the app must be backwards-compatible with the old schema: every single reference to a new database object must be gated with a feature flag, every time.

In contrast, the decoupled deploy / forwards-compatible approach is much easier, because generally application code already won't interact with database objects that the code doesn't know about.

Chrome should be letting me switch to the reader mode or whatever it's called. It doesn't work on this website. I have no idea why there's no button to do that. I just get a little pop-up seemingly on random websites.