3 reasons you should use Postgres Functions and Transactions

I'm going to show you how to use postgress functions to run a series of SQL statements in a transaction even better we're going to use RPC to call this function from our next JS application meaning we don't need to bake a bunch of logic about our database and how to keep the data consistent into our app we can just call the function to do the thing and move on now you may have seen some recent drama about why database functions are a terrible idea you may have also seen a bunch of responses about why it's a great idea and unrealistic to do it any other way for performance reasons so let's look at why it's awesome and then you can make the call for whether it's right for what you're building but firstly what is a transaction a transaction is a way to group together a collection of related statements that all need to succeed in order for our data to still make sense the classic example is bank accounts where you want to transfer money from one account to another this is multiple steps as we need to subtract money from one account and add money to the other account if we take money out of this account and then something unexpected happens to our database we now have inconsistent data and we've actually lost someone's money data loss or corruption is is basically the worst possible thing that can happen to the most valuable asset of your company it's data so don't do that that being said if we add money to this account first and then something happens to the database we've actually created extra money and everyone gets Rich wait forget I said that don't steal my startup idea but all jokes aside both of these steps need to happen in order to keep the data consistent so we can wrap them in a transaction which will ensure that each step runs successfully otherwise it will automatically roll back or undo any of those changes that have been made so jumping back into our email client example this is based on an awesome project from Lee Robertson at first cell in the last video we refactored the data fetching logic to use views and a hosted superbase instance I'll leave links to all of that in the description but in this video we're going to focus solely on the mutations or the actions that the user can perform so there's no need to watch those other ones first but they're there if you want to go deeper so this is our email client we have a list of folders the emails that are in that folder and then if we click on one of these we have the contents of the email we have some actions that we can perform up here and in this video we're just going to focus on these two so the ability to send an email and the ability to delete an email now looking at the code this has been nicely organized into this single file called actions. TS which has a function for sending an email and another one for deleting an email so to send an email we're just doing some validation to make sure the user has sent the right stuff then we're connecting to the database so we get back a database client we're then sending a query to begin which signifies the start of our transaction and then we have a few steps that need to succeed in order to keep all of our data consistent so firstly back up here we are selecting the ID from the users table where the email matches the email that's been sent to this function so we know the user's email but we want to look up their ID now if the user doesn't exist which is what this check is doing then we want to insert a new user using that email and get back that user's ID so then our recipient ID variable will will either be the user that already existed or the new user that we've created so now that we have the recipient's ID we can send an email by inserting a new email into the emails table and sending across the values for our sender ID so that one's hardcoded above because this example doesn't Implement authentication or it doesn't yet but stay tuned for the next video where we'll implement this using superbase orth so along with that hardcoded value for our sender we're also passing across the recipient ID the subject of our email the body of our email and a Tim stamp for right now so that's the time that this email was sent and then we're returning the new email's ID so that we can use it in the next statement so now that the email has been sent we need to add it to the sent folder so we know that the folder is called sent but because this lives in its own table called folders We need to look up the ID so we can associate our new email with that specific folder so then we insert a new row into the email folders table with the value for our new email and the ID for the sent folder we then send this query to commit which means everything succeeded so let's apply those changes otherwise if something went wrong we fall into this catch block and we want to roll back any of those changes and finally we want to release or disconnect from the database and then because we may have mutated data we want to revalidate or go and fetch fresh data for everything on the root layout and then redirect to our new email that was a lot but really there are two main steps here insert a row into the emails table and insert a row into the email folders table everything else is just collecting up the right data to be able to do that and ensuring it runs in a transaction rolling back any changes if there were problems along the way and we can see what this looks like from a user's perspective so if they want to send a new email to Tyler superb.com with the subject great job exclamation mark and the body that new Android content is absolute fire emoji and we can click Send to send this email and you can see it's taking a little while cuz there's a lot of steps that are running in that transaction and we can see that new email has been added to our scent folder and we can see the contents of it there so it's successfully been added to our database so what's the problem here well the next JS application is the thing that's responsible for ensuring our data remains in sync so once we insert a new email we need to remember to also insert it into the email folders table and this needs to run in a transaction to ensure our data remains consistent but there's nothing stopping a new Dev or even the same Dev after a couple of days of not working on this project from writing a new function called send faster email that inserts a new email without a transaction or any of the other steps we need to keep the data across our tables in sync this maybe isn't the end of the world for an email client application it would probably just be considered a bug that we could fix down the line but with the bank example from earlier this could be a really expensive problem anything this critical to the consistency of your data should probably be enforced at the database level making it much harder to make these kinds of mistakes additionally this looks like it's making several requests to the database we wait for this trans transaction to begin then we select some data we insert some data we insert some more data we select some more data we insert some more data and then finally commit these changes I'm not sure if bundling this up as a single query is handled behind the scenes but given we are awaiting each of these steps to complete it looks like we're making seven requests from our application to our database just to ensure that a couple of values across a couple of tables are kept in sync let's move this logic from our application to the database meaning we only need to send a single request across that Network boundary and we can put the responsibility for consistent data on the database itself we can do this with a postest function so from the superbase dashboard let's head over to the SQL editor and we can ask superbase AI create an empty postgress function called sendor email that returns an INT and this looks good so we'll accept that change and then we're creating or replacing a function called sendor email we can specify some parameters that this takes as we need them it returns an INT because we need that email ID to redirect our user we're then declaring that function as and then we have the body of our function or the steps we actually want to run which sit between these dollar signs which conveniently runs as a transaction and automatically handles rolling back if anything goes wrong and lastly we specify the language which is plpgsql which is just SQL with some convenient programming things like if statements and the ability to declare variables both of which we're going to need to send this email so we just need to make this begin and end lowercase so my code isn't screaming at me and then replace this return zero statement with the statements that we want to run within our transaction so I'm going to use the magic of editing to paste all of those in here just like that and then some more editing magic to make it stop yelling at us in all caps ah much nicer so we're selecting the ID for the user if they don't exist we're inserting a new user then we're inserting or sending out email then getting the ID for the scent folder and then inserting that email into that folder but we've still got all of these javascrip variables so let's refactor those to work in our postgress function so this one is the recipients email which can be passed into this function as a parameter so recipient email which is of type text and then let's just add some spaces around this equal sign and now we're getting back our users ID but we need a place to store it to be able to use it in this statement to insert our new email so let's add a declare section above begin and so this is where we can declare our variables to remember values between statements so we want to remember remember our recipient undor ID which is of type int and then we can select this ID into our recipient uncore ID variable and so now we need to handle the case where the recipient doesn't exist and so we want to create a new user for them so we can say if recipient ID is null then we want to insert this new user and we just need to add an end for our if statement and change this to be our recipient _ email that got passed in as a parameter and again this insert statement is returning an ID so we just need to put that into our recipient ID variable so next we want to insert our new email our sender ID is going to be hardcoded as one but we'll fix this up in a future video when we Implement authentication and authorization and I just realized I put that in the wrong place anyway so let's undo this is the name of our column sender ID the value is this one so then for the value of our recipient ID this will be our recipient underscore ID variable for our subject and body we'll need two more parameters that are being passed into our function so subject which is of type text and body which is of type text and then we can get rid of this stuff and this stuff and this stuff and this stuff and calling the Now function will give us back a timestamp for our sent date again we need to store this returning ID somewhere so let's declare a new variable for email ID which is also going to be an INT and then we can return this ID into our email ID which is also the value we want to return at the end of our function for this next one we're selecting the ID for the folder with the name sent so we need somewhere to store this ID so we'll declare a new variable for folder ID which is also of type int and then we want to select ID into folder ID from folders where the name equals sent and we can even add our spaces around this equal sign to make it look real nice and then lastly we want to insert into the email folder table with the values for our email ID and then our folder ID and again we're returning the ID for that new email that's been created so we can redirect the user to that new email and I've just noticed one small typo that we still have paed body so let's get rid of that one and then make this panel a little bit bigger and click run to create our new postgress function and we see success no rows returned so everything is all good and we're done we can forget all about what it takes to keep our data in sync and instead just call this postgress function from our application which really just cares about sending the email so we can get rid of this entire TR catch block we'll keep our revalidation and redirection logic and get rid of this extra bracket we can also get rid of all of this but we'll keep our validation logic now we just need to create a new superbase client by calling the create client function which comes in from do/ utils superbase which we created in the previous video but it's just wrapping a call to the create client function from superbase JS so we don't need to copy and paste these environment variables everywhere we want to create a client so now that we have our superbase client we can get some data by awaiting a call to superbase and we can use RPC or remote procedure call to call our sendor email function which lives in postgress and this function takes in a collection of parameters so we had our recipient email our subject and our body but these three parameters conveniently map to our past object which gets a subject an email and a Budd and valid validates that they're all the right shape so we just need to change the key for email to be recipient email and then the same for the key of our past object and then we can just send that whole object along for the ride to our send email function so this will give us back some data which will be our new email ID and also an error object if anything went wrong so if we have an error then we want to log it out to the console to help ourselves with debugging but if everything's all good then we want want to revalidate our data and redirect to that new email so let's check this is still working by sending a new email and let's send this to a user who doesn't exist yet so we'll go Thor superb.com the subject can be postgress functions are so cool and in the body we can say they are efficient and so secure and click Send we get redirected to that new email and the email has been added to the list of emails in our sent folder meaning each of our steps in that transaction have succeeded awesome our application code is now significantly more simple the database is now responsible for keeping things consistent and we're only making a single request from our application to our database to handle all of that logic let's refactor our delete function so we can see we're creating a new client by connecting to the database we're then beginning a new transaction we're selecting the ID from the folders table where the name is this folder name which we can see is a formatted version of this parameter that's being passed into our function so we're selecting the ID for that folder name we're then deleting the row from the email folders table where the email ID matches the email ID that was passed into our function and where the folder uncore ID matches the folder ID from our select statement and then we're deleting that same email from the emails table so basically this logic is reversed from sending an email so if we delete an email we also want to remove it from the email folders table now since these tables have a relationship we can actually use a Cascade delete to do this automatically so anytime email is deleted we can Cascade those deletes to the email folders table and remove any rows that have that email's ID so back over in the superbas dashboard we can create a new query and this one is going to alter the table email folders We want to drop our existing constraint for email uncore folders uncore _ idore F key so that was a lot but a constraint is just a set of rules that applies to a relationship across tables so we want to drop or delete the existing set of rules we then want to add a new set of rules or a new constraint which we want to have the same name so let's copy that from here and paste we want to specify a foreign key relationship for the email ID column which references the emails ID column and then on delete we want to Cascade so when a particular ID is deleted from the emails table we want to Cascade those deletes to the email folders table and we can click run and we see success no rows returned and now back over in our application we can again remove this entire try catch and finally block so let's go all the way up to try and remove we can get rid of this extra bracket we want to keep our revalidation and our redirection logic and then rather than creating this client by connecting to our database we want to create a superbase client by calling the create client function and we no longer need to turn our folder name into the title case version so we can get rid of this and make a little bit of space and now we can wait a call to superbase and from the emails table we can just delete the email that matches the ID field and our email ID that gets passed in as a parameter to this function now we don't really care about the data that comes back from this but we do want to know if there's an error and then if we do get an error so if error then we want to be kind to our future selves and console log it out and then we revalidate the path and redirect our user back to that folder so as we can see this state is much simpler where we don't need to think about any of that logic to keep things consistent we just delete the email and the database can handle whatever that means to keep things consistent so we can check our application is still working by choosing one of our emails and clicking delete we can see we no longer see the contents of that email and it's been deleted from our list confirming that delete statement has been cascaded to that sent folder and again we've managed to improve efficiency and security while simplifying our application code we've reduced the number of trips that need to happen between our next xjs application and the database and moved the responsibility for keeping data consistent and insync to the database itself which should be responsible for that kind of thing if you want to take this concept even further and learn how you can Implement an entire authorization system in the database itself I recommend you check out this video right here we use Road level security to lock down access to the database and write policies to enable specific users to be able to access specific data but until next time keep building cool stuff

Channel: Supabase

Views: 7,055

Keywords: supabase tutorial, best firebase alternative, open source database

Id: xUeuy19a-Uw

Length: 16min 57sec (1017 seconds)

Published: Fri Mar 29 2024