I actually love your approach and haven’t thought of that before.
My problem with relational databases often stems from the fact that remodeling data types and schemas (which you often do as you build an application, whether or not you thought of a great schema beforehand) often comes with a lot of migration effort.
Pairing your approach with a „version“ field where you can check which version of a schema this rows data is saved with would actually allow you to be incredibly flexible with saving your data while also being able to be (somewhat) sure that your fields schema matches what you’re expecting.
Having to write and perform migrations for every small schema change is a bore, but it means your software doesn't have to worry about handling different versions of data. Going "schemaless" with version numbers means moving code from "write-and-forget" migrations to the main codebase, where it will live forever.
I think not doing database migrations only makes sense when you can make do without version numbers (or if you can't do atomic migrations due to performance constraints, but that's only a problem for a very small number of projects).
You’re correct there. I mostly work on CMSes with page builder functionality, which often bake the content schema into the database columns, which makes changing that schema (for new frontend features or reworking old ones) difficult and often prone to losing content, especially in dev environments.
Best case is obviously that you never have to version your changes, but I‘d prefer making a new schema and writing an adapter function in the codebase depending on the schemas version to spending a lot of time migrating old content. That might just be due to me not being too comfortable with SQL and databases generally.
> Not having to write and perform migrations for every small schema change is a bore, but it means your software doesn't have to worry about handling different versions of data.
Same here. If your entities are modelled mostly correctly you really don't have to worry about migrations that much. It's a bit of a red herring and convenient "problem" pushed by the NoSQL camp.
On a relatively neat and well modelled DB, large migrations are usually when relationships change. E.g. One to many becomes a many to many.
Really the biggest hurdle is managing the change control to ensure it aligns with you application. But that's a big problem with NoSQL DB deployments too.
At this point I don't even want to hear what kind of crazy magic and "weird default and fallback" behavior the schema less NoSQL crowd employs. My pessimistic take is they just expose the DB onto GraphQL and make it front ends problem.
I agree. Migrations have never been a problem at my company and this codebase is 9 years old. Just regular old postgres with a few JSONB columns of things that don't need to be relational.
Same for the database that was about 8-9 years old at my last company. Migrations are fine. It's the relationship-changing that is painful.
>> If your entities are modelled mostly correctly you really don't have to worry about migrations that much
I'm gonna take a wild guess here that you have never worked in unfamiliar domains (like lets say deep cargo shiping or subpremium loans) where your so called subject matter experts provided by client werent the sharpest people you could hope for and actually did not understand what they where doing for most of the time?
Because I on the other hand am very familiar with such projects and doing schema overhaul third time in a row for production system is bread and butter for me.
Schemaless systems is the only reason I'm still developer and not lumberjack.
Pairing your approach with a „version“ field where you can check which version of a schema this rows data is saved with would actually allow you to be incredibly flexible with saving your data while also being able to be (somewhat) sure that your fields schema matches what you’re expecting.