This was a great read! I’ve been looking at postgraphile but wanted something that was just regular rest. Now I know there is postgrest!
But I have been thinking more and more about how critical knowing databases is for being able to build new things, especially when you don’t have the money or the people to build abstractions around databases. But, like mentioned in the article, it does require time, and that is precious.
There are a few tools like PostgREST out there. Supabase, Hasura, XGeneCloud, Prisma. You can also generate a REST API from OpenAPI specs with OpenAPI Generator. I’ve been working on a similar tool that can generate a REST API and admin UI from Postgres or MySQL.
FWIW I’m writing out the Part 2 of this blog post where I ship actual code on AWS (hopefully for publishing tomorrow, repo here: https://github.com/yingw787/postgres_as_app), and it’s shockingly easy to get started with PostgREST. It’s tar one binary, copy in a conf file, and go. The robustness of Haskell with the simplicity of Go. All runtime conf options, like pagination, are handled via HTTP headers. It’s definitely a tool I want to incorporate into my future apps, because it’s stupid simple and transparent.
If the problem with postgres RDS is lack of extensions or similar, do https://www.elephantsql.com/ or https://aiven.io/ offer more flexibility so that one could avoid the latency of having a postgres to postgres proxy?
That’s…surprisingly great. Do you know whether the “AWS Marketplace” offerings are purely EC2 costs, or do vendors also charge a flat fee for the AMI? I have Startup School credits through AWS (extremely generous) and that’s why all my infra is AWS; would love to try out Aiven for AWS if the former is true. :P
I was amused by the automatic conversion of inline graphviz code to a graph with JavaScript (try disabling JS (or enabling it if you have it disabled)). Pretty nifty!
Thanks! All credit goes to Munif Tanjim (creator of Minimo), Adam Charytoniuk (creator of Engimo), and @spf13 / maintainers of Hugo for their hard work and dedication!
I tried to understand what exactly this is trying to do, but I don’t understand why you’d have two postgres instances. If I understand correctly, the problem with RDS is that you can’t install custom extensions (but they do supply FDW out of the box). But if you then install a second Postgres (in a regular VM?) and connect it to RDS, isn’t the RDS instance redundant and just sitting there costing you extra money, then?
I mean, if you’re installing your own custom install, why not just use that and be done with it?
Only reason I could think of is that the data storage is still on them. Yes you’re proxy going down is more likely, but proxies are normally easy to operate.
One thing I didn’t realize when I first interacted with numerous databases is how SQL is different between each database. I don’t like having to relearn having to fetch and shape data over and over again if I don’t have to, so I proxy data sources through Postgres in order to use Postgres’s flavor of SQL and stored procedures PL/pgSQL.
Just using Postgres as a “front end” to a different database strikes me as very inelegant and too much needless overhead. Also, from my experience, FDWs don’t handle data migrations in the source database very well. It’s very easy for the source table and the FDW definition to get out of sync.
Testing FDW-based tables is a right pain in the neck, too, due to how they interact with transactions (unless you do everything through the FDW, and the databases you connect to are defined statically). I wrote a bit about my experiences with FDW on the company blog a while ago, but I never really bothered to update it. We used it mostly to do queries over multiple instances of the same system.
So while I think FDW are very cool and powerful, I’m not sure I’d be very quick to use them myself.
Very interesting! I was planning on using FDWs as a pass-through entity, where dataflow goes from table -> view -> HTTP. In this situation, I wouldn’t be pushing any data on the source table, and therefore wouldn’t have any issues with data migrations (because there would not be any). For synchronizing foreign table references, I was planning on a stored procedure + batch job to pull in foreign tables every so often and blow existing references away, though that may be tricky if there’s any dependencies on those foreign tables. Any transactions you’d handle on the source table, in a “push/pull” configuration (OLTP work on source, OLAP work on destination). I think for BI / analytics stuff, this should be fine.
I just felt restricted by what you can do with an ORM. I figured since an ORM supports so many databases, it has to cater to the least common denominator of database features, and I wanted more from Postgres.
It’s good to experiment, so definitely do so! Maybe for your use case the issues I ran into are not even relevant.
I’m not a big fan of ORMs either (they can produce inefficient queries, are often a barrier to getting exactly out of the db what you want because not every query result maps cleanly to “objects”, they can easily accidentally generate many more queries than you would want to perform, and they are a total memory hog), but they don’t have to be the least common denominator. For the query language, usually, yes (and for that, dropping down to the SQL level tends to be painful), but for example Django allows you to use Postgres extensions like arrays, JSON fields, partial indexes, and even Postgis fields and spatial operators. Only thing I kind of miss are proper enum fields.
Hmm.
Reading just the title, I had a, perhaps, different ‘rendition’ of Postgres-as-an-App idea.
I thought:
Let’s call this thing ‘Positron’ (as alternative to Electron :-) ).
It will be a GUI-enabled app engine that runs on Mobile as well as on desktop
With built in database, and with built-in capability to synch app the data (through postgres) to backend server also in postgres (optionally).
And built in localized (in app) caching, as well as backend distributed caching
And a built in push mechanism from the backend into the Positron app on mobile/desktop
With a GUI layer for mobile platforms (eg Flutter ?, maybe?, or something else that runs on mobile+desktop)
With platform specific account management (where Postgres user account is integrated with a mobile/desktop account management api)
In future, With a play store integration such that the Positron based apps, can re-use same runtime (so that a user does not have download all the binary code for identical runtime elements, but different apps).
PG runs on rasberryPI, so I know it compiles on Arm (which is what most mobile phones use
Sort of like FoxPro, with a modern remote backend, covering mobile platforms (and without a visual gui builder).
This was a great read! I’ve been looking at postgraphile but wanted something that was just regular rest. Now I know there is postgrest!
But I have been thinking more and more about how critical knowing databases is for being able to build new things, especially when you don’t have the money or the people to build abstractions around databases. But, like mentioned in the article, it does require time, and that is precious.
There are a few tools like PostgREST out there. Supabase, Hasura, XGeneCloud, Prisma. You can also generate a REST API from OpenAPI specs with OpenAPI Generator. I’ve been working on a similar tool that can generate a REST API and admin UI from Postgres or MySQL.
Do any of these tools intercept the query and rewrite it, possibly joining across multiple SQL backends? Of those that do, do they do query push down?
Stardog’s virtual graphs can do that.
Not that I’m aware of.
I think Hasura can stitch GraphQL queries. Or I guess they call it remote joins: https://hasura.io/blog/remote-joins-a-graphql-api-to-join-database-and-other-data-sources/
FWIW I’m writing out the Part 2 of this blog post where I ship actual code on AWS (hopefully for publishing tomorrow, repo here: https://github.com/yingw787/postgres_as_app), and it’s shockingly easy to get started with PostgREST. It’s tar one binary, copy in a conf file, and go. The robustness of Haskell with the simplicity of Go. All runtime conf options, like pagination, are handled via HTTP headers. It’s definitely a tool I want to incorporate into my future apps, because it’s stupid simple and transparent.
If the problem with postgres RDS is lack of extensions or similar, do https://www.elephantsql.com/ or https://aiven.io/ offer more flexibility so that one could avoid the latency of having a postgres to postgres proxy?
Do they offer extensions / root access? I thought they were managed solutions.
Aiven supports 47 postgresql extensions, including pg_cron, postgis, postgres_fdw and timescaledb.
I searched Elephant SQL’s docs for “Extension” but got no meaningful hits. Looks like they do not support extensions.
That’s…surprisingly great. Do you know whether the “AWS Marketplace” offerings are purely EC2 costs, or do vendors also charge a flat fee for the AMI? I have Startup School credits through AWS (extremely generous) and that’s why all my infra is AWS; would love to try out Aiven for AWS if the former is true. :P
I was amused by the automatic conversion of inline graphviz code to a graph with JavaScript (try disabling JS (or enabling it if you have it disabled)). Pretty nifty!
Thanks! All credit goes to Munif Tanjim (creator of Minimo), Adam Charytoniuk (creator of Engimo), and @spf13 / maintainers of Hugo for their hard work and dedication!
I tried to understand what exactly this is trying to do, but I don’t understand why you’d have two postgres instances. If I understand correctly, the problem with RDS is that you can’t install custom extensions (but they do supply FDW out of the box). But if you then install a second Postgres (in a regular VM?) and connect it to RDS, isn’t the RDS instance redundant and just sitting there costing you extra money, then?
I mean, if you’re installing your own custom install, why not just use that and be done with it?
Only reason I could think of is that the data storage is still on them. Yes you’re proxy going down is more likely, but proxies are normally easy to operate.
One thing I didn’t realize when I first interacted with numerous databases is how SQL is different between each database. I don’t like having to relearn having to fetch and shape data over and over again if I don’t have to, so I proxy data sources through Postgres in order to use Postgres’s flavor of SQL and stored procedures PL/pgSQL.
Doesn’t have to be Postgres as the data source, you can use MySQL, Oracle, Google Sheets, GPU VRAM, or your smarthome lightbulbs, or a combination of these: https://wiki.postgresql.org/wiki/Foreign_data_wrappers
You can also write your own FDWs using Multicorn, a Python library: https://multicorn.org/
Just using Postgres as a “front end” to a different database strikes me as very inelegant and too much needless overhead. Also, from my experience, FDWs don’t handle data migrations in the source database very well. It’s very easy for the source table and the FDW definition to get out of sync.
Testing FDW-based tables is a right pain in the neck, too, due to how they interact with transactions (unless you do everything through the FDW, and the databases you connect to are defined statically). I wrote a bit about my experiences with FDW on the company blog a while ago, but I never really bothered to update it. We used it mostly to do queries over multiple instances of the same system.
So while I think FDW are very cool and powerful, I’m not sure I’d be very quick to use them myself.
Very interesting! I was planning on using FDWs as a pass-through entity, where dataflow goes from table -> view -> HTTP. In this situation, I wouldn’t be pushing any data on the source table, and therefore wouldn’t have any issues with data migrations (because there would not be any). For synchronizing foreign table references, I was planning on a stored procedure + batch job to pull in foreign tables every so often and blow existing references away, though that may be tricky if there’s any dependencies on those foreign tables. Any transactions you’d handle on the source table, in a “push/pull” configuration (OLTP work on source, OLAP work on destination). I think for BI / analytics stuff, this should be fine.
I just felt restricted by what you can do with an ORM. I figured since an ORM supports so many databases, it has to cater to the least common denominator of database features, and I wanted more from Postgres.
It’s good to experiment, so definitely do so! Maybe for your use case the issues I ran into are not even relevant.
I’m not a big fan of ORMs either (they can produce inefficient queries, are often a barrier to getting exactly out of the db what you want because not every query result maps cleanly to “objects”, they can easily accidentally generate many more queries than you would want to perform, and they are a total memory hog), but they don’t have to be the least common denominator. For the query language, usually, yes (and for that, dropping down to the SQL level tends to be painful), but for example Django allows you to use Postgres extensions like arrays, JSON fields, partial indexes, and even Postgis fields and spatial operators. Only thing I kind of miss are proper enum fields.
Hmm. Reading just the title, I had a, perhaps, different ‘rendition’ of Postgres-as-an-App idea.
I thought:
Let’s call this thing ‘Positron’ (as alternative to Electron :-) ). It will be a GUI-enabled app engine that runs on Mobile as well as on desktop
With built in database, and with built-in capability to synch app the data (through postgres) to backend server also in postgres (optionally).
With a GUI layer for mobile platforms (eg Flutter ?, maybe?, or something else that runs on mobile+desktop)
With platform specific account management (where Postgres user account is integrated with a mobile/desktop account management api)
In future, With a play store integration such that the Positron based apps, can re-use same runtime (so that a user does not have download all the binary code for identical runtime elements, but different apps).
PG runs on rasberryPI, so I know it compiles on Arm (which is what most mobile phones use
Sort of like FoxPro, with a modern remote backend, covering mobile platforms (and without a visual gui builder).