A (somewhat sketchy) connector between HTTP and postgres stored
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
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:
This is rather helpful, but the same thing could be done by setting up
which performs the join internally.
You can now query the View, which encapsulates the underlying details.
run when changes are made to objects in the database.
The equivalent in SQL is to use
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
various databases allow “plugin” languages such as
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
Because these messages are not necessary
idempotent, messages should
be carried by HTTP POST.
This very small bit of Python code demonstrates the general idea. It
uses psycopg2 and wsgiref libraries:
Having to learn SQL … One of the nice things about
SQLAlchemy and all the other ORMs is not
having to write your own SQL. The downside of this is you have to
keep a pretty close eye on the SQL they generate or you end up
getting burned. So you end up
learning SQL anyway.
Versioning stored procedures and triggers and migrating them …
This is worthy of its own whole article.
South does a great job of this for
Django, so I’m looking for an equivalent. I’ve seen a few techniques
for this but will come back and write a separate “Best Practice”
post on this later.
The application server layer just got a lot thinner … so
you’ll probably need to scale Postgres
sooner than you might otherwise have had to.
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
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.
Postgres supports advanced
which could be passed in and out as JSON data structures. Functions
which return the json data
should be able to construct their return value directly, rather than
trying to squash their return data into a faux-table.
Many operations areRESTful,
and so rather than implementing a whole heap of tiny stored
procedures it would make sense to support GET, PUT, PATCH and
against Views. Stored procedures can then be invoked by Triggers
Having the web server handle session cookies and so on and pass them
through to the stored procedures would be useful. Perhaps a
“request” parameter would make sense for this.
Obviously, it needs
protection and careful consideration of SQL
injection hacks too!
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
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