DBSlayer + Node.js

Lately it seems the rage among developers is to take node.js and combine it with something else unusual. So here’s my contribution.

DBSlayer is a project by NYTimes a few years ago that seems to be somewhat forgotten but is pretty cool. It’s another MySQL proxy, but with a slight twist. Rather than use a binary protocol, or XML, they went with JSON. It supports things like connection pooling, round-robin distribution to slaves, automatic fallover, and it’s mutithreaded. It’s pretty fast and easy to work with. It’s almost like turning a MySQL database into a REST API. You pass a SQL query as a query argument and it gives you a JSON response.

Once you start it you can do something query using a request like:

http://localhost:9090/db?%7B%22SQL%22%3A%22SELECT%20*%20FROM%20facts%20WHERE%20id%3D1%3B%22%7D%20

That will give you a JSON object containing the result of your query.

So doing that in node.js is roughly:

var sql = ‘{"SQL":"SELECT * FROM facts WHERE id=7;"}’;
 
var http = require(‘http’);
var client = http.createClient(9090, ‘localhost’);
 
var request = client.request(‘GET’, ‘/db?’ + escape(sql), {});
request.end();
request.on(‘response’, function (response) {
  console.log(‘STATUS: ‘ + response.statusCode);
  console.log(‘HEADERS: ‘ + JSON.stringify(response.headers));
  response.setEncoding(‘utf8’);
  response.on(‘data’, function (chunk) {
    console.log(‘BODY: ‘ + chunk);
  });
});

Running that looks like this:

$ node test.js
STATUS: 200
HEADERS: {"date":"Fri, 27 May 2011 02:02:27 GMT","server":"dbslayer/beta-12","content-type":"text/plain; charset=utf-8","content-length":"290","connection":"close"}
BODY: {"RESULT" : {"HEADER" : ["id" , "fact" , "author" , "ip" , "timestamp"] , "ROWS" : [[7 , "1+1=2" , "raccettura" , "127.0.0.1" , 123456]] , "TYPES" : ["MYSQL_TYPE_LONG" , "MYSQL_TYPE_VAR_STRING" , "MYSQL_TYPE_VAR_STRING" , "MYSQL_TYPE_VAR_STRING" , "MYSQL_TYPE_LONGLONG"]} , "SERVER" : "db"}

You could obviously clean that up and create a little library to hide the HTTP parts.

It’s an interesting JS centric way to abstract your database while maintaining SQL level control. JSON is becoming the new XML.

Leave a Reply

Your email address will not be published. Required fields are marked *