Both Haskell and Scala, for example, have sufficiently powerful type systems to allow for building up a typed query expression of arbitrary complexity, which can then be deconstructed via pattern matching to assemble the sql statement. Easy? Not at all, but very much doable, and incredibly elegant...until you need to support various database engines and their limitations/extended features; then the implementation hacks begin :\
Personally I think the work of Stephan Zeiger on the Slick library in Scala is ground breaking. Also, Wadler et al's recent-ish paper on a composable query DSL in F# is worth checking out[1]
[1] http://homepages.inf.ed.ac.uk/wadler/papers/yow/dsl-long.pdf
Let me put it this way... it is precisely because a fluent, Haskell-native SQL querying interface little resembles SQL in either syntax or usage that there is the problem. It is precisely that there have to be these library at all that is the problem. If it were easy, these library wouldn't even exist, or would be little more than drivers, but they're not just drivers... they do a lot of real work.
If SQL didn't stink by the standards of modern separation of concerns, we wouldn't need "ground breaking" work!
Or, to put it another another way:
$ git clone [email protected]:slick/slick.git
$ cd slick
$ cloc .
---------------------------------------
Language files blank comment code
---------------------------------------
Scala 261 3589 3844 23129
Cut down to just the Scala. 23kilolines of Scala is a lot of Scala! This is not an "easy" task. "Easy" would be something that just wrapped up the existing syntax in a slightly more native form and would clock in somewhere in the several hundred range.Why these libraries exist is because of string-ly typed programming; in the case of sql: 1) it doesn't compose; 2) is not safe (sql injection attacks); 3) difficult to refactor; 4) untyped, therefore whole class of bugs arise.
And yes, these libraries do a ton of work, well beyond just generating sql statements, which, in the case of Slick pushes the LOC count way higher (non-blocking IO, supports basically every database engine, native function support, jdbc modeled in scala, etc., etc., it's a huge engineering effort, somehow by one person).
Anyway, I'd like to see a much smaller composable query dsl with fewer features and opt-in database support. Compile that to javascript and run in the browser against local database would be very interesting. I think this can be done, but would probably be pretty restrictive in terms of features supported.
It also really doesn't help that "SQL is declarative" is basically a lie, and it very frequently totally matters which "synonymous" query you actually throw at the database, thus eliminating a lot of the obvious clean answers in any practically-useful library.