The problem is that it's rigid, and constructing queries based on filters or user input is painful. It disrupts the flow of your code.
That's why I now use query builders, which aren't ideal, but they at least allow me to build my queries in a logical order (adding additional ORDER BY conditions, adding/removing JOINs depending on my needs, etc.)
The problem is that no query builder that I've used flows as well as SQL or "gets out of the way".
I should probably write this at a better time as a blog post, but briefly:
Why are we trying so hard to "abstract" out the database? If you have done any significant SQL work you'd agree that no ORM or abstraction can ever hope to beat the expressiveness and power of raw SQL.
We have spent decades trying to reduce all these awesome database technologies down to their lowest common denominator so that you can switch databases in and out and as if they are not important.
Most software revolves around data, software mostly is data. So if you can treat your database layer as a blob-store you are not making good use of it. Database in my view is such a core piece of technology that I pick one, and the right one, and stick to it and make use of its capabilities to the maximum.
And we have spent decades trying to adapt SQL into programming-language-specific concepts. We have to grab SQL and twist and turn it with some similarly named half-assed SQL pasta function chain to imitate SQL.
So my point is this, why don't we try to make the programming languages adapt to SQL, instead of the other way around?
Every non-trivial piece of software needs some form of data persistence with querying and sorting and ordering and all that good stuff that SQL does.
After this long, as far as our programming languages are concerned our SQL queries are still plain 'strings'.
I think there should be a tighter integration, and acknowledgement from those making the languages, that hey, every half-serious user is going to be using SQL with this thing.
One idea could be something along the lines of 'literal-sql' syntax. To 'upgrade' SQL from being a plain string into something that the language acknowledges.
Then the compiler could do a whole lot more. The language/compiler could be made aware by some mechanism about the database layer so it could figure how to turn your DATETIME column into the corresponding type in the programming language and vice versa.
And you could write something like "DateType myDate = <SQL>SELECT date from posts</SQL>;".
The model-based ORM does not make it easy for you to query custom fields because it tries to package everything into a box.
I think the programming languages need to embrace and accept SQL as a first-class citizen.
The compiler could potentially be able to connect to the database and generate some type-safe classes for use from the schema.
Ada with the GNATColl package is the closest thing to this idea I have seen.
It can inspect the schema and generate rock-solid type-safe classes for use with database.
It frees you to make full use of the power of your database instead of crippling it.
Put another way, after all these years the way programming languages treat data persistence is virtually unchanged. The attitude is stuck mostly at "ehh whatever just open your socket/file and write the bytes... I don't care if it goes down the drain or to a database".
I think there lies some potential for a next-generation change. The programming language and the database can work a bit more closely together, I don't mean in an interactive or real-time way but basically the compiler/tooling could say "hey we know everyone is going to need to save these things into some database at some point and get them back ... so let us help you with that ..."
It could in the form of an inspection step that you can trigger and it could say 'ok I see you've got these 20 tables that you are interested in and that column from that table is a boolean with a not null constraint so I'll watch out for that when you are writing your queries...' sort of thing.
Obviously statically and strongly typed and generally more 'safe' languages would have an advantage here because they can know more about your program statically.
Sorry it's a bit late and that's not in the most coherent way I could put it, just quickly typed it up for the sake of visibility and discussion.