Supabase Hosted PostgreSQL Database

[00:00:00]
>> Erik Reinert: If we go back to our slides really quickly here, we want to get app runner set up. What was the first thing we wanted to do? We wanted to do a container image. So what's the next thing we want to do? Database. Exactly. Yeah. So let's go ahead and do that.

[00:00:11]
So what we're going to do is we're actually going to go to Supabase. Now, this is not an ad for Supabase. I'm not saying that all of you should use Supabase. I'm just simply saying that it's an easy way to get started with a database, a postgres database.

[00:00:25]
If you don't want to do something like rds, there's a lot of really great options out there. I just happen to like Supabase and if Supabase wants to sponsor me, they're more than welcome to. But no, this is just, I really do happen to like this platform. I've used them for a little while now, and yeah, so anyways, what we're gonna do is you're gonna log in and then after you log in, you're gonna go to your dashboard.

[00:00:49]
Now, you'll notice that I already have a few projects here and so really quickly what I can do is I can show you what we're going to be creating, but basically we're going to be creating a database in Supabase. What's really nice about Supabase, and this is when you're a startup company, it's really important to have good metrics.

[00:01:12]
It's really important to be able to have good metrics. Easy management. One of the things I learned when I was working at startups was if there's not a control panel for it, you're probably not enabling everyone to be able to do their job. Like, just because you can do CLI and use CLI doesn't mean that everyone can.

[00:01:31]
And so I've made decisions around where to deploy and how to deploy, not realizing that if maybe I had taken another approach or extended it a little bit more, I could have enabled a lot of other people to do their jobs. And so one of the reasons why I actually like Supabase is just that you have all of the database management needs really at your fingertips.

[00:01:55]
And so again, if you're a new company, and you don't want to have to worry about setting up and managing all this stuff, your database is probably one of your most important things, right? Like, that's where all of your user data exists, that's where Every, all of your data exists there.

[00:02:09]
And so using a service like this that has like a nice UI on top of it, be able to manage the database, really user friendly and well, can enable a lot of other parts of the company to do things too if they need to as well. Whether it be other teams want to be able to go in and manage data or whatever.

[00:02:30]
It's a very approachable platform. And so yeah, like I said, using tools like this is not bad, especially in the future or in the early on stages when you don't really know what you're going to be doing yet. So it's got a table editor, you can see here, here's all the existing tables that we have.

[00:02:49]
It's also got an SQL editor which is really nice. We'll be using that shortly where you can just copy and paste scripts. You can literally just run SQL commands right in the browser. It is really powerful. Again, if you're going to put your money anywhere, I definitely think it's worth finding those valuable things that can bring value back.

[00:03:09]
We're going to go into, like I said, we're going to go into Supabase. I'm going to go back to my dashboard here and then what we're going to do is we're going to create a new project. So I'm going to click New project. Keep it on free. Make sure that you have it on free.

[00:03:24]
Okay, let me go ahead and delete one of these really quickly. I'll just delete this really fast. Project settings and I didn't realize that I was going to have a limit. I actually pay for it. Cool. Okay, so we're going to go back to New project and we're going to enter our project name.

[00:03:51]
It's going to be fem md service, right? You're going to want to generate a password for it. Something pretty decently strong. I use a password generator most of the time anyways. So what I would do is I would come over here and generate something like this. I'm not going to generate it on screen though.

[00:04:12]
I don't want you to access this database. I'm just gonna copy and paste this password in, and then I'm going to keep it for later. I would recommend you doing the same. Open up a tab in neovim or Visual Studio code, whatever editor you're using and just keep these credentials available because this is not the first and last time we're going to use all of these things.

[00:04:41]
Cool. Okay, so the next thing we're going to do is we're going to pick a region, let's do Ohio, that's fine, it doesn't really matter anyways. Then once you do that, the last thing you're going to do is you're going to click Security Options and you'll see here that it says what connections do you plan to use?

[00:04:58]
Data API connection string or only connection string? What's actually nice about Supabase is that it has an API built on top of it in front of your schemas. So you have out of the box rest API calls, so if you don't want to have to make postgres connection, you want to just start grabbing data immediately from your database.

[00:05:20]
Again, think about the ability that others now have. You don't even have to worry about giving them a connection. They can just immediately start working with the data, which again is very powerful for even metrics and all that stuff. In this case we're just going to say only connection string.

[00:05:36]
We are going to try and make it a little secure, obviously. So that's why we're going to say we want to make it so that that data API is turned off and only connection string is there. So I'm going to hit create new project and then once I do that, I should be brought into this dashboard.

[00:05:54]
So this is going to take a few seconds. It is setting up the project, but underneath the hood they're going out and doing what you would have been doing. They're creating that database instance for you, they're setting up all the ACLs and all the permissions and everything. Like they're doing all the work that you would have been doing anyways underneath the hood.

[00:06:12]
Once the project is set up, you basically should be landed on a page like this. And if we go to our database, for example, we will see that there, I'm sorry, our table editor, we'll see that there's no tables there, SQL editor is blank. So what we need to do now is we actually need to start working with the database.

[00:06:29]
We need to actually start setting it up and whatnot. What we're going to do is we're going to go to the SQL editor and just like before, not even changing the process, we're going to copy that SQL out of the readme Txt and into the editor. So I'm going to go up here, start, go here, copy, and I'm just going to paste it into there.

[00:07:02]
Again, it not making fun at all. But in all seriousness, it's a common approach. A lot of people do. There's still a lot of just manual database work and that requirement of just copying and pasting and scripts. In having this ability out of the box again, it already paid for itself.

[00:07:23]
I don't have to figure out how to run this. That saves time. That literally just saved us time, which is nice. Once you paste that in and then you hit run, it will run the query. It should say success, no rows returned. But then if I go to my table editor and there we go, I can see all of my tables now exist.

[00:07:44]
If I click on one of them, I can see administrators table. I can see my aspiration updates comments followers now again, this is a completely empty database, but the ability to have something out of the box that's so easily usable is really, really nice. Again, there you go.

[00:08:02]
Now, we didn't need to change anything, I didn't have to figure out how to script a whole bunch of stuff. As I said before, there was a lot of reason as to why we took this approach and we're starting to see it now really take fruition. So after we do that, we're going to just grab our connection URL.

[00:08:21]
At the very, very top there's a button that says connect. And please make sure to write this down and put it somewhere because it's all unique. Once you click that button, it will show you all the different ways that you can connect to your database. You'll see direct connection, you'll see Transaction Pooler, Session Pooler on the right.

[00:08:42]
You'll see each of how it works. The only challenge is that Direct connection, even though it sounds like the default, it's not IPv4 compliant. So they use direct IPv6 connections, which means that if you don't provision or get a database or service provisioned in IPv6 land. It won't be able to connect to it, or at least easily without making some weird things work.

[00:09:08]
So we won't be using Direct connection in this case. We also won't be using Transaction Pooler. Transaction Poolers are a little bit more focused for lambdas and a lot of connections simultaneously. We don't really need to worry about that. We know we're only really connecting one service. The last one is the Shared pooler, which is IPv4 compatible and really just a direct connection.

[00:09:33]
But over IPv4 I can grab this little button right here when I hover over it. Copy that, and then, we're just going to put that in the notes too. Let's just put this in notes because we're going to need this as well. So I'm just going to say database URL in my notes, and then I'm just going to paste that.

[00:09:50]
One thing I do want to point out is you'll notice in the URL it does say your password. So I did not dox myself here, just be aware that you're going to have to pass in the password that you generated, right? With the password portion of that URL, so that you've got the full URL.