Our second migration: Users Table

Summary

In this video, I show how to build an authentication system starting with database setup. I use Goose to create a new migration for the users table, which includes id, timestamps, email, password, and email_verified_at columns. While we won't implement a full email verification flow for our blog admin users, I note that we'll cover this for subscribers in module 7. Next, I create two SQL queries in psql: query_user_by_email and insert_user. After running sqlc compile and generate, I apply the migration to create the users table. The database is now ready for storing and retrieving users for authentication. In the next video, I'll implement the model logic for password hashing and other authentication features.

Transcript

All right, so with the theory out of the way, we can actually start building this authentication system. And the first thing we're going to need is somewhere to store our users, which is, of course, going to be in the database. So let's use Goose to create our new migration. And this is exactly how we did last time. We are just creating a SQL file here called Create Users Table. So go ahead, Enter, and now we have a new file. Go into our migrations directory. And we need to create an up version and a down version. And I'm just going to grab the up version here, paste that in. And this is exactly how we did the articles table, right? Nothing new here. We then have an email column and a password column, which we would expect. And then we also include this email verified ad column. Now, for our use case, we are not going to be having users sign up for our website to use some service. You would typically, in an app that has users that sign up for services, you would have this flow where they create a user. We send them an email with a token or something similar that says, hey, thank you for signing up. Please click this link. And when they then land on our page with that link, we would then update their account or the row in our users table to have this verified ad set to the time that they did this action. And then after that point, OK, divided this with a real email they can access. But this is not really going to be relevant for how our blog is going to be working. I will show you another way to create users. And for our subscribers, we actually got to be mimicking this flow. So if you were to want to enable signups on your blog, you can follow the authentication or verification flow that we have in module 7 for our subscribers. But for our use case, this is going to be all we will need. And finally, let's add a down migration. There we go. And this is basically all we need for the migration. Now we need to create a few queries. So go back into psql. Create a new file called users.sql. And in this one, we're going to create two queries. We're going to create one called query user by email. That's going to return one record. Let me say select star from users where email equals whatever we pass it. Really straightforward. And then we're going to need another query called insert user. That will also return us one record. And we're going to say insert into users. And we need to insert into the ID. Created at, updated at the email, the email, verified at, and password. And the values is going to be, we have, how many do we have? We have six values, right? So let me just create this really quickly. And we have 1, 2, 3, 4, 5, 6, and there we go. Now we say return star one more time. And this is all we're going to need for our insert statement. And again, note here that we have this email verified at that we're going to use on insert. So we are basically going to be treating email verified at the same as created at in our scenario. But again, in module seven, we will do a proper email verification flow that you can replicate if you need to. But for now, we don't really need more. We can actually jump out back into a terminal and say sqlc compile and sqlc generate. There we go. And finally, we can apply our migration by running basically the same as we used to create, but just replace the create, the file name, and the type with op. So there we go. Created a users table. And if you quickly just verify that we created the go file, the generated go files in the correct directory, we can see internal db. And we now have users beautifully insert and query user by email. Awesome. So this is all we need to do from a database standpoint. In the next video, we will implement the model logic to insert and hash password and do all of those kind of things. So for now, our database is ready to start storing users and pulling them out for authentication purposes.