Aquameta Chapter 4: WWW - Mapping PostgreSQL to the Web

Follow @aquameta

Aquameta is a web development platform built entirely in PostgreSQL. This is chapter four (introduction, meta, file system, event) of our rollout of Aquameta's architecture.

This is where our hard work of datafication comes into play. Aquameta's datfied architecture menas that with a simple REST interface for data manipulation, a end user can control the entire database using only CRUD operations. This radically simplifies app development, and brings being a user and being a developer into the same space.

Here's how this module works.

The endpoint module enables Aquameta to host applications on the World Wide Web via the following features:

  • User registration and login
  • REST interface for data read/write and function calls
  • Hosting of arbitrary resources stored in the database at arbitrary URLs
  • Hosting files and directories stored on the file system at arbitrary URLs
  • Pub/sub data change events via Websocket

Request Handling

Under the hood, Aquameta uses the uWSGI webserver to handle HTTP requests. uWSGI accepts requests on port 80, connects to PostgreSQL as either the anonymous role, or the authenticated user, and hands the request off to the endpoint.request function, passing in the requested HTTP verb, path, query string parameters and request headers. endpoint.request processes the request and delegates it to the appropriate handler function as either a data read/write operation, a function call, or a static resource.

Registration, Authentication and Permissions

Aquameta uses PostgreSQL's Roles and Privileges, so there is a one-to-one mapping between a PostgreSQL role and an Aquameta user. However, PostgreSQL roles don't have email addresses, activation codes, the typical things that website registration needs, so Aquameta's endpoint.user table augments PostgreSQL's roles with this data.

Aquameta ships with /register and /login pages that allow users to register and login. If a user is not logged in, the anonymous role is used by default, and has whatever permissions that role has in PostgreSQL.

REST Interface

The REST interface exposes a minimal set of database operations via HTTP, namely 1.) data read/write operations and 2.) function calls. Because of Aquameta's first principle of datafication, this minimal set of operations should allow complete administration of PostgreSQL using only data manipulation. For example, creating a new schema would be a POST to the meta.schema view. Deleting a column would be a DELETE to the meta.column view.

This a grand unified theory. Nobody does this. It saves us writing routines for routines for everything that isn't "regular user data".

Javascript API

The datum.js API makes the REST interface accessible via Javascript. Here's a sample usage:

// request a Rows object from the beehive.customer table.
// a HTTP request will be sent to /endpoint/0.1/TODO
var joes = endpoint.schema('beehive').table('customer').rows({  
    where: {
        column: 'name',
        op: 'like',
        value: 'Joe%';

// request beehive.order rows that reference any of the beehive.customers
// in the joes object
var joes_orders = joes.related_rows('id','beehive.order','customer_id');

// call a function
var result = endpoint.schema('beehive').function('submit_purchase_order', [48201]);  

Full API documentation is coming soon.

There is also experimental support for data change events, via Websocket, by which you can subscribe to any change events that occur on a particular data set.

File and Resource Hosting

The server can host static resources in two ways: Files stored on the file system, and binary or text resources in the endpoint.resource_binary and endpoint.resource_text tables. Each of these tables foreign-key to the endpoint.mimetype table, where you can specify the mimetype of the resource. They also have a path column where you specify the URL path such as /images/einstein.png.

Resources such as images and text files can also be stored in arbitrary fields and retrieved via the REST interface. You can set the mimetype of any column in the database via the endpoint.column_mimetype table, which

The end result is this schema:


comments powered by Disqus