Adding User ID Migration

[00:00:00]
>> Melkey: We're close to wrapping up, but there is one very, very obvious issue in this entire app, not middleware at all, and it's the fact that people can still create workouts and delete them however they want. There is no core safety between someone deleting all of my workouts that even if they don't belong to them.

[00:00:25]
And if you remember at the very, very beginning in migrations, in workouts, we have this commented out, this user ID. Well, we have come to the time where we're gonna basically start giving every workout an associated user ID. So user IDs are gonna be responsible of creating a workout, no more will people be quote, unquote, anonymous to create workouts, and this will allow us to validate if the person submitting the request is in fact the owner of that workout, okay?

[00:01:03]
And so how do we do this? Well, if we go back, we're not gonna uncommon this, we're gonna leave this we won't touch this file anymore, but under migrations, we're gonna create a fifth file, okay? We're gonna create 00005. This file, we can call it user_id_alter.sql.
>> Melkey: All right, so it should look like just an empty file, but go ahead and just copy an existing SQL file cuz it would save us some time.

[00:01:34]
So paste it, let's go ahead and save. Okay, so we're not gonna be creating a new table, but what we're going to be doing is alter table. The table name is gonna be workouts. All right, and this is truly a good use of of migrations. We're modifying the schema of this table, okay?

[00:01:55]
And here we're gonna do add column, okay? User_id, I'm gonna describe what this user_id is, it's gonna be a BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE. And then DROP TABLE, we're not gonna be doing any drop tables. So on the goose down ALTER TABLE workouts DROP COLUMN user_id.

[00:02:31]
And with that if we go back up, here we can rerun our same Goose command, so you might have it saved, so I recommend just using this up. So goose -dir migrations postgres, and it's just the up command at the very end. And if you run this, you should see this error, this is intentional.

[00:02:50]
I wanted to showcase this because what I wanted to do is highlight the importance of really understanding your data before you go too deep in programming. The reason why we can't use this is cuz we have already. If you go into PSQL and we select all from workouts, we have a workout here already.

[00:03:17]
And if we were to introduce this new column, this user_id that makes a relation to our users table, how do we find who remade this entry, right? Do we call everyone, all of our users? No, so the unfortunate side of this is this migration, although convenient to execute via Goose, it still presents a giant flaw in the architecture engineering, right?

[00:03:44]
So especially dealing with user databases with relational database to user fields or even other fields, make sure that when you are adding a field that if it is a field that references another table, you make a backup or some way to identify how to make that successful relationship.

[00:04:04]
Because the answer here is, there is no good answer. We actually have to delete and wipe the data here. So NPSQL right here, what I'm gonna do is write DELETE FROM workouts like so. And if I run this, it should say I've deleted one workout. So here, DELETE 1.

[00:04:23]
We can validate that that no longer exists. We also validate from workout_entries that they are in fact empty. So ON DELETE CASCADE does work indeed. So with that, we can now rerun our Goose command, and here we are on successfully migrate database to version five. So yeah, just kind of sound like a broken record all because we have the ability to make migrations doesn't excuse any kind of laziness on the engineer side, right?

[00:04:55]
So again, just beware of the feature you are going to add the level of your application, right? If your app is already having the content of thousand of users, there's certain features that gonna be very complicated to add if they do require adding a new relationship, right? That has to be some creative fields, creative ways to do that.

[00:05:16]
Some of this does actually involve making a back of the DB in a script to put that data into the modified database. So I just wanna kind of give a heads up on how to handle something like this. Cool, so now, if we go to psql and we do select all from workouts, we can see we now have our new user_id field and that is working pretty well.