3 reasons you should use Postgres Functions and Transactions

Transactions allow related statements to be grouped together - ensuring every statement succeeds or they all get rolled back. In this video, Jon Meyers shows how to use PostgreSQL Functions to run multiple statements in a Transaction.
Learn Postgres playlist: • Learn Postgres
By wrapping the logic to keep the database consistent in a Postgres Function you can improve the security of your application, as the database is responsible for ensuring the data is correct. Additionally, this can improve the performance of your entire system as this Postgres Function only requires a single RPC network request to be invoked, rather than a web application sending each step of the transaction as a separate request.
📹 Full Next.js Email Client playlist: • Build an Email Client ...
🧑‍💻 GitHub repo: github.com/dijonmusters/nextj...
00:00 Why you should use Postgres Functions and Transactions
00:36 What is a Transaction?
01:54 Overview of Email Client project
06:36 Runs multiple statements in a Transaction with Postgres Functions
11:02 Invoke Postgres Function from JavaScript using supabase-js and RPC
13:12 Using Cascade Deletes to remove related data from other tables
16:40 Implement an authorization system in PostgeSQL with Row Level Security policies
💻 Videos to watch next:
▶ Simplify complex SQL queries with Views in Postgres: • Simplify complex SQL q...
▶ Fastest way to build a SaaS in 2024 - Next.js, Supabase and Stripe: • Fastest way to build a...
▶ Fastest way to get started with Next.js and Supabase in 2024: • FASTEST way to get sta...
👇 Learn more about Supabase 👇
🕸 Website: supabase.com/
🏁 Get started: app.supabase.com/
📄 Docs: supabase.com/docs
🔔 Subscribe for more tutorials and feature updates from Supabase: / @supabase
📱 Connect with Us:
🐙 Github: github.com/supabase
💬 Discord: discord.supabase.com/
🐦 Twitter: / supabase
📸 Instagram (follow for memes): / supabasecom
ABOUT SUPABASE:
Supabase is the open source Firebase alternative. Supabase provides a full Postgres database for every project with pgvector, backups, realtime, and more. Add and manage email and password, passwordless, OAuth, and mobile logins to your project through a suite of identity providers and APIs.
Build in a weekend, scale to millions.
#Supabase #AppDevelopment #RealtimeApps #DeveloperTools

[@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.

[@damiankociszewski]

True

[@jpsytaccount]

So true!

[@Yusuf-ok5rk]

yeah just give us folders and it is 10x 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

[@supirman]

Great video as always. Thank you, sir.

[@BJRNextCode]

He is awesome 💥

[@fastneasy]

I like that he wobbles like a jelly non stop

[@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...

[@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.

[@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.

[@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?

[@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

[@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'.

[@luisandrade1291]

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

[@drancerd]

Postgre es mi sistema operativo favorito. ❤❤❤

[@edumorangobolcombr]

Yes.SQL commit

[@xgtwb6473]

So no second video on the auth?

[@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.

[@ScriKidding-eg6vn]

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

[@codewithguillaume]

🔥

[@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...

[@ibrahimkouma6751]

supabase is the future believe or not