4 ms·

Can someone explain where/why this might be used? Or is it just for fun?

statenjason 6y ago
First thing that came to mind was in one of the "Postgres-backend" projects like Hasura[1], Postgraphile[2], or PostgREST[3]. [1]: https://github.com/hasura/graphql-engine/ https://github.com/hasura/graphql-engine/ [2]: https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/ [3]: https://postgrest.org https://postgrest.org
- smallnamespace 6y ago
  Benjie, one of the PostGraphile creators, has a good talk [1] advocating for database-driven development. OTOH, having dug into PostGraphile a bit, I personally wouldn't advocate for pushing so much backend logic into the DB. Like, if you decide to do auth and TOTP in the DB, you'll end up implementing it in PL/pgSQL. Writing core security logic in a less-familiar language feels like it adds risk. Also, for smaller projects your backend is often just a single server, so moving auth in into the DB doesn't save you from managing distributed state. [1] https://www.youtube.com/watch?v=XDOrhTXd4pE https://www.youtube.com/watch?v=XDOrhTXd4pE
  - pyramation 6y ago
    exactly! this is the ecosystem that I'm a part of that inspired me to build this :) I do agree about making sure you have experience writing PL/pgSQL and would also add that you should make sure you have a good test-driven environment when writing code in the db.
  - e12e 6y ago
    > Like, if you decide to do auth and TOTP in the DB, you'll end up implementing it in PL/pgSQL. Not really? Postgres (as well as other rdbms) support many other languages - I don't see many good reasons to insist on pl/pgSQL for uses like these? Both python, perl and TCL are part of the standard distribution in addition to pl/pgSQL. https://www.postgresql.org/docs/13/xplang.html https://www.postgresql.org/docs/13/xplang.html https://www.postgresql.org/docs/13/external-pl.html https://www.postgresql.org/docs/13/external-pl.html
    - smallnamespace 6y ago
      Right, except if my backend is in JS, there's a cognitive cost to adding another language to the stack. I could use plv8, but there's a more subtle preference here for JS to wrap SQL, rather than the reverse (plus tooling is less convenient, e.g. if you're writing Typescript). My rule of thumb is: SQL great, PL not so much.
      - e12e 6y ago
        > except if my backend is in JS, there's a cognitive cost to adding another language to the stack. Agreed. However, I don't see why not use one of the other mature "real" extension languages other than pl/pgSQL. As for typescript, maybe there's (distant) hope: https://github.com/supabase/postgres-deno https://github.com/supabase/postgres-deno
- pyramation 6y ago
  exactly! https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/ is the system I'm using on and wanted to avoid writing a resolver in JS
rbut 6y ago
Incrementing the counter, and generating the next HOTP code in a single UPDATE statement could be useful.
pyramation 6y ago
Hi! It was a bit of both fun and work ;) I use graphile and didn't want to have to implement a resolver in JavaScript, keeping logic and integrity of auth systems in the database.
- e12e 6y ago
  On the other hand, one could also write js in postgres: https://www.postgresql.org/docs/13/external-pl.html https://www.postgresql.org/docs/13/external-pl.html https://github.com/plv8/plv8 https://github.com/plv8/plv8
  - pyramation 6y ago
    yea totally! I love plv8... it's actually how I got started with postgres functions. I ultimately switched to native PG. There were known memory leaks in plv8 and eventually after learning pl/pgsql it became more natural and clean, plus AWS at the time didn't support plv8 which was another incentive not to use language extensions

First thing that came to mind was in one of the "Postgres-backend" projects like Hasura[1], Postgraphile[2], or PostgREST[3]. [1]: https://github.com/hasura/graphql-engine/ https://github.com/hasura/graphql-engine/ [2]: https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/ [3]: https://postgrest.org https://postgrest.org

smallnamespace 6y ago
Benjie, one of the PostGraphile creators, has a good talk [1] advocating for database-driven development. OTOH, having dug into PostGraphile a bit, I personally wouldn't advocate for pushing so much backend logic into the DB. Like, if you decide to do auth and TOTP in the DB, you'll end up implementing it in PL/pgSQL. Writing core security logic in a less-familiar language feels like it adds risk. Also, for smaller projects your backend is often just a single server, so moving auth in into the DB doesn't save you from managing distributed state. [1] https://www.youtube.com/watch?v=XDOrhTXd4pE https://www.youtube.com/watch?v=XDOrhTXd4pE
- pyramation 6y ago
  exactly! this is the ecosystem that I'm a part of that inspired me to build this :) I do agree about making sure you have experience writing PL/pgSQL and would also add that you should make sure you have a good test-driven environment when writing code in the db.
- e12e 6y ago
  > Like, if you decide to do auth and TOTP in the DB, you'll end up implementing it in PL/pgSQL. Not really? Postgres (as well as other rdbms) support many other languages - I don't see many good reasons to insist on pl/pgSQL for uses like these? Both python, perl and TCL are part of the standard distribution in addition to pl/pgSQL. https://www.postgresql.org/docs/13/xplang.html https://www.postgresql.org/docs/13/xplang.html https://www.postgresql.org/docs/13/external-pl.html https://www.postgresql.org/docs/13/external-pl.html
  - smallnamespace 6y ago
    Right, except if my backend is in JS, there's a cognitive cost to adding another language to the stack. I could use plv8, but there's a more subtle preference here for JS to wrap SQL, rather than the reverse (plus tooling is less convenient, e.g. if you're writing Typescript). My rule of thumb is: SQL great, PL not so much.
    - e12e 6y ago
      > except if my backend is in JS, there's a cognitive cost to adding another language to the stack. Agreed. However, I don't see why not use one of the other mature "real" extension languages other than pl/pgSQL. As for typescript, maybe there's (distant) hope: https://github.com/supabase/postgres-deno https://github.com/supabase/postgres-deno
pyramation 6y ago
exactly! https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/ is the system I'm using on and wanted to avoid writing a resolver in JS

rbut 6y ago

Incrementing the counter, and generating the next HOTP code in a single UPDATE statement could be useful.

pyramation 6y ago

Hi! It was a bit of both fun and work ;) I use graphile and didn't want to have to implement a resolver in JavaScript, keeping logic and integrity of auth systems in the database.

e12e 6y ago
On the other hand, one could also write js in postgres: https://www.postgresql.org/docs/13/external-pl.html https://www.postgresql.org/docs/13/external-pl.html https://github.com/plv8/plv8 https://github.com/plv8/plv8
- pyramation 6y ago
  yea totally! I love plv8... it's actually how I got started with postgres functions. I ultimately switched to native PG. There were known memory leaks in plv8 and eventually after learning pl/pgsql it became more natural and clean, plus AWS at the time didn't support plv8 which was another incentive not to use language extensions