Adding Database Migration

[00:00:00]
>> Erik Reinert: So now, we're just going to create a new branch. We're just going to do git checkout dash B stage 02 dash growth. So we're just going to create a brand new branch and switch into it every stage. We basically create a new branch, create a new PR and work off of that PR.

[00:00:14]
What we're going to want to do is we're going to first want to set up our migrations. Now, when you set up migrations for schemas and repositories or whatever, there's tons of different tools out there, there's ORMs that exist and whatever. Don't be afraid to pick whatever solution works best for you.

[00:00:31]
I'm saying that you have to take this approach. But I decided to use a tool called Goose. It's basically a migrations tool written in Go. The whole purpose of this tool and what it does is it effectively allows you to create SQL files and then make changes to your database over a period of time.

[00:00:54]
That's the easiest way to put it. Every time you make a new change to your database, you basically create a new migration, which does two things. First, it makes the changes that you want it to change and then it also reverts those changes. The reason for that is because if you have something that breaks or you need to roll backwards, then you only roll backwards for that one specific version or a specific set of changes.

[00:01:21]
When you do a migration and you create a migration, you're actually creating the changes you want to make and then you're also making SQL that reverts the those changes. So that's our goal, that's what we want to do first is we want to create a document or a, what's it called, a migration for our code.

[00:01:41]
So the first thing I'm going to do is if you guys have Go installed, you're going to go ahead and do go install go space install space GitHub.com, pressley goose v3 command goose @ latest. So go ahead and run that command for me. Now, after you run that command, you might run it and it doesn't work.

[00:02:01]
Does the goose command not work for you guys? It does work. Okay, cool. So if it doesn't work, the reason why is potentially that you don't have your go bin in your path. So if I do echo and then I do go path, you'll see my go path is empty.

[00:02:23]
And if I do path, you'll see that I don't have anything in here for go at all, except for the go binary itself. Basically what I want to do is I want to export a go path. I basically want to tell Go where all of my go code is expected to be.

[00:02:47]
Now, I'm not going to do this. I'll just do, for now, just do home. So I'll put my go directory as home. I'll just do home slash Go. Then what I'm going to do is I'm going to export my go bin to be gopath bin. So I'm telling Go where the Go path is and then I'm telling Go bin, hey, Go bin is just the bin directory inside of the Go path.

[00:03:17]
Then the last thing I will do is I will export my path where I will say, hey, export path, Go bin path. This makes it so that now if I install anything or do anything like that, it should actually become available to me. I think if I do Goose now.

[00:03:35]
There you go, you can see Goose is now available. I'll scroll up just so that you have those commands one more time. If it doesn't work for you, you want to set a Go path home Go, right? Set a Go bin, Go path bin and then path Go bin.

[00:03:55]
Now, if you've installed Go by default, this should work. It's pretty much set up out of the box to work. But if you didn't have any weird setup or anything like that, that may be something you need to do.
>> Speaker 2: And as a note, if they do that and close their terminal and reopen it, it's just for this session, right?

[00:04:09]
They don't have to add it to their ZSHRC or something if they wanted that to stay forever.
>> Erik Reinert: Exactly, yeah, yeah. So just to reiterate for if you guys don't know, because I'm exporting environment variables, right, they're only going to stay in that shell. So if I close this shell, these will go away.

[00:04:26]
So, you know, if you want this to be persistent, add this to like your bash profile or your bash rc, whatever you want. You would just add these three go path go bin and then update path to have go bin inside of it. Yep. Okay, awesome. Now that we've done that, we want to create our first migration.

[00:04:44]
What we're going to do is we're going to run Goose, dash dir migrations, create base schema and SQL. Now this may fail because the directory doesn't exist. So let's make the directory really quickly. I didn't remember if it did. So there you go, migrations and then bam. So create the directory migrations.

[00:05:15]
If that fails for you, and then run the create command and you'll see that it says that, hey, I created a new file, migrations 200-blah-blah, blah, right? Now, if we go into that file. Go ahead and go into that file, you, you'll notice that it has generated us a very simple and empty SQL file, right?

[00:05:37]
This SQL file is what they expect us to update with the schema changes that we actually want to run. And so what we're going to do is we're just going to stay within the statement begin and statement end statements for both the up and the down. Now, up means the changes that you want to make, and down means the changes that are how you revert those changes, right?

[00:06:00]
So what do we want to do? Well, for starters, I want to. Let's do this. I want to open up the readme, right? I want to take this whole block of code right here, all of it, and then I want to go back and open up the migrations file.

[00:06:18]
And then I just want to paste. Oops. I just want to paste that whole thing inside of there. That's it, just want to paste that. And there we go.
>> Erik Reinert: Now, I have all of my great migrations for creating my schema. However, I don't have the delete part.

[00:06:40]
So this is the part that we need to add. And so what we're going to do is underneath down and in the statement, we're just going to add drop values. So we're just going to say, hey, if we go down, we want to drop comments, followers, likes, aspiration updates, administrators and users, basically everything that we added.

[00:07:03]
So now that we've got that, we're going to go ahead and just save that file. And then now what we want to do is we want to update our env one more time because we have a couple of new environment variables that we need to add. So the first thing we're going to do is we're going to above postgres URL.

[00:07:21]
We're just going to say, now do this in your dot m file, I'm doing it in the example one so that I don't dot myself, but do this in your normal env file, right? So open up your m file and then inside of it add goose DB string and then just add an equals for now.

[00:07:41]
And then. Yep. Thank you. Copilot goose driver equals postgres.
>> Speaker 3: There's copilot for neovim.
>> Erik Reinert: Yeah, of course. Come on now. Not living in ancient times in neovim. [LAUGH] So here's what I would say is it's up to you on how you want to do this. You can either take the existing value and then just copy and paste it.

[00:08:06]
Or if you wanted to, you could actually take the DB string, move it down here and. And then you could just say postgres URL and so it'll use the exact same value. So it's up to you on which way you want to do it. If you want to just have the value be the same twice, or if you just want to do.

[00:08:23]
If you want the DB string to use the exact same thing as the postgres URL, which it needs to. That's what it needs to use, right? Then you can just tell it to reuse the postgres URL underneath it. Makes sense? Awesome. All right, so I'm going to go ahead and save this file.

[00:08:44]
All right, awesome. And then I'm just going to source my M file one more time. So now if I do echo Goose DB string. Cool works. That should work for you as well. Let's do a quick docker compose down. Let's just bring down our instance running in the background or our container running in the background.

[00:09:08]
Then let's do another docker compose up, detach. So we're just restarting it now, I want to note, make sure you add in the down remove orphans and volumes. Make sure you add both of those. Because we want to delete the volume for the instance. We want to make sure that the volume goes away.

[00:09:26]
Because we're about to run new migrations on the database. We've already added the schemas to it once, so we want to make sure that we remove it so that we can add them again.
>> Speaker 4: What if we already ran it without those flags?
>> Erik Reinert: Then just rerun it with them.

[00:09:41]
Yeah, and you'll see here, when I rerun it with the flags, it just removes my volume by itself. Cool, okay, so then we're going to do up and detach. And then what we're going to do is we're going to first run a goose dir migrations status. And you should see that, hey, I was able to connect to the database.

[00:10:09]
We're good. And we have a pending migration, meaning that this has not ran yet. Then the next thing we want to do is validate. This will make sure that our migrations are actually valid because, yes, we can do validation. Then the last thing we will do after that is up.

[00:10:25]
If I hit enter. We should see very, very quickly it finds the migration it needs to run. It runs it in under 33ms. And then it tells me that the database has been successfully migrated to that new version.
>> Speaker 5: Where does it keep track of which ones it's run?

[00:10:46]
>> Erik Reinert: Great question. So if you were to open up the database, which I don't, maybe I can. If you look in my database tables, you'll see that there is a goose DB version and version ID sequence for. Yeah, exactly. So it creates an index in a table that records hashes, so it'll actually take your file and then make like a hash of it.

[00:11:10]
And then it'll say, okay, this is a version that I need to go to. And it'll save those in the database. Yeah. So you have just by doing the little bit we just did, you now have complete, like, we just added data reliability. That's the easiest way to put it.

[00:11:26]
Yeah. Cool. Okay. So again, if I do up and run it again, it'll just say, hey, no migrations to run. And that's something to kind of note for future. With regards to deployments, we don't have to worry about this running over and over and over, right? We can just run it in the pipeline on deploy, run it again, doesn't matter, as long as it can connect and check.

[00:11:46]
So, cool, we've got migrations now. Okay. So what I would like you to do really quickly is just add those changes that you made and commit them to your branch. So we'll just say feature added migrations.