Programming

17398 readers

95 users here now

Welcome to the main community in programming.dev! Feel free to post anything relating to programming here!

Cross posting is strongly encouraged in the instance. If you feel your post or another person's post makes sense in another community cross post into it.

Hope you enjoy the instance!

Rules

Follow the programming.dev instance rules
Keep content related to programming in some way
If you're posting long videos try to add in some form of tldr for those who don't want to watch videos

Wormhole

Follow the wormhole through a path of communities [email protected]

founded 1 year ago

MODERATORS

[email protected]

Genuine Question - have you migrated DBMS on a Production System which wouldn't have been possible with vendor lock-in on the backend? (lemmy.world)

submitted 7 months ago by [email protected] to c/[email protected]

17 comments fedilink hide all child comments

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.

you are viewing a single comment's thread
view the rest of the comments

[–] [email protected] 6 points 7 months ago

I've done one DBMS migration (MySQL > PostgreSQL) and also maintained an RDBMS agnostic system that supported MySQL, MSSQL/TransactSQL and PostgreSQL.

The system I migrated was extremely young and had terrible SQL discipline (I actually had to move queries using ye olde mysql_query to a tool set I wrote above pdo. This was a little while after the MySQL/Maria fragmentation and we weren't comfortable with Oracle stewardship but also didn't have high confidence in Maria's ability to resist further fragmentation.

After that migration we've committed hard to postgres and utilize a lot of language specific features where they're useful, I still encourage agnostic SQL where possible though.

I prefer writing raw SQL and, while I've added tooling to support Doctrine (an ORM framework in PHP), I don't personally use it. I also advocate against complex stored procedures, I personally think developers have difficulty understanding the whole system if stored procedures are allowed to be non-trivial - developers should be aware of the complexity that underlies their actions. I do, however, leverage materialized views extensively to keep a denormalized performant layer over our normalized source of truth.

I'm happy to answer any specific questions but it's super late here so it'll probably be in the morning.