This is something I have thought a lot recently since I recently saw a project that absolute didn’t care in the slightest about this and used many vendor specific features of MS SQL all over the place which had many advantages in terms of performance optimizations.
Basically everyone always advises you to write your backend so generically with technologies like ODBC, JDBC, Hibernate, … and never use anything vendor specific like stored procedures, vendor specific datatypes or meta queries with the argument being that you can later switch your DBMS without much hassle.
I really wonder if this actually happens in the real world with production Software or if this is just some advice that makes sense on surface level but in reality never pans out. I personally haven’t seen any large piece of Software switch to a different DBMS, even if there would be long term advantages of doing so, because the risk and work to retest everything would be far too great.
The only examples I know of (like SAP) were really part of a much larger rewrite or update rather than “just” switching DBMS.
Sure - for example we migrated all our stuff from MySQL to MariaDB.
It was completely painless, because all of the source code and many of the people who wrote that code migrated to MariaDB at the same time. They made sure the transition was effortless. We spent a months second guessing ourselves, weighing all of our options, checking and triple checking our backups, verifying everything worked smoothly afterwards… but the actual transition itself was a very short shell script that ran in a few seconds.
I will never use a proprietary database unless it’s one I wrote myself and I’d be extremely reluctant to do that. You’d need a damned good reason to convince me not to pick a good open source option.
My one exception to that rule is Backblaze B2. I do use their proprietary backup system, because it’s so cheap. But it’s only a backup and it’s not my only backup, so I could easily switch.
I’m currently mid transition from MariaDB to SQLite. That one is more complex, but not because we did anything MariaDB specific. It’s more that SQLite is so different we have a completely different database design (for one thing, we have hundreds of databases instead of just one database… some of those databases are less than 100KB - the server just reads the whole thing into RAM and slow queries on our old monolithic database are less than 1 millisecond with this new system).
Yeah we don’t do anything like that. All the data in our database is in a JSON type (string, number, boolean, null) with the exception of binary data (primarily images). It doesn’t even distinguish between float/int - though our code obviously does. All of the queries we run are simple “get this row by primary key” or "find all rows matching these simple where clauses. I don’t even use joins.
Stored procedures/etc are done in the application layer. For example we don’t do an
insert queryanywhere. We have a “storage” object with simple read/write functions, and on top of that there’s an object for each model. That model does all kinds of things, such as writing the same data in different places (with different indexes) and catching “row not found” failures with an “ok, lets check if it’s in this other place”. That’s also the layer we do constraints which includes complex business rules, such as “even if this data is invalid — we will record it anyway, and flag it for a human to follow up on”.MariaDB is a fork of MySQL that keeps compatibility with its APIs. That’s why the move was effortless for you. It’s a simple drop-in replacement. Not because “many migrated at the same time”. (Maybe you were referring to that, but it wasn’t written like that.)