12 ms·
PostgREST: Providing HTML Content Using Htmx
- Thaxll 3y agoPostREST is a hack, a bad one. Every time I'm seing that tech coming up, I'm like why would anyone use something like that, so much limitation and coupling.
- DevX101 3y agoPostgREST is a phenomenal project, and you get A LOT for it's design. How many thousands of applications are simply wrappers around CRUD databases? PostgREST gives a great REST API implementation for exactly this use case. PostgREST probably has a more robust API interface than 70% of basic CRUD apps, with RLS built in. 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.
- naasking 3y agoCoupling isn't intrinsically bad. You could design a three tier architecture with all sorts of loose coupling, or with PostgREST you could have a one or two tier architecture with orders of magnitude less code but tight coupling. I'm not sure why the first choice is necessarily better in all cases.
- WhatsName 3y agoWhile cool as a proof of concept and kudos for execution, this looks like a nightmare to maintain for any non-trivial webapp.
- robertlagrant 3y agoIt's definitely for websites or light apps only. There's a lot of mileage in that, though.
- leptons 3y agoThe "light apps" that also require a database that don't have a more capable front-end framework seem to make HTMX look like a solution in search of a problem.
- rakoo 3y agoI'm thinking this is more interesting for admins who need common functions on top of their databases but don't want to deal with the cumbersome cli calls. Make a nice UI with the most used views and functions and you can ship it directly with the database, regardless of the stack above it. Your functions change when your admin tasks change or when your schema changes, not when the application has new requirements.
- robertlagrant 3y agoOr even something like Django's admin interface could be implemented in htmx.
- intrasight 3y agoMany web sites don't have or need frameworks
- ralmidani 3y agoI would argue the “more capable” frameworks, while invented with the best of intentions, have become unmanageable, unmaintainable monstrosities that just keep rolling out, as you put it, solutions in search of a problem. 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.
- gen220 3y ago
- brodo 3y agoI'm experimenting with it right now using Squitch [1] to make maintenance easier. It still feels like a hack and I also still have my doubts about the viability of this for real-world use. It's fun though and I'm learning about all kinds of advanced Postgres features. [1] https://sqitch.org/ https://sqitch.org/
- agumonkey 3y agoI'm honestly wondering what in our main web application would not be 100x easier using this.
- crooked-v 3y agoWell, for now, sure. Now we just need a way to compile and deploy HTMX routes to the database server as part of a CI process...
- deleted 3y ago[deleted]
- rudasn 3y agoIs this kind of functionality / coding pattern used in new or modern applications? 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.
- throwup238 3y agoOh man, those really were the good old days”; DROP TABLE users;
- gedy 3y agoI think it's just a new generation relearning the same stuff, even if it's been rejected in past for good reasons. I really liked the CouchDB web stuff around 13? years ago for personal projects, but it was really awkward for teams to deal with.
- ako 3y agoIt’s far from modern, Oracle had this over 25 years ago.
- WM6v 3y agoRelated Show HN: Render HTML in SQL with pg_render https://news.ycombinator.com/item?id=38677852 https://news.ycombinator.com/item?id=38677852
- steve-chavez 3y agoReally cool! I'm working on something similar https://github.com/PostgREST/plmustache https://github.com/PostgREST/plmustache.
- claytongulick 3y agoI'm currently in the process of evaluating PostgREST via Supabase for replacing the CRUD aspects of our legacy PHP/Laravel code base. 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.
- steve-chavez 3y ago> PostgREST has a scary "allow by default" permission model which is nearly enough to turn me off of the whole project. 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-create-database-for-api 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 https://supabase.com/docs/guides/api/using-custom-schemas. [2]: https://postgrest.org/en/stable/explanations/schema_isolation.html https://postgrest.org/en/stable/explanations/schema_isolatio...
- boomskats 3y ago> Supabase overrides this default Ah, thank you for this clarification. I was wondering what that comment was referring to. (and keep up the great work <3)
- statusfailed 3y agoI used postgrest (without htmx) on an old project; it's impressive how far you can push it. HTMX seems like a perfect fit for it too, although I'm not sure how much I really want to maintain htmx templates inside SQL functions...
- cdaringe 3y agoZero interest. BTDT (php). Static analysis, local testing, refactors, bulk changes delivered atomically (transactions, if you will) are missing critical features with this type of pattern. Great for hacking around, but not for making something stable.
- kreetx 3y agoWhat a neat web development stack, just html & database! No back-end and no front-end required.
- smegsicle 3y agocrud without the cruft
- markbnj 3y agoYears ago, mid-90's, I visited Compuserve in Ohio. The web was just getting going and one of the engineers I met there showed off a music store he was building by returning html from SQL stored procedures :).
- bsdpufferfish 3y agoThe quality of open source databases with views, partitions, etc as well as improvements in server hardware have made this approach much more appealing.
- est 3y agoif you think about it, serverless lambdas are just stored procedures with bells and wistles.
- icedchai 3y agoLambdas feel more like CGI scripts. Too bad they didn't just use the CGI standard instead of something proprietary.
- lemper 3y agoif you think about it, php is just stored procedure with bells and whistles.
- est 3y agonah you have to worry about deployments of where .php were placed and mod_php and php-fpm stuff. DB can scale and you don't have to worry where SP were executed.
- deleted 3y ago[deleted]
- oliverrice 3y agoWhat 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?
- buremba 3y agoFor anything mid-large size, I believe a separate abstraction layer is needed, which would be an API. I also tried to build something similar on top of SQL and the tech stack is Jinja-templated SQL and an OpenAPI layer implemented in YML but I would still scope it out for internal tooling. Here it is: https://jinj.at https://jinj.at
- jadbox 3y agoI've been using Astro (https://astro.build https://astro.build) for static site serving + PostgREST Htmx for just simple data-centric components.
- DevX101 3y agoPostgREST is one of my favorite opensource projects. Supabase's success as a billion(?) dollar company is a direct result the great designs of PostgREST and of course Postgres. I don'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. 1. https://www.patreon.com/postgrest/about https://www.patreon.com/postgrest/about
- dsizzle 3y agoNow 13 (but still only $1529/mo)
- lemper 3y agoI can already hear the usual bullshit they spout when we're seeing situation like this. "users has no obligation to support you financially, mate." "if you don't like it, don't release your project in permissive license." and many other similar sentences. Now, I only use agplv3 and similar license. want to use it commercially? pay me 1% of your gross revenue.
- inian 3y agoWe hire the lead maintainer for PostgREST Steve [1] to primarily work on PostgREST since it’s a core part of the Supabase stack as you mentioned [1] https://github.com/steve-chavez https://github.com/steve-chavez
- OJFord 3y agoIn situations like that, how does one handle (or plan, if you do, to handle) potential conflict between the company's product plans and what the maintainer believes is right for the OSS? 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.)
- shinycode 3y agoI 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
- quickthrower2 3y agoIf you copy/paste this code, note that the Tailwind CSS reference is a big download. You can reduce this by using the Tailwind build tool (https://unpkg.com/tailwindcss@2.2.19/dist/tailwind.min.css https://unpkg.com/tailwindcss@2.2.19/dist/tailwind.min.css) for example to only include what you need.
- xet7 3y agoDoes this do input validation and sanitization? Where?
- steve-chavez 3y agoAnswered that above: https://news.ycombinator.com/item?id=38692597 https://news.ycombinator.com/item?id=38692597 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-content-using-htmx.html#listing-and-creating-to-dos https://postgrest.org/en/stable/how-tos/providing-html-conte...
- smitpatelx 3y ago[dead]
- deleted 3y ago[deleted]
- dangoodmanUT 3y agoCool, but this reminds me of the "we only thought whether we could, and didn't stop to think whether we should" meme
- nsonha 3y agofirst direct DB to API, now direct DB to HTML, when does this insanity end?
- promiseofbeans 3y agoI mean, if you set your permissions very carefully, you could use database accounts as user accounts...
- nsonha 3y agothat's not the problem. The problem is that your back-end always has things to do OTHER than crud to DB. What about working with users' token? in-memory caching? calling other services? sending emails? And don't tell me that you have postgres plugins and other roundabout ways to solve it, that's just more insanity.
- quickthrower2 3y agoA new web server that has this all embedded as a single go binary?
- mayli 3y agoDo you mean fossil?
- socketcluster 3y agoI really like this general approach of using HTML as a declarative language. I've been working on a similar concept except as a serverless platform which updates all data in real time: https://saasufy.com/ https://saasufy.com/ Docs: https://github.com/saasufy/saasufy-components/#saasufy-components https://github.com/saasufy/saasufy-components/#saasufy-compo...
- typedef_struct 3y agoFor comparison, MSSQL has been able to provide query results in XML for quite some time. I've found it useful in a couple of situations. See https://learn.microsoft.com/en-us/sql/relational-databases/xml/examples-using-path-mode https://learn.microsoft.com/en-us/sql/relational-databases/x...
- steve-chavez 3y agoPostgreSQL has supported XML [1] for a while too. With that, even SOAP endpoints are possible: https://postgrest.org/en/stable/how-tos/create-soap-endpoint.html https://postgrest.org/en/stable/how-tos/create-soap-endpoint... [1]: https://www.postgresql.org/docs/current/functions-xml.html https://www.postgresql.org/docs/current/functions-xml.html
- pictur 3y agoInstead 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.
- epalm 3y agoThis 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.
- fzeindl 3y agoPostgREST is amazing software, I have written an article on how to use it as a generic data-checking pipeline: https://www.fabianzeindl.com/posts/business-information-server https://www.fabianzeindl.com/posts/business-information-serv... And I already have the next article in the works on how to use it as a CQRS/REST-api-layer.
- indigo945 3y agoI'd like to write down a task with this app so I don't forget it! My task is this: <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.)
- rafram 3y agoIn the bad old days, XSS was rampant because our views talked directly with our databases. Then we put templating engines and models in the middle and the problem seemed to be solved. Now we have XSS again because the database is the view?! This is a preventable problem!
- indigo945 3y agoI don't even hate the idea of generating HTML responses in the database (why not, that's where all the data is!), but this is very clearly not the way to do it. Those functions read like plain PHP scripts, and are obviously just as vulnerable. Just as PHP's problems are largely solved these days by frontend frameworks like Laravel, so could a frontend framework in the database solve the same problems here. 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?
- deleted 3y ago[deleted]
- steve-chavez 3y agoThis doc was meant to be a POC, just to show what's possible. We're working on migrating it to Mustache templates which do automatic escaping: https://github.com/PostgREST/plmustache?tab=readme-ov-file#escaped-and-unescaped https://github.com/PostgREST/plmustache?tab=readme-ov-file#e... You're right though, we'll add a warning there. Thanks for the feedback.
- 3y ago
- whateveracct 3y agoPostgREST is fun because when you have a Haskell perspective..it's such an obvious project. But that's why it's genius. It's such a Haskell idea. Love it.
- twsted 3y agoFor those who might be interested, I've recently published SmoothDB on GitHub [1], which, like PostgREST, provides a RESTful API to PostgreSQL databases. 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. [1] https://github.com/sted/smoothdb https://github.com/sted/smoothdb
- ritzaco 3y agoReminds me of Derek Sivers' post on using postgres as a backend here https://sive.rs/pg https://sive.rs/pg
- frou_dh 3y agoPostgREST overall is neat, but essentially this article is pointing out that it's possible to use a 'sprintf' equivalent SQL function as a grotty way to template HTML. That's one of the approaches of all time.
- mnd999 3y agoThe 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.
- cpursley 3y agoWhat, huh? Aren’t stored procedures SQL in function form? 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.
- Octabrain 3y agoIMHO and I might be entirely wrong but placing and coupling all that logic into the database seems like a bad idea and it's not a question of speed, it's a question of separating responsibilities. Also, for the case shown in the article, it seems all right for a "hello world" kind of thing. For something complex or prone to deep changes (like most of software projects I've been involved with), this seems like a true nightmare.
- mnd999 3y agoI’m sure you know this but the reason for taking computation off the database is that it’s much easier to horizontally scale a stateless middle tier than it is to scale a sql database. 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.
- cpursley 3y agoIs it really easier to scale a Rails or Node app than Postgres (Scala might be an exception)? And how many pieces of software actually ever reach the kind of scale where database is the bottleneck? For many use cases, biz logic in the database will absolutely smoke doing it on server side due to query planner optimizations.
- alabhyajindal 3y agoI 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't demand complex styling.
- sensanaty 3y agoTailwind is great for simple stuff though, you just stick the classes in your html and don't have to worry about thinking up class names or organizing CSS files
- lovasoa 3y agoI feel obligated to add a shameless plug here. The idea is very close to a project I presented at pgconf.eu last week: SQLPage https://sql.ophir.dev/ https://sql.ophir.dev/ 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.
- ramesh31 3y agoCool. Now I have to redeploy a Node server to change the padding on a text input.
- thevidel 3y agoI'm learning Rust by developping a backend that will template out HTML with JSON data, with Mustache. I'm testing everything with HTMX and PostgREST.
- fzaninotto 3y agoFinally! Someone managed to build SQL on Rails. It only took 11 years. https://www.youtube.com/watch?v=0_PK1eDQyVg https://www.youtube.com/watch?v=0_PK1eDQyVg
- ianschmitz 3y agoIt’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!