PostgREST: Providing HTML Content Using Htmx
postgrest.org
postgrest.org
Now, I only use agplv3 and similar license. want to use it commercially? pay me 1% of your gross revenue.
I just think without some sort of pre-agreement for it, or way of avoiding it, whether intentional or not this way of 'supporting' a project also (or even instead) buys control of it, doesn't it?
(I've never used PostgREST, I'm not referring to anything that may or may not have actually happened, just musing.)
if you know a model that works where we could have clear boundaries, we'd be happy to explore! in the meantime, I hope you can look at the past few years of development to get an appreciation of whether we have maintained an arms-length relationship
some other non-obvious ones:
- most of the client libs are a result of supabase: https://postgrest.org/en/stable/ecosystem.html#client-side-l...
- we sponsor a contributor, managed by steve (I believe he mostly works on performance): https://opencollective.com/supabase-postgrest/expenses
steve joined supabase in June 2020 (before our seed round). iirc the project was earning something like ~$300/m in donations which he was splitting between the contributors. it's basically impossible for open source projects to sustain themselves (IMO) through donations
This has been working well until now and if you follow PostgREST's development, you'll notice that all enhancements are vendor-neutral and keep the original design.
We're a much smaller team but we took some inspiration from PostgreSQL distributed model (no single company owns development) for this.
[1]: PostgREST author https://github.com/begriffs
[2]: Also part of the PostgREST team and major contributor https://github.com/PostgREST/postgrest/graphs/contributors
htmx is not meant to do anything fancy that you can’t do with Ember/Angular/React/Vue/etc.
The main motives for me deciding to go with htmx:
- No duplication of data models and routing, and all business logic stays on the server-side where it belongs.
- Locality of behavior: you can see exactly where a request will be triggered and what will be done with the response, so less jumping between files or scrolling up and down.
- No build step, no dependency hell, and no outrageous churn; just include one JS file that browsers should be able to run indefinitely.
I’ve been using it to build a nontrivial site for a few months. It has some surprises coming from a background of experience with SPAs but it’s been pleasant to use overall, once I got over my own biases.
Not having to define types in JS is massive, and having a number of transitive frontend dependencies that I can count on one hand is huge.
It might not be the best tool for every job, but it’s a pretty great tool for small full stack shops.
Couchdb, a (json) document database, whose api is http-based, had built-in list and detail methods that allowed you to respond with any type of format you could generate within their javascript interpreter. In other words, no need for a server as the client can directly hit the database and get html and/or json back.
After v1 they stopped working on that front as it makes for nightmare maintenance work.
I think many here remember the good old days of php or asp files having sql statemts mixed with html all in a single file. This doesn't look very different.
I really liked the CouchDB web stuff around 13? years ago for personal projects, but it was really awkward for teams to deal with.
That’s how everyone used to build apps before Rails came along and made everone think putting biz logic into a slow server side language was a good idea.
Some DBAs I’ve worked with even advocated for taking sorting off the database. I wasn’t entirely convinced by that one.
My server side language in this case was Scala, so it wasn’t slow, just memory hungry.
Already PostGREST is getting complicated, additions like this will make it less attractive to me.
This feature[1] actually simplified a lot and removed a lot of magic assumptions in the PostgREST codebase. It goes in line with REST as well — SQL functions are REST resources and HTML is just another representation for them.
Most of the code you see here is pure SQL and plpgSQL. The only PostgREST-specific part is the CREATE DOMAIN.
Right now most users view PostgREST as a HTTP->JSON->SQL->JSON->HTTP service and we're trying to turn that into HTTP->SQL->HTTP. If that's not some true top level simplification, I don't know what is!
[1]: https://postgrest.org/en/stable/references/api/media_type_ha...
If your app has the occasional custom backend logic, you can spin up a separate server (or edge function) to handle those one-offs endpoints.
I _might_ introduce a load balancer to support migrating between servers with only 1-2 seconds downtime, but probably only for the duration of the move.
(I'm not sure whether PostgREST also serves requests from inside the process, but another comment mentioned that it's a separate server process. I can see advantages to both ways.)
“Simple” form validation is a clusterfuck and that’s what they’re leading with.
i discuss this extensively in chapter 4 of our book:
https://hypermedia.systems/extending-html-as-hypermedia/
the tldr is that htmx generalizes:
- the event that triggers an HTTP request
- the element that triggers an HTTP request
- the type of the HTTP request
- the placement of the hypermedia response to that HTTP request
In this sense, htmx adds functionality to (or, more dramatically, "completes") HTML.
This is in contrast with, for example, https://unpoly.com, which also uses HTML-over-the-wire, but provides higher level concepts (e.g. layers) and isn't as focused on generalizing hypermedia controls. This has advantages, unpoly gives you more functionality out of the box, but puts it further way from being a pure conceptual HTML extension.
My colleague James is tall and he's a basketball player. But, in one pretty straightforward sense at least, he's not a tall basketball player.
Edit: I thought of a better analogy. Compare Lodash and uBlock. Lodash is clearly a "JavaScript library", uBlock is clearly not. Why not, given that like Lodash it's just so much JavaScript? Because uBlocks' intended users don't use it as a JavaScript library. They use it to modify the default behavior of the chrome browser to something more to their liking, not as a tool for more easily expressing themselves when writing JavaScript code. I'd argue the relation of htmx to HTML is very much like that of a Chrome/VSCode extension to Chrome/VSCode: its purpose is to change the built-in behavior of some tool (HTML) used by the user, whereas a library at bottom serves an expressive purpose.
Edit: Oh I found it here: https://postgrest.org/en/stable/how-tos/sql-user-management....
That’s a pretty neat design. Also an interesting attack surface
Where things went south for me:
- hard to hire developers that know how to work with this or that are willing to learn
- very hard to debug performance issues
- version control gets weird, since migrations are not really meant for functions and procedures (although Pyrseas[0] helped a lot)
You should almost always rewrite the system in the future, that's just the nature of growth and systems evolution.
And I'm actually not contesting the value of a regular back-end and a front-end framework: postgrest and htmx make it easy to achieve more with configuration, but this only works up to a degree. After that point the benefits of a custom application start to overweight its absence.
<script>alert("XSS is still a thing and building plain HTML responses without a proper templating engine is irresponsible");</script>
(In this case, I don't see how the "task" column is sanitized anywhere.)
I have to say I always liked the Postgres project, but this kind of dangerous and wrong information in an official tutorial makes me wary of using the platform. Who knows what other lessons from the bad old days have been forgotten by the developers?
You're right though, we'll add a warning there. Thanks for the feedback.
the examples are to show how htmx works, the "server-side" (such as it is) is just there to support the front end demo.
Of course "you have to sanitize your inputs somehow", but this example does that exactly nowhere, i.e. it is exploitable. Having the tutorial omit this problem altogether is dangerous, as not discussing it might create the impression that PostgREST somehow already handles this, or that the toy example given here does not have a glaring security vulnerability. This is particularly problematic because the reason that the docs for many other backend frameworks also don't discuss the problem is that they do in fact already handle it (usually via a built-in templating language).
Steve acknowledged the problem in a sibling comment, so hopefully the next iteration of the tutorial will address this. (Thanks!)
If you construct your DOM imperatively on the client with newElement and textContent then there is no room for XSS to sneak in, because you're never even parsing (non-static) HTML. You're inherently correct by construction! React is just a declarative wrapper around that, but all dynamic data is still structurally separate from the shape of the DOM tree.
HTMX abandons all of that, going back to "lol hope your HTML escape function is correct" (and that you use it consistently!).
You could argue that you were just moving the goalpost to serializing JSON safely… but that's a much smaller (and static!) target than escaping HTML's bespoke flavour of SGML.
The issue here is not whether we broke a few rules, or took a few liberties w/hypermedia — we did.
winks
But you can't hold a single library responsible for the behavior of a few developers unfamiliar w/ server-side template escaping. For if you do, then shouldn't we blame the whole web development ecosystem? And if the whole whole web development ecosystem is guilty, then isn't this an indictment of hypermedia's uniform interface in general?
I put it to you, Nullability: isn't this an indictment of our entire American society?
Well, you can say what you want about us, but I'm not going to sit here and listen to you badmouth the United States of America!
Gentlemen!
"Server-side template escaping" is a much thornier issue than it seems as first, and I can certainly blame it for trying to return to a paradigm where it's a problem.
> For if you do, then shouldn't we blame the whole web development ecosystem? And if the whole whole web development ecosystem is guilty, then isn't this an indictment of hypermedia's uniform interface in general?
Yes! It's almost like SGML is a pile of garbage for safely embedding user content, and HTML doesn't exactly improve things.
> Well, you can say what you want about us, but I'm not going to sit here and listen to you badmouth the United States of America!
Oh, don't get me started on the state of the US... :)
string templates work fine for lots of people (xss was/is a solved issue w/ escaping, but whatever) but if you want to use a DOM builder, or some sort of reactive mechanism, go head my dude
and that's leaving aside the danger of exposing so much application logic on the client side (an untrusted computing environment) inherent in SPAs, things like GraphQL, always revalidating server side (do you always remember to?) etc.
https://intercoolerjs.org/2016/02/17/api-churn-vs-security.h...
the idea that securing hypermedia driven applications is hard is dumb, it's been done successfully for decades now
And you're still not reading. The DOM is useless on the server, you still need to serialize and parse it.
> the idea that securing hypermedia driven applications is hard is dumb, it's been done successfully for decades now
And yet, TFA is doing it wrong in an example meant to showcase the virtues of HTMX.
where exactly are you expecting the attack vector to occur? between when you serialize the DOM you've built up and sent it across the wire?
> And yet, TFA is doing it wrong in an example meant to showcase the virtues of HTMX.
is it? i thought it was showing off some HTML generation thing they are working on in PostgREST. yeah, they are gonna need to get their HTML generation working w/escaping if they want people to really use it to generate HTML, like all the other HTML generation tools do...
I'm expecting there to be some minor mismatch between what the serializer considers worth escaping, and what the parser considers to have some special meaning.
> the DOM isn't useless on the server if you take that DOM that you've built up, serialize it and send it
Exactly...?
You make this sound like an unsolved problem. You can use any of the frameworks, including react, in your backend to render HTML and automatically handle all of that for you.
In terms of using it consistently? I guess you could say the same about prepared statements. Yes, you have to actually use the tools and not go "lol I'm gonna just pass the HTML myself for no reason."
Because HTML escaping is more complex than it should have to be. Image parsing is also a "solved problem" (if you ignore all the parser bugs that crop up in image libraries all the time).
> I guess you could say the same about prepared statements.
Actually, prepared statements are another case of doing it right! We finally realized that safely embedding text into SQL queries is way too complex, so instead we submit prepare/parametrize the query, and transfer the values separately using a vastly simpler protocol.
It's a compelling option, but there are already a lot of sharp edges.
For example, PostgREST doesn't really highlight this, but for any non-trivial and sane application you have to create a separate schema ("api" or similar) to carefully pick what's exposed. PostgREST has a scary "allow by default" permission model which is nearly enough to turn me off of the whole project.
To help mitigate this, I'm evaluating only using PostgREST for reads in the "api" schema via access-restricted views, and having all writes go through supabase edge functions. This should simplify the RLS permissions (hopefully).
RLS has some pitfalls too, and it's the only mechanism you have to secure your data.
Serving assets from Postgres seems like a bad idea aside from some simple edge use cases. In general, you want to treat your DB as a precious resource and minimize the amount of work it has to do.
Nginx and similar are built and optimized for serving assets. Using your database to do this doesn't seem like a great idea if your application needs to scale.
PostgREST follows Postgres' "deny by default", you have to explicitly grant permissions for tables and views to be used. This is noted on the first tutorial[0].
Supabase overrides this default via `ALTER DEFAULT PRIVILEGES .. GRANT`[1]. This is done for easier onboarding of new users but you can turn this off with `ALTER DEFAULT PRIVILEGES .. REVOKE`.
PostgREST also encourages you to create a dedicated schema for your api[2].
[0]: https://postgrest.org/en/stable/tutorials/tut0.html#step-4-c...
[1]: See an example of this on https://supabase.com/docs/guides/api/using-custom-schemas.
[2]: https://postgrest.org/en/stable/explanations/schema_isolatio...
Ah, thank you for this clarification. I was wondering what that comment was referring to.
(and keep up the great work <3)
Yep, I'm aware of the ability to revoke permissions, it's the design choice of "allow by default" that worries me - but I agree this is a Supabase thing, not PostgREST.
On a new project, if you look at the dashboard (pointing at an existing db) access to all tables and data is on by default, you have to explicitly deny access to those tables. That's a concerning design choice for my industry (healthcare).
> PostgREST also encourages you to create a dedicated schema for your api[2].
Yes, it does - but this isn't really called out strongly by Supabase (not PostgREST's fault).
As some other commenters mentioned, it's not a big deal to create views in the api schema, and I agree. The part I'm still evaluating is whether there will be actual time savings and efficiency vs just creating CRUD endpoints in NodeJS (for example).
The big "win" for PostgREST here is the ability to do deep querying and result shaping directly from the UI without needing to create a bunch of custom endpoints. If I have to create views anyway, it seems like I lose a lot of the benefits of using PostgREST in the first place, and add a layer of complication to the stack.
I like PostgREST a lot, and I've been hoping that Supabase would be an effective Directus replacement (now that Directus has gone closed source), but I'm still figuring out whether the juice is worth the squeeze on my project.
Would love any insight you or others have from using it in production with a medium-scale sized app.
Yep, but it's a CREATE VIEW instead of writing a route in python or ruby which also will likely hit an ORM...
> Serving assets from Postgres seems like a bad idea aside from some simple edge use cases. In general, you want to treat your DB as a precious resource and minimize the amount of work it has to do.
The database should still be the source of truth (of generated assets). An easy solution is to configure NGINX to cache those asset requests, or write a script to unpack them to the file system.
> PostgREST doesn't really highlight this, but for any non-trivial and sane application you have to create a separate schema ("api" or similar) to carefully pick what's exposed.
That recommendation is highlighted here: https://postgrest.org/en/stable/explanations/schema_isolatio...
> PostgREST has a scary "allow by default" permission model which is nearly enough to turn me off of the whole project.
I assume you're talking about access via the db-anon, without authenticating your users. I can't think of what else you could be referring to. If you rely on JWT-based authentication, as any non-trivial and sane application would do, you'd have access to transaction-scoped impersonated roles. https://postgrest.org/en/stable/references/auth.html#overvie...
> having all writes go through supabase edge functions
Why is this necessary, what are you unable to achieve with JWT + RLS/RBA? Again, I have to assume you don't have auth configured correctly.
> RLS has some pitfalls too, and it's the only mechanism you have to secure your data.
You also have role-based access, and the JWT auth to start with. However I have found RLS to be flexible enough, especially as you can define policies using subqueries into which you can embed UDFs. What are you trying to do that you can't do with it?
> Serving assets from Postgres seems like a bad idea aside from some simple edge use cases.
I instinctively agree in terms of devex, although I'm reluctant to just dismiss it. I think the performance hit with a database is the data retrieval, not the templating. I can see a possibility of this evolving into something very usable with some packaging of templates, especially once you realise the approach eliminates the need for more services, simplifies auth, and reduces rendering latency. The memory footprint of PostgREST is trivial compared to most middleware you'd have to add to the mix just to do some SSR.
And I already have the next article in the works on how to use it as a CQRS/REST-api-layer.
It's a beta, aiming for compatibility with PostgREST, but it's not ready for production yet (so continue to use the very good PostgREST).
Written in Go, SmoothDB can be used both stand-alone and as a module for more complex server applications, which was my main motivation for writing this.
Your thoughts and contributions are greatly appreciated as they will help in the ongoing development and refinement of SmoothDB.
That's one of the approaches of all time.
I've been working on a similar concept except as a serverless platform which updates all data in real time: https://saasufy.com/
Docs: https://github.com/saasufy/saasufy-components/#saasufy-compo...
SQLPage has the same goal as postgrest+htmx, but is a little bit higher level. It let's you build your application using prepackaged components you can invoke directly from SQL, without having to write any HTML, CSS, or JS.
[1]: https://www.postgresql.org/docs/current/functions-xml.html
We've also updated the doc with some manual sanitization[1], but that's definitely not the final form of this POC.
[1]: https://postgrest.org/en/stable/how-tos/providing-html-conte...
Am I using PostgREST for a project with lots of complex backend logic, long running tasks, etc? Absolutely not. But that's not what it was built for.