Actually, it's a really good question. I'm one of the ReQL designers at Rethink, and I was the one pushing for no SQL compatibility. Here is some of my reasoning (we could talk about this for days, though):
* Even SQL designers would tell you SQL isn't a very good
programming language. It even looks like Cobol! Imagine if
every language after Cobol decided to be backwards compatible
-- what sort of world would we live in?
* SQL is really bad for querying hierarchical data with lots of
empty columns. The quality of experience you get from learning
a new language designed for JSON, far outweighs the downsides
of learning it. We're assuming people will be using ReQL
fifteen years from now.
* Designing a language that embeds into your programming language
wasn't possible before -- but it is now. That means no more SQL
injection, no more string manipulation, no more heavy
ORMs. Well worth it in my opinion.
* The chaining paradigm (largely pinoneered and proven by jQuery)
is magical for getting people to intuitively understand how to
write complex queries. No more StackOverflow questions of "How
do I do X in SQL?" because there is now an intuitive
consistency to the language.
* SQL compatibility is very, very difficult. The standard is hard
to implement, and has lots of grey area around the edges. You
can go for full bug-by-bug compatibility, which would take
decades. Or you can go for basic compatibility, which confuses
people. They try to port their application, it works for a
while, and then breaks in some grey area in production, which
results in a terrible user experience.
You're probably right that SQL should have earlier defined this sort of recursion, so that you could sequence groupings and filters more easily, but don't fret: that future is here with common table expressions. Recursion is even supported!
I'm reminded of this abomination of a query I wrote in 2010 that produced a numbers table:
http://blogs.msdn.com/b/sqlazure/archive/2010/09/16/10063301...
Code:
DECLARE @N int = 1000000;
WITH RecursiveRowGenerator (Row#, Iteration) AS (
SELECT 1, 1
UNION ALL
SELECT Row# + Iteration, Iteration * 2
FROM RecursiveRowGenerator
WHERE Iteration * 2 < CEILING(SQRT(@N))
UNION ALL
SELECT Row# + (Iteration * 2), Iteration * 2
FROM RecursiveRowGenerator
WHERE Iteration * 2 < CEILING(SQRT(@N))
)
, SqrtNRows AS (
SELECT *
FROM RecursiveRowGenerator
UNION ALL
SELECT 0, 0
)
SELECT TOP(@N) 1 + A.Row# * POWER(2,CEILING(LOG(SQRT(@N))/LOG(2))) + B.Row# Row#
FROM SqrtNRows A, SqrtNRows B
ORDER BY A.Row#, B.Row#;What I couldn't see at a quick glance was a way to constrain the data in a table or enforce foreign key constraints. I guess those are the trade offs against something like Postgres for schema flexibility and easy distribution. Is that a correct way to view it?
Under the hood, yes. How much of that is exposed to the user is debatable.
> What I couldn't see at a quick glance was a way to constrain the data in a table or enforce foreign key constraints. I guess those are the trade offs against something like Postgres for schema flexibility and easy distribution. Is that a correct way to view it?
That's about right. Some of this is by design (lack of schema enforcement), and we might add features to do that later. There are no architectural constraints preventing us from doing that.
Foreign key constraints will probably never be added to Rethink (or at least not for a long, long time) because doing those efficiently in distributed systems is anywhere from hard to impossible.
I think, If I were implementing a database from the ground up, I'd indeed make my own query language--SQL does suck--but I would probably strive to accept SQL as an option, with the proviso that it's "just SQL syntax with ThisDB's semantics."
This would let people point their old applications, written under "the database is just an SQL-speaking dumb store for simple CRUD operations" assumptions (e.g. any ActiveRecord app), at the new DB and get somewhat-sensible results out, while still letting them write new applications against the new API.
The alternative is forcing any user with existing services to hack up some sort of application-level back-and-forth replication between their old DB and your new DB during the long-and-possibly-indefinite migration period, rather than just migrating in one fell swoop.
I think that the lack of a SQL interface is a big negative. It's really hard to find analyst (not programmers) with good technical skills or train them to proficiency in new technologies. I would have a very hard time selling a platform that existing analysts wouldn't be able to use right away and that few people on the market could work with.
Also, designing a new language makes integration with other applications that much harder.
"We're assuming people will be using ReQL fifteen years from now."
What problem does your product solve that, for example, PostgreSQL doesn't?
r.table('foo').filter(...)
r.table('foo').group('category').filter(...)
From the examples you have posted, it appears that ReQL is a lower-level abstraction that SQL. In SQL you specify what you want logically and the DB turns this into a query plan. It appears that ReQL is more like a query plan itself, where you explicitly specify the data flow from stage to stage of query evaluation.
As a more specific example of this, it appears that in many cases in ReQL the user specifies what index should be used in the query itself. SQL is more abstract than this; the idea is for the query planner to figure out what index(ex) should be used.
It's suggesting:
SELECT category,
SUM(num_comments)
FROM posts
GROUP BY category
HAVING num_comments > 7
r.table("posts")
.filter(r.row['num_comments']>7)
.group('category')
.sum('num_comments')
I don't think they are? Your ReQL to me looks like it's applying WHERE num_comments > 7 and then aggregating that.
I mean regardless, your example SQL should be doing the >7 against an aggregate (e..g sum(num_comments)) not a field, that SQL does not work as written.
In ReQL any command you call after `group` runs on each group. So once you've called `group`, you can run anything you could run on a table on each group and that just works.
SQL just happens to call those WHERE and HAVING instead of "filter" both times.
# get a sample of 3 elements from a table
r.table('foo').sample(3)
# get a sample of 3 elements from each group
r.table('foo').group('category').sample(3)
HAVING exists because it was created prior to subqueries/dynamic tables, otherwise we'd have been likely to just use:
select * from (select category, sum(num_comments) as comments from posts group by category) as temp where comments > 7The simple reason is we now have plenty of decent scripting languages to run as interactive prompts, all of which are far better at interfacing with the rest of the system than SQL is. Exposing a sane, direct, API in those languages gets you much more than SQL, along with having removed multiple layers of confusion, including string generation, escaping, reparsing, then is it doing what you want, etc.
I've been using LevelDB (via plyvel) a lot lately for data storage, and every time I end up having to use SQL (even indirectly via ORM) is painful by comparison because you can just feel the control being taken away from you, and somewhere you end up having to fire up the DB prompt for no good reason such as adding strange DB specific indexing flags to columns or even setting up authorization and DB creation, making it yet another thing to go wrong during deployment.
Protocol buffers stored in LevelDB prove so easy to use by comparison with something like SQLite or PgSQL just at an API level. The resulting code is simpler, cleaner, and much easier to reason about. If I need to move it to another format the code, again, is amazingly small.
As someone that cares deeply about my app's data structure seeing the acceptance of a world beyond SQL is one of the best developments in my career, and experience means I simply don't trust any SQL based abstraction to give you the controls to get it right.
Actually it's the very opposite.
SQL is a higher level abstraction for all this ad-hoc junk, based on actual mathematical principles (relational algebra), and it's also declarative, instead of imperative.
Furthermore, all this NoSQL query systems now emerging are nothing new. They were tried in the 70s and early 80s, and people found out that they sucked. Before SQL what we had was, well, NoSQL.
Wanting a flexible schema-less db or one that's denormalised for certain needs (like Google's or Facebook's) makes sense.
Replacing SQL and RBDMS for the common tasks they are used (company logistics and accounting, etc) with NoSQL is a regression to the primitive past. I guess people not knowning (computing) history are doomed to repeat it.
Another reason is that SQL does its best to decouple the query (what you're asking for) from the execution plan (how it actually gets evaluated). But for operations like joins, the precise way the query is evaluated can have a huge effect on performance. So it makes sense to design your query language to explicitly expose those knobs to the programmer.
MongoDB's querying is nowhere near as powerful as SQL. And understanding its limitations and pain points are essential when structuring your data. Otherwise you'll never really scale unless you've got data that's embarrassingly easy to query (eg: filtering on 1 or 2 indexed fields).
Being able to write JS map-reduce queries is fine when you need to do stuff ad-hoc, but none of it can be used in production at scale. Then there's the aggregate framework which helps quite a bit but still doesn't offer the same level of performance I'd expect from Postgres, MSSQL, or Oracle. Things get really yucky when you have to start unpacking arrays - especially since there's a memory limit on $sort and $group. There are further issues related to skipping being slow on large collections because it must step through.
And to cope with these limitations you need to invest a ton of time into coding around them. The freedom of the database being schemaless? Gone. You must carefully structure data and decide up-front how it needs to be queried - or cope with poor performance.
And those complex queries, when written in ugly JSON, aren't the most human-readable things in the world. Certainly not any better than SQL.
I'd rather just invest the time in learning SQL than partake in the mental gymnastics necessary squeeze high-performance non-trivial queries out of MongoDB.
I understand MongoDB is a big hit with people who use it for low volume internal tools or people building MVPs. I can totally understand how its an awesome tool for those. But rusty old SQL starts looking better when your app scales and the business demands change.
SQL really is well-geared towards relational, flat database systems. Mongo's querying is an obtuse command-based language that always felt like it was adding hacks on hacks to get the data you wanted.
REQL is almost like having your data in-memory, and you're running programmatic expressions on it that seamlessly melt into your native language. The lack of a query optimizer almost makes it better because you have to think about your query plans and indexes instead of just firing it off at the server and crossing your fingers. You're not running commands, you're processing data.
I don't have a lot of experience scaling Rethink, but I do know from reading the docs and architecture that it scales out better than most SQL servers will and certainly scale up better than Mongo. I've tried to scale both MySQL and Mongo. Both are difficult and painful.
You can't conclude that because of Mongo's failures, SQL beats Rethink. Rethink is light years ahead of Mongo.
And, really, if you've worked with some of the "higher level" interfaces to SQL like SQLAlchemy or Laravel's QueryBuilder, ReQL doesn't seem that alien; they all tend to work by method chaining as well. (But they usually don't support everything SQL does, unlike ReQL's interfaces.)
Also: unlike my (admittedly limited) exposure to other NoSQL systems, RethinkDB supports relations and joins -- it's actually a lot easier for an SQL fiend to move to Rethink than any of its competitors.
- It's actually easy to learn -- especially with the data explorer
- It's nicer to use than SQL -- You can chain commands in the language you are using