5 ms·

Derek Sivers's database and web apps

146 points by surprisetalk 1y ago

jppope 1y ago
Echos of the old web. Feels great to see this posted here
sivers 1y ago
Whoa. How weird to see this on the HN front page. It's so not ready yet. I'll write up more about it some day soon. For the big idea, see https://sive.rs/pg https://sive.rs/pg I really took Rich Hickey's "Simplicity Matters" talk to heart. https://www.youtube.com/watch?v=rI8tNMsozo0 https://www.youtube.com/watch?v=rI8tNMsozo0 I've been making PostgreSQL-centered web apps for 9 years that way, by having PostgreSQL just return JSON. Then the "controller" (Ruby or whatever) parses the Mustache or ERB template with the JSON and returns HTML to the browser. What I'm doing differently now is having PostgreSQL parse Mustache templates directly! So now the controller just has to pass in the HTTP params, give it to the PostgreSQL function, and it returns HTML ready to return in the HTTP response.
- danielcberman 1y ago
  Have you crossed paths with Chris Dancy? He’s been doing something similar with a life database using Airtable. https://www.chrisdancy.com/howto https://www.chrisdancy.com/howto
  - chrisweekly 1y ago
    Great link, Chris Dancy's experiences and relationship to tech are very interesting.
- hatefulheart 1y ago
  Despite reading the README and the article I am still unclear about how these templated values are populated. So presumably we store our HTML on the sql server along with some templating syntax then how do we plug that value so to speak? Secondly, what do we do about things like HTML fragments à la HTMX / Datastar hypermedia approach? Do we just hit the DB for 10 lines of HTML to populate the next card in a multi step form?
  - sivers 1y ago
    I edit my HTML templates in the templates/ directory. Then I use this little Ruby script to sync them into the database, which is where they're actually used: https://github.com/sivers/sivers/blob/master/scripts/template-sync.rb https://github.com/sivers/sivers/blob/master/scripts/templat... I haven't done HTMX fragments yet. This repository is quite new, and only like 5% done.
    - hatefulheart 1y ago
      Hey, Thanks for your response. I didn’t explain myself properly. Suppose I have a html template that contains the dynamic value {{ foo }}, that template is on my SQLDB, how do I populate {{ foo }} whilst querying the template table? I hope that makes more sense.
      - sivers 1y ago
        Ah, sorry. I misunderstood. The parser for Mustache templates - https://mustache.github.io/ https://mustache.github.io/ - is in the PostgreSQL functions. See it in practice here in the unit tests: https://github.com/sivers/sivers/blob/master/omni/test/template.sql https://github.com/sivers/sivers/blob/master/omni/test/templ... https://github.com/sivers/sivers/blob/master/omni/test/must_template.sql https://github.com/sivers/sivers/blob/master/omni/test/must_... It comes from these three functions, but really only using the top-level "o.template" function: https://github.com/sivers/sivers/blob/master/omni/template.sql https://github.com/sivers/sivers/blob/master/omni/template.s... https://github.com/sivers/sivers/blob/master/omni/must_template.sql https://github.com/sivers/sivers/blob/master/omni/must_templ... https://github.com/sivers/sivers/blob/master/omni/must_sections.sql https://github.com/sivers/sivers/blob/master/omni/must_secti... I'm using it for https://nownownow.com/ https://nownownow.com/ and https://my.nownownow.com/ https://my.nownownow.com/ already. Example test: https://github.com/sivers/sivers/blob/master/nnn/test/mynow-welcome.sql https://github.com/sivers/sivers/blob/master/nnn/test/mynow-...
        hatefulheart 1y ago
- jdmoreira 1y ago
  Hi Derek! It's not new, my first job circa 2007 was working on a Delphi 7 desktop application and all the "business logic" was stored procedures in an Oracle db. It was early in my career but I believe this was fairly popular in the early 00s. I was too young to have an opinion but for sure others will remember and be able to add more colour to it. Nice seeing you around here! I'm a fan.
  - sivers 1y ago
    Thanks! I do often get "YOU IDIOT!" type comments from people that did too many Oracle stored procedures in the 90s, and were burned by it. But PostgreSQL is not Oracle and doing things this way has been working wonderfully for me for 9 years so far.
    - ako 1y ago
      Oracle has a nice way to bundle stores procedures in packages, which makes large amounts of stored procedures manageable. So still ahead of Postgres, but Postgres is definitely good enough.
      - sirtaj 1y ago
        Asking from ignorance - are schemas not enough to replicate this most of the way? What are the extra nice to haves that would bring PG on par with Oracle here?
        ako 1y ago
        With packages you can group stored procedures, stored functions, types and variables in logical bundles. Easier to install and understand.
    - whizzter 1y ago
      I haven't had the "pleasure" to work with stored procedures,etc but from conversations the main takeaways seems to be: 1: cooperation, nowadays database instances are cheaper and with Docker we can spin them up but having them shared doesn't feel like a fun thing when developing (triggers more than stored procedures here) 2: version control, kinda ties to the above but being able to keep track of changes (and related then to code being out of sync even if that would matter less in a application-less world) 3: debugging in general ? 4: debuging "spooky effects at a distance" when triggers,etc run. What's your take on these?
- arunaugustine 1y ago
  For someone who wants to tread this path, using postgreSQL stored procedures in a team settings, what would be a good dev workflow in a team. Update them in git and use a CI to update the DB etc? Is there some tips that you can share on that?
RollAHardSix 1y ago
This is great stuff. I fell away from web dev for a very long time because I have always been a developer who focused on building from the database first and outwards while using very light amounts of business logic. This feels very old web and I encourage others to try this approach. The lightweightedness of the approach is the beauty of it and you will get much, much better at your database language of choice.
- sivers 1y ago
  Thanks! And yeah the other inspiration is that in the last 27 years of making web apps, I've gone from PHP to Ruby to JavaScript back to Ruby, considered switching to Go or Elixir, but my PostgreSQL database was at the center of it all, throughout. So it makes sense to notice what's constant, what's ever-changing, and organize accordingly.
  - derwiki 1y ago
    OOC how did you land on PostgreSQL 27 years ago? That’s before I was doing this sort of thing but 7 years later MySQL definitely seemed like the “no one got fired for choosing..” option. PS been a big fan of your writing over the years and it’s a little intimidating to just respond to one of your posts asking a silly question haha
    - zem 1y ago
      my vague memories of ~2000 were that postgres was acknowledged as the way to go if you wanted to do it right, but mysql would let you get set up quickly and easily so you could get on with your actual application, and postgres had the reputation of being harder to set up and administer
      - bigiain 1y ago
        Same memories here. In the late 90s I chose MySQL over Postgres, at the time for its speed and replication. And at least partly because I got to talk with Monty Widenius at an Open Source Conference (or perhaps even a Perl Conference) in the late 90s about replication, and asked how hard it'd be to make replication use SSL - and he sent me a beta MySQL version with that implemented a few days later. So I had a quite serious "feel good" reason for using MySQL. In the subsequent 5-10 years or so I regretted not choosing Postgres instead over it's stored procedure handling, but we had way to much deeply embedded MySQL tech and skill by then which made switching always end up on the too hard list.
novoreorx 1y ago
Business logics in PostgresSQL? Impressive, never think of using it this way but it looks actually pretty good! Last time I saw similar concept is at https://spacetimedb.com/ https://spacetimedb.com/ , maybe one day PostgresSQL can do the same, since it's already powerful enough to support web apps this way.
ptman 1y ago
Postgrest/supabase, prest or similar
apgwoz 1y ago
And here I am doing this as a joke (https://sigusr2.net/serving-traffic-directly-from-sqlite.html https://sigusr2.net/serving-traffic-directly-from-sqlite.htm...)…
renegat0x0 1y ago
Not really sure if that counts but I also created a db centered app. I do a lot of meta link archiving and I wanted to create a link browser. My project supports data in zipped sqlite, or JSONs. I believe data are as much important as programs, So everything is public. Links https://rumca-js.github.io/search https://rumca-js.github.io/search - search (JSONs) https://rumca-js.github.io/music https://rumca-js.github.io/music - music (JSONs) https://rumca-js.github.io/bookmarks https://rumca-js.github.io/bookmarks - bookmarks (JSONs) https://github.com/rumca-js/Internet-Places-Database https://github.com/rumca-js/Internet-Places-Database - scripts use SQLite https://github.com/rumca-js/web_link_browser https://github.com/rumca-js/web_link_browser - clean repo of scripts
luis_cho 1y ago
I was working in ASP.NET when projects like Backbone.js, Knockout, and Angular started to pop up. They were trying to address issues on mainstream tech at that moment (compilation times vs. hot reload, avoid full-page reloads). There was a lot of criticism around the use of JavaScript in the server back then. Now I see the opposite. Web standards can solve the full reload page problem, so it's time to rethink if we still need to manage state in the client, with all the complexity that this involves. The biggest issue for me it’s the division between backend and Frontend roles. I like to do both, but lately I’m doing backend. It changes a lot how we do things, refinements are mostly to create contracts between the Frontend and backend tasks, we’re they should focus on business use cases.
jsumrall 1y ago
Lukas Eder has a nice presentation arguing the same point: https://www.jug.ch/events/slides/180612_SQL_Algorithms.pdf https://www.jug.ch/events/slides/180612_SQL_Algorithms.pdf I have recalled over the years this quote from his presentation: "Your app is sitting on a Ferrari-style computation engine!"
sockbot 1y ago
See Postgrest for a project similar in spirit. https://docs.postgrest.org/en/v13/ https://docs.postgrest.org/en/v13/
cpursley 1y ago
Thanks, Derek - Postgres is enough! Your article years ago really put on the “use the database” path - that post is the first in this gist: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...
4ndrewl 1y ago
This is how we used to do it in the Olden Days with business logic in stored procedures. It's really hard to debug and incredibly difficult to build out automated testing, controlled releases etc.
irarelycomment 1y ago
This is great read with examples of running some business logic on the database: https://theartofpostgresql.com/ https://theartofpostgresql.com/
jopsen 1y ago
I can totally see how this is attractive. But I personally find SQL and triggers/stored procedures to have: - Horrible syntax - Poor LSP support and poor auto complete - poor debugging experiences, error reporting, etc. I kind of wish someone would make a better syntax with better LSP tooling and solid auto-complete. Geared compiling application logic into databases. I would personally love to see tooling for compatibility analysis too. It always feels weird that app logic and database are two completely different languages.

146 points by surprisetalk 1y ago

Echos of the old web. Feels great to see this posted here

Whoa. How weird to see this on the HN front page. It's so not ready yet. I'll write up more about it some day soon. For the big idea, see https://sive.rs/pg https://sive.rs/pg I really took Rich Hickey's "Simplicity Matters" talk to heart. https://www.youtube.com/watch?v=rI8tNMsozo0 https://www.youtube.com/watch?v=rI8tNMsozo0 I've been making PostgreSQL-centered web apps for 9 years that way, by having PostgreSQL just return JSON. Then the "controller" (Ruby or whatever) parses the Mustache or ERB template with the JSON and returns HTML to the browser. What I'm doing differently now is having PostgreSQL parse Mustache templates directly! So now the controller just has to pass in the HTTP params, give it to the PostgreSQL function, and it returns HTML ready to return in the HTTP response.

danielcberman 1y ago
Have you crossed paths with Chris Dancy? He’s been doing something similar with a life database using Airtable. https://www.chrisdancy.com/howto https://www.chrisdancy.com/howto
- chrisweekly 1y ago
  Great link, Chris Dancy's experiences and relationship to tech are very interesting.
hatefulheart 1y ago
Despite reading the README and the article I am still unclear about how these templated values are populated. So presumably we store our HTML on the sql server along with some templating syntax then how do we plug that value so to speak? Secondly, what do we do about things like HTML fragments à la HTMX / Datastar hypermedia approach? Do we just hit the DB for 10 lines of HTML to populate the next card in a multi step form?
- sivers 1y ago
  I edit my HTML templates in the templates/ directory. Then I use this little Ruby script to sync them into the database, which is where they're actually used: https://github.com/sivers/sivers/blob/master/scripts/template-sync.rb https://github.com/sivers/sivers/blob/master/scripts/templat... I haven't done HTMX fragments yet. This repository is quite new, and only like 5% done.
  - hatefulheart 1y ago
    Hey, Thanks for your response. I didn’t explain myself properly. Suppose I have a html template that contains the dynamic value {{ foo }}, that template is on my SQLDB, how do I populate {{ foo }} whilst querying the template table? I hope that makes more sense.
    - sivers 1y ago
      Ah, sorry. I misunderstood. The parser for Mustache templates - https://mustache.github.io/ https://mustache.github.io/ - is in the PostgreSQL functions. See it in practice here in the unit tests: https://github.com/sivers/sivers/blob/master/omni/test/template.sql https://github.com/sivers/sivers/blob/master/omni/test/templ... https://github.com/sivers/sivers/blob/master/omni/test/must_template.sql https://github.com/sivers/sivers/blob/master/omni/test/must_... It comes from these three functions, but really only using the top-level "o.template" function: https://github.com/sivers/sivers/blob/master/omni/template.sql https://github.com/sivers/sivers/blob/master/omni/template.s... https://github.com/sivers/sivers/blob/master/omni/must_template.sql https://github.com/sivers/sivers/blob/master/omni/must_templ... https://github.com/sivers/sivers/blob/master/omni/must_sections.sql https://github.com/sivers/sivers/blob/master/omni/must_secti... I'm using it for https://nownownow.com/ https://nownownow.com/ and https://my.nownownow.com/ https://my.nownownow.com/ already. Example test: https://github.com/sivers/sivers/blob/master/nnn/test/mynow-welcome.sql https://github.com/sivers/sivers/blob/master/nnn/test/mynow-...
      - hatefulheart 1y ago
jdmoreira 1y ago
Hi Derek! It's not new, my first job circa 2007 was working on a Delphi 7 desktop application and all the "business logic" was stored procedures in an Oracle db. It was early in my career but I believe this was fairly popular in the early 00s. I was too young to have an opinion but for sure others will remember and be able to add more colour to it. Nice seeing you around here! I'm a fan.
- sivers 1y ago
  Thanks! I do often get "YOU IDIOT!" type comments from people that did too many Oracle stored procedures in the 90s, and were burned by it. But PostgreSQL is not Oracle and doing things this way has been working wonderfully for me for 9 years so far.
  - ako 1y ago
    Oracle has a nice way to bundle stores procedures in packages, which makes large amounts of stored procedures manageable. So still ahead of Postgres, but Postgres is definitely good enough.
    - sirtaj 1y ago
      Asking from ignorance - are schemas not enough to replicate this most of the way? What are the extra nice to haves that would bring PG on par with Oracle here?
      - ako 1y ago
        With packages you can group stored procedures, stored functions, types and variables in logical bundles. Easier to install and understand.
  - whizzter 1y ago
    I haven't had the "pleasure" to work with stored procedures,etc but from conversations the main takeaways seems to be: 1: cooperation, nowadays database instances are cheaper and with Docker we can spin them up but having them shared doesn't feel like a fun thing when developing (triggers more than stored procedures here) 2: version control, kinda ties to the above but being able to keep track of changes (and related then to code being out of sync even if that would matter less in a application-less world) 3: debugging in general ? 4: debuging "spooky effects at a distance" when triggers,etc run. What's your take on these?
arunaugustine 1y ago
For someone who wants to tread this path, using postgreSQL stored procedures in a team settings, what would be a good dev workflow in a team. Update them in git and use a CI to update the DB etc? Is there some tips that you can share on that?

RollAHardSix 1y ago

This is great stuff. I fell away from web dev for a very long time because I have always been a developer who focused on building from the database first and outwards while using very light amounts of business logic. This feels very old web and I encourage others to try this approach. The lightweightedness of the approach is the beauty of it and you will get much, much better at your database language of choice.

sivers 1y ago
Thanks! And yeah the other inspiration is that in the last 27 years of making web apps, I've gone from PHP to Ruby to JavaScript back to Ruby, considered switching to Go or Elixir, but my PostgreSQL database was at the center of it all, throughout. So it makes sense to notice what's constant, what's ever-changing, and organize accordingly.
- derwiki 1y ago
  OOC how did you land on PostgreSQL 27 years ago? That’s before I was doing this sort of thing but 7 years later MySQL definitely seemed like the “no one got fired for choosing..” option. PS been a big fan of your writing over the years and it’s a little intimidating to just respond to one of your posts asking a silly question haha
  - zem 1y ago
    my vague memories of ~2000 were that postgres was acknowledged as the way to go if you wanted to do it right, but mysql would let you get set up quickly and easily so you could get on with your actual application, and postgres had the reputation of being harder to set up and administer
    - bigiain 1y ago
      Same memories here. In the late 90s I chose MySQL over Postgres, at the time for its speed and replication. And at least partly because I got to talk with Monty Widenius at an Open Source Conference (or perhaps even a Perl Conference) in the late 90s about replication, and asked how hard it'd be to make replication use SSL - and he sent me a beta MySQL version with that implemented a few days later. So I had a quite serious "feel good" reason for using MySQL. In the subsequent 5-10 years or so I regretted not choosing Postgres instead over it's stored procedure handling, but we had way to much deeply embedded MySQL tech and skill by then which made switching always end up on the too hard list.

novoreorx 1y ago

Business logics in PostgresSQL? Impressive, never think of using it this way but it looks actually pretty good! Last time I saw similar concept is at https://spacetimedb.com/ https://spacetimedb.com/ , maybe one day PostgresSQL can do the same, since it's already powerful enough to support web apps this way.

ptman 1y ago

Postgrest/supabase, prest or similar

apgwoz 1y ago

And here I am doing this as a joke (https://sigusr2.net/serving-traffic-directly-from-sqlite.html https://sigusr2.net/serving-traffic-directly-from-sqlite.htm...)…

renegat0x0 1y ago

Not really sure if that counts but I also created a db centered app. I do a lot of meta link archiving and I wanted to create a link browser. My project supports data in zipped sqlite, or JSONs. I believe data are as much important as programs, So everything is public. Links https://rumca-js.github.io/search https://rumca-js.github.io/search - search (JSONs) https://rumca-js.github.io/music https://rumca-js.github.io/music - music (JSONs) https://rumca-js.github.io/bookmarks https://rumca-js.github.io/bookmarks - bookmarks (JSONs) https://github.com/rumca-js/Internet-Places-Database https://github.com/rumca-js/Internet-Places-Database - scripts use SQLite https://github.com/rumca-js/web_link_browser https://github.com/rumca-js/web_link_browser - clean repo of scripts

luis_cho 1y ago

I was working in ASP.NET when projects like Backbone.js, Knockout, and Angular started to pop up. They were trying to address issues on mainstream tech at that moment (compilation times vs. hot reload, avoid full-page reloads). There was a lot of criticism around the use of JavaScript in the server back then. Now I see the opposite. Web standards can solve the full reload page problem, so it's time to rethink if we still need to manage state in the client, with all the complexity that this involves. The biggest issue for me it’s the division between backend and Frontend roles. I like to do both, but lately I’m doing backend. It changes a lot how we do things, refinements are mostly to create contracts between the Frontend and backend tasks, we’re they should focus on business use cases.

jsumrall 1y ago

Lukas Eder has a nice presentation arguing the same point: https://www.jug.ch/events/slides/180612_SQL_Algorithms.pdf https://www.jug.ch/events/slides/180612_SQL_Algorithms.pdf I have recalled over the years this quote from his presentation: "Your app is sitting on a Ferrari-style computation engine!"

sockbot 1y ago

See Postgrest for a project similar in spirit. https://docs.postgrest.org/en/v13/ https://docs.postgrest.org/en/v13/

cpursley 1y ago

Thanks, Derek - Postgres is enough! Your article years ago really put on the “use the database” path - that post is the first in this gist: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...

4ndrewl 1y ago

This is how we used to do it in the Olden Days with business logic in stored procedures. It's really hard to debug and incredibly difficult to build out automated testing, controlled releases etc.

irarelycomment 1y ago

This is great read with examples of running some business logic on the database: https://theartofpostgresql.com/ https://theartofpostgresql.com/

jopsen 1y ago

I can totally see how this is attractive. But I personally find SQL and triggers/stored procedures to have: - Horrible syntax - Poor LSP support and poor auto complete - poor debugging experiences, error reporting, etc. I kind of wish someone would make a better syntax with better LSP tooling and solid auto-complete. Geared compiling application logic into databases. I would personally love to see tooling for compatibility analysis too. It always feels weird that app logic and database are two completely different languages.