I've been using SQL over 20 years in Oracle/Informix/Sybase/IBMDB2/MSSQL and have written exam questions on SQL.
That said, concatenating raw strings to form SQL that has no compile time type-checking is tedious and error prone. Thankfully, my "expert" knowledge of SQL does not blind me to the benefits of what a well-written ORM can do for productivity. (E.g. results caching, compile-time validation, auto-completion in the IDE, etc)
Powerful sorta, fun debateably, highly rewarding probably, flexible, absolutely not. By far one of the biggest reasons to use something other than raw SQL is because of SQL's near total lack of composability. Show me a valid SQL fragment that represents "the 'permission' field of the given table is set to true OR the joined-in user has the superuser flag", where the join is represented in the fragment itself such that any query I apply it to will have the requisite join added in if necessary. Then show me a fragment that represents "this email and all of its attachments". Then show me how to compose those fragments into a full query. You can't; if you're using raw SQL you have to manually interleave those things into every query you want to use them in. SQL basically mandates copy and paste as the only abstraction available, and it isn't even all that great at that since there's no such thing as scopes within a query making it easy for your copy & pasted fragments to stomp on each other's names.
Some of the additional query languages might be able to do it (they could certainly do chunks of what I said), but they'd still be pretty klunky about it since they bodge it on the side, and still don't compose anywhere near as well as they could or should.
Mind you, I'm not sure this library can do it either; SQL is also quite difficult to wrap around because of its structural deficiencies. Trying to hack away foundational issues at higher levels is always messy, error-prone, and still filled with the quirks that shine through.
You can use VIEWs [1] as an abstraction mechanism. It's not perfect, but it can abstract certain things away. I think it would work for your first example.
Something like:
CREATE VIEW permitted_X AS
SELECT X.*
FROM X, user
WHERE X.user_id = user.user_id
AND (X.permission OR user.is_super);
You second example doesn't have enough info for me to see if it can be accomplished by a VIEW.
Other abstraction mechanisms are user-defined functions.
With your view I still think I can't pass a table in; your X is not a variable. That's my point. There's a lot of power in SQL and its associated technologies, but there's also a loooooot of places where in a modern system we'd have a variable or some other composition mechanism, but there's no first-class existence of that mechanism in SQL. For instance, in systems programming languages, "first-class functions" have won so thoroughly that it's hardly even a "feature" anymore, it's just part of the baseline for any new language, or it better have a darned good reason they aren't present. SQL has not even remotely had that revolution.
Some of these things are fixed up by the product-specific languages, but only some of these things.
Here's another example; using your product-specific language, can you create a table for me with a variable number and types of columns based on the parameters passed in? I don't know them all, but I bet it's hard in most or all of them. No credit if your product-specific language lets you bash a string together and then somehow execute it; I'm calling for everything to be done via first-class mechanisms. (Also, I'm not asking for whether this is a good idea; it is obviously a tricky thing of dubious use. But that should be a software engineering determination, not a language restriction.)
As they've stated, the authors of SQL are not opposed to generics. They just haven't found an idiomatic way of incorporating them into the language that doesn't also impact prepare times. ;-)
I thought the unit object in conceptual model of SQL was relation/table. So a valid "SQL fragment" is a expression that evaluates to a result table, i.e a subquery. And in something like Postgres, you have functions returning tables, which should address the scoping issue that you raise.
When I run sql profiler for performance issues I like to see what is pass is what is received by DBEngine, I am not sure if it is helpful to add another layer for simple crud apps which is more than 85% in real world.
Not to argue your general point, but it seems to me that your specific composability examples could be addressed with PostgreSQL's temporary views and WITH clause.
That is not my experience. I have seen it countless times that people use an ORM in a way that leads to horribly inefficient queries behind the scene (e.g. looping over a result of a query and then executing a query for each of the result, where both could be combined in one). Things that should take milliseconds were taking hours...
It's also not enough to know SQL. You also need to know how your ORM maps objects to the database. E.g. do you get the same object if you execute a query twice? A surprising number of people don't know, and assume that ORM objects behave like normal objects and that is not always the case.
I think it's probably very dependent on the language and ecosystem you find yourself in when you need to approach this aspect of software engineering. It's probably much easier for someone learning to program and using Rails to let ActiveRecord be what you learn, and let it build your schema, and learn the minimum to make that work. Like all tools, shallow learning leads to misunderstanding, inefficiencies and mistakes.
ORMs are usually easier to maintain. Update your objects, write a migration. Things are usually updated.
If you start creating custom views and queries then you need to update them whenever there's a change to your objects. Also getting views into source control is something that you'll have to manage on your own.
An ORM is just less work. Although you'll often need to drop into real SQL for performance reasons or for complex work.
ORM does the reverse, it tries to map objects into relational data, the problem is that those are two incompatible way of storing data, you can do this for some objects but it doesn't apply for all cases.
Since relational algebra was proposed by Cobb, it took over all databases because it was proven that it's most efficient way to store data we know.
ORM might be useful for some cases, but it will generally limit you with what you can do with data.
IMO I think what's really missing is to have have another query language that is integrated with your programming language (so you don't have to write it as a string, and can benefit from language features such as auto completion, type checking etc).
I kind of wish QUEL[1] would win instead of SQL, since that language seems to be easier to be integrated.
There are also attempts such as LINQ[2] which appears to do good (I did not use it myself, since I don't program in .NET), but it looks great, I wish it became a standard and got integrated in other languages.
That said, concatenating raw strings to form SQL that has no compile time type-checking is tedious and error prone. Thankfully, my "expert" knowledge of SQL does not blind me to the benefits of what a well-written ORM can do for productivity. (E.g. results caching, compile-time validation, auto-completion in the IDE, etc)