User SQL Queries

>> Melkey: So now that we have the interface, we have all the structs, we can start writing the functions for the things we have here. So first, let's go ahead and create user. So we're gonna do func. You could do, I don't know, s *PostgresUserStore. It's going to be called createuser, right. It has to, again, match the method of the interface there, the signature so user be a pointer to user. I'm just going to turn an error like so, and then here we're gonna create our query. So exact same foundational things that we did earlier, nothing new is really changing. So here we're going to insert into users. We're gonna do all the fields we care about, so username, email, password, underscore hash, and then bio values. I'm gonna insert four values, so one, two, three and four, username, email, password, hash and bio, okay. And here we can just do something like RETURNING id. And if you want for users, for anniversaries of when they created the account, or if you want to store that sometimes you see this account was created. So, like on Twitter, you can also return the created at and if you want also the updated at. Okay, and then here we can just do errors, it's going to be s.db.queryrow. I'm going to pass in that query like so and then all the fields as they appear. So user.username, user.email, user.password hash.hash, okay, and user.bio, it's going to yellow this because we need to use the scan method now and scan the returning fields into the appropriate fields here. So it's ID created at and updated at, again, these have to be addresses. So we can do user.ID, user.CreatedAt and then user.UpdatedAt, like so. For standards, go ahead and check this error. So if our error does not equal to nil, we can do a few things. We can just return error actually.
>> Melkey: Okay, otherwise, let's just return nil. Let's move on to the next function, which is get user by user name. Okay, so we just do us PostgresUserStore, GetUserByUsername. It's gonna take the username string and it's gonna return user and an error, like so
>> Melkey: Okay, we're gonna first define our user. So here we can make it define a new user like so. Okay, and we have to instantiate the password, so password hash, and we can just do it's gonna be password like so.
>> Melkey: Okay, let's go ahead and run the query so we can do SELECT id, username, email, password_hash, bio, created_at and the last field is updated_at. From our users table where the username equals the one and only value we're gonna pass in. And here we can declare our error as sdb. We can do QueryRow pass in that query, like so, and all the fields that we want to see, which is just username,okay. But then we can scan, and I'll open this up just a bit easier to read. And because we've declared our user a few lines above, it's going to be pretty easy that we can just do user and start pulling in those fields. We can do user.Username or sorry, the first field is ID, okay? User.Username
>> Melkey: User.email, okay, then we can do user dot, oops, user dot password hash dot hash like so. Okay, in the user.Bio and then user.CreatedAt, user.UpdatedAt.
>> Melkey: And as we know with the select clause, it's not guaranteed to return anything, but nor is that an error. So we can handle this with if error equals sql error no errors or no rows excuse me. You can simply do return nil and nil, if our error does not equal to nil, so if something wrong happened, if you turned nil and the error. Okay, otherwise, we can go ahead and return the user and nil like so let's rip that bandit off and create the update user function as well. And then we will not have to be in this file anymore for a little bit. We do func and feel free if you already kind of know where we're going with this, write it out as I'm writing it to maybe have, if you have to look up to see if you kind of stumble, it's a good way. But I encourage everyone just to try it out as well. So this UpdateUser, I'm going to say user, pointer to our user, like so and so this is update user. All we care about is if, if there's an error or not, we don't necessarily need to return the full user up to if you want to implement that. Okay, I'm gonna define the query here and here I'm gonna update the users table, we're gonna SET username as the first value we potentially could be getting,email as the second, bio as the third. And an updated app, we can set this to CURRENT_TIMESTAMP, which is a native function in Postgres. Where id lowercase equals the fourth value, okay, returning updated_at, then we can do result and error sdb I'm gonna execute this pass in that query and then the four variables it expects. So user.Username, user.Email, user.Bio, okay? And then user.ID. Let's check that error, if error does not equal to nil let's just return that error back to our caller. With an update, we're gonna see how many rows were affected. So we can do rows affected. Error is going to be result.rows affected like so whoops. Okay, we can check this error if error does not equal to nil, let's go ahead and also return that error.
>> Melkey: And if rows affected is zero we can just return sql.ErrNoRows. Otherwise we can return nil.