10 ms·

Building single-page-apps with PostgREST

161 points by nileshtrivedi 5y ago

text2db 5y ago
A bit off topic, but you can convert simple text to database script here (PostgreSQL also supported): https://text2db.com/ https://text2db.com/ You can find tutorial here : https://www.youtube.com/watch?v=8hYtZ38wuz4 https://www.youtube.com/watch?v=8hYtZ38wuz4
nileshtrivedi 5y ago
Author here. I really think there's a lot of value in this model for users to self-host apps. PostgreSQL can run cron jobs, operate on external data with foreign data wrappers, and even do some number-crunching with Madlib right within SQL. So, the user only has to manage a postgresql db, retains control on data and can easily get upgrades of webapps via CDN and mobile apps via app stores. Developers benefit because (a) there is no server-side programming language to deploy/maintain and (b) PostgREST API is very well thought-out and very predictable - leading to easier development across frontend/mobile frameworks.
- klyrs 5y ago
  FYI: On android/ff the site menu hovers, and takes up over a third of my screen. It's pretty annoying.
  - nileshtrivedi 5y ago
    The site is made with Gitbook which seems to have gone through major changes recently. I'll see what I can do about it.
  - cutler 5y ago
    Seconded.
- steve-chavez 5y ago
  Nice! In the past I've also rendered an OSM map[1] with this approach and as you mention caching/CDN should make it suitable for production. [1]: https://gist.github.com/steve-chavez/c1435a8c9583d2524e87e4f7c378d322 https://gist.github.com/steve-chavez/c1435a8c9583d2524e87e4f...
  - nileshtrivedi 5y ago
    Pretty cool! Was the function being named "index.html", made it available on /rpc instead of /rpc/index.html ?
    - steve-chavez 5y ago
      Ah, no - PostgREST doesn't have that kind of convention builtin(default to searching an index.html on path). I'll update the gist and change the function to `map.html` to avoid any confusion.
- chrismorgan 5y ago
  Meta: your code blocks have the CSS `overflow: scroll`, which is wrong, causing scrollbars to appear unnecessarily; 9999⁄10000 times, `overflow: auto` is what you want instead. (macOS is the only significant desktop platform that uses overlay scrollbars by default, where the difference is not generally visible.)
  - nileshtrivedi 5y ago
    I am using Gitbook. They seemed to have changed things in a big way and a number of issues seem to have cropped up. Unlikely I can fix these myself.
andrenotgiant 5y ago
This is kind of crazy but really interesting as a sort of extreme manifestation of data-centric applications. I say it's crazy, but startups like Supabase are essentially building the professional version of your idea. The share of discourse on HN about PostgreSQL, SQL, and Databases is growing noticeably. [0] I, for one, hope we are seeing the pendulum swing back from code-centric architectures and NoSQL to a new golden age of data-centric architectures. Next you need push and pub/sub semantics in PostgreSQL so you can build something like this [1] [0] https://toddwschneider.com/dashboards/hacker-news-trends/?q=postgresql%2Csql%2Cdatabase&f=title&s=text&m=frac_items&t=year https://toddwschneider.com/dashboards/hacker-news-trends/?q=... [1] https://tonsky.me/blog/the-web-after-tomorrow/ https://tonsky.me/blog/the-web-after-tomorrow/
- nileshtrivedi 5y ago
  "Reversible SQL" is such a great idea. PostgreSQL does have pub/sub semantics via LISTEN and NOTIFY. At some level, we should think about removing the centralized database completely, and let all of user's devices synchronize with each other somehow. Perhaps SQLite database files synchronized with syncthing? That would really be fantastic!
  - CuriouslyC 5y ago
    CouchDB has a javascript client called PouchDB that can do multi-"master" replication and streaming. I wouldn't use it as a primary database, but the syncing story is pretty much top notch.
- ovao 5y ago
  Postgres has logical replication[0], which the Supabase client uses for real-time data subscriptions. [0]: https://www.postgresql.org/docs/10/logical-replication.html https://www.postgresql.org/docs/10/logical-replication.html
  - cpursley 5y ago
    Yep, I actually pulled out the Supabase realtime code into an individual library so that you can listen to Postgres changes right in Elixir: https://github.com/cpursley/walex https://github.com/cpursley/walex
rco8786 5y ago
This is amazing. I’ve been wondering more and more myself why it’s always “proper” to run your backend code on separate servers from your database. Like this is considered a must have for even the most basic architectures. No cloud provider or PaaS has real support for running your app code and your database on the same machine, but that should be the default for a huge majority of apps out there that don’t need the scalability beyond a single server.
- nileshtrivedi 5y ago
  Absolutely. Also, somehow, the ability to write postgresql triggers and functions in standard languages (Python, Javascript) seems to be vastly underutilized. The times are changing though. If there's anybody here from Supabase team, would love to chat about these topics.
  - kiwicopple 5y ago
    Supabase ceo here. Your article is very cool. > write postgresql triggers and functions in standard languages (Python, Javascript) seems to be vastly underutilized. This is definitely something we provide tooling for over time. We have plv8 installed on all databases so it should be simple enough
- mbreese 5y ago
  I’m confused. You can always run your backend code on the same machine as your database. It’s a good idea to keep the code decoupled (so when you get popular, you could move the database to a bigger box). But you have to maintain/install those things yourself. If you’re using a PaaS vendor, they will use specific instances or installations for your database to help make your setup more scalable. In reality, it is to make your code and all of their other customers code more scalable as a whole. But, the first step in developing an MVP should probably be running all of the code on a single server. But again, that assumes you’re the one setting up the server.
  - rco8786 5y ago
    > You can always run your backend code on the same machine as your database. What I am saying is that nobody ever, ever does this. It’s considered a strict antipattern for any app, even dopey internal MVPs. And I think it should be the default pattern until you need to scale.
doopy1 5y ago
The sql/js syntax kind of reminds me of coldfusion for some reason.
- duxup 5y ago
  I thought that too. I still work on an old cold fusion application that works fine / makes money. There’s something super fast in some cases where you write your sql, markup in one file and you are done.
amitu 5y ago
I am a huge fan of this stack. I love that Supabase is spending millions of VC money to popularise this stack, solving all issues, improving PostgreSQL and PostgREST, writing documentation, conference talks etc. kind of sucks the stack success requires Supabase to die (technology becoming commodity, which it already it, Supabase people did make everything open source, true to their promises). Supabase has zero differentiation. They have a nice "PGAdmin" if you may, but no one will pay for entire database hosting because of an admin interface. When it comes to hosting DB AWS, Azure etc have massive advantage. Supabase will simply popularise the stack, and then when time to actually host the database comes people will move from Supabase to AWS etc. Also the best language for UI is not JS, but something like FTD if we continue to make the progress we are making. Hopefully FTD + SQL Is all you need to know soon. Checkout this design that we are working towards for making this a reality: https://fpm.dev/journal/#explanation-of-dynamic-documents-and-actions https://fpm.dev/journal/#explanation-of-dynamic-documents-an...
- cstpdk 5y ago
  FTD = Frontotemporal demensia?
  - amitu 5y ago
    FTD: https://ftd.dev/language/ https://ftd.dev/language/. A DSL for creating "programmable prose". Markdown with data declaration, event handling and soon arbitrary computation.
    - bhawks 5y ago
      There are many rendering issues on their landing pages. Things like unreadable elements with near 0 contrast between font and background and a dark mode with pieces of widgets with a white background does not fill me with confidence for a UI focused technology.
      - bobthebuilders 5y ago
        Yeah this has to be some sort of joke, I can't even read what 80% of the sidebar on mobile, and it crashes my phone every other page.
invalidname 5y ago
First, it's a pretty cool PoC. Kudos on that. Back in the 90's we used to call direct access from the client "fat client" and we stopped doing that. We moved to n-tier with the server in the middle. It takes more work but we still did that move. Here's why: * Writing logic in server code is easier * SQL servers are more powerful and harder to secure * Caching and scale - direct DB access just couldn't handle even 90's traffic * Vendor lock-in - pretty much everything here is Postgress specific I haven't kept up on DB progress as much as I should but honestly, most of what you have here can be done in a few lines of python and maybe a bit more in Java/Spring Boot/NodeJS etc. I'm trying to think of a use case for this other than "we can do that cool thing". Can't come up with it.
- nileshtrivedi 5y ago
  The use case is what I mentioned in the article: Easier development and deployment of open-source, self-hosted apps. This paradigm makes things a lot easier both for the user and the volunteer developers. It simplifies the 3-tier architecture (DB, server, client) into effectively 2-tier because the server environment is replaced by a standard binary (PostgREST) with zero hand-written code. This is 33% improvement in language/framework complexity if nothing else. If needed, one can also write postgresql functions/triggers in Javascript/Python/etc for productivity - although this doesn't seem to have taken off yet.
- dvdkon 5y ago
  In a "typical" 3-tier app, you need a specific, explicitly written handler for each combination of data type, filtering, aggregation and output. That might not be a big deal if the amount of endpoint variants roughly corresponds to the amount of custom views in your app, but not every app is like that. Quite a few make do with just a few general views like a table, form and maybe map. Having to write explicit server-side handlers for every query variant means abstracting these views on the frontend is not viable, so apps with some 100 tables and little table-specific functionality take much longer to make and are much less flexible. It might just be personal bias, but I think these kinds of projects are common and important. They're near invisible, but even small-ish companies need somewhere to store their domain-specific data, and we should be thinking of ways to make that kind of software better.
duxup 5y ago
>Checking your browser before accessing blog.polyglot.network. Anyone stuck at that page trying to follow the link? Edit: it finally worked for me… had to click the link half a dozen times before I stopped getting the refreshing cloudflare page
- mbreese 5y ago
  I could only get it to work by switching from Firefox to safari. (On my phone)
- unboxingelf 5y ago
  Stuck here on iPhone/firefox… The irony.
aswinmohanme 5y ago
I have a love and hate relationship with this stack. On one hand it makes sense to do everything in SQL and generate the data access patterns from it. On the other hand pushing business logic into the database has it's disadvantages - Scaling Databases is hard, - It's easier to scale a stateless application server rather than a stateful database. - SQL is still hard to maintain - Adding features using Postgres Functions means you have an additional language to maintain. The code might be spread across different migration files. Maintaining these functions long term has it's own issues.
- antupis 5y ago
  How about adding tool like dbt there.
  - glogla 5y ago
    Dbt is for analytics. It expects you to have some "raw data" and then building bunch of models and reports off of it. I do think dbt is the answer for most of analytics, but for CRUD stuff with business logic it's not going to help you.
- qaq 5y ago
  You can write functions PL/Python or whatever other availible languages. It's trivial to have well structured files with functions that you auto generate migrations from.
  - oreilles 5y ago
    Hell you can even write Postgres functions in Javascript with PL/V8.
- glogla 5y ago
  I'm on a similar boat. I really really like the idea of this stack. You are going to have the pains of a database (migrations and scaling) anyway, all your data is in a database, so why not put everything there, right? And lot of business logic fits SQL and transactions very well - like finance stuff - relying on SERIALIZABLE is probably less work than writing your own locking for moving money around. But I think the tooling is not really there, and developer experience directly translates to both productivity and team attrition so that's a really big problem. Also, lot of logic does not fit into SQL very well, say "give me a pdf" or something. Most importantly, most developers are used to think procedurally and SQL is not natural to them. And to let my inner corporate manager speak, Java or Python or node backend developers are everywhere, while for something like this you need specialized people.
fzaninotto 5y ago
I must be missing something. What is the advantage of this approach versus hosting the index.html on a CDN as well?
- nileshtrivedi 5y ago
  The first page gives users control on what code ends up being loaded with access to their data over API. First page coming from CDN, would not just be an issue for cross-origin resource sharing, also removes the ability to use SRI hash for subsequent JS. Also, the HTML page on CDN wouldn’t know what endpoint (domain, port) user has deployed their API on. One more thing for user to configure. Here, the HTML configures window.postgrest_url which the JS code can use.
  - fzaninotto 5y ago
    In the example, the SRI hash is not computed by the database, it probably comes from a build phase. This build phase can (or often does) generate the HTML at the same time it computes the JS file. As for configuration, I see it as a minor improvement over manually deploying one (or several) index.html with the API URL conf. The CORS issue is the bigget sbenefit I think, but easy to accomplish in a CDN with custom headers, or with a custom domain. So I think that it's smart, but it brings no benefit over existing approaches, and opens a whole new class of problem (like sizing your Postgrest instance for when the site is under attack, or visited by a bot).
LAC-Tech 5y ago
Is there a good solution for rendering HTML with PostgREST? Without starting an MPA vs SPA flameware, it'd be useful to be able to do.
- steve-chavez 5y ago
  What do you think is missing? Do you have any suggestions? In the article HTML is just treated as a `text` type inside the SQL function, you could do the same for rendering different pages - create functions that return HTML as text.
vogre 5y ago
I can’t actually imagine the niche for this tech. Pet projects with simplest logic? ToDo app examples? It won’t work in a large scale projects because this approach doesn’t scale well. On a small scale it adds very uneven comlexity curve. Until some point everything is quite easy, but one day you need to write a stored function in plsql, and it’s not an easy task. Python/js won’t help in this case, because there will no be maturity of nodejs/django in decades. Why should anyone prefer this thing to Rails?
- nileshtrivedi 5y ago
  Open-source apps meant for personal self-hosting as the article mentions? - Scaling is not a worry. - Simplicity is essential because developers are volunteering their time. - Users also benefit because they only need to manage a database and not 6 different server language runtimes. Can even use managed services like Supabase or RDS.
- qaq 5y ago
  It scales fine for 99.9% of projects. You can write functions in Python, JS or whatever other language you fancy. Why do you need rails for the REST API?
  - vogre 5y ago
    Because i need production-ready auth, csrf. I want to add some cache layer with redis/memcached. I want control over fields I pass to/from frontend. I want to be able to handle multipart requests. And I want a decent logging system and debugger.
    - qaq 5y ago
      I don't see any issues with everything you just outlined.
andag 5y ago
"there is no backend code!" - no... There is, just you moved it into the database. That doesn't mean you don't have to deal with it, maintain it, and change it over time.
- RedShift1 5y ago
  Perhaps a more apt description would be "there is no middleware code".
- TedDoesntTalk 5y ago
  This.
sgt 5y ago
Recently started a small project with a limited number of developers and in the end; a limit number of users. But the project has an important function. Decided on PostgREST as backend, with a thin VueJS frontend. We get a backend and API for free, and our developers know SQL very well. I think it's a brilliant choice for our use case, although admittedly not something I would use for some other projects where I'd need more scaling on DB level, etc.
morelish 5y ago
This looks very good. Especially avoiding lots of repetitive backend code. Anyone know if Supabase can manage something like firebases offline data capabilities? E.g. https://firebase.google.com/docs/firestore/manage-data/enable-offline https://firebase.google.com/docs/firestore/manage-data/enabl... I'm pretty tempted by this stack for business applications that really are mainly storing stuff in a database.
- kiwicopple 5y ago
  We don't have offline capabilities yet - it's something we will investigate probably in the later half of this year. In the meantime you can use something like WatermelonDb: https://nozbe.github.io/WatermelonDB/ https://nozbe.github.io/WatermelonDB/
  - morelish 5y ago
    Interesting. Thanks for pointing me to Watermelon. I'll take a look.
s5806533 5y ago
Have used a stack like that on one application, a rather simple "knowledge vault", a few years back. It worked very well, and at the time I liked it very much. But these days, I'm no longer fond of client-side scripting (because it gets very complex very quickly, and npm+webpack is a monster). So my next stack would be closer to BCHS [1], only with Linux, Golang, and nginx instead of BSD, httpd, and C. I might add in a tiny bit of client-side scripting such as [2], but no more npm, webpack, etc. for me. [1] https://learnbchs.org/index.html https://learnbchs.org/index.html [2] https://htmx.org/ https://htmx.org/ https://unpoly.com/ https://unpoly.com/ https://hotwired.dev/ https://hotwired.dev/
- nileshtrivedi 5y ago
  I used Preact+HTM for the same reason. The latter uses tagged templates which are well supported in browsers now, instead of JSX which would have required a compiler of some kind. Do have a look at the frontend.js mentioned in the article.
- brainbag 5y ago
  Can't do much about NPM, but webpack is legacy now, no reason to use it for new projects. You can use esbuild if you just want compiling, or vite (which uses esbuild and roll up) if you want compiling + bundling + front end assets. It Just Works™. It's very fast, minimal effort, and vastly less complicated than webpack ever was. This is first time since we first started transpiring that I have found JavaScript tooling to actually be maintainable production quality and not a brittle hacky configuration management nightmare.
  - s5806533 5y ago
    Thanks for the tip! I guess I only used webpack because vue-cli set up my project that way. Whenever I return to the project, hell breaks loose: "npm install" (whoops, 51 vulnerabilities!), "npm audit --fix" (what now? 72 vulnerabilities? I thought this was supposed to go down not up), initialize a fresh project with the CLI so as to get the latest possible scaffolding and then migrate stuff over from the old project etc. etc. This really appears brittle to me.
_xnmw 5y ago
I feel very uneasy about this, and I am not sure why. I feel like the "meat and potatoes" of any application is its data and backend code, not the frontend. Frontends can change among clients, or even be given up altogether (just use a CLI/API interface for example) but the backend business logic is what defines a program. An app is what it _does_, not what it _looks like_. This approach has been tried several times over the past few years: I first used DreamFactory[1] several years ago for a small production app (less than a few thousand users) and now I see Supabase [2] trying the same. But in my experience these don't go past very limited internal enterprise use-cases, MVPs and toy frontend apps. [1] https://www.dreamfactory.com https://www.dreamfactory.com [2] https://supabase.com https://supabase.com
helloboy 5y ago
Awesome job! I want know how can javascripts in frond-end browser get access to PostREST api with the CORS limit.

161 points by nileshtrivedi 5y ago

A bit off topic, but you can convert simple text to database script here (PostgreSQL also supported): https://text2db.com/ https://text2db.com/ You can find tutorial here : https://www.youtube.com/watch?v=8hYtZ38wuz4 https://www.youtube.com/watch?v=8hYtZ38wuz4

nileshtrivedi 5y ago

Author here. I really think there's a lot of value in this model for users to self-host apps. PostgreSQL can run cron jobs, operate on external data with foreign data wrappers, and even do some number-crunching with Madlib right within SQL. So, the user only has to manage a postgresql db, retains control on data and can easily get upgrades of webapps via CDN and mobile apps via app stores. Developers benefit because (a) there is no server-side programming language to deploy/maintain and (b) PostgREST API is very well thought-out and very predictable - leading to easier development across frontend/mobile frameworks.

klyrs 5y ago
FYI: On android/ff the site menu hovers, and takes up over a third of my screen. It's pretty annoying.
- nileshtrivedi 5y ago
  The site is made with Gitbook which seems to have gone through major changes recently. I'll see what I can do about it.
- cutler 5y ago
  Seconded.
steve-chavez 5y ago
Nice! In the past I've also rendered an OSM map[1] with this approach and as you mention caching/CDN should make it suitable for production. [1]: https://gist.github.com/steve-chavez/c1435a8c9583d2524e87e4f7c378d322 https://gist.github.com/steve-chavez/c1435a8c9583d2524e87e4f...
- nileshtrivedi 5y ago
  Pretty cool! Was the function being named "index.html", made it available on /rpc instead of /rpc/index.html ?
  - steve-chavez 5y ago
    Ah, no - PostgREST doesn't have that kind of convention builtin(default to searching an index.html on path). I'll update the gist and change the function to `map.html` to avoid any confusion.
chrismorgan 5y ago
Meta: your code blocks have the CSS `overflow: scroll`, which is wrong, causing scrollbars to appear unnecessarily; 9999⁄10000 times, `overflow: auto` is what you want instead. (macOS is the only significant desktop platform that uses overlay scrollbars by default, where the difference is not generally visible.)
- nileshtrivedi 5y ago
  I am using Gitbook. They seemed to have changed things in a big way and a number of issues seem to have cropped up. Unlikely I can fix these myself.

andrenotgiant 5y ago

This is kind of crazy but really interesting as a sort of extreme manifestation of data-centric applications. I say it's crazy, but startups like Supabase are essentially building the professional version of your idea. The share of discourse on HN about PostgreSQL, SQL, and Databases is growing noticeably. [0] I, for one, hope we are seeing the pendulum swing back from code-centric architectures and NoSQL to a new golden age of data-centric architectures. Next you need push and pub/sub semantics in PostgreSQL so you can build something like this [1] [0] https://toddwschneider.com/dashboards/hacker-news-trends/?q=postgresql%2Csql%2Cdatabase&f=title&s=text&m=frac_items&t=year https://toddwschneider.com/dashboards/hacker-news-trends/?q=... [1] https://tonsky.me/blog/the-web-after-tomorrow/ https://tonsky.me/blog/the-web-after-tomorrow/

nileshtrivedi 5y ago
"Reversible SQL" is such a great idea. PostgreSQL does have pub/sub semantics via LISTEN and NOTIFY. At some level, we should think about removing the centralized database completely, and let all of user's devices synchronize with each other somehow. Perhaps SQLite database files synchronized with syncthing? That would really be fantastic!
- CuriouslyC 5y ago
  CouchDB has a javascript client called PouchDB that can do multi-"master" replication and streaming. I wouldn't use it as a primary database, but the syncing story is pretty much top notch.
ovao 5y ago
Postgres has logical replication[0], which the Supabase client uses for real-time data subscriptions. [0]: https://www.postgresql.org/docs/10/logical-replication.html https://www.postgresql.org/docs/10/logical-replication.html
- cpursley 5y ago
  Yep, I actually pulled out the Supabase realtime code into an individual library so that you can listen to Postgres changes right in Elixir: https://github.com/cpursley/walex https://github.com/cpursley/walex

rco8786 5y ago

This is amazing. I’ve been wondering more and more myself why it’s always “proper” to run your backend code on separate servers from your database. Like this is considered a must have for even the most basic architectures. No cloud provider or PaaS has real support for running your app code and your database on the same machine, but that should be the default for a huge majority of apps out there that don’t need the scalability beyond a single server.

nileshtrivedi 5y ago
Absolutely. Also, somehow, the ability to write postgresql triggers and functions in standard languages (Python, Javascript) seems to be vastly underutilized. The times are changing though. If there's anybody here from Supabase team, would love to chat about these topics.
- kiwicopple 5y ago
  Supabase ceo here. Your article is very cool. > write postgresql triggers and functions in standard languages (Python, Javascript) seems to be vastly underutilized. This is definitely something we provide tooling for over time. We have plv8 installed on all databases so it should be simple enough
mbreese 5y ago
I’m confused. You can always run your backend code on the same machine as your database. It’s a good idea to keep the code decoupled (so when you get popular, you could move the database to a bigger box). But you have to maintain/install those things yourself. If you’re using a PaaS vendor, they will use specific instances or installations for your database to help make your setup more scalable. In reality, it is to make your code and all of their other customers code more scalable as a whole. But, the first step in developing an MVP should probably be running all of the code on a single server. But again, that assumes you’re the one setting up the server.
- rco8786 5y ago
  > You can always run your backend code on the same machine as your database. What I am saying is that nobody ever, ever does this. It’s considered a strict antipattern for any app, even dopey internal MVPs. And I think it should be the default pattern until you need to scale.

doopy1 5y ago

The sql/js syntax kind of reminds me of coldfusion for some reason.

duxup 5y ago
I thought that too. I still work on an old cold fusion application that works fine / makes money. There’s something super fast in some cases where you write your sql, markup in one file and you are done.

amitu 5y ago

I am a huge fan of this stack. I love that Supabase is spending millions of VC money to popularise this stack, solving all issues, improving PostgreSQL and PostgREST, writing documentation, conference talks etc. kind of sucks the stack success requires Supabase to die (technology becoming commodity, which it already it, Supabase people did make everything open source, true to their promises). Supabase has zero differentiation. They have a nice "PGAdmin" if you may, but no one will pay for entire database hosting because of an admin interface. When it comes to hosting DB AWS, Azure etc have massive advantage. Supabase will simply popularise the stack, and then when time to actually host the database comes people will move from Supabase to AWS etc. Also the best language for UI is not JS, but something like FTD if we continue to make the progress we are making. Hopefully FTD + SQL Is all you need to know soon. Checkout this design that we are working towards for making this a reality: https://fpm.dev/journal/#explanation-of-dynamic-documents-and-actions https://fpm.dev/journal/#explanation-of-dynamic-documents-an...

cstpdk 5y ago
FTD = Frontotemporal demensia?
- amitu 5y ago
  FTD: https://ftd.dev/language/ https://ftd.dev/language/. A DSL for creating "programmable prose". Markdown with data declaration, event handling and soon arbitrary computation.
  - bhawks 5y ago
    There are many rendering issues on their landing pages. Things like unreadable elements with near 0 contrast between font and background and a dark mode with pieces of widgets with a white background does not fill me with confidence for a UI focused technology.
    - bobthebuilders 5y ago
      Yeah this has to be some sort of joke, I can't even read what 80% of the sidebar on mobile, and it crashes my phone every other page.

invalidname 5y ago

First, it's a pretty cool PoC. Kudos on that. Back in the 90's we used to call direct access from the client "fat client" and we stopped doing that. We moved to n-tier with the server in the middle. It takes more work but we still did that move. Here's why: * Writing logic in server code is easier * SQL servers are more powerful and harder to secure * Caching and scale - direct DB access just couldn't handle even 90's traffic * Vendor lock-in - pretty much everything here is Postgress specific I haven't kept up on DB progress as much as I should but honestly, most of what you have here can be done in a few lines of python and maybe a bit more in Java/Spring Boot/NodeJS etc. I'm trying to think of a use case for this other than "we can do that cool thing". Can't come up with it.

nileshtrivedi 5y ago
The use case is what I mentioned in the article: Easier development and deployment of open-source, self-hosted apps. This paradigm makes things a lot easier both for the user and the volunteer developers. It simplifies the 3-tier architecture (DB, server, client) into effectively 2-tier because the server environment is replaced by a standard binary (PostgREST) with zero hand-written code. This is 33% improvement in language/framework complexity if nothing else. If needed, one can also write postgresql functions/triggers in Javascript/Python/etc for productivity - although this doesn't seem to have taken off yet.
dvdkon 5y ago
In a "typical" 3-tier app, you need a specific, explicitly written handler for each combination of data type, filtering, aggregation and output. That might not be a big deal if the amount of endpoint variants roughly corresponds to the amount of custom views in your app, but not every app is like that. Quite a few make do with just a few general views like a table, form and maybe map. Having to write explicit server-side handlers for every query variant means abstracting these views on the frontend is not viable, so apps with some 100 tables and little table-specific functionality take much longer to make and are much less flexible. It might just be personal bias, but I think these kinds of projects are common and important. They're near invisible, but even small-ish companies need somewhere to store their domain-specific data, and we should be thinking of ways to make that kind of software better.

duxup 5y ago

>Checking your browser before accessing blog.polyglot.network. Anyone stuck at that page trying to follow the link? Edit: it finally worked for me… had to click the link half a dozen times before I stopped getting the refreshing cloudflare page

mbreese 5y ago
I could only get it to work by switching from Firefox to safari. (On my phone)
unboxingelf 5y ago
Stuck here on iPhone/firefox… The irony.

aswinmohanme 5y ago

I have a love and hate relationship with this stack. On one hand it makes sense to do everything in SQL and generate the data access patterns from it. On the other hand pushing business logic into the database has it's disadvantages - Scaling Databases is hard, - It's easier to scale a stateless application server rather than a stateful database. - SQL is still hard to maintain - Adding features using Postgres Functions means you have an additional language to maintain. The code might be spread across different migration files. Maintaining these functions long term has it's own issues.

antupis 5y ago
How about adding tool like dbt there.
- glogla 5y ago
  Dbt is for analytics. It expects you to have some "raw data" and then building bunch of models and reports off of it. I do think dbt is the answer for most of analytics, but for CRUD stuff with business logic it's not going to help you.
qaq 5y ago
You can write functions PL/Python or whatever other availible languages. It's trivial to have well structured files with functions that you auto generate migrations from.
- oreilles 5y ago
  Hell you can even write Postgres functions in Javascript with PL/V8.
glogla 5y ago
I'm on a similar boat. I really really like the idea of this stack. You are going to have the pains of a database (migrations and scaling) anyway, all your data is in a database, so why not put everything there, right? And lot of business logic fits SQL and transactions very well - like finance stuff - relying on SERIALIZABLE is probably less work than writing your own locking for moving money around. But I think the tooling is not really there, and developer experience directly translates to both productivity and team attrition so that's a really big problem. Also, lot of logic does not fit into SQL very well, say "give me a pdf" or something. Most importantly, most developers are used to think procedurally and SQL is not natural to them. And to let my inner corporate manager speak, Java or Python or node backend developers are everywhere, while for something like this you need specialized people.

fzaninotto 5y ago

I must be missing something. What is the advantage of this approach versus hosting the index.html on a CDN as well?

nileshtrivedi 5y ago
The first page gives users control on what code ends up being loaded with access to their data over API. First page coming from CDN, would not just be an issue for cross-origin resource sharing, also removes the ability to use SRI hash for subsequent JS. Also, the HTML page on CDN wouldn’t know what endpoint (domain, port) user has deployed their API on. One more thing for user to configure. Here, the HTML configures window.postgrest_url which the JS code can use.
- fzaninotto 5y ago
  In the example, the SRI hash is not computed by the database, it probably comes from a build phase. This build phase can (or often does) generate the HTML at the same time it computes the JS file. As for configuration, I see it as a minor improvement over manually deploying one (or several) index.html with the API URL conf. The CORS issue is the bigget sbenefit I think, but easy to accomplish in a CDN with custom headers, or with a custom domain. So I think that it's smart, but it brings no benefit over existing approaches, and opens a whole new class of problem (like sizing your Postgrest instance for when the site is under attack, or visited by a bot).

LAC-Tech 5y ago

Is there a good solution for rendering HTML with PostgREST? Without starting an MPA vs SPA flameware, it'd be useful to be able to do.

steve-chavez 5y ago
What do you think is missing? Do you have any suggestions? In the article HTML is just treated as a `text` type inside the SQL function, you could do the same for rendering different pages - create functions that return HTML as text.

vogre 5y ago

I can’t actually imagine the niche for this tech. Pet projects with simplest logic? ToDo app examples? It won’t work in a large scale projects because this approach doesn’t scale well. On a small scale it adds very uneven comlexity curve. Until some point everything is quite easy, but one day you need to write a stored function in plsql, and it’s not an easy task. Python/js won’t help in this case, because there will no be maturity of nodejs/django in decades. Why should anyone prefer this thing to Rails?

nileshtrivedi 5y ago
Open-source apps meant for personal self-hosting as the article mentions? - Scaling is not a worry. - Simplicity is essential because developers are volunteering their time. - Users also benefit because they only need to manage a database and not 6 different server language runtimes. Can even use managed services like Supabase or RDS.
qaq 5y ago
It scales fine for 99.9% of projects. You can write functions in Python, JS or whatever other language you fancy. Why do you need rails for the REST API?
- vogre 5y ago
  Because i need production-ready auth, csrf. I want to add some cache layer with redis/memcached. I want control over fields I pass to/from frontend. I want to be able to handle multipart requests. And I want a decent logging system and debugger.
  - qaq 5y ago
    I don't see any issues with everything you just outlined.

andag 5y ago

"there is no backend code!" - no... There is, just you moved it into the database. That doesn't mean you don't have to deal with it, maintain it, and change it over time.

RedShift1 5y ago
Perhaps a more apt description would be "there is no middleware code".
TedDoesntTalk 5y ago
This.

sgt 5y ago

Recently started a small project with a limited number of developers and in the end; a limit number of users. But the project has an important function. Decided on PostgREST as backend, with a thin VueJS frontend. We get a backend and API for free, and our developers know SQL very well. I think it's a brilliant choice for our use case, although admittedly not something I would use for some other projects where I'd need more scaling on DB level, etc.

morelish 5y ago

This looks very good. Especially avoiding lots of repetitive backend code. Anyone know if Supabase can manage something like firebases offline data capabilities? E.g. https://firebase.google.com/docs/firestore/manage-data/enable-offline https://firebase.google.com/docs/firestore/manage-data/enabl... I'm pretty tempted by this stack for business applications that really are mainly storing stuff in a database.

kiwicopple 5y ago
We don't have offline capabilities yet - it's something we will investigate probably in the later half of this year. In the meantime you can use something like WatermelonDb: https://nozbe.github.io/WatermelonDB/ https://nozbe.github.io/WatermelonDB/
- morelish 5y ago
  Interesting. Thanks for pointing me to Watermelon. I'll take a look.

s5806533 5y ago

Have used a stack like that on one application, a rather simple "knowledge vault", a few years back. It worked very well, and at the time I liked it very much. But these days, I'm no longer fond of client-side scripting (because it gets very complex very quickly, and npm+webpack is a monster). So my next stack would be closer to BCHS [1], only with Linux, Golang, and nginx instead of BSD, httpd, and C. I might add in a tiny bit of client-side scripting such as [2], but no more npm, webpack, etc. for me. [1] https://learnbchs.org/index.html https://learnbchs.org/index.html [2] https://htmx.org/ https://htmx.org/ https://unpoly.com/ https://unpoly.com/ https://hotwired.dev/ https://hotwired.dev/

nileshtrivedi 5y ago
I used Preact+HTM for the same reason. The latter uses tagged templates which are well supported in browsers now, instead of JSX which would have required a compiler of some kind. Do have a look at the frontend.js mentioned in the article.
brainbag 5y ago
Can't do much about NPM, but webpack is legacy now, no reason to use it for new projects. You can use esbuild if you just want compiling, or vite (which uses esbuild and roll up) if you want compiling + bundling + front end assets. It Just Works™. It's very fast, minimal effort, and vastly less complicated than webpack ever was. This is first time since we first started transpiring that I have found JavaScript tooling to actually be maintainable production quality and not a brittle hacky configuration management nightmare.
- s5806533 5y ago
  Thanks for the tip! I guess I only used webpack because vue-cli set up my project that way. Whenever I return to the project, hell breaks loose: "npm install" (whoops, 51 vulnerabilities!), "npm audit --fix" (what now? 72 vulnerabilities? I thought this was supposed to go down not up), initialize a fresh project with the CLI so as to get the latest possible scaffolding and then migrate stuff over from the old project etc. etc. This really appears brittle to me.

_xnmw 5y ago

I feel very uneasy about this, and I am not sure why. I feel like the "meat and potatoes" of any application is its data and backend code, not the frontend. Frontends can change among clients, or even be given up altogether (just use a CLI/API interface for example) but the backend business logic is what defines a program. An app is what it _does_, not what it _looks like_. This approach has been tried several times over the past few years: I first used DreamFactory[1] several years ago for a small production app (less than a few thousand users) and now I see Supabase [2] trying the same. But in my experience these don't go past very limited internal enterprise use-cases, MVPs and toy frontend apps. [1] https://www.dreamfactory.com https://www.dreamfactory.com [2] https://supabase.com https://supabase.com

helloboy 5y ago

Awesome job! I want know how can javascripts in frond-end browser get access to PostREST api with the CORS limit.