Call PostgreSQL functions via HTTP JSON-RPC

http://joelonsql.com/2012/06/03/call-postgresql-functions-via-http-json-rpc/

by JoelJacobson • 14 years ago

25 28 14 years ago

joelonsql.com

kbanman• 14 years ago

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

gaius3• 14 years ago

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

einhverfr6• 14 years ago

It scares me because it is letting your front-end pick your stored procedures with very little restriction.

EugeneOZ5• 14 years ago

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• 14 years ago

> 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• 14 years ago

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

EugeneOZ5• 14 years ago

Lot of reasons. Refactoring, unit-tests, readability... If you don't understand my points, it doesn't means they falls :)

mryan4• 14 years ago

"business logic should be written in code, not in data and not in the database"

This makes no sense. Stored procedures are code - just code that executes within your DB, instead of on your app servers. I agree that it makes sense to keep your business logic all in one codebase, but there are times when stored procs are a very efficient solution to business-logic problems.

Overall I agree that this "DB API" solution is not entirely elegant, and might provide as many security holes as it does interesting debugging challenges.

However, there are some situations in which I could see this being incredibly useful. Combining this Nginx's JSON/API modules means some parts of your API could entirely bypass your app servers.

Let's say you have an API that shows the ten items most recently liked by a particular user. Typically, this request would hit Nginx, your app server, then your cache and/or DB. By using the "shared procs API" and some Nginx modules, you could provide the same data by letting Nginx query your cache/DB directly.

EugeneOZ5• 14 years ago

No, stored procedures is not a part of business logic. Read books of Robert Martin and Martin Fowler, they will explain it for you.

JoelJacobson5• 14 years ago

That's a religious argument. Many applications are a perfect fit for a database centric design with business logic written in stored procedures. Not all, but some.

You might find find old article interesting, http://ora-00001.blogspot.com/2011/07/mythbusters-stored-pro...

gaius3• 14 years ago

This is silly. If you have many applications accessing the database - and not every app will go via your API, unless you plan to rewrite everything - then you want your business rules enforced in one place, that will be effective for any client, be it a command line, a web app, client-server, Excel, third party products, anything.

The web world has produced people for whom there is a 1:1 correlation between app and DB. In the enterprise world, most apps are written to run against existing DBs, some of which have been around for decades. This is scalable development processes. And the funny thing is, web kids will blithely repeat "DRY" without ever understanding the truth of it.

JoelJacobson5• 14 years ago

What's wrong with a 1:1 correlation, when it makes sense? If what you need to do is to access or modify data, then a stored procedure language such as PL/pgSQL is perfect for the job. But if you need to render some graphics using GD in PHP, that's obviously not a job for the database. The total API of a system is probably not only stored procedures, but quite a lot of API methods, stored procedures can take care of the entire task, without the need to involve PHP or any other middle-layer language.

einhverfr6• 14 years ago

I agree with you about API's (and would further note that stored procedures are a way to build API's around your database, as are views).

However, I disagree that stored procedures are a bad thing. There are several ways that stored procedures can be used.

1) Named queries (An API around your db!)

2) Set-intensive operations are really best done in SQL. You can do a heck of a lot (of even multi-stage logic!) in a single query when you throw in some in-line views, common table expressions. Writing in stored procedures gives you a single, testable reference for functionality.

3) Exporting application functionality to other programs that may want to use the db (like Excel for example).

4) Unit tests in the db are very, very helpful and highly underrated. Gotta love the fact that rollback means "none of this ever happened."

einhverfr6• 14 years ago

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.

JoelJacobson5• 14 years ago

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?

einhverfr6• 14 years ago

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...

einhverfr6• 14 years ago

Quick guide to the code (see https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/br... and https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/br...).

The typical call goes something like this:

$object->execute_method({funcname => 'my_stored_procedure'});

This calls execute_method in DBObject.pm

The primary magic happens between lines 112 and 170 which is where we look up the argument names and process them. Then this gets handed off to LedgerSMB.pm's "call_procedure" interface which builds the query and executes it.

It isn't great code and there is room for improvement. It will be improved over coming versions. The 1.4 codebase is moving to use Moose for newer code (all code by 2.0), and the combination of Moose and this approach to method mapping is proving quite useful.

I am also working on a more ambitious library for doing this query sort of thing which will add the possibility to add windows and windowed columns onto stored procedure calls at mapping time, so you can ask for a GL running balance while specifying ordering.... That probably won't be ready for 1.4 though.

Call PostgreSQL functions via HTTP JSON-RPC

Related Stories