https://github.com/foursquare/rogue
Typesafe queries using SQL-like syntax. Particularly when used with the Spindle library (https://github.com/foursquare/spindle), this is best ORM I've ever used.
It's totally possible. There's a ton of libraries that do it in various ways. It's just not even remotely easy, like it should be. If you look into the inside of those libraries you'll find a C'thulian monstronsity of special cases interacting with special cases until the whole thing just explodes into a brain-consuming mess, because SQL was very, very clearly not written for this use case.
In another 10 or 20 years I look forward to data-based analysis that tries to determine how much of the "NoSQL" movement was because the relational data model doesn't work for all use cases, and how much of it was the entirely accidental (in the Brooks sense) problems with A: SQL, the language itself, not its capabilities and B: schema migrations with no essential reason to be as painful as they are. (And something something column stores, but I'm not sure where they fit into this story exactly.) And to be clear on tone, I really am interested. I'm pretty sure the answer won't be either extreme but I'm pretty uncertain about where in the middle we'll fall.
You can do this today, in various statically typed languages[1] (perhaps dynamic languages as well, though the composition will likely be more ad hoc).
Agreed re: under the hood complexity, but that's more in supporting multiple database engines (and related corner cases) than the transformation of Query > AST > SQL.
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.
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.
And really, when you think about it, it is a bit weird, (we send sentences of almost-english toward the DB, then get structured data back) and there's nothing wrong with that.
But many devs do value the ability to stay in hash-map land all day without needing to think about how to cross an abstraction boundary into the DB, and MongoDB actually has a pretty cool solution there.
which is essentially every single production deployment of a SQL database in the last twenty years. That's the problem.
As someone who has built with Oracle’s Pro C(++) and with various ORMs, Sequel is an ORM that makes me reasonably happy and gives me the expressiveness of embedded SQL without compromising my object model.
No, it was not. It is an abomiation, just like SharePoint CAML Query or any other "express-your-query-as-an-AST" approach. Sure, it's a paradise for Buzzword-Compliant Fluent Interface Enterprise Query Builder-type libraries.
> Querying SQL data involves constructing strings...
No. Case in point: LINQ. How it is implemented is irrelevant to the statement above.
> ...and counting arguments very carefully
No. Named parameters to the rescue.
1. http://mongodb.github.io/mongo-csharp-driver/2.1/reference/d... 2. https://www.progress.com/connectors/mongodb
That said, it was really clunky dealing with ordering as JSON doesn't guarantee serialization order (sort fields), and that was cumbersome to work around iirc...
Today RethinkDB would be my first choice for a similar solution... better ops interfaces with replication + failover. Instead of having to do a replica of a sharded system in mongo for distribution with redundancy. Also, while the query interface is a little more complicated to get started with, when your queries get more interesting, it's not nearly as messy as mongo gets.
Well SQL is just an AST abstraction, which was made to be human readable. And that's exactly the issue, SQL is excellent for humans to express querys but really inconvenient to interface with other programs unless you express every single query ad-hoc (which seems the way most people choose since ORM tend to perform poorly)
The vast vast majority of SQL in the wild is pretty straightforward and sucks only when the schema sucks (can't blame language for that). Most of the time people hate SQL, they do so because they are working with crap schema (whether someone else created it or they themselves didn't think it through enough). Sane schema + putting a bit of effort write readable SQL can go a long long way.
Optimizing for the "newbie" case is not a failure.
If a newbie asked me whether to learn MySQL/PostgreSQL, or MongoDB, I'd absolutely say *SQL. Yeah, it's not as straightforward as MongoDB. It's not intuitive for many. But this knowledge will still be useful a year, or 5 years later. Finding a job will be easier too. So, yeah, it could be better, but it's still a good, safe choice.
In the relational world, you can get a lot of insight into what will and won't work with simple math. There certainly are surprises in every database, but most things are not surprises.
NoSQL databases tend not to leverage such a clear and complete model. You have to study each implementation to learn where the surprises are, and there are more of them.
That's not to say they don't both have their role, I just wonder if people who have a little (and in my care very little) CS theory still remembered find RDBMSes easier than those who have learned development without theory.
Not at all. SQL is trivial. Thinking through some joins is tricky, but it's usually because I actually underestimated how logically tricky the thing I was trying to do actually was. Optimizing queries can be very difficult, though.
Years of SQL injection mitigation don't agree with you. Are you sure you just don't prefer it because you are familiar with it?
I don't see how using vanilla mongo in the same fashion would solve the blight you have placed on SQL.
While SQL is a powerful language, the article is right. Mongo query language is useful enough to easily give application developers a place to query and store data without giving them enough rope to hang themselves.
Because it doesn't have to be shinny and all, it just works. There are no widely used alternatives, so we can say that people are happy-enough using it. See the situation with JS. CoffeeScript and all, it's quite popular. Is there an analogy (in terms of functionality and popularity) in SQL? Nope. No one bothered to do it well enough, or not enough people found that it's worth enough to learn it.
We don't need to reinvent something every few years just because new JS frameworks come out and others are forgotten every few months.
Granted, I think a big part of it has to do with joins being the annoying part of SQL...
Joins might be tricky if you are unused to them, but they are the secret sauce of relational databases. They are what make it possible to have dozens of views on a single normalized data structure.
Here's the repo:
https://github.com/adewes/blitzdb
The implementation is stable but I'm still working on finishing Python3 support and documentation.
I think this is the most significant factor, by far. With Mongo it's turtles (or at least Maps/Hashes) all the way down, without a strange pseudo-english layer near the bottom that forces you to translate back and forth. For some devs that's a big deal.
For the last while I've been experimenting with bringing the same feature to PostgreSQL (http://bedquiltdb.github.io), turns out it's very do-able, but I don't have enough time to make it as featureful as it needs to be.