3 reasons you should use Postgres Functions and Transactions

[@mtmdrew]

Great video Jon. I just wish there was a better way to organize these functions. Seeing a flat list of functions in the dashboard is what deters me from using them more.

[@PERQ1S]

True

[@jpsytaccount]

So true!

[@Yusuf-ok5rk]

yeah just give us folders and it is 10x better

[@parkerrex]

this

[@JohnBailey]

THIS! Yes please organize better

[@eleah2665]

Jon is back! He says Dahtah funny! But he sure knows his transactions.

[@janglad9136]

While there are obvious performance benefits (and even security ones, especially when accessing your DB from multiple sources) I think it's undeniable the DX for this is much, much worse vs handling this on your server. Both in writing, but also in debugging and maintaining. Wish there could be some progress made on this front.

[@joepetrillo6185]

I do agree the DX is much easier when doing this on the application side (more familiar and more code-like, as opposed to SQL). What are all the options for handling this? We have 1 - application side flow (doing repeated sql calls to db and handling responses) 2 - postgres function (this video) do ORMS like prisma just do it like option 1?

[@edhahaz]

introducing postgres react procedures... with redux

[@JackBrunsion]

Bleh i almost throw up when i need to manage my transaction logic from supabase dashboard instead of codebase :c

[@charlyyshell]

Using Prisma for those cases is a good solution, you can create nested writes or use the $transaction api to write your array of transactions. They have an example in /docs/orm/prisma-client/queries/transactions for a similar example of a money transaction where they withdraw and deposit in the same transaction and rollback if it encounters an error.

[@luisandrade1291]

Calling a db functions inside other db functions makes a single transaction?

[@drancerd]

Eso es usar el paradigma "procedural" o "funcional", en una base de datos. Yo lo hago todo el tiempo, literalmente manejo todo desde la base de datos, en el frontend solo hay; labels, layouts, textboxs y botones (pocos, porque no confío en los usuarios/humanos). El resto... Desde la vase de datos ❤ (Toma en cuenta que Postgres es una de las bases de datos más pulidas que existen) Entonces... Cuando le haces ing. Inversa a mi App/ programa, solo te encuentras con un cascarón vacío. Y de ahí a romper/hackear el Api y el server... Es un trabajo que bo cualquiera podría llegar a lograr (Sobre todo, si manejaste bien las Funciones y sus argumentos, RLS, Gatillos, Roles, Permisos, etc.) Aunque no lo creas... Con Appinventor + Supabase; e creado "MVP's" más, eficientes, eficaces y seguros que el producto final de un equipo de "programadores", con 160k de usuarios conectados simultáneamente, con más de 40k de transacciones por segundo (con datos ultra confidenciales). [Sin usar autenticación, JWTs, Oauth, etc]. Solo porque se manejarme con la base de datos.

[@abhilashpatel6852]

No

[@shanehoban]

Great video. Is there any way to manage these functions via your git repo and have them update on push or merge to master? Now that would make me use them much more and maybe some day will be possible if not already

[@wembleyleach]

It depends on your project setup, but at the end of the day these functions are written in plain SQL in files that live in your project repo, possibly next to all your other SQL scripts. You can use any migration tool you’re familiar with to add them to Supabase. Just make sure to drop the previous version of any function you’re updating before applying the new ones.

[@supirman]

yes, you should be using supabase migrations framework which allows you to easily keep local/staging/prod up to date with your functions and schema changes.

[@hipdev_]

We need a way to sync local Postgres functions with Supabase using the Supabase CLI, it's much better to have them in code locally.

[@supirman]

that's what migrations are for...

[@wdavidow]

Struggling to find the documentation around the `client.query` API the way transactions are shown in early part of this video. Is that API documented on the Supabase docs site?

[@luisandrade1291]

Is it possible to declare a transaction outside of a db function?

[@drancerd]

Postgre es mi sistema operativo favorito. ❤❤❤

[@edumorango]

Yes.SQL commit

[@francbele]

Can you use, and does it make sense, to use Postgres functions for Auth table?

[@CV-wo9hj]

Thanks Jon. How does putting it inside a function make it into an atomic transaction? What happens if that first insert fails how does it know to roll back?

[@michaeldausmann6066]

This is a great question. I'm not convinced that just putting statements in a function "conveniently" wraps them in a transaction. My understanding was that the function runs within the transactional context of whatever is executing the function. Perhaps supabase RPC begins and commits the transaction? It's important because if the intention of the function is to encapsulate a transaction perhaps it's better to do it explicitly in the function body.

[@supirman]

@@michaeldausmann6066from the postgres docs: PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block. so triggering the function executes it within a transaction. It's easy enough to try it out to verify.

[@joshuaowusu-ansah5142]

@supabase @jon

[@flowstatehandle]

@@joshuaowusu-ansah5142 would love to hear some more about this feature too, I can't find anything about it in the docs

[@theashishmaurya]

How do we revert if one transaction fails?

[@supirman]

Great video as always. Thank you, sir.

[@xgtwb6473]

So no second video on the auth?

[@christophernolan8761]

A video show how to do table joins in supabase postgres functions would be really appreciated

[@ScriKidding-eg6vn]

the problem is its so sick! to write another language than calling a transaction api like what firebase is doing

[@cristianpassos1176]

Hello, how are you? I created a function and I'm calling it using RPC: let { data, error } = await supabase.rpc('get_products_for_establishment', { establishment_id: 1, }); if (error) console.error(error); else console.log(data); But I'm encountering this error: Argument of type 'string' is not assignable to parameter of type 'never'.

[@santiagovelezsaffon]

Postgres has also a really cool way to tell if the previous query returned null. If not found then

[@Shmarti]

This is way too involved. I just want to call a native transaction function from my swift client like Firebase. I don't have time for the rest...

[@siyoonjung2879]

I don't want to perform business logic in the database function, as it is not good for maintainability.

[@hueseyinguendogan8541]

Doing a "rollback" in an "EdgeFunction" would be awesome

[@AntonSarg-e5n]

I find it way to complicated to create functions and it is such a bad experience. Would be nice to have solution inside the application code. Since those functions also hold valuable information about the logic and what the app actually does.

[@nafg613]

A bigger issue with the app-side transaction management approach than coding mistakes IMO is there's no guarantee your code completes. Worst case the VM loses power unexpectedly. Although the recent crop of durable execution platforms such as Golem Cloud could mitigate that.

[@caseyspaulding]

So good! Thanks!

[@cnikolov]

One thing Id like to see from ms sql coming to supabase is temporal tables 🎉❤

[@codewithguillaume]

🔥

[@BJRNextCode]

He is awesome 💥

[@jenialherdianto292]

Slow down just a little bit. Your contents really-really good..

[@drancerd]

Postgre es mi sistema operativo favorito 😂 ¿Sabías que puedes jugar Doom en Postgre? Y Supabase es palabra mayor ❤ porque solo apareció, de la nada! Y todo aquel que buscaba oro en encontró Adamantium y Vibranium en un solo lugar.

[@flanderstruck3751]

¿Qué clase de comentario retardado es este?

[@amiraliazimi1464]

that lower casing ocd

[@jerryphm]

tysm

[@fastneasy]

I like that he wobbles like a jelly non stop

[@ibrahimkouma6751]

supabase is the future believe or not