I’m kind of guy who likes to make jokes or take some ideas ad absurdum. It may looks useless, but examination of extremes helps me appreciate the middle way.

Some time ago I was heavily infected by an idea: “let’s ditch the server side” (see my article: Do we still need server side programming?). The “PostgreSQL as an Application Server” idea was born then. My Vertabelo colleagues insisted that I should write an article about it. I didn’t like it at first: it would require some real work :) This week I said why not? I’ll learn something new about PostgreSQL at least. So here we go.

PostgreSQL as an Application Server

What is an application server? My definition is: a runtime and store for business logic. PostgreSQL has stored procedures so we use them as a runtime. PostgreSQL is a database so it can store anything :)

The database for an application server may look like:

create table handler(
    path varchar(255),
    code text,
    template text);

Each row of the handler table contains the information how to handle an HTTP request for a given resource:

• path contains the path to a requested resource (e.g. /, /admin)
• code contains a code to run while processing a request
• template contains an HTML template which will be rendered as response

Here is a pseudo code of how a response is generated.

response_html = template(code(path))

Yep. That’s nothing revolutionary. You may have seen this in other web frameworks.

PL/pgSQL or Python? An Obvious Choice

As I said before, PostgreSQL supports stored procedures and I’ll use them to implement the core of an application server. Stored procedures can be written in built-in language PL/pgSQL (yikes!) or, for example, in Python (yummy). I’ve decided to use Python for hmmm... obvious reasons. (The core PostgreSQL distribution also supports Perl and Tcl as procedural languages.)

You’ll have install and enable an extension to have Python working in your database. The Extension is called postgresql-plpython-9.3, at least in Ubuntu.

Let’s create a sample database and enable support for Python.

1. Connect as a postgres

   su - postgres
   psql
   

2. Then execute following queries:

   # create test user and the database
   create user test with password 'test';
   create database test owner test;
   
   ># connect to test database
   \c test
   
   # enable python in that database
   CREATE PROCEDURAL LANGUAGE 'plpythonu' HANDLER plpython_call_handler;
   
   # enable python for test user
   UPDATE pg_language SET lanpltrusted = true WHERE lanname LIKE 'plpythonu';
   

Support has been enabled. You may check it by running example code from documentation.

A D V E R T I S E M E N T

Handling Requests

Let’s code the core of our solution. As I’ve mentioned before, the core will execute code for a given path and render output as an HTML page. The path, code and HTML template are stored in the handler table. Here is a handle_request function written in Python for PostgreSQL.

CREATE OR REPLACE FUNCTION handle_request(IN path text) RETURNS text AS
$$
import urlparse

url=urlparse.urlparse(path)
params=urlparse.parse_qs(url.query)

stm = plpy.prepare("SELECT code, template FROM handler WHERE path = $1", [ "text" ])
rv = plpy.execute(stm, [ url.path ])

if(len(rv) == 0):
  return "not found"
else:
  code = rv[0]['code']
  template = rv[0]['template']
  locals = {'path': url.path, 'params': params, 'ctx': {}}
  exec(code, globals(), locals)	   
  ctx = locals['ctx']
  return template.format(**ctx)

$$
LANGUAGE 'plpythonu' VOLATILE;

The procedure parses URL parameters. Then it reads the Python code and the HTML template from the database. Finally, it runs the Python code and renders the result.

And here is a sample request handler for / page.

insert into handler values('/', '# put you python code here',  '
Hello world
');

Let’s see how it works.

test=> select handle_request('/');
    handle_request    
----------------------
 
Hello world
(1 row)

It works :).

Handling User Input

Let’s do something more dynamic. The user can provide a message to display in the “message” parameter. The code displays the value in the template.

insert into handler values('/dynamic', 
'
message=""
if params.has_key("message"):
   message = params["message"][0]

ctx["message"] = message
'
,  '
Dynamic
 message: {message}');

test=> select handle_request('/dynamic?message=foo');
            handle_request            
--------------------------------------
 
Dynamic
 message: foo
(1 row)

Reading Content From the Database

Or even more dynamic. The code inserts new items into the todo table.

create table todo (
		item varchar(255)
);

insert into handler values('/todo', 
'
if params.has_key("item"):
   item = params["item"][0]
   stm = plpy.prepare("insert into todo values($1)", [ "text" ])
   plpy.execute(stm, [item])

stm = plpy.prepare("select item from todo", [])
rv = plpy.execute(stm,[])
list=""

for r in rv:
   list =  list + "
%s
" % (r["item"])

ctx["list"] = list;
'
,  

'
TODO

{list}


add

');

Please test this example by yourself just after you launch the HTTP connector.

HTTP Server

I was thinking about how to handle an HTTP request in the PostgreSQL. I’ve decided to write a simple HTTP connector instead of embedding HTTP server in the PostgreSQL itself. Here is the code:

#  http.py
import SimpleHTTPServer
import SocketServer
import psycopg2 as dbapi2

PORT = 8000

class Handler(SimpleHTTPServer.SimpleHTTPRequestHandler):
	def do_GET(self):
		self.send_response(200)
		self.send_header("Content-type", "text/html")
		self.end_headers()

		db = dbapi2.connect (database="test", user="test", 
			password="test", host="localhost", port=5433)
		try:
			cur = db.cursor()
			try:
				cur.execute("select handle_request(%(path)s);",
					{'path': self.path})
				rows = cur.fetchall()
				result=rows[0][0]
				self.wfile.write(result)
			finally:
				cur.close()
		finally:
			db.commit()
			db.close()

httpd = SocketServer.TCPServer(("", PORT), Handler)

print "serving at port", PORT
httpd.serve_forever()

Save it as http.py and run it

Now you can open your browser and click this link.

And your favourite database editor:

Now you have configured the development environment. Enjoy!

Conclusions

That’s all folks. If someone decides to develop a CRUD application for the handler table he/she may end up with IDE for “PostgresSQL as an Application Server” :D.

Seriously though, what did I learn with this exercise? Well, I have overcome my fear of writing stored procedures. It turns out that with a developer-friendly programming language, stored procedures are yet another piece of code, not something you only approach with a manual at hand.

What do you think about storing business logic in the database and treating it as a data?