Aquameta: Web development platform built in PostgreSQL
github.com
github.com
I applaud this effort and can't wait to try it out!
This changes the structure of programs from a file/disk based structure, to the potentially richer structure of the programming environment (e.g. graphs of objects). Although it comes with disadvantages like losing the tool inter-operability of files.
The strength of the file system, is that much tooling already exists for it.
In the past I've built a scheme similar to their semantics layer, where a bunch of additional metadata is associated with schema metadata via PostgreSQL's COMMENT statement (which lets you associate a free-form text comment with all sorts of schema elements). In that scheme we had JSON COMMENTs and a set of views that ultimately generates a nice JSON representation of a database's schema, including the JSON COMMENTs in the right places, and _that_ gave us UI control that we could then use to generate Admin-on-REST UIs from. And PostgREST can be used to get an HTTP JSON API for free.
For event pub/sub, I've written an used an alternative (all-PlPgSQL-coded) view materialization system that supports live-updating of materialized views as well as recording deltas, which then can be combined with NOTIFYs. Unlike Aquameta, the fact that NOTIFY requires no authorization, and its payload is free-form text, I feel queasy about sending out too much information in NOTIFYs -- instead I use them to drive queries for new deltas as recorded by the delta recorder mentioned earlier in this paragraph. The component that LISTENs for NOTIFYs then writes deltas to a file which is served with a special HTTP server that supports hanging GETs of files -- "tailfhttpd" -- and any HTTP client can then be used to tail these files.
Anyways, the Aquameta scheme is pretty good.
EDIT: I've been tempted to write an authorization-for-NOTIFY patch to PG... I really don't like the idea that if I give someone direct access to the DB they can NOTIFY anything they like on any channel.
Yeah trying to figure out how to annotate the schema was a big motivator for inventing the meta identifier system. Thought about using COMMENT for documentation, but once you have meta-ids, I thought it was cleaner to just put schema annotations in a different table.
Yeah I think the LISTEN/NOTIFY system in PostgreSQL is a bit primitive. Still working on that part of the project. We got NOTIFYs to propagate up through nginx over a WebSocket and get them into web-world that way, but that section of the project is still fairly immature.
I need to finish the open sourcing of tailfhttpd though. It's... an open-coded HTTP server, written in C, specifically tailored for tailing files over HTTP. It uses epoll and inotify, and is C10K, and blazingly fast. You have to front it with nginx or envoy to get TLS support, naturally. Because it's so simple, open-coding HTTP/1.1 seemed reasonable at the time, though nowadays writing this in Rust with some reasonable framework would be better.
The key to tailing files over HTTP is to have a server that supports:
- weak ETags (st_dev, st_ino, generation number)
- If-Match and If-None-Match conditional request
support
- Range requests
- when the right end of the requested range is
left unspecified, use chunked transfer-encoding
and don't send the terminator chunk until
a) EOF is reached, and b) the file is renamed
out of the way or unlinked (which the server can
detect using inotify or similar)
If client loses its tail, it can simply resume by using If-Match and Range starting at the next byte offset after the last byte received before. So recovery is trivial.
And presto, super cheap, simple, and reliable pub/sub.
So while the added network layer of Tor (or any overlay network really) certainly adds a level of complexity, from the application standpoint it actually simplifies matters for the following reason: Your application doesn't need any longer to think about the topology of the underlying IP network, as this implicit detail dragged in by direct IP connectivity use is abstracted by the overlay network. Instead, your application is able to interface with any peer via (in the case of Tor) an HTTP proxy and a set of opaque base URLs.
IOW, you're going to need one or more additional daemons to make the P2P part go, and Tor is legitimately the simplest thing available today that accomplishes this without ruling out other styles of overlay network from an architectural point of view.
I don't mind adding additional daemons to the system if it's essential. But, Tor also (as I understand it, given that this is a ways outside my core competency) adds a whole encryption and anonymization layer, which would be undesirable overhead in at least some scenarios, which is why it seems too heavy to be part of core. Maybe WebRTC isn't going to work reliably which would be very disappointing. I'd like to hear more about your experiences and could really use some help making a good decision here. Ping my email if you're available!
Demo: http://rusrs.com/
It works in modern browsers with some extra JS-based niceties, but is also compatible with just about every browser I've tried, including Mosaic and Dillo.
There's definitely something to this. I'm not sure that building the IDE on top of it is practical (partly because web IDEs are still pretty limited and partly because the scope is just enormous).
But if we had a way to version code that combines source, data, and issues, I'm super interested. It just needs to pipe into existing tools rather than recreating existing tools.
Experienced a slightly different take a few years later where Some Genius wrote an interpreter in stored procedures in an off-brand RDBMS. Web pages were a combination of HTML and his custom language, and these pages were stored in the database.
I have strong negative opinions about putting a web development platform entirely inside an RDBMS.
What about your experiences led to your negative opinions?
* version control
* environment mismatches (think managing dev, staging, prod with multiple devs)
* deployment management
* discoverability of where things live so you can maintain them, and autocomplete in IDEs
The last is the big one. If a client asks me to change the header, and me going to my IDE and quick searching "header" doesn't automatically show me all header related files/code then your platform has already lost the developer experience battle. Something like this would need tooling to match what is already possible in that regard.
Further, in my Oracle instance, relying on the late-1990s Oracle database server to also run our business logic came with performance and scalability concerns, especially around licensing.
In my other case, the custom language was terrible, but that's not a complaint about the use of the db engine. The interpreter was implemented in stored procedures, the pages were stored in text fields ... every bit of the stack depended entirely on this off-brand database that had its own issues (like corrupt indexes that needed repairing about once per month.)
I think ultimately, in both cases it just felt so much like putting all your eggs in one basket and then hoping you never needed to augment the basket with additional baskets, nor replace the basket with one of a different shape or made of other materials.
I'm the author of an infrastructure-as-code tool for MySQL/MariaDB schema management, Skeema [1]. Basically it allows you to store CREATE statements in a repo, and you can diff/push/pull between the repo and live DB environments. Originally the tool only supported tables, as my personal MySQL experience skews towards social networking companies that banned stored procs outright.
Earlier this year a company generously sponsored development of stored proc/func support in Skeema. And although I've long been a stored proc skeptic, I have to say my opinion has softened considerably after building this functionality. It essentially solves the first 3 bullets you've listed here: allowing storage of CREATE PROCEDURE statements in a git repo; ability to diff the current state of the repo against any live db environment; ability to push the current repo state to any live db environment. And the 4th bullet just depends on your IDE's support for different SQL / T-SQL / PLSQL dialects.
I still have some scalability and maintainability concerns around extensive use of stored procs (especially in MySQL/MariaDB), but nonetheless found this experience to be unexpectedly eye-opening. My perspective went from "stored procs are an operational nightmare, avoid" to "huh these are actually quite useful and totally manageable given a solid development/deployment story."
[1] https://news.ycombinator.com/item?id=19882611
The key was to build the tool up front. Without the tool we couldn’t have done it.
Schema migration is hard. Skeema looks cool. Aquameta's idea with schema migration is to treat the schema as data, so that each table is a meta.table table, each view is a row in the meta.view table, each column... etc. So when version control checks out a new version of the project, if columns say were added, they're just inserted at runtime. Checkout prioritizes meta entities so they happen first and in a sensible order [1] so the migration happens first. This does technically work for all scenarios (I think!), but in the case of say a column rename, it would naively delete all the values and then have to re-set them per row, which could be a big inefficiency. It would still work just fine for smaller tables though. Alternately some kind of optional migration script per commit was another idea.
Oracle have already done this, it's called Application Express[0]. I used it for my last job. In practice it was pretty good for fast prototyping and iterating on database-backed apps. It took the schema as the source of truth and would add useful interface features based on it (eg, dropdown lists derived from foreign keys, converting some check constraints to javascript validation code etc).
But on the downside it was essentially untestable and version control meant dumping a massive file of autogenerated PL/SQL and checking it into git. For anything more complicated than basic CRUD and reporting you'd wind up cracking the hood to directly use PL/SQL and then skin it with APEX. The tradeoff being that you lost some of the roundtrip niceties.
I wouldn't recommend it in general, but it fit ... OK in its context. It was included in the Oracle license, the org had experience with operating Oracle and it allowed each of us in a small programming group to build little apps to solve genuine business problems that were either too small to select OSS/COTS or too specialised to find anything to select.
The best part of the job was getting rid of painful processes built around sharing Excel and Word docs. I remember occasion when a user broke down in tears because a (to me) trivial app meant that a stressful, high-pressure, error-prone month-long process turned into a non-event. One of the best days of my career and I partially owe it to a platform that I think is, in most respects, a mistake.
Wikipedia reckons HTML DB (the original package) was released in 2004, but the APEX website clear claims to being 19 years old, which would put it circa 2000.
But in the meantime if you're doing late-90s CGI with Perl, why not `SELECT my_amazing_function()`?
PostgreSQL says use an UPDATE statement, but that looks like "update template set content='<html><body>......</html>' where id = x", which means you have to paste the entire content of the page into the update statement. There's no CL-based field editor, just one of many reasons why the db feels like a black box. Awful.
Aquameta has a filesystem integration layer that lets you browse the database from the command line, grep database content, edit the content of a field using your preferred text editor. http://blog.aquameta.com/intro-chpater2-filesystem/
Generally just trying to ease these pain points has been a big goal of the project... still a lot to do.
PL/SQL is really a powerful language and entirely capable of this. It is not a language who's power is best utilized for this -- it is a maze to encode (much) business logic within the DB.
Now, that's not quite what you said! You're against "putting a web development platform entirely inside an RDBMS", and this much I agree with.
But you seem to object to putting business logic in the RDBMS as well, and I want to tell you why I don't agree with that.
The problem with not putting business logic in an RDBMS is that you risk ending up with an application which evolves towards... implementing features already provided by the RDBMS.
You also need to be very careful with direct DB access, since anything you do there will not be implementing a lot of your business logic. This means you can't use things like PostgREST to get a REST API for free. Conversely, if you do want to use PostgREST or alike, you must move your business logic into the RDBMS.
Now, I understand not wanting to do this with Oracle. There are many reasons for that. And I understand not wanting anyone to write an interpreter in a PL/SQL! I also understand not wanting static HTML and other such resources stored in the DB -- there's nothing wrong with using a filesystem and $FAVORITE_VCS for that, and indeed, the version control in Aquameta is something I don't quite approve of. But none of that says you shouldn't have business logic in the RDBMS.
Aquameta is an experimental project, still in early stages of development. It is not suitable for production development and should not be used in an untrusted or mission-critical environment.
Not really the basis for ’reference success stories’ approach to evaluation. More, check out the repo and hack at Postgres, see where the hypothesis can be made pragmatic.
http://blog.aquameta.com/introducing-aquameta/
Obviously this is has been the panacea goal in business software forever and there's a long trail of failed companies or projects who tried to do this.
IMO it's always going to require specialized knowledge of a certain level of abstraction above the machine, it will never be as simple as pushing buttons in a GUI. The question is making the languages/frameworks simpler and lowering the bar.
In practice these sorts of things work great for simple scenarios but are very brittle one you start to. Just like Excel spreadsheets used like databases it will quickly turn into a hacky maze of things forced into places where it shouldn't be.
I'd also never think 'PLpgSQL' when it comes to simplifying things. I'm genuinely curious to see if they can pull it off... eliminating the file system part is also an interesting idea.
PlPgSQL is not a great language, and that's obviously not a reason to use it. But it's not obviously a reason to not use it, and more than that, it's no reason not to put business logic in the RDBMS. Once you decide to put business login in the RDBMS, PlPgSQL is just one of several languages you could use, and actually the most accessible one.
Has anyone built anything in Aquameta? Would like to see some success stories.
Every time I need a little app or something, instead of reaching for some SAAS product, I would just build a quick prototype for myself. I could make a prototype in a couple hours and over the course of a week or two I would polish it up when I had a few extra minutes. Rather than using something built for the masses, I could tweak the interface to my own taste and I owned all the data.
"Any sufficiently complicated C or Fortran program contains an ad-hoc, informally-specified, bug-ridden, slow implementation of half of a JavaScript IDE." (previously Common Lisp)
There might be other reasons - but note that postgres actually supports transactions and rollback of DDL - Oracle with full enterprise lisence has something similar (there's undo/a "trashcan" style cache for schéma modifications).
But in general (as far i understand; I have not used this in anger) pg lets you simply BEGIN drop table (...) ALTER table (...) (who's, something wrong) ROLLBACK.
It's very neat, and not generally supported.
There are a very small number of DDL statements that you can’t do in a transaction, but the devs are fixing them (adding a value to an enum is the only one that comes to mind)
We use it to great effect when upgrading schemas.
Adding enum values in a transaction is now supported in PG 12 as long as you don't try to use the new value in the same transaction.
Besides letting you read schema via normal SELECTs on a well-designed meta-schema, something like Aquameta can let you run DMLs as DDls too. That means you can now generate and execute DMLs dynamically but without needing EXECUTE -- you can just have a bunch of normal INSERT/UPDATE/DELETE statements (including via CTEs) and generate schema from other data the same way you'd generate data from data. I think that's a big plus. But then I've worked with a database before that did this schema as data-in-a-meta-schema thing, and I find it very comfortable.
For example, without a metaschema you have to use CREATE THING IF NOT EXISTS then ALTER THING ... in order to apply schema changes. Whereas with something like Aquameta you just INSERT INTO ... WHERE NOT EXISTS ... (or with ON CONFLICT DO ...) and that's that. You can have one set of DDLs that create schema, and the same set of DDLs also updates schema. That's wonderful.
Do you end up with old data in old schema - or is there some implicit conversion? Is a UPDATE that drops a column equivalent to dropping a column - and can you roll it back normally?
Just as in Lisp you might rather not use eval but instead use macros, here you'd prefer to use DDLs on a metaschema over DMLs. Hygiene is one of the motivators.
And yes, you could DELETE from a view that represents columns instead of using ALTER TABLE ... DROP COLUMN ... All the usual transactional semantics apply. You can BEGIN, delete that row, and rollback, leaving the DB unchanged.
This is triggering painful flashbacks to SharePoint development.
I did not enjoy my time working with it but it had some neat ideas and did do some things well.