Expanding the database: Tokens & Subscribers

Summary

Coming soon.

Transcript

Alright, let's kick things off by creating our subscribers table. So, I'm going to create a new migration file as we've done all the other times. So if we go into our migrations folder, we have our subscribers migration here. And there's an editor-related error, it's not really important. So, I'm just going to grab the migration here. And this is pretty standard, we create a table if it doesn't exist, we add an ID, that's a UUID, we create it at, update it at. We have our email column, that's a varchar, it has to be unique and null, so that we don't accidentally duplicate any of our subscriber emails. And then finally, we have this isVerified, that's a boolean that defaults to false. Now, we are relying on some database checks here, like the not null and the default false, that you could argue is slowing down the database a little bit, and we could do this in application code. But for certain things, it's just nice to have this guarantee that we don't store anything that's empty, for example, with the email. Next up, let's create the tokens table, and we're going to use Goose again for this. Create a new file, jump into our migrations folder, and we have the tokens migration here. Of course, my editor is still complaining about SQL files, but it's not relevant. Alright, so similar to before, we create the table if it does not exist, we use the UUID as an ID, it's the primary key as well, we have only the createdAt here, we can't really update our token. Then we store a hash that we're going to use to validate emails a little bit later on in the course. We have our expiresAt, so basically we can reuse tokens for a lot of different things here. We can use it for unsubscribing from our newsletter, for validating emails, for doing a lot of things. So we simply just have a unique hash, and then different explorations, timestamps for these tokens, depending on the operation. For example, if we want to do a forgot password, the expiresAt would be maybe an hour, whereas let's say we have a subscriber that subscribes, it's probably going to be a day or 48 hours, something along those lines for the time frame they have to validate the token. And finally, we use something here called meta-information, which is a JSON-B field, which literally just lets us store some information about the token. So if you take our subscriber example again, it would be nice to know the ID of the subscriber and the table name for subscribers, right? So this is all going to go into meta-information. All right, so with our migrations done, we also need some queries to actually interact with our subscribers and our tokens in the database. So let's go into psql here and create a file called subscribers.sql. And in here, I'm just going to copy-paste a bunch of methods. And if you look, this is very similar to what we've already seen. We have a query by ID, we have a query by email, we have an insert, and we have an update. And for now, this is going to be the only thing we will need to start adding subscribers and do the whole subscription flow. Next up, we also need a way to interact with our tokens. So let's create a new file called tokens.sql that will have a bit fewer methods. We will only have three methods here that I'm not for some reason allowed to copy-paste. All right, we'll write them out together. So we'll have insert token that will return the token. And in here, we have insert into tokens where we have ID created at the hash that expires at the meta information that goes into these values of arguments one. And we have five of these. So two, three, four, five, we let the last one go. Finally, just say returning star so we get everything back. There we go. I can put this up on this line right here. This is looking good. Then we also need a query token by hash that only returns one row. Select star from tokens where hash equals to star to $1. So the first argument finally delete token, which is only going to return an error if there are any. So delete from tokens where t equals argument one. Oh, there we go. So if we save now and quit it, go out to our terminal and then run SQLC generate, we have no errors. And if you now go into our models internal, we should have subscribers we do. We have the insert, the query, all of that. We then look at the tokens and we also have the free operations that we need. So this is all the database operations we're going to need for now. In the next episode, we're going to be adding the models so we can start building out the UI and fill in the controllers and actually start having subscribers on our blog.