The trouble with SQL is that its foundation is old and hasn't been given much love with regards to modern language theory, and any attempts to talk about how the language could be improved are shot down on the basis of confusing the language with the application of the language.
The trouble with SQL is that it doesn't easily allow for basic building blocks that ORMs benefit from, like composability. This leads many ORM authors to build their own query language, which support features that SQL lacks or does poorly, that compile to SQL in order to simplify the rest of the development of the ORM.
This comes as a result of SQL not being a very good language (not to be confused with the application of declarative querying of relational data, which is beneficial and could benefit greatly from a good query language) by modern standards.
SQL is a great language. It's for querying records from a database. Trying to wrap stateful objects around SQL is where the industry went wrong, IMO. It does make some code more portable across datastores, but for me the price is too high. When you know how to write efficient SQL statements, ORM feels like having a hand tied behind your back.
The application of the language does not make the language itself great. As we have seen with DSLs that often come bundled with ORMs, there are other languages to query databases with (even if they ultimately compile to SQL), and I would argue that some of them do a lot better job than SQL does for providing a comfortable and cohesive environment for developers to write queries in.
In the imperative language space, we have one hundred and one different languages all trying to make things slightly more comfortable to developers. C, Go, and Rust can all be used to write the same kind of application, more or less, but that does not mean all of those languages are equally great. The same is true of declarative queries. Just because SQL is popular does not mean it is great.
> When you know how to write efficient SQL statements, ORM feels like having a hand tied behind your back.
ORMs and SQL are orthogonal concepts, really. There is no reason an ORM couldn't require you to hand-roll every single SQL statement. An ORM's concern is simply mapping the results of that query into the application's objects. That some ORM implementations also include functionality to build queries for you, often on top of the aforementioned DSLs, to make that mapping require less effort on the developer is, I would argue, largely a result of SQL being a bad language.
Working with relational databases might be difficult for those not acquainted with them. However, I think the SQL language as an interface primarily for relation databases is a great language.
I think, invariably when you use ORM, you end up having query-specific object structures. For example [1].
You also might be passed objects with deferred fields [2]. This will be completely opaque to someone consuming the resulting object. You'll eventually run into this problem [3]. Solving the lazy load problem requires an understanding of how SQL works in the first place. And if you look at the solution in that example, it's an ORM-wrapped series of joins.
From 3:
> For good measure, we add a raiseload to throw an exception if we try to load anything that we didn’t load here.
> However, I think the SQL language as an interface primarily for relation databases is a great language.
Which language(s) are you using as a point of comparison and why is SQL better than those other languages? SQL is no doubt better than nothing, but that is not in the spirit of our discussion.
I agree that SQL as the lingua franca for talking to relational data stores is not ideal, and that in various ways it could be improved. I personally would start out with some of the issues of cosmetic syntax orthogonality, though I realize there are bigger fish to fry.
But two things come to mind on reading your comment.
1. For the problem of Object-Relational-Mapping (ORM) which query language is used is not that important. If one accepts that there is one general purpose language for application development and another for managing persistent data, you will be left with a situation where there is a bunch of redundant code to write in whatever query language and application language you have available, and it would be nice to not have to write all that by hand.
2. Over the years, I have encountered many projects (languages) that attempt to address various deficiencies in SQL. I feel like a lot of these projects make life too hard on themselves. Rather than building extensions to existing database engines, they want you to adopt not just their new language, but their whole persistence stack. If I was smarter, and I had a design for a "better SQL", I would try to integrate it into Postgres or some other existing database engine. I guess that's what some of the ORM authors are trying to do, but by compiling their language to SQL supporting all databases at once. But returning to point 1, that seems pretty separate from the issue of Object-Relational-Mapping.
The trouble with SQL is that it doesn't easily allow for basic building blocks that ORMs benefit from, like composability. This leads many ORM authors to build their own query language, which support features that SQL lacks or does poorly, that compile to SQL in order to simplify the rest of the development of the ORM.
This comes as a result of SQL not being a very good language (not to be confused with the application of declarative querying of relational data, which is beneficial and could benefit greatly from a good query language) by modern standards.