I was reading a few interesting postgreSQL solutions to constrain polymorphic tables (anti-pattern, I know) when something caught my eye.
Some solutions were looked down upon (or looked favorably upon) based on their portability to other rdbms like MySQL.
Is that really so important? I get it, if somehow you decide to change from one to the other for some god-forsaken reason it will make the switch easier. But really, how often will that happen?
I feel there is a tendency to just avoid using SQL beyond the basics.

Very good question.

In my xp, orm and use of simple sql make sense at the beginning of the project, bcz it's too liquid, too dynamic to settle for speciffic solutions. But as it matures, phasing out of this pattern is not only viable, but also welcome/desired, bcz it very soon becomes a bottleneck.

But if devs only worked with orm and simple sql, they might be lacking the competence to do anything more advanced

on the other hand, cross-db solutions are easier to reuse for different dbs [no need to write different posts for different dbms]. Also, there's less to remember. And the more basic tools are used, the easier it is to find people qualified to work with it

Switching from one RDBMS to another RDBMS actually never happens. SQL-pureness is basically a cargo cult ritual.

What actually does happen sometimes in some fields is that some part of the database has to move to some better scalable (most often key-value-oriented) storage.

So don't worry: Choose the DBMS based on what you need and then actually use that language features, that make building your app easier. You should still have some layer between your business logic and your database though. Don't sprinkle your SQL all over the place...

TL;DR premature optimisation applies. I'd rather fix things some tool can't handle or rewrite stuff idiomatically for the next DB system, than wasting a shitload of time and complexity trying to keep things generic.

It's not true that it never happens. But it's also true that you need to commit to something. Not committing to the technology you are truly relying on makes things super complex and bloated. Switching database is a migration anyway. It's fine if you have to rewrite some stuff for that. Might be that some parts aren't going to be SQL at all.

I try to write as standard SQL as possible but that is for readability sake. Not everyone knows all the fancy features so use them only when they bring a lot of value that could not be brought otherwise. Else just use what is commonly used in the dialect, like replace into for example. It's not standard but everyone who does mysql can be expected to know it.

@Oktokolo actually it does happen. I've participated in a couple of migrations like that in the same huge project. Mostly cost maintenance and unlocking bottlenecks [eg rds ora does not support rac, but other dbms can be clustered].

Although most of such migrations failed anyway - nothing can beat ora when it comes to perf 😁 but we did other db migrations.

I don't know how much this has to do with migrations, because let's be real, nobody migrates from MySQL to PgSQL expect for really early on in development. If there's going to be a good reason to migrate from one DB to another, it's going to be a much more drastic change than switching from one dialect to another, and if that happens, rewriting a bunch of queries is going to be a pretty small part of the project; even more so if you have a well-defined data layer.

In addition to what others have said, there are two more reasons why some people promote the idea of dialect-independant SQL:

- people like to have a single SQL server for everything, they don't want to install a different SQL server just because some third-party software uses a few minor optimisations from another dialect

- people on large OSS projects could never agree on using a single SQL dialect because everyone has a favourite one and many value their opinion over the benefits of using dialect-specific features