Fastest way to host a Postgres database (2024)

I'm going to show you the fastest possible way to get started with an entirely free hosted postgress database we're going to use superbase which has this lovely UI for quickly building out a schema but gives you direct access to the underlying postgress instance for any amount of complex tinkering or query optimization that you could dream of we're also going to look at some super powerful AI features that can save you heaps of time so let's get into it start by heading over to database. new and signing in with GitHub or any of these other methods I'm going to call my project to-do cuz this is going to be a to-do list with super base I'm going to click here to generate a secure password make sure you copy this somewhere like a password manager but if you forget don't worry about it you can reset this database password at any time now let's choose a region that's close to our users I'm the only user of this so I'm going to select Sydney and then let's click here to create our new project and while our project is spinning up these are the credentials that we'd use if we wanted to connect this to something like a nextjs app so now that this is up and running we can go to settings and then under API we can see our URL and our non key from earlier and under database we can see our connection string if we wanted to connect something like Prisma or another postgress client directly and this is also where we would reset our database password if we didn't copy and paste that to a password manager so if we head over to the table editor we can use the UI to build out our schema so we can click here to create a new table we can give it the name todos we'll leave row level security enabled but we'll come back to this soon for our columns we can leave ID and created at and then add another column for the title of our to-do and the data type for this one is going to be text and then let's click this Cog and turn off is nullable because we want every to-do to have a title and then let's add another column for an author and we'll click this little chain link thing here to set up a relationship to another table the schema for this one is going to be or the table we want to reference is going to be users and the column is going to be ID the or. users table is a special super based table that we don't really need to worry about but it just stores all of our users and information about their sessions and then we can click save to set up that relationship and then save again to create that table so we could keep using the UI to insert some rows into our to-dos table or we could write this as raw SQL and run it in the SQL editor so let's say insert into to-dos table and we're just going to specify the title and then for our values we can specify our first to-do and then our second to-do and super creatively a third to-do and we'll get rid of that dangling comma now we can click run to execute this or command enter and we see success no rows returned so no errors means everything's all good so let's select back our to-dos We'll add a semicolon to this one cuz we're going to have multiple statements and then we're going to say select star from to-dos and we put a semicolon on there too now if we highlight over just this one statement you can see we would be running selected if we did command enter rather than everything in this query so the insert as well as the select so let's run just the select and we get back our three to do now currently we don't have any authors cuz if we go over to authentication and then users we can see we don't have any users in our project so we can add a new user we could send an invitation if this was a real user that we wanted to invite to our application but we're just going to create a new user for test purposes so this one can be John superb.com and we can enter in a super secure password and again we'll Auto confirm this user cuz we're just using it for test purposes and we can create another user for Thor at superb . and another Super secure password and autoc confirm this user now that we have some users we can go back over to the table editor and Associate some of our to-dos with specific authors so we can just double click here and this is going to look up all of the rows in the related table of au. users so we can scroll across and see this is our John at superbase user and this one's Thor so let's say Thor authored this first post and then our John user authored this second one and then who should we pick for our third one I shouldn't have made this an odd number all right let's just give it to Thor and now each of our to-dos have an author and if we go back to the SQL editor and select all of our to-dos again we can see those authors for our to-dos so what would this look like if one of our authenticated users tried to run this select query well we can do that from the superbase studio so if we change this role from the postest role which has admin privileges and bypasses roow level security and instead change it to the authenticated role meaning we can impersonate either of our users so let's impersonate John now if we run that same select query we'll see success noos returned so Thor gets two to-dos and Jon doesn't even get to see them anymore well we can see even if we change this to stop impersonating JN and instead impersonate Thor and run this same select query again Thor can also not see any of the rows and this is because when we set up our table we enabled Rowl security or RLS but no policies have been created yet and so we need to create a policy to allow access to this table so by default Road level security or RLS automatically denies any requests to this table no one can can select insert update or delete any of the rows if we want to enable one of those actions we just need to write a policy so let's create a new policy there are some templates that we can use to get started but let's create this one from scratch the name of this policy is going to be users can select their own to-dos this is going to enable the select action we'll leave the target roles as the default which is public and then for this using expression we need to provide an expression that evaluates to either true or false if it's true then the user will be able to select this row and if it's false RLS will continue denying it so we want to check that the author column of this specific to-do contains the value of our current user and so the way we get this value is with the or. ID function so this is a special superbase function which gives you back the ID of the user who's trying to perform this action so let's scroll down to click review which shows us the SQL that's been generated by each of these form fields and then we can click save policy to run that against our postgress database now if we go back to the SQL editor and we again change our role to the authenticated role and impersonate one of our users and then run this select statement again simulating this user being signed into to an application that's making a request to superbase we can see our John user is only able to see that second too because he is the author of that one and if we were to change this to Thor so let's stop impersonating Jon and impersonate Thor and run this select statement again we can see Thor can very unfairly see both of his to-dos and if we change this back to the postgress rooll which Again by passes roow level security policies and run this select again we can see all three of those to-dos come back because this is an admin user so the RLS policy is acting like an implicit wear clause on our select statement it's filtering down the result set to just the to-dos that the signed in user authored but a quick optimization tip you should always write an explicit wear clause on the select statement rather than just relying on RLS not only does this make it more clear and explicit for anyone working on this code in the future but it helps the postgress query planner to optimize the result set that it's working with so let's select star from to-dos where the author column is equal to the or. ID function and now if we run this as our postgress roll we don't get any rows returned because or. uid returns null but if we change the role to our authenticated role and impersonate one of our users and run our select statement again we'll see exactly the same result set but the postgress query planner is much happier with us even though we're only working with three rows but what if I don't want to write all this raw SQL myself can the robots help me out of course they can so if we create a new query we can ask superbase AI to help us out by saying something like count the number of to-dos for each author and sort descending by count column and then super base AI will have a little think about it and then we get this nice diffing view where we don't have anything at the moment and these are the lines that it's suggesting to add so let's accept that change and and then this is selecting the author column and then counting the rows and aliasing it as count from our public. too table so public is the schema that our to-do table exists Within by default we could actually remove this if we want or we can just leave it as is it's then doing what's called an aggregate function where it's grouping by a particular column which in this case is author and then ordering by that count column in descending order so this looks right let's run it and we're still impersonating our John user so we only got that one row back that he's allowed to see so let's go back to our postgress roll and then run this query again and we can see that very unfair result set where our Thor user has two to-dos and our John user only has one so now that we have our superbase project let's connect it to a nextjs app but first we need to talk about cookies and authorization I recommend you check out this video right here we go through why you need to store sessions in cookies especially in the nextjs app router but until next time keep building cool stuff

Channel: Supabase

Views: 4,591

Keywords:

Id: 9T6tjTQ4Zo4

Length: 8min 55sec (535 seconds)

Published: Sat Feb 10 2024