SQL Migrations with Goose

>> Melkey: So building off of everything we've done so far, we've created our backend, we've created our database. We have a few things running, it's looking good, and we have the connection, but it's not the strongest connection. I'm gonna introduce another really good package, and this is called Goose. Goose is a database migration tool. It supports SQL migrations and Go functions. If anyone has ever dealt with migrations, just going at it, that is a very unfortunate experience. Migrations are notorious for losing data, breaking databases, and, unfortunately, just taking souls. Because if you're not properly equipped, whether it is not making backups, not having the right tools, just misaligning on your infrastructure, on your initial schemas, you can possibly run into some really nasty consequences. So I found this tool a while ago, and this is recommended to me by a very very brilliant engineer. It's called Goose. And it's a migration tool specifically for Go. And what migrations are for those who may be unfamiliar with the lingo. A migration is used when we have to make a modification to the underlying schema structure. So when you create a database, and you have a schema, unless it's a user, and it has a bunch of fields, but let's say, down the road, your project blew up, and a new feature came in that your users absolutely want, that requires you to modify that database schema. Well, you can't just add a column and then what? How do you run that? How do you run that SQL, right? Or, if you have to add that column, and it had breaking changes, and you have to revert, what do you do? So goose allows us the ability to migrate between versions of our database, so it allows us more flexibility and an easier way to run SQL that does the underlying changes to our database, okay? Now to install Go or install goose, it's a bit different, we've previously seen we did Go get. Go get is specifically used to just bring in a package into the scope of our project for our go.mod and our go.sum. In this one, I would like you guys to install goose, and the reason for that is goose is going to be applied to our binary, to our GoPathBin, directory. Because we're going to use the goose command. Okay, so I'm gonna copy this command over to the terminal, so hopefully it's a bit easier to see, I'm gonna clear this. So let's go install github.com/presley/goose/v3/cmd/goose@Latest, to get the latest version of goose installed. So I'm gonna go ahead and click Enter, so I already have installed to make sure you have installed, please do goose version, or goose-version. Is anyone getting any ZSH or script not found? Okay, so to solve that is open up a terminal, first of all, you wanna make sure that it's installed correctly. So you can do the following command to check first if it's installed, it's a grep command for goose so that makes sure that exists in the go/bin. If you have that, that's a pretty easy fix, all you need to do is export or run this command, export PATH=$HOME/go/bin:$PATH. Export that into your ZSH or bash script, whatever it is, we're gonna have to write some SQL. I know you guys want to do a go course, but because we're doing a back end application SQL, you cannot get too far from SQL in the back end already, eventually you're gonna have to do it. And we had a question earlier, if we're going to use RMS, no, we're gonna write this raw, all right? So, at the root of your project, let's go ahead and make a new folder, let's call this migrations, okay? And the first file we're gonna create is fs.go. This fs.go file is going to look a little wonky, so first, let's go ahead and declare our migrations package. And over here, we're gonna import the embedded library, embed. And what we're going to do, we're specifically reserving this file for when we compile our application to binary. Because what this tells us, fs is going to be a file structure, and what we're going to tell our compiler is that there's going to be a bunch of SQL files that exist in this file structure we're going to create. And then when we use this downstream, we actually run our goose migration up in our application, it's going to refer to the file structure here, okay? So I'll show you what I mean, so we do this Go build command so it's commented out, and then go:embed, and then here we're gonna do wildcard.sql. This is saying, find all of the SQL files that are going to exist in the directory path here. I'm gonna create a new variable, var FS, and it's going to be embed.fs, you can see it's a read only collection of files, usually initialized with a go embed directive, and that's it, that's all this file is going to do. We're going to capitalize it because we're gonna pull in FS, but essentially, we're going to be building a bunch of these files into this variable, okay? Now comes the fun part, in migrations, add a new file, the syntax of this doesn't really matter, I'm gonna share with you what I use. I use 00001, and it's gonna be called _users.sql, okay? So here we're gonna ship right SQL, but first we're gonna actually give it some goose command, some goose directive. So we need to decide to do a comment SQL file--plus goose up, which says on up, do the following, note that. We'll do plus, want to say goose statement begin, okay? And here we'll do create TABLE IF NOT EXIST, I'm gonna fix this extra space here. The name of the table is gonna be users, all right? And here we're gonna define what that schema looks like, so we're gonna have a field called ID, BIGSERIAL, It's gonna act as our primary key, okay? We're gonna have our username, it's gonna be a VARCHAR. I didn't put the most effort to thinking about the size of a username, I just put 50, I'm sure there's more optimal ways of handling this. For me, I just put 50 here, and it's gonna be fine, I'm gonna make sure this is unique and not null, meaning every entry in the user's SQL database is going to have to have a username and that user cannot be a copy of someone else's. We're also gonna have another field called email, I'll make this one a bit longer, VARCHAR(255), oops, okay? Now we can make this UNIQUE NOT NULL as well, here we'll do password_ hash. So this one's gonna be pretty important, we are not going to be storing the actual hash of like, the actual password, right? That's not what we're going to be doing at all, we're gonna be storing a hash representation of the password. I'll show this down the line, we don't have to worry about this too long or yet, we'll make this 255
>> Melkey: 255 and we'll make this not null, okay? And then here you can just add any fields you want, right? This could be just extra fields, I'm gonna put bio to make this a text, I'm gonna create a created underscore at field. It's gonna be a TIMESTAMP WITH TME ZONE, I'm gonna put default to current underscore timestamp, which is a built-in function for Postgres. And I'm gonna also have an updated_at, which can be the same thing, TIMESTAMP WITH TME ZONE, DEFAULT CURRENT TIMESTAMP.
>> Melkey: When you do that, make sure to end the statement, so StatementEnd for goose, and basically what that blog does is when we run goose up, this is going to be the SQL that gets run depending on the version of goose that we're currently running at. And with every up command, we just also need a goose down command, so we'll do goose down, we'll do +goose StatementBegin, and this is pretty easy. We just do DROP TABLE users because if you think about it, this is the role before the users table exist and when we go back to it whatever that looks like, okay? And here we'll do +goose StatementEnd, cool, now, what I'd like you to do is go ahead and copy this. I'm gonna make a new file, it's gonna be 00002, and this is gonna be called workouts, so workouts.sql. Go ahead and just paste this because we're gonna repeat a lot of stuff, and it doesn't make sense to just copy the same thing over. So goose up, goose down begins to be the same thing, create table, if not exist, not users but workouts. The ID is still gonna be a big serial primary key. Now, this whole project is allowing users to create workouts, so if you've ever worked with relational database, a workout has to be tied back to that user. Here's the thing, I'm gonna make this not obvious, we're gonna ignore that for now. The reason why is because I wanna focus on first getting everything working with workouts, and then we're gonna introduce the user, and then we can tie it back into users owning workouts. So for right now, we're gonna make a mental little note of a comment, I'm gonna put user underscore ID.
>> Melkey: Our workout is gonna have a title, which would be VARCHAR(255), NOT NULL, we're gonna have a description, which is going to be text. We're also gonna have a duration_minutes, which are going to be INTEGER NOT NULL, and we're gonna have one more field called calories_burned. And this is also gonna be an INTEGER, and we don't care if this is null, and then you can go ahead and remove the username, email, password hash and biofuels. So double check that your end result looks something like this.
>> Melkey: You should have a workouts table ID, the commented out user ID, title, description, duration minutes, calories burned, and then the created and updated at timestamps
>> Speaker 2: You wanna drop workouts instead of users?
>> Melkey: Yeah, we're gonna drop workouts
>> Melkey: Okay, let's do that one more time, go ahead and go to the top of your file, copy everything, and in migrations, we're gonna make our last SQL file, have four zeros, so 00003, and this one can be called workout_entries.sql.
>> Melkey: Just paste this here, and I'm gonna explain the dynamic here of what a workout entry is versus a workout. So a workout is going to be an umbrella, high level description of the workout. It's gonna have the title, duration and things of that nature, but a workout could have many different workouts in it. Someone could be doing leg day, could be the title of the workout, but then they could be squatting, they could be lunging, they could be running, they could do calf raises, they could do all these different things. And each of those is going to be an individual workout entry, so from a schema perspective, we're gonna have a one-to-many relationship here. One workout can have many workout entries to them, right?
>> Melkey: And all those workout entries are gonna have only one relationship back to a workout.
>> Melkey: Okay? So with that being said, CREATE TABLE IF NOT EXIST, instead of workout is gonna be workout_entries. It's gonna make some space here, the ID is still going to be big, serial primary key. We're gonna have a new field called workout ID, it's gonna be big hint, all right? Not null, and going to references, I'm gonna pass in our workouts table like so, and the value is going to be ID. So the work at ID here references the ID of our workout, and we're gonna do ON DELETE CASCADE, meaning if we delete the workout from the workouts table, all of the worker entries that are binded to it via this relationship will also be deleted, okay? Next field we're gonna have is the exercise name, this would be a VARCHAR(255). Make sure this is not null, and we'll have sets, this will be an integer, not null. We'll have reps, make sure this is an integer. Okay, we'll have duration, underscore, seconds, we'll make this also an integer.
>> Melkey: I have weight for the individual workout, we'll make this a decimal value.
>> Melkey: We'll add an optional field for notes, this could be also a text, so if anyone has like PR, or personal record or, I mean, personal dash, whatever, they can add into the notes section. And then another field that's gonna be crucial is order index, this is because if somebody has a workout with five different workout entries, we want these to be ordered to mimic exactly what that user did in the workout, right? If you submit a form with all those entries, we wanna adhere to that and not just jumble them around. I'll make this INTEGER NOT NULL, then go ahead and copy that created at timestamp. So at this point, your SQL table should look similar to this, so we can ignore the goose down for now, let's save that to the end, okay? I think this is cool, we're gonna add a constraint, I'm just gonna add a constraint. Oops, make sure, expel the right constraint on our Postgres, I'm gonna call this valid_workout_entry and this is gonna be a check. And in this check, we wanna make sure that if somebody submits a work at entry, this could either be them lifting or running or doing some non lifting exercise. So for example, if you're going to be bench pressing, that's have reps, right reps and weight. If you're gonna be running, though, you can't really rep running, but you can do duration, you can tell us how long you ran for. So we're gonna make sure that there's a constraint and not a workout entry can have both of these fields, and every workout entry must have at least one of these fields. So how that looks like is, reps IS NOT NULL OR duration_ second IS NOT NULL, and we'll make another constraint here, REPS IS NULL OR duration_second IS NULL.