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

It was evening, there was nothing to do, but the bad head didn’t give rest to the urks ... 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 your needs, implemented in Javascript, it became necessary to interact with the already existing database where the desired data is stored. Base - MySQL. One of the easiest ways is to implement a server-side script (in PHP or any other language), which, using incoming parameters, makes the necessary query and returns the result in JSON form.

Another option is DBSlayer proxy for MySQL. Whoever did not hear about him, I tell him in the story: he was created in the depths of the New York Times as a means of abstraction and load balancing on the database. Read more at code.nytimes.com/projects/dbslayer/wiki/WhyUseIt . DBSlayer provides an API based on JSON, known among NodeJS developers.
')
But this is also not our method. 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 remind you, is solved purely for myself, no loads and other joys of production solutions. I am a bit tired of writing in PHP, my soul asked for a variety and some thread. I decided to make my own 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 writing a couple of lines on a bash, can be turned into a WEB server. On 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 write a simple helper utility to convert the output of the results from the mysql console client to the 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 is placed in the dbjs.sh file:

 #!/bin/bash :;while [ $? -eq 0 ] do. nc -vlp 8880 -c '( r=read; e=echo; $rabc; z=$r; while [ ${#z} -gt 2 ] do. $rz; 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 something like in the console:

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

Then just turn to our server at the specified port and 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 there is a need for it at all.

PS: To the question: Why ?, I will immediately answer: by fan. Life must brings joy. I like to receive a positive from such non-standard solutions.