DBSlayer proxy on BASH in 5 minutes or another way to send JSON from MySQL

It was in the evening, there was nothing to do, but the head of the lessons did not give rest ... This post was created as a result of purely academic interest. It all started with the fact that when developing a small client application for their needs, implemented on Javascript, it became necessary to interact with an existing database where the required data is stored. The base is MySQL. One of the simple ways is to implement a server-side script (in PHP or some other language) that, by incoming parameters, makes the necessary request and returns the result in JSON form.

Another option is a DBSlayer proxy for MySQL. I’m telling you who didn’t hear about him: it was created in the bowels of the New York Times as a means of abstraction and balancing the load on the database. You can read more at code.nytimes.com/projects/dbslayer/wiki/WhyUseIt. DBSlayer provides a JSON-based API, known among NodeJS developers.

But this is not our method either. Under the cut is a simple solution to this problem on BASH.



Well, I was too lazy to put this stray, and the task, I recall, is solved purely for myself, no loads and other joys of production solutions. Writing in PHP is a little tired, the soul asked for variety and some thread to show off. I decided to make my simple layer, in the form of an HTTP server on BASH, with the implementation of the necessary functionality.

It's no secret that Netcat, in conjunction with adding a couple of lines on the bash, can be turned into a WEB server. On a habr there were even posts about it. Taking this idea and finalizing it, we can get a simple analogue of DBSlayer on the bash knee in five minutes.

From words to action ...

First, we’ll write a simple utility to convert the output from the mysql console client to JSON format:

cat ~/bin/mysql2json.sh
#!/bin/bash
sed -e 's/\t/\",\"/g' \
    -e 's/^/\[\"/'    \
    -e 's/$/\"\],/'   \
    -e '1s/\(.*\)/\{\"fields\":\1\ \"data\":[/g' -e '$s/.$/\]\}/' \
| tr -d "\n"

It is used simply:

mysql -e "SELECT * FROM `mytable`" | ./mysql2json.sh
{"fields":["field_1","field_2","field_3"],"data":[["1","2","3"],["4","5","6"],["7","8","9"]]}

Now the server code itself, which we put in the dbjs.sh file:

#!/bin/bash
:;while [ $? -eq 0 ]
do.
  nc -vlp 8880 -c '(
    r=read;
    e=echo;
    $r a b c;
    z=$r;
    while [ ${#z} -gt 2 ]
    do.
      $r z;
    done;
    f=`$e $b|sed 's/[^a-z0-9_.-]//gi'`;
    h="HTTP/1.0";
    o="$h 200 OK\r\n";
    c="Content";
    m="mysql -ulol -ptrololo"
    m2j="~/bin/mysql2json.sh"
    $e "$o$c-Type: text/json";
    $e;
    if [[ ( -n "$f" ) && ( "$f" != "favicon.ico" ) ]]
    then.
      $e "+ Connect to [$f]">&2;
      db=${f%.*};
      tb=${f#*.};
      if [ "$tb" = "$db" ]
      then
        $e `$m ${db:-test} -e "show tables" | $m2j`;
      else
        $e `$m ${db:-test} -e "select * from $tb" | $m2j`;
      fi;
    else
      $e `$m -e "show databases" | $m2j`;
    fi
  )';
done

Actually that's all. We start and see in the console something like:

[ bash: ./dbjs.sh
listening on [any] 8880 ...

Next, we simply contact our server, on the specified port, and we get the output:

// http://127.0.0.1:8880 - Выводит список баз данных
{
    fields: [
           "Database"
    ],
    data: [
         [ "information_schema" ],
         [ "test" ]
    ]
}

// http://127.0.0.1:8880/test - Выводит список таблиц базы данных test
{
          fields: [
              "Tables_in_test"
          ],
         data: [
             ["prods"],
             ["shops"],
             ["sp"],
             ["stat"]
         ]
}

// http://127.0.0.1:8880/test.shops - Выводит данные таблицы test.shops
{
     "fields": [
           "id",
           "name",
           "adress"
     ],
     "data": [
           ["1","aaaaa",""],
           ["2","bbbbbbbbb",""],
           ["3","cccccccccccccccc","ccc"]
     ]
}

The script is not perfect, but no one bothers to modify it, if at all there is a need.

PS: To the question: Why ?, I’ll immediately answer: for the fan. Life must bring joy. I like to get positive from such non-standard solutions.