Having written some of this code myself, I have to disagree; transforming SQL fragments is legitimately frustratingly challenging. I have to admit I don't know how to show a small example that captures the problem, though, because this is one of those cases where the small examples always look easy. It isn't until you're trying to support all of them at once that it is a problem. Combining two fragments that each specify tables, joins, where clause filters, and potentially subqueries with each of those recursively is nontrivial when you get down to it.
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.
Definitely not an easy task but the degree of difficulty depends on the language.
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]
You seem to persist in believing the problem is on the input side. It's not; it's on the output side. My entire point is that the resulting SQL generation code is what ends up quite hairy, because the way we have found to separate concerns in 2015 and SQL are very, very different.
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!
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.
No, I'm saying input is easy, type system does virtually all the work. Output is where the effort is spent (i.e. pattern match on query expression to assemble the statement).
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.
Comments
Having written some of this code myself, I have to disagree; transforming SQL fragments is legitimately frustratingly challenging. I have to admit I don't know how to show a small example that captures the problem, though, because this is one of those cases where the small examples always look easy. It isn't until you're trying to support all of them at once that it is a problem. Combining two fragments that each specify tables, joins, where clause filters, and potentially subqueries with each of those recursively is nontrivial when you get down to it.
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.
Definitely not an easy task but the degree of difficulty depends on the language.
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
You seem to persist in believing the problem is on the input side. It's not; it's on the output side. My entire point is that the resulting SQL generation code is what ends up quite hairy, because the way we have found to separate concerns in 2015 and SQL are very, very different.
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:
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.No, I'm saying input is easy, type system does virtually all the work. Output is where the effort is spent (i.e. pattern match on query expression to assemble the statement).
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.