omni_httpd is an embedded HTTP server for Postgres. It allows one to write HTTP request handling in SQL. This approach lends to less indirection that is common in traditional application architectures, where applications are interacting with the database directly.
omni_httpd, once installed, will provide a default page on port
You can change the handler by updating the
query column in the
handlers table. Currently, the idiomatic way
to write this query is to use
omni_httpd.cascading_query aggregate function (though, of course, one can roll their
own SQL completely by hand). This function simplifies building priority-sorted request handling:
update omni_httpd.handlers set query = (select omni_httpd.cascading_query(name, query order by priority desc nulls last) from (values ('headers', $$select omni_httpd.http_response(body => request.headers::text) from request where request.path = '/headers'$$, 1), ('not_found', $$select omni_httpd.http_response(status => 404, body => 'Not found') from request$$, 0)) as routes(name, query, priority));
What if the query is invalid?
omni_httpd enforces validity of the query using a constraint trigger at the transaction boundary when updating or inserting a handler. This means that once the transaction is being committed, the query is validated and if it, say, refers to an unknown relation, column or is invalid for other reasons, it will be rejected and the transaction will not succeed.
Please note, however, that at this moment, this enforcement will not help avoiding runtime errors if you render your query invalid afterwards (for example, by dropping relations it references), this will lead to runtime errors, ultimately leading to HTTP 500 responses.
The query called
headers will dump request's headers,
not_found will return HTTP 404. We can test it with
The above method of defining the handler can work well when the queries that it is composed of are either stored in a database or can be retrieved during deployment (say, from a Git repository or any other source.)
What did you mean by "priority-sorted" request handling?
If you look at the order of handlers we added (
headers followed by
not_found), it means that
cascading_query-built query will first try to get results from
headers and if none available, will
not_found. Suppose we changed the order:
update omni_httpd.handlers set query = (select omni_httpd.cascading_query(name, query order by priority asc nulls last) -- (1) from (values ('headers', $$select omni_httpd.http_response(body => request.headers::text) from request where request.path = '/headers'$$, 1), ('not_found', $$select omni_httpd.http_response(status => 404, body => 'Not found') from request$$, 0)) as routes(name, query, priority));
- We changed the order from
not_found will always take the precedence:
An interesting corollary to this approach is that if all of the handling sub-queries are of the same priority,
then priority-ordering is not required and one can simply use
The idea behind
cascading_query is that it aggregates named queries in a
UNION query where all given queries
will become common table expressions (CTEs) and the
UNION will be used to cascade over them, something like:
All good. But looking back into the queries itself, they mention
request which is nowhere to be found. Where does this
come from? This is actually a CTE that
omni_httpd supplies in runtime that has the following
If this signature seem a little incomplete (where's the source IP address, can the body by streamed, etc.?), that's because it is still work in progress. Please consider contributing if you feel up to it.
Also, omni_web provides complementary higher-level functionality.
In order to test your request handlers without having to run actual HTTP
requests against it, one can use
omni_httpd.http_request function to compose
|method||omni_http.http_method||HTTP method [^http-method]||
|headers||omni_http.http_headers||An array of HTTP headers||empty array|