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:
Article.objects.get(id=7).select_related('comment_set')
… 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 *,
(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.
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.
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:]
assert(name.isalnum())
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()
In the code above, all parameters are passed through as strings, and all returns are just done by JSONifying whatever comes back.
Postgres actually has quite a lot of function type information
available
through its internal pg_catalog.pg_proc
and pg_catalog.pg_type
tables, and this could be scanned through at startup and used to
help prepare parameters before calling the proc.
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.