
DBSlayer - SQL over JSON for easy scaling-- from the folks at NYTimes.com - joshwa
http://code.nytimes.com/projects/dbslayer
======
SwellJoe
This is quite a brilliant little idea. A couple of years ago I was involved in
a newspaper project (Boston.com) that used Squid with ESI to build the page
from disparate Zope backend servers, and it resolved quite a lot of problems--
like being able to have a fast and database-less server in the front that
could make decisions about which backend servers to draw from based on the
required content. The backend servers then didn't need full copies of the
database, because they would only ever be asked for content they were
authoritative for.

DBSlayer takes the web server and the proxy server out of the picture, and
puts the site building burden (to some degree) on the client. It could,
theoretically, also make decisions about which database to query, such that
the memory caches on the database servers will always be primed with the
content that the database is authoritative for.

The comments so far seem concerned about security...but DBSlayer wouldn't need
write access, or access to all tables. It could simply be a read-only window
into some tables. I guess that's a foreign notion to folks who do everything
in one process, which has to have write access...but it's one of the aspects
of proper databases that makes them useful in large scale deployments.

~~~
staunch
I do think there would be serious security implications in exposing read-only
access to even a limited number of tables via arbitrary queries from clients.
DoS would be trivial and unexpected data leaks easy. Having to explicitly
expose parts of your data only as you intend is a great security precaution
(and good use of abstraction).

But I don't think the intended use is to have client browsers hit DBSlayer
directly. My understanding is that you send SQL from your app layer via HTTP
to DBSlayer then it hits a DB and returns JSON.

------
tlrobinson
It's cool that NYT is releasing some of their code as open source projects,
but I'm not sure I understand this particular project. The first thing I
thought of when I heard "SQL" and "JSON" in the same sentence was "oh please
don't let this be some client side JavaScript SQL thing"... it basically
exposes your database as a HTTP service: you send it SQL in a HTTP request and
it returns JSON in a HTTP response. Where's the authentication? What's
stopping someone from doing
"<http://myserver.com:9090/db?DROP%20TABLE%20articles>" or something?

~~~
jsjenkins168
This is basically just a database proxy, but allows the responses to be
transmitted as JSON, which is cool because it can interface with just about
any other server backend that supports HTTP requests and can parse JSON.

I can't find any indication of being able to authenticate with the HTTP
request. So I assume this means you would need to ensure the connection
between the web server and DB proxy services were locked down, meaning no
access outside of the subnet between the severs. I guess since the service
pools connections to the actual DB that is where the authentication to the DB
is handled. However like you say if someone could get access to the DBSlayer
they could get access to all of your data, if this were the case. An option
would be to just use SSL if it were supported. There is a bit more overhead
with this, but if you are not needing to make tons of frequent queries it
should be fine and sufficiently secure. I cant find any info on if SSL is
supported..

Still though, if it could be made secure I like this because it is so
universal.

------
DanielBMarkham
This is cool stuff, guys. Using JSON means you can put the authentication in
the HTTP request, split up the services between various platforms, proxy out
the web apps to any kind of tech, and scale the page load performance in a
hardware-agnostic fashion.

I wrote someting a few years ago with JSON. IMO, it's way better than that old
XMLHTTPRequest stuff. Done correctly, it really makes this web-as-a-platform
thing sing.

Very cool stuff.

------
kogir
I wish they had given examples of how they use it internally. From the lack of
authentication I would guess that they don't call it directly from the client,
but it could be made safe for certain readonly data.

While their reasons for using http and its benefits are clear, I'm still
trying to figure out why they used json though. Why not XML or a compact
binary protocol?

~~~
SwellJoe
"From the lack of authentication I would guess that they don't call it
directly from the client"

I don't see how lack of authentication has anything to do with requests coming
directly from the client.

Do you make people authenticate before they can read your website?

------
toisanji
It does look pretty neat. Too bad it only works over Mysql currently. I wonder
how their approach to the problems compares with CouchDb

------
khoerling
Brilliant... I suspect that quite a large number of similar projects already
exist in non-open-source fashion.

