

Call PostgreSQL functions via HTTP JSON-RPC - JoelJacobson
http://joelonsql.com/2012/06/03/call-postgresql-functions-via-http-json-rpc/

======
kbanman
Maybe coming from a web development background has left me paranoid, but this
just screams scary to me.

~~~
gaius
Nah, it's safe enough. See Oracle's take on this, <http://apex.oracle.com/>

------
einhverfr
We've been doing something kinda similar in LedgerSMB with versions of
PostgreSQL going back to 8.1 when it was current....

Our approach was different though. We have object methods explicitly mapped to
stored procedures by name (this is done to limit the stored procedures exposed
by the application). If the number of arguments changes, the middleware does
not need to be updated.

The queries are then called through an interface that, in essence, hands a
hashref (in Perl) and a stored procedure name to a mapper function which then
looks up the argument names and assembles the query.

This is then called, with an optional order by clause, and the results
returned as a list of hashrefs.

While this isn't the exact technologies listed here, the approach is something
I have found wonderful and so it's probably worth sharing.

~~~
JoelJacobson
It this part of the LedgerSMB project? If so, what part of the source code
handles things? Would be interesting to see how you have implemented it.

When you create a new function, do you have to do anything in addition to do
CREATE FUNCTION, before you can use the function in your front-
end/backoffice/whatever external system component?

~~~
einhverfr
Yes it is. The file that has the mapping routine is LedgerSMB/DBObject.pm.

I won't say it's great code. It was one of the first things I wrote when
trying to break out of the inherited codebase from SQL-Ledger (shudders).

We also prefix all argument names in the sql function definition with in_ to
prevent collision with column names.

Here's a post I wrote about the mechanics:

[http://ledgersmbdev.blogspot.com/2011/10/introduction-to-
sod...](http://ledgersmbdev.blogspot.com/2011/10/introduction-to-soda.html)

~~~
JoelJacobson
I looked at the code. Reminds a bit of what we are currently doing. We also
have a Perl function for each SP. But that's what I want to get away from, the
middle-layer should be fully capable of automatically mapping the request to
the appropriate SP, like in this proof-of-concept. Can you think of one
scenario when it isn't possible to do so? I've tried but haven't came up with
anything.

Also, in execute_method, what if two functions matches the same name? Looks
like you just pick the first one it finds. Shouldn't you throw an error if
unsure which one to pick?

~~~
einhverfr
Answering in inverse order:

Since the name is the discovery criteria, function overloading is not
supported in this interface. It's possible to define a different one, but the
idea here is that the stored procedures should be discoverable based on name.
So we have test cases that raise errors when functions are overloaded that we
don't know should be.

So in answer to your last question, behavior is undefined, and such a
condition will trigger failures in our database unit tests.

On the other side, the reason to enforce an explicit mapping is because it
isn't clear how much you really want to trust the client to execute any
function in the database at all. An earlier version used AUTOLOAD to map these
methods in directly so no middleware changes would be necessary. We abandoned
that approach in part because of concerns it could be abused. So the issue
isn't what can you do with my framework that you can't do with your framework
but actually the other way around. Do I want to whitelist or blacklist stored
procedures? Whitelisting seems safer.

A future version will probably use Module::Compile to build mappings from the
database, but we haven't defined interfaces sufficiently to make this happen.
However one idea is to tie Perl classes to PostgreSQL complex data types, and
grab functions which return those complex types. Since all procedures are
supposed to return useful data (even delete operations might return the
deleted row), then this might work.

Again that's in the design phase.

Edit: It occurs to me that if the return type and the function name become the
discovery criteria, then two functions could have the same name but different
args as long as they have different return types.

------
EugeneOZ
It's horrible. You don't need API, just give to clients of your API
credentials to your database and they will use DB directly. And it still will
be horrible (because only good way to use application by another application
is API, not direct access to data). Also, stored procedures is a bad thing -
business logic should be written in code, not in data and not in the database.

~~~
ocharles
> Also, stored procedures is a bad thing - business logic should be written in
> code, not in data and not in the database.

Why?

Your post generally makes a lot of criticisms, but fails to back up any of the
points made.

~~~
NickNameNick
Primarily, because the stored procedures are not effectively under version
control.

~~~
JoelJacobson
Maybe yours aren't, but mine are. I've been using this simple tool for three
years now.

<https://github.com/gluefinance/PgDeploy>

It gives you an extra safety net, in case the version in the database would
differ compared to the version in the VCS, even though they shouldn't, someone
might have been evil behind your back. :)

PgDeploy lets you preview the change and show you a diff. If the diff is
expected, you can proceed and deploy.

