[tomnipotent]

> Stored procedures have the downside that they often work only in one vendor's database.

Doesn't really matter, because no one is writing database-agnostic SQL (unless that's part of your product). Any non-trivial implementation is going to require the use of proprietary SQL.

[motogpjimbo]

It has always amused me when developers try to make their application's database layer vendor-agnostic. Not only does that restrict you to the most vanilla dialect of SQL imaginable, it's also a pointless exercise generally because it is orders of magnitude more likely that your app will be replaced and the database kept than the other way round.

[kasey_junk]

It was much more important when software products were sold to companies that ran their own databases. You didn’t have the luxury of choosing which database your app would run on so half the job of a software dev was making running it on many different db so you didn’t lose the sale. It’s one of the many reasons saas became the dominant way to sell software.

Once you control the deployment environment it largely makes very little sense to spend much time on db agnosticism. But that’s also true of many other things that have left vestigial forms in our software today such as IOC containers, pluggable log libraries, configuration files instead of configuration code, etc.

[spacemanmatt]

it makes sense in a few cases. keycloak and other middleware are smart to support several RDBMS.

[citrin_ru]

Even if you think you use database-agnostic SQL unless you actually run tests with multiple DBMS you cannot be sure. One can write code which depends on some DB specific behavior unintentionally, Hyrum's Law [1] works for databases too.

[1] https://www.hyrumslaw.com/

[galaxyLogic]

Exactly. Isn't it a sad state of affairs that you can not write plain pure standards-compliant SQL and be done with it?

It's a bit like if you wrote C and could only compile your program with Oracle's C-compiler. Where is progress in SQL standardization going?

[citrin_ru]

Its not only about standards, but also about tools to check that you are actually following them. It is easy to write C code which will compile with one compiler but not with another. That's why if compatibility is desired it is better to compile code with different compilers in CI.

In theory we can use a tool which tells if you SQL is ANSI compliant, but this would not tell if your code expect only standard compliant behavior from the database. E. g. some RDBMs are flexible in which formats they accept datetime strings and some are not. SQL can be standards-compliant but code still can fail with one DB and work with another.

It is not to say that standards are useless. It much easier to port an application from one DB to another if a developer was trying to use only ANSI SQL. Or you can write an application which can work with multiple RDBMs without modification but like with almost everything else - if you haven't tested it you cannot be sure that it works.