3 ms·

I thought the "modern" approach to this was like with ORMs like diesel (in rust)[1] et al.: Have a directory with all DDL-migrations (sometimes generated, somet

• giraffe_lady 4y ago
  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.
• evanelias 4y ago
  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.