PostgREST: Providing HTML Content Using Htmx

PostgREST is one of my favorite opensource projects. Supabase&#x27;s success as a billion(?) dollar company is a direct result the great designs of PostgREST and of course Postgres. I don&#x27;t know the details of the Supabase sponsorship of this project, but I hope its VERY significant. Seeing this project have only 12 paying supporters [1] even though its certainly a core dependency used by at least hundreds of revenue producing companies, makes me very sad at the state of open source financial support.<p>1. <a href="https:&#x2F;&#x2F;www.patreon.com&#x2F;postgrest&#x2F;about" rel="nofollow noreferrer">https:&#x2F;&#x2F;www.patreon.com&#x2F;postgrest&#x2F;about</a>

While cool as a proof of concept and kudos for execution, this looks like a nightmare to maintain for any non-trivial webapp.

Is this kind of functionality &#x2F; coding pattern used in new or modern applications?<p>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&#x2F;or json back.<p>After v1 they stopped working on that front as it makes for nightmare maintenance work.<p>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&#x27;t look very different.

The only postgREST app I ever worked on was awful. Why? Because like most of these ‘simple’ frameworks it’s only simple until your requirements get complicated. Then the original authors had to resort to writing a ton of stored procedures on the database to get back the results they wanted and that led to scalability problems. The solution, as always, is go back to SQL.

What a neat web development stack, just html &amp; database! No back-end and no front-end required.

I used postgrest (without htmx) on an old project; it&#x27;s impressive how far you can push it. HTMX seems like a perfect fit for it too, although I&#x27;m not sure how much I really want to maintain htmx templates <i>inside</i> SQL functions...

PostgREST is fun because when you have a Haskell perspective..it&#x27;s such an obvious project. But that&#x27;s why it&#x27;s genius. It&#x27;s such a Haskell idea. Love it.

What additional tooling do you think would be needed to turn this concept into a maintainable stack with good UX for mid-to-large sized applications?

Related Show HN: Render HTML in SQL with pg_render <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=38677852">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=38677852</a>

I&#x27;d like to write down a task with this app so I don&#x27;t forget it! My task is this:<p>&lt;script&gt;alert(&quot;XSS is still a thing and building plain HTML responses without a proper templating engine is irresponsible&quot;);&lt;&#x2F;script&gt;<p>(In this case, I don&#x27;t see how the &quot;task&quot; column is sanitized anywhere.)

I&#x27;m currently in the process of evaluating PostgREST via Supabase for replacing the CRUD aspects of our legacy PHP&#x2F;Laravel code base.<p>It&#x27;s a compelling option, but there are already a lot of sharp edges.<p>For example, PostgREST doesn&#x27;t really highlight this, but for any non-trivial and sane application you have to create a separate schema (&quot;api&quot; or similar) to carefully pick what&#x27;s exposed. PostgREST has a scary &quot;allow by default&quot; permission model which is nearly enough to turn me off of the whole project.<p>To help mitigate this, I&#x27;m evaluating only using PostgREST for reads in the &quot;api&quot; schema via access-restricted views, and having all writes go through supabase edge functions. This should simplify the RLS permissions (hopefully).<p>RLS has some pitfalls too, and it&#x27;s the only mechanism you have to secure your data.<p>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.<p>Nginx and similar are built and optimized for serving assets. Using your database to do this doesn&#x27;t seem like a great idea if your application needs to scale.

PostgREST is amazing software, I have written an article on how to use it as a generic data-checking pipeline: <a href="https:&#x2F;&#x2F;www.fabianzeindl.com&#x2F;posts&#x2F;business-information-server" rel="nofollow noreferrer">https:&#x2F;&#x2F;www.fabianzeindl.com&#x2F;posts&#x2F;business-information-serv...</a><p>And I already have the next article in the works on how to use it as a CQRS&#x2F;REST-api-layer.

For those who might be interested, I&#x27;ve recently published SmoothDB on GitHub [1], which, like PostgREST, provides a RESTful API to PostgreSQL databases.<p>It&#x27;s a beta, aiming for compatibility with PostgREST, but it&#x27;s not ready for production yet (so continue to use the very good PostgREST).<p>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.<p>Your thoughts and contributions are greatly appreciated as they will help in the ongoing development and refinement of SmoothDB.<p>[1] <a href="https:&#x2F;&#x2F;github.com&#x2F;sted&#x2F;smoothdb">https:&#x2F;&#x2F;github.com&#x2F;sted&#x2F;smoothdb</a>

PostgREST overall is neat, but essentially this article is pointing out that it&#x27;s possible to use a &#x27;sprintf&#x27; equivalent SQL function as a grotty way to template HTML.<p>That&#x27;s one of the approaches of all time.

I created a full website a few years ago that allowed the search, and CRUD of art pieces. I enjoyed bringing the project to life and having a fully working website with no back-end. It was a side project to proof concept so I don’t know about scaling this kind of project, databases tend to cost more. What was harder is to maintain and evolve the logic which is deported to the db and less easily readable. Htmx seems not trivial to maintain either but a nice project still

I really like this general approach of using HTML as a declarative language.<p>I&#x27;ve been working on a similar concept except as a serverless platform which updates all data in real time: <a href="https:&#x2F;&#x2F;saasufy.com&#x2F;" rel="nofollow noreferrer">https:&#x2F;&#x2F;saasufy.com&#x2F;</a><p>Docs: <a href="https:&#x2F;&#x2F;github.com&#x2F;saasufy&#x2F;saasufy-components&#x2F;#saasufy-components">https:&#x2F;&#x2F;github.com&#x2F;saasufy&#x2F;saasufy-components&#x2F;#saasufy-compo...</a>

I feel obligated to add a shameless plug here. The idea is very close to a project I presented at pgconf.eu last week: SQLPage<p><a href="https:&#x2F;&#x2F;sql.ophir.dev&#x2F;" rel="nofollow noreferrer">https:&#x2F;&#x2F;sql.ophir.dev&#x2F;</a><p>SQLPage has the same goal as postgrest+htmx, but is a little bit higher level. It let&#x27;s you build your application using prepackaged components you can invoke directly from SQL, without having to write any HTML, CSS, or JS.

I&#x27;m learning Rust by developping a backend that will template out HTML with JSON data, with Mustache. I&#x27;m testing everything with HTMX and PostgREST.

For comparison, MSSQL has been able to provide query results in XML for quite some time. I&#x27;ve found it useful in a couple of situations. See <a href="https:&#x2F;&#x2F;learn.microsoft.com&#x2F;en-us&#x2F;sql&#x2F;relational-databases&#x2F;xml&#x2F;examples-using-path-mode" rel="nofollow noreferrer">https:&#x2F;&#x2F;learn.microsoft.com&#x2F;en-us&#x2F;sql&#x2F;relational-databases&#x2F;x...</a>

If you copy&#x2F;paste this code, note that the Tailwind CSS reference is a big download. You can reduce this by using the Tailwind build tool (<a href="https:&#x2F;&#x2F;unpkg.com&#x2F;tailwindcss@2.2.19&#x2F;dist&#x2F;tailwind.min.css" rel="nofollow noreferrer">https:&#x2F;&#x2F;unpkg.com&#x2F;tailwindcss@2.2.19&#x2F;dist&#x2F;tailwind.min.css</a>) for example to only include what you need.

Cool, but this reminds me of the &quot;we only thought whether we could, and didn&#x27;t stop to think whether we should&quot; meme

This reminds me of using xquery with MarkLogic circa 2010 to store the data, act as the middle tier, and generate the xhtml views, all in the same language. It had its quirks, but it was refreshing to do everything in one language.

Finally! Someone managed to build SQL on Rails. It only took 11 years.<p><a href="https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=0_PK1eDQyVg" rel="nofollow noreferrer">https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=0_PK1eDQyVg</a>

It’s interesting to see both PostgREST and Hasura (two similar ish tools) are both built on Haskell. I don’t see too many Haskell projects in the wild. I wonder what prompted them both to use it. Neat!

I was just scrolling through the article and saw that they are using Tailwind CSS for styling. Why? Why would you do that for a simple demo, that doesn&#x27;t demand complex styling.

Reminds me of Derek Sivers&#x27; post on using postgres as a backend here<p><a href="https:&#x2F;&#x2F;sive.rs&#x2F;pg" rel="nofollow noreferrer">https:&#x2F;&#x2F;sive.rs&#x2F;pg</a>

Does this do input validation and sanitization? Where?

Cool. Now I have to redeploy a Node server to change the padding on a text input.

first direct DB to API, now direct DB to HTML, when does this insanity end?

Instead of dealing with this torture, you can do what is explained in the article in 10 minutes with any library or framework. This is an effort beyond reinventing the wheel.

PostREST is a hack, a bad one. Every time I&#x27;m seing that tech coming up, I&#x27;m like why would anyone use something like that, so much limitation and coupling.