The types of issues you're enumerating are really part of a learning curve that every language and environment will have.

> not having basic functions for calculating percentiles or even a basic median

I don't think this is really true anymore; windowing functions are pretty prolific, and I think every major database will have some percentile functions

> The types of issues you're enumerating are really part of a learning curve that every language and environment will have.

They're not though. That's why excellent coders often despise having to learn and use SQL, and basically just refuse to. SQL is uniquely terrible -- and I say this as someone with a career spanning from Win32 C++ to every major web technology.

> every major database will have some percentile functions

MySQL still doesn't. MariaDB only added it in 2018, and I've been writing queries for a lot longer than that. (And MySQL only added window functions in 2018 as well.)

And remember that, for various reasons, you're often (usually?) stuck using a database engine in production that's several years old, since upgrading a database engine is not something to be taken lightly.

How many application languages have percentile functions?

Nearly all of them, because any language designed in the last 30 years permits user defined libraries in trivial fashion, even amazingly letting you write those libraries in the same language you’ll use it. And even more incredibly, the libraries themselves can build on other libraries! (There’s even often a standard allowing libraries to be shared by different implementations … or rather, a standard that actually standardizes things beyond the language aesthetic)

Databases follow the COBOL model of extensibility — tis for me and not for thee. It is only to be done by the vendor, and maybe some third party specialists

Every database I've ever used is extensible

> They're not though. That's why excellent coders often despise having to learn and use SQL, and basically just refuse to. SQL is uniquely terrible -- and I say this as someone with a career spanning from Win32 C++ to every major web technology.

This is basically a strawman + appeal to authority argument. I've no desire to bicker. "Uniquely terrible" is quite subjective, we'll have to agree to disagree on if it applies to sql.

Excuse me, but I'm certainly not "bickering" and I resent the insinuation. And you don't seem to understand what a strawman argument is.

I'm not going to write an article on this for you, but you can certainly see the sentiment frequently expressed on HN that the rise of NoSQL and of ORM's is a direct result of otherwise capable programmers not wanting to deal with SQL and its conceptual building blocks such as joins. Take that for what you will, but you might suspect there's a reason for it, and that it's not merely the learning curve for "every language".

> you can certainly see the sentiment frequently expressed on HN that the rise of NoSQL and of ORM's is a direct result of otherwise capable programmers not wanting to deal with SQL and its conceptual building blocks such as joins

I think I saw that sentiment a lot 5+ years ago. The last 2-3 years have been a lot more for postgresql and writing SQL directly.

Either way I don't think that "excellent coders" "despise" learning SQL, almost every senior coder that has interacted with a medium-to-large database has understood and valued SQL in my experience.

I take it as the same reason they don’t want to use boring technology like HAProxy, or why they think infrastructure in general is beneath them: because they are easily influenced by breathless grifters on Medium promoting the New Thing.

SQL isn’t going away any time soon. At its heart, it’s relational algebra. If you can learn DS&A, you can learn relational algebra.

FWIW, I love HAProxy but hate SQL (for the exact reasons the OC suggests). It turns the simple into soup and makes bugs more likely. Every single non-trivial usage of it I've seen in the wild has been a ball of spaghetti. I bought the learning curve argument for a long time until I finally realized: no, SQL is a victim of its own success. It's popular because it's popular, not because it's a superior language. That popularity has encouraged a gambling addict level of doubling down that continues to gaslight less-experienced developers into believing it's "the way."

You're right that it isn't going away, but when it does, I'll be the first to dance on its grave.

ORMs and NoSQL were a direct result of the desire to have an object-oriented data worldview in a programming landscape dominated by OO languages. They were not introduced because programmers didn’t want to learn SQL (implying incompetence etc).

I feel like your perspective might be more narrow than you think it is.

I've been coding professionally for 20 years and at every job I've ever had we've made the decision to move away from ORMs or as much as possible given the constraints of our system in preference of directly writing SQL. My previous employer dumped Rail in favor of a Go backend (a language whose userbase almost entirely eschews the idea of ORMs) and my current employer never even had one to start with, choose to write SQL directly in a typescript backend.

I'm not going to pull the stats for you but this very website is FULL of articles talking about the benefits of writing your own SQL and I can't even remember the last time someone tried to pitch me a shiny new ORM.

> teaching people arcane terminology distinctions like between WHERE and HAVING

This is akin to complaining about needing to teach people arcane terminology distinctions between FOR and WHILE. I think it's a crime that many CS degrees I see don't include a course in database and SQL. There are of course some arcade corners of SQL (I sometimes have to look up the different JSONB operators in postgres), but WHERE and HAVING ain't it.

> flipping a coin on whether the query engine will use the index and execute in 10 ms, or decide to follow a different execution plan and take 5 minutes to run

Heavens, if you think using an ORM is going to help you here, have you got another think coming.

Your take on this is so foreign to me I was honestly shocked to see it. That being said, you probably aren't the only one, so perhaps my perspective is more narrow than I had assumed as well.

The common sentiment on HN I'd say is the opposite: ORM's are needed for junior devs who didn't have time to learn SQL properly. After NoSQL hype died out (around 5 years ago), the HN attitude towards both ORM's and using NoSQL instead of relational dbs has been mostly negative for sure.

I am happy for all the disagreeing posts and that HN is a place for it.

It also helps me understand my colleagues.

It may be how our brains are wired. For me whenever I see a for loop I see bugs.

It is also about that you get the concurrency from map/reduce-type jobs for free without having to think about it.

But yeah there are a couple of places you need to be careful. Avoiding duplicates and handling of NULL.

Are you using Postgres? It seems like you haven’t discovered CTEs or lateral joins.

Oh, I've worked with all the major engines. To which we can add: trying to remember which seemingly basic features are supported by which engines, and which aren't (e.g. percentiles are great in Postgres, but you're SOL in MySQL). That there's virtually nothing "standard" about SQL syntax or functionality at all, except for the simplest of queries and the general basic concepts.

We're in the process of migrating our on-premise business application from SQLAnywhere to MSSQL.

Imagine the fun we're having...

I'm really curious, in the practice, what causes a product to decide "we are moving from abcSQL TO xyzSQL" ?

For us there's been two major motivations, and another few lesser.

First and most prominent is that Sybase was bought by SAP and SAP has pivoted SQLAnywhere from a general RMDBS to focus on mobile applications (ie more embedded). Apart from product development changing focus we've struggled to buy licenses for our customers, so the writing on the wall was clear.

The seconds is that we've got some big customers who run their own MSSQL servers, and they've been pushing harder and harder for us to use their servers so they can handle administration and access control directly.

A third motivation is that while we've been primarily on-prem, a lot of customers these days don't want that, so we've started offering cloud hosted options. Since we're a Windows shop Azure made sense, and as such the hosted MSSQL also makes sense and is quite convenient.

Fourth is that, while MSSQL is quite anemic in terms of SQL language functionality, it does support transactional schema updates, which makes schema upgrades much less error prone.

Given that MSSQL syntax support is quite limited and we now have developed tooling and library support for multiple databases, we'll probably also add support for PostgreSQL later.

In that direction, it's usually going to be about robustness -- more tooling, less edge cases, a wider developer base, built-in cloud support, and so forth. The more popular a database is, the easier just everything is.

Occasionally it's going to be some very specific aspect of performance limits, or a feature like spatial indexes that work in a special way. And occasionally it's going to be about cost or better integration with a particular cloud or other specific piece of tooling.

Well Java and JavaScript differ a lot too.

Some similarities, but a lot of differences too.

I agree that CTEs are a solution to some of the pain listed above, but CTEs aren't exotic. Lots of the major RDBMSs support them.

CTEs are an absolute must to get the right "do-this-then-that" approach.