Squilla: Serving up Stored Procedures

2014-01-20 sql / django / backend / silly / postgres
A (somewhat sketchy) connector between HTTP and postgres stored procedures ...

I’m a pretty keen user of Django but it has to be recognized that ORM-based frameworks do introduce a certain amount of complexity into a project.

You do get field validation, delete cascading, event triggers, all kinds of exciting stuff, but here’s an interesting thought: a lot of that stuff already exists in SQL.

I’ll be concentrating on PostgreSQL 9.3 for the purposes of this article but many other databases work very similarly.


Django allows you to perform queries across multiple tables, pulling together related entities in a single query. For example, a query like:


… would pull in an Article, and pull in all Comments related to that article by doing something like:

SELECT * FROM article
LEFT JOIN comment ON (comment.article_id = article.id)
WHERE article.id = 7;

This is rather helpful, but the same thing could be done by setting up an article_with_comments View which performs the join internally.

CREATE VIEW article_with_comments AS 
    (SELECT JSON_AGG(comment.*) FROM comment WHERE article_id=article.id) AS comments_json ,
    (SELECT MAX(id) FROM comment WHERE article_id=article.id) AS max_comment_id
FROM article;

You can now query the View, which encapsulates the underlying details.

SELECT * FROM article_with_comments;

SELECT * FROM article_with_comments WHERE max_comment_id > 3;


Django supports Signals which run when changes are made to objects in the database.

The equivalent in SQL is to use Triggers which offer the same kind of functionality.

Stored Procedures

All modern SQL databases support Stored Procedures: chunks of code stored in the database and which run within it. Stored procedures are often written in some kind of SQL dialect, but various databases allow “plugin” languages such as Python or C.

They’re certainly not trendy any more, but just perhaps that makes them worth another look!


I’ve talked before about APIs based on messages. The general idea is that each of these messages translates into a stored procedure call. Each stored procedure runs a series of SQL commands, and sends back the result as JSON. This is very similar to what happens in a Django (etc) handler, only it is happening right there in the database server!

Because these messages are not necessary idempotent, messages should be carried by HTTP POST.

Stored Procs take named parameters, which map nicely onto the standard HTTP POST x-www-form-urlencoded, so we’ll pass input through that way.

Sketchy Demo Code

This very small bit of Python code demonstrates the general idea. It uses psycopg2 and wsgiref libraries:

from urlparse import parse_qsl

import psycopg2
import psycopg2.extras

import json

db = psycopg2.connect("dbname='squilla' user='nick' password='hunter12'")

def application(environ, start_response):

    # Get the HTTP parameters

    name = environ['PATH_INFO'][1:]
    query = parse_qsl(environ['QUERY_STRING'], keep_blank_values=True)

    # Turn them into an SQL Query accessing a stored procedure

    sqlquery = (
    'SELECT * FROM public."%s" (' % name +
    ', '.join( '"%s" := %%s' % q[0] for q in query if q[0].isalnum())
    sqlparams = [ str(q[1]) for q in query if q[0].isalnum() ]

    # Turn the response into JSON

    cursor = db.cursor(cursor_factory = psycopg2.extras.RealDictCursor)
    cursor.execute(sqlquery, sqlparams)
    response = json.dumps(cursor.fetchall())

    # Return the response in HTTP

    start_response('200 OK', [
        ( 'Content-Type', 'application/json' ),
        ( 'Content-Length', str(len(response)) ),
    return response

if __name__ == '__main__':
    from wsgiref.simple_server import make_server
    make_server('localhost', 8001, application).serve_forever()


Further Work


I can’t see everyone abandoning Django and Rails and Node.js to come and try this out, but I’d like to kick the idea around a bit longer and see if I can write a demo project in it and see how it goes. Once there’s something worth seeing it’ll be on GitHub.

Every project needs a name, so I decided to call it Squilla, because I couldn’t come up with any good “SQL on Squ…” jokes and anyway Mantis Shrimp are Awesome.