On paper, a more expressive language that spits out SQL queries sounds great, but I've never seen a single one not become a pain in the ass to use.
That's precisely because of some the flaws of SQL outlined in the article.
Generating SQL is complex. Generating portable SQL is impossible.
Take a look at the queries sent by something like Power BI to MS SQL Server when running in direct-query mode. It's just obscene how complex the queries can get!
I've tried to write some SQL generators before, of various types. I always got bogged down in the ludicrous complexity.
The author of the article makes so many good points that it's easy to gloss over entire categories of mistakes in the SQL spec. For example, he briefly mentions that getting a column schema back in query is weird and non-standard. The real problem here is that SQL is not homoiconic: tables, columns, and their types could have been represented as tables, including when defining them. E.g.: instead of "CREATE TABLE" the syntax should have been more like "INSERT INTO sys.tables ... ". (Similarly for columns, constraints, foreign keys, etc...)
Instead, we have a language designed for data that until recently had implementation-defined read-only system views for the schema data! Writing into that data uses an entirely different set of key words and syntax, which is "not really SQL" in the sense that it isn't relational and cannot use tables as input unless coded in an external language like Java, or built up using string manipulation an called via some sort of non-standard "exec string". Of course, then you have to worry about things like not having a built-in standard escape/unescape function! (QUOTENAME is the MS SQL version of this, surely non-standard).
Decoupling the materialisation of a schema from its theoretical representation would make all migrations "instant". Many column-oriented databases can do this, or nearly so, but most row-oriented databases can't or won't.
I.e.: ideally database engines should be able to keep track of multiple "versioned" physical schemas and transparently map in-flight queries to them. Big data migrations often implement this kind of thing manually with "writeable views" or similar techniques. This shouldn't be manual, the database engine should be able to do it behind the scenes with minimal human involvement.
e.g. when I use sqlalchemy, I end up writing queries using core than ORM, because it becomes simple for me to optimize versus having to keep printing str(query_statement) to analyze sqlalchemy thinks it should do versus what it really should do.
Do remember to find a good DB optimization expert (hint: not the type that answers "add an index" when asked how they would speed up a query) and you'd be surprised how well SQL continues to scale today.
And for those of you about to say "but KV/NoSQL", by all means don't shoehorn SQL into every single thing; use the right tool for the right job; SQL will handle that 80+% of what you need, then implement the "other" stuff in memcached/redis/mongo/kafka/whatever you find is "easiest" to implement, deploy, manage and maintain.
ORM generally aren’t meant to totally replace sql, it’s an anti pattern. There was a quote by Gavin king explicitly calling this out; ORM for crud operations on object hierarchies, sql for everything else (reporting, adhoc queries etc). I try to educate this whenever I can but so many teams resist it.
The problem with most abstractions on top of SQL is that the first thing they abstract away is the relational model; you end up with an opaque result set, a mapped object, or an untyped collection. In my opinion, in order for a higher-level SQL language to be useful it must retain all the low-level data type information. The abstraction should be on the operation level (DML), not the data level.
End result? Abstractions target the easier of the two markets: people who don't want to learn relational modeling.
As an example, ActiveRecord allows you to define scopes. If you have an account things like:
platinum: -> value >100000
churn_risk: -> churn>.95
You can then chain these together i.e Account.platinum.churn_risk to get all the platinum accounts at risk of churn.
Afaik there's nothing similar in SQL. If your definition of a platinum customer changes you have to change a bunch of different queries instead of one definition.
It breaks down in really stupid and unintuitive ways. Now you're not just learning LINQ, but also the ideosyncracies of how LINQ translates queries to SQL and why sometimes your performance plummets even though you expressed yourself in the same way, but if you put some query in a variable first and then continue grouping it does a query that takes milliseconds. It's the best example of a leaky abstraction I've ever come across.
The context for my generators was querying over data whose schema was customer defined. I wrote two generators in Ruby and one in Java. They were designed to take advantage of quirks and performance characteristics of MySQL when doing arbitrary ad-hoc sorts and filters, and the optimization was mostly about delaying joins if they weren't necessary for the predicates, and creating an inside out nest of subtable queries to control the execution plan.
Doing this without a generator of some kind is impossible - the columns are dynamic - and then you leave a lot of performance on the table if you don't tune.
I find that if my query is getting overly complex, that some of the logic is often better suited in the app layer.
At best, you are spending time learning something niche with little upside. At worst, you are introducing buggy bad performing complexity.
While I agree with you, it's too bad you can't create macros over common patterns. Not sure about you but I copy and paste a ton for this.
Really, I'd just want a coffee script, or moon script (https://moonscript.org/) where you have the idea for a query in your head, and know what the constructs will ultimately generate.
If you have bounced disappointingly off ORMs like I have since the 1990s, EntityFramework is a great step forward.
Adding another abstraction layer on top would only increase this frustration, as they'd have even less control.
