Joel on SQL

Everytime I deploy a new stored procedure, it bothers me I have to update the middle-layer to allow my front-end code to use the function.

My middle-layer between the front-end code and the database simply translates JSON-RPC requests
to SELECT queries calling stored procedures returning JSON back to the front-end.

This is almost perfect.

But, I still have to update a file where I keep all queries, queries.xml, every time I add a new function.


    <query name="Get_Bank_Transaction_Fee" 
           params="Username, Password, Host, TransactionType, ClearingHouse, BankCode, Amount, Currency" 
           result="hashref">
        SELECT * FROM Get_Bank_Transaction_Fee(?, ?, ?, ?, ?, ?, ?, ?)
    </query>


This is a quite nice way to separate the SQL code from the programming code, but since the SQL query consists of
nothing more than a SELECT statement, the name of the stored procedure and the input arguments,
it might as well be automatically generated based on the JSON-RPC request.

I wanted to do CREATE FUNCTION and nothing more before the front-end could start using the new function!

PostgreSQL 9.0 introduced calling functions using named parameters, instead of relying on the position of the arguments.


-- this,
SELECT myfunc(foo := 'Hello', bar := 'World!');
-- is the same thing as,
SELECT myfunc(bar := 'World!', foo := 'Hello');
-- before 9.0 you had to do,
SELECT myfunc('Hello','World!');
-- which means you must not get the positions wrong


This nice feature made it trivial and safe to map a JSON-RPC request to a stored procedure.
The method is the named of the stored procedure and the params is simply a key/value hash,
where each key maps to a named input argument.

The only tricky part which required some usage of pg_catalog.pg_proc was to determine if the function
returned a SET OF or a single row.

I’ve created two perl modules to do the trick, DBIx::Pg::CallFunction and pg_proc_jsonrpc.psgi.

http://search.cpan.org/~joeljac/DBIx-Pg-CallFunction/scripts/pg_proc_jsonrpc.psgi

http://search.cpan.org/~joeljac/DBIx-Pg-CallFunction/lib/DBIx/Pg/CallFunction.pm

Proof-of-concept demonstration:


joel@ubuntu:~$ psql -c 'CREATE OR REPLACE FUNCTION myfunc(foo text, bar text) RETURNS TEXT AS $ SELECT $1 || $2 $$ LANGUAGE sql SECURITY DEFINER'
CREATE FUNCTION
joel@ubuntu:~$ curl 'http://localhost/postgres/myfunc?foo=Hello&bar=World'
{
   "error" : null,
   "result" : "HelloWorld"
}
joel@ubuntu:~$ curl 'http://localhost/postgres/now'
{
   "error" : null,
   "result" : "2012-06-03 10:46:23.972644+07"
}
joel@ubuntu:~$ curl 'http://localhost/postgres/random'
{
   "error" : null,
   "result" : "0.664589043240994"
}
joel@ubuntu:~$ 


Extract from PostgreSQL log:


2012-06-03 10:46:14.270 ICT,"www-data","joel",3601,"[local]",4fcade06.e11,2,"SELECT",2012-06-03 10:46:14 ICT,2/52,0,LOG,00000,"execute dbdpg_p26924_1: SELECT * FROM myfunc(bar := $1,foo := $2);","parameters: $1 = 'World', $2 = 'Hello'",,,,,,,,"pg_proc_jsonrpc"
2012-06-03 10:46:23.972 ICT,"www-data","joel",3603,"[local]",4fcade0f.e13,2,"SELECT",2012-06-03 10:46:23 ICT,2/58,0,LOG,00000,"execute dbdpg_p27386_2: SELECT * FROM now();",,,,,,,,,"pg_proc_jsonrpc"
2012-06-03 10:46:27.732 ICT,"www-data","joel",3605,"[local]",4fcade13.e15,2,"SELECT",2012-06-03 10:46:27 ICT,2/64,0,LOG,00000,"execute dbdpg_p17663_2: SELECT * FROM random();",,,,,,,,,"pg_proc_jsonrpc"


No more middle-layer hassle!

Well, OK, not yet, this is just a proof-of-concept, a lot of work and testing remains until it can be put into production.

I think this could potentially be a very interesting way to make a PostgreSQL database more accessible to the outside world,
in a transparent and convenient way, not requiring any extra work or restarts.

Imagine front-end developers writing their own stored procedures in PLV8 (Javascript) and accessing them using JQuery (Javascript).

All that’s left is the browser and the database!

Less is more.