Holiday Hackdays: Building an advent calendar - Day 4

[Music] all right oh the suspense i love the layers like it played multiple times over the top of itself it was great that's beautiful like every time we should add another layer so it's just like yeah alrighty happy friday well happy friday to you if you are in the future part of the world here we have friday and we're soon i'm actually gonna go sailing in a little bit i'm hoping there will be some wind so that should be fun uh what do you have any weekend plans john i think you muted yet i am hearing some crazy echo hang on let me just quickly check if i am also streaming it there we go all right all right i'll stop being insane now um i was hearing i had it open in a tab and it was playing and it played over the top of each other and then you were like saying multiple things over the top of yourself and i was like hang on this this is not right uh so yeah hey how's it going do i have weekend plans um i i was going to try and build another project for the hackathon over the weekend but we'll see how far i can get um i i don't know well can you talk about it was it the sound port idea yeah yeah maybe i'll just build it after after the hackathon anyway maybe maybe that could be my actual like holiday project well while i'm taking some time off work what better way to to take time off from super bass and recharge then build a project with super bass it's a great idea indeed um but yes so i was thinking about building a um a sampler uh kind of kind of thing so you've got a collection of um samples that you can trigger like a soundboard um and i've partially built a project in the past um that was like i never it never ended up um actually getting finished but um i had this cool like keyboard layout so it was every key on the keyboard um on like a qwerty keyboard and you could just drag and drop samples onto any key and then immediately play it on that key yeah and so you could like just like what i actually did use it for a little bit was just like um tinkering around with like hip-hop samples and stuff when i wanted to produce a beat i just like bring in a kick drum and a snare and a hi-hat and then just like tap out on my keyboard a little a little drum pattern um and it used lots of like the web api web audio api stuff uh because if they were just audio elements there's too much lag like when you press the button it doesn't play immediately and so yeah solved lots of cool problems about efficiency and making it really responsive and so now i feel like i could um build the the back end part of that um using super bass so i can actually store those samples in uh in a super base database and then uh i was thinking about building like a preset collection of um of sound bites of me saying silly things a uh a pre-built collection of you saying silly things and so you can toggle between between the two of us and then have a custom one for like whoever is actually using the app and signed in and they to record drag and drop their own samples yeah um which would be very cool oh yeah they could record their voice and then it it uploads to storage that sounds yeah sounds pretty cool all right so many great ideas uh if you're watching and you have great ideas or you're building something for the hackathon let us know in the chat um so we are building something for the hackathon or well kind of themed for the hackathon which is the holiday hectares um if you want to read all about it if you haven't heard it about it yet you can read our blog post here um i've posted it in the comments and yeah uh get hacking uh we're still running this until i think sunday night uh us time um so that's gonna be fun and we are building an advent calendar uh and maybe john you can uh should we pull up your screen and have a look at where we where we are right now we absolutely should um all right that's actually cool so we can go through that in a second um so to give you some context all the way from the start um we are building a similar interface to this it's definitely not going to look as nice as this give it that this is the last day of our of our streams but uh functionality wise we wanted to to try and build something similar where we have a collection of um of super base resources um that we could release on different days and so egghead at the moment are running a holiday course release extravaganza where they are releasing a new course every single day um and you can see that that's going to go up to um i don't think it's synced up with each day like i think it started just before the 1st of december but they will be releasing 20 courses over over the course of 20 days which is very exciting um and i highly recommend you check out this one right here it's made by a really smart person about a really smart product so i would uh jump in there and have a look uh but yeah basically we want to build something similar to this um only yeah using super bass behind the scenes although they might be using super bass i don't know i suspect they're using rails though because i think the rest of the app is fun um oh ah gotta need to run out that server again server uh this is our almost exactly as beautiful version of this same interface and it is the 10th of december we've got our new door open yeah yeah there it is that's just appeared i think so no let's have a look because in our back end which is uh hosted on superbase we have a collection of doors so all yeah all four doors are now open and all four doors are open here so the functionality is is working that's very cool and and so yeah we have a collection of um of resources that you can go and check out about superbase um we'll i think i'll continue building this out a little bit um and make it a proper uh useful um collection of a whole bunch of content i was thinking of uh maybe next week putting together like basically just finding um a whole heap of useful resources uh on superbase and just populating a whole um the whole of december with um different bits of cool courses that you can um or cool videos that you can check out um but yeah i don't think we'll make it all the way to that by the end of this video but at the end of this stream but we'll see um so yeah clicking on it any of these courses takes you to that actual resource so this one's on egghead this one's on youtube um and so yeah that's where we're at at the moment one thing i did want to shout out is in the first stream if you've been with us the whole time uh thor and i implemented github uh authentication which was was was really simple um but we ran into a problem trying to um set up the callback from twitter to to set up twitter auth as well um and so i wanted to shout out this um avnisha's uh guide that he's written very recently um actually just in the last couple of days maybe he watched the stream and was like oh they need my help i need to get in there and fix this um i could that is the case he's put together this awesome guide um of implementing twitter auth with superbass and nexjs so that would be a good one to check out if you want to do that in your own application we have our super base database we've got our doors our authors we've been stepping through row level policies and yeah we've been using superbase ui which is an awesome ui library built by some people at superbase and some people from the community um which gives us a whole bunch of really nicely styled things that we can use out of the box so if i go to our admin page ah i'm not signed in and so it said you can't go to the admin page you need to sign in and so if we sign in with github and go back to our admin page you'll see a collection of nicely styled form components which have all come in from that superbase ui library as well as the auth itself auth was super easy to implement because we could essentially just bring in this pre-built component and then we could just pass it a collection of things that we wanted to actually be able to authenticate with where are we where am i trying to look a list of providers somewhere uh somewhere up in the maybe up in the uh in the provider but yeah we were able to just pass a list of providers that we'd like to be able to sign in with and it basically just took care of the rest of it for us so very cool uh very cool ui library that has been built for super bass um we've been working our way through this checklist of things um and so in the first few streams we've created a super bass project an xjs project we've played with some row level security and written some policies um to ensure that only courses um basically because we want these courses to incrementally release across the whole month we wrote a policy that basically um only lets uh you see on uh this page and it lets you see all of the courses that have actually been released or the doors that have been opened and so we did all of that with um row level security policies and it was surprisingly simple um we then did like a little bit of styling not a huge amount of styling we added auth we did some more auth we did some more auth and we built out an admin page um that you can only view as a signed in user which we might be extending a little bit today um and then yesterday uh in the the most recent video or stream we went through implementing file storage and so actually um storing the we'll find it there it is uh these images storing these images in a superbase storage bucket and then being able to pull them back out and display them on this page which was yeah very fun so today i've kind of modified these a little bit before the stream started just so we didn't have to step through um you know pretending we're brainstorming what we will do again uh in this one we're going to try and implement our role-based authorization and so with that admin page currently anyone who's signed in can see the admin page but really we would only want that admin page to be um accessible by uh people who have an admin role in their user um and so yeah we'll look at implementing that um creating an individual page for each store so uh because we were thinking it would be good to because the whole theme of our hackathon is building real-time applications we realized we're not actually using any real time yet um and so we'd like to implement likes and comments um and so in order to do that um because it doesn't really make sense for us to have likes and comments on this page like it'd be a bit weird having like a stream of comments coming out of the card um we've realized we probably need to build a separate page for each one of these doors so when you click on one of the doors it will go to that door's summary page and yeah maybe we can do something with like if you click the actual image itself it takes you directly to the resource like this uh but maybe if you click anywhere else in the card uh it'll take you to the kind of more details page as good yeah and then as i said we're going to implement some real time so we're going to um add the ability to like and comment on each of those uh doors and then finally we have to make sure we actually get to here because it would uh it would be a shame not to actually deploy it but um yeah hopefully we can see how easy it is to deploy this application to herself how are you feeling about that thor that sounds great pretty packed day it is a package maybe we should jump into it all right so the first thing we want to do is implement roles um and so if we have a look at our superbase database and we make a quick query and for we can say select start from auth.users and so this is a special table that exists on the auth schema um and it's basically a table that's maintained for us by super base and so we don't want to modify anything in this um in this table ourselves this this isn't where we want to like add extra columns or um yeah basically we just want to leave this table as it is because it might change at any point um in different versions of super bass we may modify the structure of this table we might rename it if we were really mean but yeah ideally we don't want to modify this table if we would like to keep a track of our own extra data about the user for our application we probably want to put that in our own table so the way we can do that is create a new table um called profile and so this is going to be just extra information about our users or their profile for our application um and i guess the the thing that i'm thinking of adding into this is a role because we have a role in that orthodox users table but that kind of just determines whether our user is signed in or signed out if we want to implement our own role that exists within our application so whether someone is a user or an admin um it's probably a good idea just to put that in our own separate table so this the description for this one because i know you love your descriptions is a let's say app extra app specific user data so we can have a role here and then because because we want um a profile basically to have a one-to-one mapping with our current orth.users table um anytime we create a new user we would want to have an associated profile and so our profile table um can actually reference our orth.users table even though it's in a different schema we can set up a foreign key relationship between those two tables so that we can't have a profile without having a user that it's associated with so we can click this little link icon here next to our id and we can set up a foreign key relationship to our auth.users table and we can set that to the id and now is there anything else you think we'd want to keep a track of for our users at the moment yeah i think for now we can try to copy over the the email if it's available so for example with some of the uh oauth providers we get the email address and we can automatically copy that over maybe that's a good idea um but yeah i think otherwise yeah let's start out with that and um yeah and then set up our our trigger yes triggers now that is a good idea though what is a trigger yeah so um a trigger is i mean if you're coming from the firebase world you're probably quite familiar with that where you can uh basically listen to changes uh and then do certain actions and now postgres you can do um pretty much the same thing so you can describe a trigger that is practically just a function that executes when something happens um and so for us we actually want to execute a certain function to copy over our user data into our public users table anytime our superbase auth signs up a new user and that user could be signed up with the email login it could be the oauth login so no matter how they sign up basically when we're adding a new record into when we're adding a new row into our um auth schema which is used by super base auth so a new user has signed up then we want to copy that information over to our public users table awesome cool so it sounds like we want a function first to to set up the the logic that we actually want to run and then we can add a trigger to call that function to say basically anytime an insert happens or we can attach it to an update event or a delete event anytime this happens in the database call this function and so our function can be create profile for user and this one is going to be on our public schema the return type is going to be trigger so this will if you're setting up a a trigger that calls a function that function has to return a trigger otherwise they don't they don't line up properly so we want to return a trigger and then in our definition we can add a begin and an end which is required because we're using pl pgsql which is a procedural language that comes with postgres and then that allows us to basically have multiple statements within this this block of of sql or plpg sql um so basically it's a superset of of sql you can just write select statements and things in here um but for us what we want to do is we want to insert into the profile table and that's the public should we should we just say public.proof did you say profiles or profile ah did i do it again i bet i did all right we've got to fix that can't have i'm so used to doing single singular sorry no thank you thank you so we'll add profiles rather than profile just so it's consistent with our authors and outdoors and then if we come back over to our functions create a new function uh did i save this damn all right create profile for user it's going to be on our public schema return type will be trigger in here we have our begin and our end and we want to say insert into uh profile and then the values that we actually public.profiles god you're so good look here you go i mean that just made you change it so it's only reasonable um so we want to insert into this table and then we can um insert all of all of the the columns um but since the id is generated for us and the created ad is generated for us the only columns that we actually want to update are our role and our email and so then we need to tell it what the values are that we want to insert into those columns and so basically we need to pass two strings i'm not sure if it's double quote strings or single quote strings but uh aren't we passing the so basically new is our record that is the new record so we're passing barrels right that's right we are so what does u represent in this case yes so new is the record that was inserted um in this case into our uh auth table so basically it is the uh yeah yeah so it's the the new the whole road the whole road that was uh just added so that's new and then we can access whatever is in there um so i think the first one is our was it id right we need to pass the uh id of the user good call we do need the id so [Music] and then we pass the roll which is new dot roll and then we pass the email which i think is new. email i'm sure we'll get a very helpful error if not question we'll see i'm actually not entirely sure what the schema is we probably could look should we maybe we save this and then we have a look at the uh public off uh sorry at the auth schema table that's a good idea i'm just gonna add the last line here so it doesn't give us an error which is return new so anytime you set up something that returns a trigger you have to return new from the end um yeah that's just something you gotta do so let's confirm that and then let's just quickly look at our auth.users table again i just realized i didn't put my resolution down is this okay or is it a bit small um i think you can probably zoom in a little bit but all right let's do it just get a flash there we go yeah and that looks right everything's all good yep all right uh cool so we have sorry if you've been struggling to see up until now squinting at the screen we have an id uh we have our aud role email so what we were pulling out from here was our id which is going to be this value our role which is going to be authenticated uh so we don't actually we don't really need the role i think yeah no um good point i don't email you that's great yeah so one way we might actually want to do this because i'm i'm imagining that we have two roles initially for our um our role our user our profile role and so i'm thinking that we have user and then we have admin so we could actually come to our profiles table and we could set a default value for our role to be user yeah i guess just lowercase and then uh when we actually want to update the value of our um of our profile if we want to make someone an admin um then we can do that by changing that role so yeah so if we come back to our database functions and then we edit our function here so we don't actually care about the role that is in our orth.users table because that's always going to be authenticated um and we don't actually need to set that when we insert a new value into our public.profile because the default value is going to be user and so every single time a new user logs into our application a new row will be created for them in the auth.users table that's going to in a second trigger a call to this function and then this function will go and create a new public profile with that id and that email so we just need to set up our actual trigger so that's the function that's going to be called and then our trigger is actually like which event do we want to listen to and call that so we can uh call this create profile trigger something we want this to be on the auth dot users table and so this is the table we want to listen to events on so anytime there's an insert event on the orth.users table after the event occurs for every row that's affected by that statement we want to call the function createprofile for user and so now we can test this as working we go back over to our table editor um and go to our uh actually we don't need to do anything there we need to go to authentication and then users and delete our user that's currently logged in and actually delete all of these users because they were just um test users that we had in the uh so we had the foreign key relationship oh what's up we have some objects in our storage that uh that are associated to this user and so that's what i was quickly going through at the end of the last stream um which may have been a little bit too much information to just kind of jam into the end of that that stream but we have this storage.objects table which has a collection of information about each of those files that we've put in our bucket and you'll see that each of those is associated to a user and so this user here is actually the id of our our orth.users user this one this is the id here and so we can't delete this user because they have items in the storage.objects so uh we can just delete the items let's just do that we can re-upload them again um once we have our application working correctly we could set them to a different user and then set them back but yeah we may as well step through um creating them again uh right were we using these for the courses so we don't seem to have the other courses in there anyway uh yeah no i think the other courses we actually just used um the image url you remember in the beginning we were just putting the image url from the cdn off like youtube or um twitter yeah cool uh awesome so now uh if we i believe if we do a select on storage.objects they should be also removed when we remove the files yeah cool so now we're able to come over to our users and delete our currently logged in user and then we can also delete our other two users oh nice hijabi coder is here again happy birthday morning four days in a row that's amazing here to hype you to the end excellent love it so now if we come back to our application what we're gonna say uh and then forbes school says they're looking for tutorials with flutter and real time oh okay anything in the advent calendar for that that's a good question um we definitely have some flutter content but i have to confirm i'm sure there is something that uses real time yeah yeah send me a dm or um publicly yeah if you question us indeed and we will definitely remind you live with something later today maybe if we're in the q a are sql functions and triggers available in the super base studio yeah i think that's what we just used right oh as in in the open source um like as in if you're running the studio locally i think so yeah is that is that what you mean um yeah i think so i believe they are available um because i mean we're running just the open source version of the studio so yeah yeah yeah yeah yeah yeah let's say yeah and if they're not you want to hassle johnny on twitter and uh and ask him why it doesn't and he will give you a lengthy explanation as to why why it doesn't right now but why it will soon but i'm pretty sure that it is already yeah supported because yeah like we're dogs thing yeah makes sense all right can you hear some kind of plane or something outside never mind we're fine uh cool so if i now try to go to this admin page uh it's not letting me go to it it's redirecting me to the login page and so that's because we're no longer logged in as our as our user from earlier so i'm going to have to log in again because we've we've deleted that user so they no longer exist and you'll see that here i'm getting an error oh i'm getting a server error here that's interesting database error is saving new user so if we come okay so maybe our function because the trigger needs to run successfully before the insert into the auth schema can happen so maybe uh we mess up the trigger i think that um i think we need to execute this trigger with a higher level of authority i think we're getting we're failing a security check um which means yeah we're gonna have to create this again so once once you've created a trigger you can't come back and modify it um but we can delete it and we can just recreate it and i can i can show you the bit that i think we forgot to do or that i forgot to do i won't believe you thought is it the language security definer or something like that yes exactly so create profile for trigger both.users insert after event row row and then the function we want to trigger ah all right it exists on the function itself not the not the trigger we're gonna have to wind back the clock we're gonna have to start all the way back from here let's have a look we can't uh edit that part of our function so we can delete our function we're going to create a new function and we're going to write it properly this time we're going to be on the public schema it's going to be a trigger we're going to have a begin and oh almost an end and we are going to insert into public dot profiles and we're going to grab the id and the email and we're going to give it the values new id new dot email return new and then the little bit that we forgot if you scroll down there are these advanced settings um and so if we drop that down we can define yeah so this this security invoker means that um whatever tries to call this whatever whatever role the the bit of code that tries to call this is um it will basically um uh it will execute this function as that user um whereas we want to execute that function as as the level of security that this is defining it i know that's a very confusing thing to say but basically this is going to give us um a higher level of authority than the service invoker yeah i think the description is pretty good so it's executed with the privileges of the user that created it and when we're in the dashboard we are a super user on our database so um yes about my semicolon gotcha awesome lovely so now we'll set up our trigger again what languages are available for functions so these are uh database functions specifically and i think there's a couple different uh languages available i think in in the dashboard is it only epg sql or um that's a good point i think in our dashboard you can only um yeah in our dashboard there are only a few languages uh plpg sql and sql um but by default postgres supports c uh javascript oh you might need to turn on pl v8 thing but uh v8 engine yeah but c java javascript linked link docs yeah yeah yeah yeah uh all right let's create our trigger um but yes if you still wanted to do that with your super base database that's why we give you this this beautiful unrestricted or mostly unrestricted sql editor and so in here you can um you can actually create or replace functions and in there you can declare any any of those supported languages that you want to um to declare your function as and if you want to learn more about that uh in real time i can say go and check out ah where's my seo uh yeah go and check out this create postgres functions with super bass video from our super bass channel i go through a lengthy explanation as to how you can define your own postgres functions using the sql editor instead of the ui but let's use this beautiful ui to create our trigger what triggers create a new trigger there we go and we want that to be on ortho.users anytime we insert into there after row and create profile for user okay so that has created correctly created successfully um let's quickly delete our user which doesn't exist because we didn't successfully sign in and so it's rolled back all of those changes so now if i try to go to the admin interface redirects me to login i sign up with github and then that looks like we've successfully signed in and now if we go to admin we see that page and this image is broken because that is the only one that was actually using our file storage and so we can go through and fix that in a little bit but the cool thing is if we refresh here we see our new user that's just signed in and so this is coming from our auth dot users table uh this is our new our new user but then this user id this e1d blah blah blah now exists in our profiles table as well so there is our id and we have the role of user which is that default value for any time we create a new role and we have our email of our user so everything looks like it's wired up correctly so that's cool and then did we actually want to basically sync back the role into the app metadata is that what we're doing or are we just querying the role in our server-side props yeah so we could do either we could um now that we have this separate table um in our admin page in our admin page ingot server side props we're basically just pulling the user out of our cookie and then checking if we if we don't have a user then we want to redirect the user to the login page and so right now this doesn't do a check to see whether that user is um an admin or not just that they are signed in and so uh we probably want to add an additional check here to see that that user is definitely has that admin role and what thor is just alluding to is that that data is stored in a different table so that's in our public.profile table and the user that comes out of this cookie comes from our auth.users table and so if we console log out our user and we refresh our admin page oh it's on the server side right you need to check here good call gets me every time cool so we have a user and they have basically all of the information from our auth.users table but there's also this user underscore metadata section and so this is a list of additional stuff that we got from github essentially and so yeah we have a little bit of extra information about what our preferred username is on github and um yeah a whole bunch of of cool stuff and so it would be really nice if because we're now keeping extra information about our user in this profile table basically anytime we modify that data it would be really cool if we could come and insert it into or update it in our user underscore metadata for our auth.users table as well and then we wouldn't need to do a second request here because otherwise um we would need to make another request to superbase and say hey superbase what's the profile for this user and then we can check whether the profile has that role but if we were to set up this kind of um cyclical relationship between these two tables anytime a new user gets added to the orthodox users table it goes and creates a record in the um in the user table you know sorry in the public dot profiles table and then anytime we update a value in that public.profiles table it goes and writes to our to our user metadata within our dot users table to say this is the new role for that user for example i think it should actually write to the app metadata right because i think the user metadata can actually be modified by the signing user um whereas the app metadata can only be modified by um a service role key um and so anything basically the user metadata it's fine for the user to update that if you know if they are authenticated it's kind of there it's a good point though the email verified flag probably should not be in the user method user can just update yeah i'm verified of course i am yeah give me that interesting okay but i think this is coming from well i don't um yeah i should i should check on that i should check that's a good one but yes so we want to update the app.metadata uh to reflect that value and so we could have like an app role or something um well yeah we'll call it app role just to distinguish it from our role here so that if we're writing some logic we don't actually mess that up uh cool at metadata so it sounds like we're going to need another function and another trigger because it sounds like we want to basically we've set up a trigger on this table this auth.users table to say hey when you when you insert a new value go over and write that value to this table we now need to set up the reverse of that where we say hey this table anytime you update a value go and tell this table about it so we're going to need another function create new function and this can be update app metadata this will be on our public schema uh the return type again is going to be trigger and then we have our begin and our end and we can say update uh oh man it's been a while since i've written an update statement i'm assuming update auth dot users uh and then i'm assuming we tell it which column to update and so a uh app oh i wonder how you do this at all because because i believe the app.metadata um is a jsonv column and so we probably need it is raw raw app and it's actually called i think raw app metadata ah okay let's have a look at trusty stack overflow ah yes of course set so we want to say update the table that we want and then set and so we have a chase on b set okay because we don't want to update the other bits of data do we like we wouldn't want to overwrite what's in there we just want to um basically upset the one value in there oh yeah so this would be how he would do it right so update our auth.users and then we want to set this is the funky syntax for for jason b and so we're saying basically this is the name of the column so in our case uh our column is did you say it's i think it's raw underscore user uh sorry sorry raw underscore app runners underscore meta underscore data whoa if i am not mistaken um yeah well let's just keep going so we want to access a particular column within there which is going to be our app role we're going to have to do lowercase and then we want to set that to be equal to whatever value we want and that should be equal to new new dot roll right yes it absolutely should yes so this is actually an interesting point to to talk about new in the insert we only had a new because we're creating a new um a new value a new uh row but with update we actually have an old value and a new value so we can actually access we don't want to in this case but just just for future reference you can access the old values if you wanted to use this function to to do some last minute validation or something like that you can actually access the old value or if you wanted to set up audit logs or something like that every time someone updates a value in your user's database or your user's table you want to write a row to an audit log to make sure that you can audit everything that's happened in your database that might be a good use case for where you want to access the old value and the new value so you could say this user updated the value from this to this anyway at the moment we just care about our new dot roll and then the most important thing to add on to any update or any delete command is a where class because if you leave this postgres will be like yep we're happy super basically like yes everything but it will update every single row in the database and so you always need to add a where um to say we only want to update this where the id is equal to our new id i think that should be it right and then we just need to return our new id equals new id yes that should be it yeah looks looks good cool so just need to clarify that is uh the name we're looking for and again we want this to happen as the security definer not the security invoker so us as the user creating it rather than the user that is calling or invoking this function oh yeah alice is saying do we need to use uh do you see this do you need to use instead of i think the double arrow you do you only use the double arrow to access the field or do you might actually be right because i'm getting an error right now yeah good call okay so we need the double error thank you the extra extra long arrow uh and then we're setting up a trigger and did we verify the name on the auth schema thank you alister do you not believe us yeah good thing he doesn't it's good it is oh there's also another question i'm loving this today is friday and people are here right i can't see any questions i can't see you i can't see you or any questions i've only got one screen so please relay them yeah yeah that's fair so uh the question is is it possible to integrate firebase off with superbase um and yes i mean it kind of depends what you're trying to do i mean you can use super bass solely as your database uh i think that you know the big power of super b uh super bass comes with the integration of super bass off with your postgres roll level security features uh and so i think that gets a bit more complicated if you're using kind of an outside auth provider but um actually mr john here has written a great um guide on how to integrate uh off xero as an example as an external auth provider i've i've replied with it in the chat um and so you can you can go there and you can have a look so i linked our guides right that's the same one the guides oh yeah uh yeah they're slightly different so this one is an article that kind of like it kind of goes through some of the justification as well and some of the um it goes into a little bit more depth whereas our integration guide is really great if you just want to like get in there and do it so it's more like a list of steps it's the same list of steps like they go through a similar process um but if you're interested to read a little bit deeper into what's actually going on behind the scenes um i recommend checking out this the oauth zero blog um where we step through that and i can check this one you'll see a big mirror of all of yourselves that i can chuck that one in too nice all right cool enough self-promotion that's all this is it's just an opportunity for for us to gloat about all of the things that we've done all of the things that you've done i need to start doing things you've done you you have been the one that's done the actual things behind what we're doing you're the you're the reason that we can sign in so easily and some some of it significant amount of work across the libraries themselves i'm just the one that you know needs to have everyone praise me for it and do it publicly um okay so we have uh you were right it is the raw underscore app underscore meta underscore data and that's nice and a nice little json blob here um and so we would be expecting that if we go and update that value now a new key should be added to this json blob which should be app underscore role and that should have our user or whatever we change it to so that's actually a good a good point i wonder if update triggers for inserts as well um or whether we need to explore that one no that's fine because it's just jsonp update so we're just updating the json b i think it's different if you're updating if you if you're adding a new column that would be different but i think this should be should be fine should we just give it a go see what happens yeah let's give it a go see if we take down us east again yeah sorry about that everyone super bases getting a little bit too powerful so if we could um just kidding uh so if we come over to our profiles this should now have a trigger on it where if we update this value so if we were to update it to admin and then once that's saved if we come over to our auth.users table again and come across to our raw app metadata we do not have no anything in there so something's gone wrong um i might just try running that same uh sequel in the uh sql editor and see if we um if we got this bit right so if i grab update come over here add a new snippet and instead of new we can make this run oh wait your arrow needs to be not not double hyphen but rather double uh crocodile what's the name for it double crocodile i love it alice alice's right here saying wrong arrow again yes alison that's it so sorry we uh uh not good enough here we go all right so we want to come back to our uh our function and in our function that updates our metadata we want to it's interesting that um it actually let us submit it because it picked up that it was the wrong syntax before oh no it doesn't like this i've actually had some issues with doing something similar in the past raw app metadata that's the column and we want to set oh oh sorry it needs to be in single quotes right aperol but that's what you had wasn't it yeah uh who's new role said maybe we need to oh okay yeah that's that's a good point the double hyphen was commenting out the rest which is why you didn't get a belt excellent i'm glad that we have some people who are actually familiar actually no postgres oh that's excellent um okay well why don't we try uh oh wait no that's inside all right any other ideas well should we try the the json b set but okay what's what's the error again that we're getting so in the in the when you try to save the function in the dashboard we are getting this syntax error so failed to create function syntax error near or at or near that double thing oh where's it going there we go no wrong one this one that that's the wrong one go back raw app metadata and it didn't work with the single this is where we just randomly change characters until it stops complaining at us okay so we got the double no no no not double hyphen you're commenting out things again here we go yeah okay uh i wasn't paying attention i yeah so jsonp set is probably what we what we want okay underscore set so we want to set the whole column to json b's set and then the column name and then is it key value i guess i feel like there were changes to postgres 14 which would be running as well um returns target with the section designated by path replaced by new value new value added if created ah so we might be able to do like this um so let's try that kind of syntax so we can go and update this and then we want to update this column uh what was it that column that one that thing that thing no sorry no no no it's in set you're currently an update you need to get back to set ah yes it is oh god undo where are we all right so we want to update orthodox users we want to set uh is that what it's telling us to do so column name i guess the key within that column name is equal to this value looks right uh let's give that a try does that work yeah the question is if so json b has a has a parameter that said create missing but i mean maybe the syntax works let's let's give it a go yeah it'll work it'll totally work if we make this crocodile so we never forget again and that's now saved uh if we come over to auth.users again we run this one oh alice thinks he might know you he's asking have you ever been to melbourne js or react melbourne i feel like i might have seen you at one yes i have been to both of those lovely yeah little celebrity here [Laughter] i'll have to uh yeah hit me up on twitter we'll work it out this is not working you need to help us out yeah you should come back to melbourne so it didn't work it didn't work right it didn't work no okay so so this is our raw app metadata which has our provider and our providers but it's not updating with our new value and looks like that's yeah i think we probably update actually have to do json b sets okay so so if we go back to our function and so here do we say set and then jason b uh yeah so yeah so chase and json b underscore set uh oh it's after the equals something like that or jason b set and then we give it is it the column again or is that um so like we i guess it's we don't want to overwrite the existing data we just want to keep the existing data and then add this should be set raw so the raw app metadata equals json be set so because the json b set function is just um a function that yeah so uh alistair has it in the chat if you uh yeah that's roughly that seems roughly correct and then oops do you see that oh you can't actually copy it right [Laughter] okay jason uh our role to be our new role is that right yeah and then we need another parameter which is just true uh because okay one two three so the fourth should be your boolean value which means create missing is true okay um i think so let's give it a go yeah all right i like that we're all in this now together so yeah it's not just us this doesn't work we're sharing the responsibility across us and the chat so json b is very powerful but like yeah you just have to actually know postgres to use it no we know postgres we're great uh now if we do our trusty select [Laughter] okay what happens if we just run the query yeah it's a good call we want that and we'll need to know what the id for l well i guess we've only got one user so we could just run it for all of them we could get rid of our where clause like i said you should never ever ever ever ever do but that's fine right now so update earth.users set uh raw metadata equal to json b underscore set raw app metadata and then we want to set the role to be um thanks about alligator something like that invalid input syntax type for jason [Music] what if we get rid of these brackets once again just randomly trying to remove bits of it until it works malformed array literal roll and so it sounds like maybe we did need that what if we do double quotes um alligator does not exist no no so column okay wait i think we're still doing we're still doing something wrong with uh gotta be a json value so it needs to be double quotes yeah that's what he's saying uh around the value part or around around this part okay wait so yeah i know so it needs to be single quotes around and then inside it needs to be a json value so double quotes and so single quotes first then double quotes around the alligator i think single quotes and then double quotes with a single quote inside it no other single quotes outside because it's and then inside the json value is the double quotes like that success jesus christ all right uh let's come back to our functions and this is why usually super bass protects us from from having to write this kind of stuff but uh since we are trying but but did the update actually work did you oh yes did it do things correctly it said success thought it said success success could mean it destroyed everything that's also success uh let's have a look no it didn't ah okay so what happened well our role is in there no yeah at the very start oh right i was looking at the end okay cool maybe it was there the whole time no no no no it absolutely wasn't okay all right oh fabulous gee now we just need to work out how we modify this and so uh the role was correct or have i just dumped this here so uh roll was correct at metadata was all correct so the only thing we should need to do is put quotes around this and i think it may break but we can do it i have done something similar to this in the past all right it thinks it's all good so now if we come to our profiles and we update our role to be working because then it has to work if you're just working as the as as the test text it has to work found a new function now that's called two json also like you you could use that uh [Music] so i think he says that we need to wrap the new role into a function called 2.json because mu dot roll is not actual json but it worked didn't it nah it didn't work it didn't work all right now so to json like this new dot roll yeah two underscore json new dot roll all right someone should should write a sequel book wait but the the query that we reached solely that worked why did that work because we were doing it we had all the quotes in the right place we messed up the quotes with the uh when we talked about because it was a very trigger yeah yeah okay now it is returns the value as json or json b or maybe it needs to be two underscore json b no it should be fine right like because it's just a variable surely it's just a string and if this works then um alistair has passed the technical challenge and he should come work at superbase yeah that and he should get a beer at the next melbourne definitely ljs for sure it didn't work sorry alistair we've got to let you go all right all right did it need to be to jason b two jason actually i think i have i mean you could try to json b but it should just because all right to chase on do we do we know if our trigger is actually running correctly no no we don't oh maybe i put it on insert that would be funny um let me just quickly check something is one thing that we could use which i've used in another project i think the trigger might not be running at all can we can we double check the trigger it's definitely set on insert wait this is wait we don't have a trigger profile oh wait where's our trigger seriously update the user with metadata all right alistair you've got your job back good job uh all right name of trigger so we want to uh let's have a thing what are we actually putting this on we are putting the truth on profiles that means our very first oh no we got a syntax error for that right okay yeah never mind right yeah so that that wouldn't have worked no uh update at metadata uh that should be all good this was a fun functions excursion invalid trigger name oh maybe yeah all right update uh app metadata alyssa says he's now emotionally invested that's great that's how we hook you yeah well andrew is a contributor and if you could just finish off the rest of the project that would be excellent uh so now we should be able to update this value yeah because we're confident we can actually use it to move forward oh unknown it's coming in as all right functions unknown so so the second the second parameter is unknown yeah so it doesn't like this part um what if we go back to this new dot roll now and then we go okay so the path the path is wrong somehow oh wait it didn't actually all right it didn't update because it didn't pass yeah because the the right okay so jason be set unknown unknown wait why is this the path unknown hmm so allison now says we need to cast it to text that makes it so new new role double colon text yep but it was the path that was giving us a hard time no okay maybe i didn't read the error message correctly no i think it was failing because one of the values was missing so if we now go admin i think you're right nope it's still unknown that's weird oh no it is the you're right it is it is the second right so it is the path the path is unknown this is like a freaking riddle it is and i love that it takes so many clicks and so much messing around to change each value as well okay returns target designated by path so the path needs to be nice all right but it's probably just an empty string so you you kind of need to because if you look at json be set the path needs to be an array of text what okay can we get uh sometimes the the postgres stocks are pretty we're gonna spend the whole stream on this chase on vsat definitely yes uh i am going to try to do this one one more time so if we go to um not that one this one if we take this go back to uh set raw app metadata for our role to be equal to and then it was new dot roll and cast it to text where the id is equal to new dot id and then if we use our double crocodile oops maybe we need to cast our raw app metadata as chase on b but it is json b isn't it that's a bit confusing because so okay have a look at uh if i post this in the chat right then you won't be able to see it yeah you kind of get it okay so have a look at this if you click you can't you can't get to the link can you no okay wait i can block the user wait i can chat private chat okay if you go to the private chat try that right chat and then if you go to the second one yeah second second how to yeah and then scroll so you see how like the first one is cast s json b oh up here yeah because everything else if you scroll you need to scroll the example uh no scroll sideways sorry i get you all right we got that right uh yeah right but we still need these quotes around it so i think we need to know what what was the erroring was our our second the path so i think it could be because i mean let's just try this exact same thing right yeah cool let's do it so we want to set so i think the only difference is the casting of json b equals json b set and we want raw app meta data casting to json b and then we want our role and we want this to be set to um to underscore json b to a new dot roll as text and then we want to close our set put it put in the two underscore json b the new role text right take that no the function the function you need to wrap it in the function the new dot roll wrap it into the two underscore json b function yeah but still cast it as text the roll yeah okay we love postgres confirm oh we got the syntax right okay let's check the comments maybe alice is shouting at us again we have a trigger so we should be able to now uh so the trigger is on our profiles table can you see him in zoom in the interface okay i think maybe the text is too small is that better i guess they're about 15 seconds behind this but um hello that looks like it's saved so we haven't had an error there if we come over here and we check out oh is that right should it have wiped out all of these other things yeah i think they were now no they were always null perfect there we go roll equal hello and that's how easy it is see how easy super bass makes it well i mean you know the power of postgres but okay so i think we learned a couple of things cast all the things and use all the functions always and then we're good yeah cool so uh okay alice says he can now sleep at night that's good yeah excellent excellent we're all all in this together okay what are you doing real time okay now we need well we get need to get the role in the get server side props but that should be easy yeah that should be very very easy because now um uh we may need to log back in but we can just try and refresh and see if that's the case but oh no it should wait no it wouldn't have updated all right refresh i'll just stop talking to myself and we can actually do it on the screen that's why that was open i get it uh app.use.org it actually is that that's awesome okay so it's updated that um so if we come to our database and we oh we can leave the role as hello for now so we can we can do some test driven development and prove it's not working first and then fix it um so here we can say we get the user out and then we want to say if not user um we could do these as separate roles and yeah they do do another if underneath and then just send them to uh index because they are locked in right yeah so it doesn't really make sense to send them to the login page yeah so we just say if the user dot uh app metadata dot roll is not admin or if it does yeah sorry if it does not get cool uh admin then we want yeah and then you redirect them to just the landing page um and we definitely want to use some optional chaining here because and you need to remove the first uh exclamation mark yeah good cop if user metadata dot roll does not equal admin uh send them back to the home page so now if we save and we come back here and refresh um we should be redirected to the landing page if everything's working which it looks like it is and again if we try and go back to slash admin it takes us back to the landing page so we can't see that page unless we have the role admin so if we come over to our superbase database go to our profiles table and change our role to the admin and then refresh if we go to the admin page or refresh and go to the i think the cookie is no it should be okay if we log out which we haven't implemented if we just go to our login page and we log in so yeah so you need to use it to log out and log back in or we can handle that and update it automatically but okay well maybe something's not working we need to check our role did it still not update i'm gonna go crazy [Laughter] hey man we've got 40 minutes that we can keep working through just getting the roll work and then we didn't do any real time okay the roll is add and it's under raw app metadata so what's it actually called in here right so if we console it's at metadata oh wow okay yeah so the underscore is only in the raw no you have meta underscore data but if we okay that's a bit consistency we're learning we're learning so much here cool and now we can go to the admin interface i'm actually curious uh just quickly while we are spending so much time on this uh i want to see if we whether you actually need to log out or not because i suspect if we change this to not admin it shouldn't need a logout and if we refresh the get user by cookie actually does an api request to get awesome the user yeah so it is from a security perspective it's great from a aws build perspective it's not so great but yeah awesome and so now uh without needing to sign out and sign back in we can now navigate to the admin interface so that is very cool so we've actually implemented roles properly was there something else that we wanted to do oh yeah just on the uh just the real time well on the actual no in the um in the because right now if we actually try and use the form it will fail our security policy because if we have a look at our policy for our uh doors we're checking that the role oh wait no they are still authenticated okay yeah because that's a different role oh do we want to step through the pain of trying to work out what it is to pull it back out of the json b column yeah i don't think we actually can because i think we only have the uid the role and the email so our query would actually need to look at i mean it basically we would need to write a query that looks at the role in our public users um oh yeah that's easy which we which we can do right so yeah so we probably don't want anyone who's authenticated to be able to do this so what we can do is we can say select star from our profiles table where id is equal to auth dot uid uh is that right that should be right so that will so our current so we're looking at the current row for profiles um sorry for doors so this will be our uh yeah so this will be our currently logged in user we want to go over to the profiles table and we want to look at the grab the road that's associated with that id and then we want to check whether is authenticated is true i mean sorry not it's authenticated what am i rambling about we want to check uh what their role is so we want to get the role from profiles uh where the id is so that will give us the role of the currently logged in user so then we want to check whether that equals can we do this [Music] did this recently in the video but i'm trying to remember what the what the okay so exists yeah alice ellis maybe we should just get alistar on the stream exist select one from profiles where id equals auth.uid and roll equal to admin that's the one exactly so we're checking whether it exists instead of selecting an actual column we can just say one which is going to give us back true uh or false or it'll only give us back true if it finds the value that we're looking for and so we can say where id equals that and uh the role is equal to admin that should be it thank you alistair you're all right let me let me find the careers page [Laughter] all right and we can see that now uh that's not what we were checking we're checking if we can actually uh create something so let's say we want to create something for the 12th day or maybe we'll say the eighth day so we can actually see it and we can upload an image of my face beautiful face uh and the content url we can do some more shameless promotion to my website and click submit and there we can see a beautiful beautiful face which means we need to go and fix this to have was it object fit tailwind come on tailwind don't even have to spell it right that's how good their uh seo is we want object fit object to cover and all right we're just going to ignore it uh and that's because i'm on the wrong page i'm on the admin page so i need to take this and i need to put it on our this page here i'm not losing my mind i know how to css it's all good all right so uh what else do we want to do we're severely running out of time so we're going to have to go very quickly yeah so we want to do an individual page for each door and we want to implement uh real time with likes and comments so we have half an hour that's heaps of time heaps of time all right so first thing we want to do you go there's also a question should i should i should we put it yeah the question what what happens with the cookie if the user leaves the website and returns the next day will they have to lock in beginning because the cookie's updated client side um yeah so basically the way yeah the way this works no no currently the cookie exploration is set to the same as the uh the uh jwt expiration so um if it expires the cookie is basically gone and then what happens on the client side basically if if the user goes back to your page on the client side we will use the refresh token to get a new jwt at which point we fire a new sign-in event and then that sign and event sets the cookie again on your server side uh it's not yeah it's not the best flow at the moment kind of how that is set up so we're um we're kind of looking at a kind of more redirect flows where actually you don't have to restore the session client side um yeah so we're working we're working on that but it should it should still work so basically we're on the client side refreshing the jwt and then setting a new a new cookie nice uh so uh i'll explain what i'm doing in a second all right so we're just creating a page um that can display more details for each of our doors um and so because uh our index page can be built statically by exporting out get static props um because we uh this this data is completely public and available and um it's not changing very frequently so we can we can do that request once when we build a new version of the site and then we can use revalidate um every hour to ensure that that's fresh at least within the last hour yeah and now we're going to create a more details page for each of our doors so we can click one of the doors and go to extra details about it because we want to statically build each of these uh we need to tell nextjs basically a finite collection of options for those paths because it can't go and build a static page for every single possible id that exists without knowing a list of those ids so basically we need to get a list of ids uh for each of our doors and then create a static page for each one of those ids so the first thing we need to do is bring in our superbase client from probably somewhere like this and then in here we can say away superbase client dot from al what are we trying to do here we're trying to get all of our doors and we just want to select the id and no we want to select it all but where the id is we want to filter right sorry this is the get static paths i was thinking about so here we want to get a list of all of our doors so each of our i guess our door ids comes from our doors table we select all of the ids and then we want to return something with paths set to actually i can do it like this cast paths equals door ids no because you want to pre-pre-built all of them is that yeah that's right so this is this is giving xjs a finite number of paths basically every single url for one of those static pages that it needs to generate a static page for so it will first call this function to find out how many static pages do i actually need to create like what are the ids for all the static pages i need to create or the paths for all of the the static pages i need to create and then it will call get static props for each one of those individual pages so that's where we'll grab the door data for each one of those individually so i'm just going to quickly look at the documentation to see yeah i think generally what i do is like because there could be new pages being added i think i i statically built nothing and then have a fallback basically that builds on the fly yeah yeah that's that's right yeah okay yeah so we have a list of pars which is an array of objects with the params set to whatever our parameter is um so here we can say params is set to id sorry id which should grab our id from here set the params to that we can then set that as what we're returning from there and then we can set fallback to be uh blocking so the reason that we need blocking um is as though was just saying if the page doesn't actually exist yet so because we're using incremental static regeneration here where we're saying um basically we want to rebuild this uh on the next visitor after every hour so if someone comes to the page at 59 minutes since the last time it was built nothing will happen and then if someone comes over an hour since it was last built it's going to go and rebuild this page for us it's going to run that get static props function again and rebuild a new static asset and so um that doesn't make sense on the get static path side because um that path might not have ever been built yet and so if we say fall back false we'll see a 404 page because that page doesn't exist and so next gs will just give us a 404 but if we say blocking it will go and attempt to build that page and then if it can't build it then you'll get a 404. cool so that looks good and then in our get static props we're going to get params and an iad from there which we can then use to say we want to go and get our door from superbase client dot from doors we want to select we're going to start off with star and we'll yeah we can slim that down a little bit if we need to we want to get that where the id is equal to our id that's being passed in here and i don't think we need to cast that to a number but we'll see soon and then we only want one row back so we can chain on dot single and that's just going to give us one row so that's why we just have a door here which we can then return as our props and then d structure in our actual component and we can use that excellent trick for mentioned in a previous video where we do a pre with a json.stringify and we stringify our door and give it the second parameter of null and then the parameter null that's our two and then that's going to pretty print it out on the page for us so if i have all of this right then we should be able to rather than uh navigating directly to our door uh content url uh we're gonna have to change all of this but yeah let's get rid of target blank and no referral because we're staying on our site we're now navigating within our site so we want to use a link tag which we can bring in from next.js link and then within that link we put our a tag oops and uh the link is what we put our href on and also what we put out key but strangely enough what not what we put our class name on so we leave our class name on our a tag but our link we put our key and our href which will now be going to slash whatever our door dot id is and so that is going to navigate to this page um that number that we're navigating to so that id if that was id1 for example then here this id would be equal to id1 we're then selecting the door where that where the id column is equal to id1 and then we're sending that into our component which is going to pretty print those values out on the screen so let's see if we got it right let's go this one damn okay so it was not provided as a string in get static paths so up here this id is a number and we need to annoyingly cast it to a string and there we go we have our specific data for that door and so now we can lay it out a little bit more nicely so we can have a h1 which can be our door dot title uh whoa maybe a paragraph which can be our door dot description oh and we still need to load in the author um from the forum table right yeah good call just uh my fingers know i'm working against the clock they're not letting me tight uh cool so we have outdoor and then as thor is just saying we have um our author id but we actually want that to be our authors um so and yeah we should probably get rid of the the stuff that we don't need here so we don't actually care about the id oh we do need the id uh no we don't need the id okay so we don't care about the id we just care about the title we care about the description we care about um from the authors table we care about the author's name and i think it was twitter url twitter twitter handle almost uh and we probably could just copy that select right it's a great idea let's do it um yeah and we'll just say we don't care about the id okay and now if we have a look at our door we have authors and so we've got all of the all of the little bits of information that we need to display [Music] do we want to go much further maybe we should implement likes and comments instead of going through laying this out yeah should we just do should we just do like the the hacker news style upvote upvote remove upload or something like that sure uh yeah yeah maybe we would just upload something no let's make it light so we can just keep keep spamming like get everyone to to like it as many times as they can yeah let's do that let's do that so we've created a um doors add likes and they can be is it not possible to do select start yes so yes it's definitely possible to do select star it just is yeah you know if you don't need the data why send it over the wire so the more explicit you are about the data you want um the better in general but yeah you could do also select star yeah so sorry i kind of um brushed over that um because we were talking about it in a previous stream and i'm sure everyone was paying attention to every single second of every other stream that we've watched if they're watching this one um and yeah so we uh yeah if if we specify star um it's going to pull everything back from the um from the doors table and then everything back from the authors table um and if we're if we have extra data there that we don't actually care about we're not using on the page then there's no need to to send that across from um from superbase to our application and so if we specify each column that we actually need um then yeah it's more efficient uh so we're gonna have a button that says like excellent oh we can probably like you know get facebook on a little heart heart emoji oh yeah hot emoji it's a good idea green green heart for super bass green heart you're just full of excellent excellent ideas green heart envy envious okay so we want to be able to click this and then next to it we want to have like however many likes um actually that shouldn't be in the button although yeah the button is pretty small otherwise right yeah good call so we want to make um so maybe just put the put the span in the button as well uh yeah okay all right and then you can click that and uh yeah this value should be updating every single time someone likes um our beautiful uh door content so we're going to need this data to exist in the database um so there are a couple of ways that we could structure it um we could have a let's just if we just do the door no yeah so we're just going to be incrementing a value um if we're going to keep like comments or something like that we want to keep a track of like which user is is um you know adding that comment and at what time but since this is just going to be a value that's constantly incrementing we can just add it as a column on the doors table um so this is going to be an integer let's make it the biggest integer we can which i think is we don't have a bigger i was going to say we should be okay we've got an eight-bit integer which i think will be should be enough uh the default value can be zero and um well i mean yeah yeah no yeah should not be knowledge yeah anything with a default value i guess would never be oh no i guess you could update it all right cool so we have zero likes for all of our uh doors and so now if we want to display that in our application we can pull in um yeah i wish we used star then we'd already have likes oh no and then here we want to say our likes sorry daughter likes and if we refresh we see zero likes um and then if we were to come here and i guess we can just we can make it update so uh if we go to our application when we actually click this button we want to add a like and that is going to be a new function and we're going to say oh wait actually we don't even need to really await we can just fire it off so we can say superbase dot sorry superbase client dot from our doors table uh we want to update uh likes and we go and check the documentation which we did have open at some point what happened uh i forgot how to do a um update statement update so we want to update so it is an object where we give it the key and then the value much nicer than postgres so we want likes to be oh we have special things for this don't we oh the increment yeah are they modifiers or filters or [Music] contains range range or elapsed text blah blah blah oh we could use a a start procedure we could use a stored procedure okay that's what uh silentworks is recommending okay we can do that all right so uh stored procedures are really cool and something that uh it is really good to cover so i'm glad that we are going to cover them basically a stored procedure is just a way for us to trigger a function but from the front end so here we've got our superbase functions or sorry postgres functions that we that we were using earlier to create a profile and update the app metadata which i'm sure you'll remember if you were here for the two hours of our stream that we're trying to do that uh so we are going to create an increment likes function it's going to be on our public schema the return type will be void i guess or should it be record if we want to return the thing that comes back that can be void uh we don't care about any arguments because we're just going to be firing this off and saying update it um so we can say i guess we want to select uh the actually we will need to pass in an argument how will we do this because we want to know um it's just the yeah which is to oh right like i mean i guess yeah yeah should we do that that sounds great so yeah uh we could actually just change this to a regular sql statement and use just sql which means we don't need our begin and end so we're just updating our doors and we're setting likes to be likes plus one where uh the id is equal to so that's what we'll need to bring in um all right field name plus x okay right okay so we need to take in two arguments so we'll need to probably bring in the id and we'll need to bring in um well i guess the increment value is always going to be one so we just want to we just want it to be one so we just need to um bring in the row id now we just got id yeah so here we want door id make it more explicit and then we have a question maybe while you do that i cannot say can you write raw sql from the front end uh instead of using next shapes like syntax are there any security sequences so no uh you should not be writing raw sql from the front end uh yes there is security concerns with that so the way superbase works actually is it exposes um a so-called postgrest api which is basically it's it's it auto generates a rest api for your postgres database uh and so you can use raw postgrest uh if you wanted to so basically the superbase clients are just postgresqd implementations in the different languages that then just do the rest api calls um yeah that's pretty much it yeah yeah so we can yeah like write some safe sql that we're happy to execute um and then yeah uh call it with rtc exactly so that's the thing where we're kind of where the client doesn't have sort of the you know if you have some crazy sql business logic that you want to put in the database then you can write it as a function and then you can just execute the remote procedure call so super base client rpc uh over the rest api and so that's how you generally would do kind of more complex sql statements yeah yeah so we've created our function um in superbase which just increments our likes um which is just going through it's taking in a door id and so which door do we actually want to increment the likes for we're then updating in the doors table uh the likes column we're setting likes to whatever they are plus one so we're just incrementing them by one every time uh where the id is equal to that door id that we were passed as a parameter and so now we have this function this increment lags function and so then we can use our superbase client to use rpc to call that function and so we're calling increment likes and we're giving it the door id and so now if i save this and click the like button a couple of times uh you'll see that we're getting a whole bunch of errors in the console and so uh i suspect we have violated some [Music] what does it say could not find public increment likes function did i make it a public increment likes public increment likes which takes a door id and wait to base client obviously increment likes and then we're passing it a door id that's the superbase client rpc all right let's get a little bit more information all right could not find public increment likes function or the public increment live function with a single unnamed json jsonparameter in the schema cache and [Music] try reloading the schema cache it's a weird one i haven't seen that before [Music] increment likes on public door id update doors like that and then superface client rpc increment likes let's just try creating it again yeah maybe do you want to just create a via sequel uh i don't know what do you mean if the dash yeah if the dashboard has any uh let's try one more oh sorry i think return type is just void yeah yeah and this was going to be id which is an int integer and that's what we want to do update doors set likes maybe public dot doors just in case be more explicit so it likes to be likes plus one yeah so it's interesting because uh silent works has some language sql volatile i don't know oh yeah so it is sql volatile uh okay so increment likes still getting that same error increment likes what did it say with a single unnamed single unnamed json or json b parameter so is the parameter no we uh oh wait how are you calling the rpc again superfaceclient.pc and then yeah one second uh okay increment likes it needs to be a json object right so rpc increment likes door id door dot id i think would be a number not that javascript really cares but type of door.id undefined uh okay so are we actually passing across the correct stuff so if we have a look at the network um if you look at the url it should be there and then where's the door id i'm not sure there is outdoor id maybe it's just expecting a single integer argument no i think with parameters you need to pass oh yeah maybe just try passing in the door id is that does it need to be a json because it was saying something about json right so if you just pass the door id there so i guess that will be doors which one are we actually looking at we're looking at an excellent course by an excellent instructor on egghead about how to build a sas product using nextgs superbase and stripe which is index too uh what we doing send that in as the parameter no but i think just instead of a json object because it says something that it's not expecting a json object right uh no that seemed to work passing it in as the number which is weird because our door oh the door is a string right coming from next chase is that it we're not using a star so we're not getting the id because we told him we don't care about the idea anymore we shot ourselves in our own foot wait but are we are we not getting the id from oh from the page yeah silly us okay this was working the whole time it was a silly by us so uh yeah so we can click this many many many many times and we don't see any feedback on the page um but if i was to refresh we'll see that there are actually 15 likes now because i spammed that button a whole bunch and so each time this updates uh in the database it would be great if we could notify the front end so that the front end could then update that value on the fly and thankfully superbase gives us exactly that functionality so we can use something called real time so the first thing we need to do is enable uh real time on our particular table that we want to be able to receive update events from so if we come to replication so if you go database replication and then choose which tables you would like to enable replication for so in this case we only care about the doors so we can enable that one and then if we we will need to set up a subscription to any changes on that table so we can import use effect from react and we can call use effect pass it arrow function and then in here we can say what's up should we make the likes a state variable right because then the subscription can just set the status is that what we're doing that's an excellent idea so we need to bring in use state as well whoa and we can say likes set likes equal to use state and initially this will be set to door dots likes nice and then we say use effect we want to set up a subscription which is equal to superbase client dot subscription i'm going to have to check this in there it's first from yeah so you say superbase.from uh and we say our table doors and we can actually just listen to the specific um row so we can say from doors colon id equals equals dodge door id so it's all a string so i think we need a string literal and now you need an equal sign then an eq dot right and then the id that was it yeah ah that's why you were saying string literal because yes yeah we want to interpolate in our door dot id cool and then uh so from there we want to is it on we want to tell it what we want to listen yes on updates and then we get a payload passed into a function which we can how about i just look at the box here we do the set set like payload.new or something like that right yeah so this is what i'm working towards here so um yeah rather than just subscribing to all tables and all events i'm just subscribing to the doors table and just subscribe to the update event i get this payload and then i can do something in that function and then i call dot subscribe so here we can do something and then we can call dot subscribe um and that gives us back a subscription and with use effect we can return an arrow function where we can do any kind of cleanup that we want and so here we would want to unsubscribe from our subscription which i think is just um subscribe i think it might be a remove subscription remove subscription yeah super base dot remove subscription my subscription uh or maybe it has an unsubscribe as well so superbase client dot remove subscription is that you me yeah remove subscription and then pass in the subscription give it our subscription which is this subscription here yep and uh yeah and then we can just do something so first we can just want to log out our payload so we can see what the structure of that looks like um and now if we click like because that's triggering an update uh in the database we should be seeing something um but we are not do you need to reload because the user fact potentially oh yeah okay cool so every time we click that we get this payload and then inside that payload we can again see the old and the new values so if we have a look at new we have likes and then we have the new value for our likes so we can go payload.new.lex and get the new value so that's what we want to set our likes to here is dot payload.new. so again that new uh that's going to be the the whole row of our newly updated um database row in the doors table um so now we just need to actually use our likes instead of door dot likes we just want it to be likes and now it's refresh just in case and let's click and it goes up to 25 let's click and click and click and click and click and click and click we can keep doing that and since we probably have just enough time let's deploy it to production so that everyone else can click that like button the whole bunch of times yes let's get them likes up absolutely and the nice thing with real time is basically as any one of you clicks the button it will go up on our screen so that's the whole point of real time it's not a you know it's not a really cool use case here with the likes but hey it is working it's a use case uh so we can import um actually all right this is actually just loading my page cool so we've got our advent calendar we can import that this is why vessel is so cool um the integration is obviously so tight with next.js because they also built that js and so through the ui we can basically just um deploy an xjs application and then have it update um every single time we make a change and one thing we will need is our environment variables um so we're just gonna show those one more time these are all i don't know right oh yeah they are well these are okay use the service rule key yeah yeah yeah which is something that is uh i think super unique and special about superbass and the way that it's been designed is that um we can expose all of this stuff to the front end and it's not a security risk because we have row level security enforcing that basically just denying all actions other than the ones that we choose to explicitly enable which is very cool so it's going to take a little bit to build and deploy our application but you'll see i'm doing this from my github repo so i've pushed this up to github we have an advent calendar um repo and so um it's reading this repo and deploying our application i didn't store for long enough it's reading every single one of our folders in this repo and checking the files that are inside those folders and then reading the contents of the files of the contents of the files in those folders and then it's deploying it to their cdn and oh no we got an error oh we can do this we can do this object object pre-rendering object object likes of null what's happened what have we done right property cannot read property likes of null okay so look for in your code for dot likes and then something door is null is somewhere door maybe now oh i think maybe makes sense if we're getting i'd get static props yeah so in get static props so we get state paths we build the paths called get static props and then we have an id yeah i think we might need to so door.likes is is the error somewhere so maybe do door question mark dot likes question question mark zero just in case there's like some right is that the only daughter of likes we have yes well we have a payload.new.likes but that should be fun but that'll happen later yeah that would trigger a build error so let's add that and commit fix e fix and push and then i guess we can just try to do this again so we've got our add then calendar um we're gonna get our environment variables we'll get there and we'll deploy again already exists okay so if i go to here and go to advent calendar and we can try to trigger another deploy which is somewhere yet but if you push push changes shouldn't it auto deploy no i don't know if it wired up correctly initially um redeploy so this is yeah we're into playing fixie fix what are your plans for the weekend though well i'm actually i need to run off because i'm gonna go sailing now so awesome i hope there's enough enough wind if not just you know slam your hammer into the ground and cool down the thunder and lightning and create your own storm all right this is going to work are you feeling confident it didn't work so why is it trying to pre-render a page slash object object so in our um okay should you maybe just say uh static paths and just set paths to zero so basically let's not pre-build anything and just build it on i know it's just an empty array sorry right right and then you can comment out the supervised line doors thing so basically then we're always in the fallback and we're just building the page as we request it okay should we do that oops and while you do that i'm gonna get my gear together okay all right let's push this up see if it works uh and this should trigger another automatic build hmm oh i just worked it out i know what it is what here it's actually [Music] um so door id we're saying yeah so this we need to actually do structure id from what we're mapping over what we're mapping over is a collection of objects that has the key id uh all right so i'm just going to quickly do this because we should do it properly which means actually we don't we don't need this i mean it's still it's still doing problems no no rebuilding anything commit commit actual fix which i actually think we might not want to pre-build anything right we do want to pre-build everything always but because of our role policies the pre-built will actually not pre-built all the doors right so there's there isn't really much point in pre-building the doors i'm just that's my fault okay right that's a very good point because it's running on the server and so it's not running as a user you mean and so our row level policy is blocking it from actually yeah well is it getting any of the doors well it's getting the doors based on the date right so depending on when you deploy it it might get a couple doors but then it doesn't get any future doors right and so because of that we might as well just build the door whenever it is is available or something i mean yeah like you can do it very certainly it's working all right let's dump this in the chat um so if i come across to is it working it is working nice so if i put this here and can you put it in the private chat as well sure i can click on it nice whoops added an extra slash enjoy uh and if we all go to that page and obviously not my face uh go to build a sash product with super bass and stripe obviously id number two then it doesn't do anything it's not working one day something will work the way that it's meant to work all right so we send off that post it goes off we're getting back no door id2 so the increment like seems to be working did we have the set set likes yeah we had it no yeah yeah yeah so it's working locally with um like set lags and initializes it as that and then sets up the subscription and then anytime that changes it updates it to the new likes and then we're displaying the likes in the button but even if i hit it a bunch of times and refresh we're still not getting because the page is statically built no no no it should be fun no yeah so it should start off as that statically built value um hmm what is going on there anyway we might need to leave it there um so you can escape so go sailing but uh i can think about it on the water yeah and uh yeah maybe we can do well yeah we'll see we'll see how next week goes but maybe we can um do a supplementary stream like a short stream next week um and just work through oh log in first we need to log in first of course ah something that we should definitely be handling in our we should be just checking uh and our login links to all right one second everybody we'll get there so because we've now deployed this to versel um in our auth settings we've explicitly said that our site url is over localhost um so we need to change that to our cell url and then uh that's it because our github um our github callback url comes to uh our superbase instance so that should be all we need to change and now we should be able to oh it is working oh my god i'm excited i want to see it oh my god there's so many likes everyone go quick quick quick quick quick quick quick quick crash the super bass service crash the super business ah that's fabulous okay i think with that we can thank you chess today oh you've been a lifesaver we love you all uh thanks for tuning in and maybe lower low lower lower lower your volume now because we have a nice little jingle outro that we're going to play okay thanks y'all thanks alistair thanks four days with us hi chubby coder sumiya thanks uh yeah it's been a pleasure and do get your hackathon entries in we're looking forward to seeing what you have built all right have a great friday and weekend bye-bye see ya you

Channel: Supabase

Views: 217

Keywords:

Id: TijBVcV4jXw

Length: 138min 57sec (8337 seconds)

Published: Thu Dec 09 2021