SQL abstractions are stuck with a market problem, for reasons in this thread. Everyone who wants better-than-SQL is generally already adept at SQL relational gymnastics. And everyone who isn't adept at SQL wants to be able to ignore relational thinking.

End result? Abstractions target the easier of the two markets: people who don't want to learn relational modeling.

I think there's some low hanging fruit out there. Simple improvements to make queries easier to write and maintain.

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.

While I agree it’s a PITA, most SQL-interfaced RDBMS support views, which is a way to abstract the Platinum customer issue you pose into something composable, albeit in SQL terms, not in the dotted property chaining style you propose. In the past I’ve created view generators to automatically populate a sweet syntactic palette for application semantics. Modern query planners are hardly bothered by the implied excess of joins any more, and will often use the embedded literals to optimize the query plan before execution…

I’ve plucked that low hanging fruit before, and it can taste great even in SQL sauce.

> .Net's LINQ comes close, at least for querying

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.