Our first migration: articles table

Summary

Coming soon.

Transcript

Okay, so before we can start adding migrations, we first need a database that we can run them against. Now, if you're not yet set up with Postgres on your local system, you can refer back to the video called Getting Started with Postgres, where I talk about how you can set up users and databases on your local system. I already created a user that I call block underscore admin, so I'm going to log in with the CLI tool using that user. In here, I'm going to say create database block dash dev, short for development, with owner block underscore admin, create database, there we go. Next we need to specify or create a place where the migrations will live. So let's just quickly say make dir dash p psql slash migrations, which will just create a directory called psql, and in there make another directory called migrations. Then we're going to be using Goose, and if you're not yet set up with Goose, you can refer back to the video that I created in module one, where we talk about how to get set up with all of these dependencies, it's the fifth episode. Now this command will simply just tell Goose to, hey, please output the resulting migrations in this directory for Postgres database with this connection string, and we're going to create a migration called create articles table, and it's going to be in an SQL file. You could also get a Go file, but we're just going to stick with SQL for now. So press enter, there we go, now we have created a file in the migrations directory. Then we can jump in to our migration, you can see here we have what is a very boilerplatey Goose migration, we have an up version and a down version, so let me just grab those really quick, there we go, this is the up version, and then we have the down version, there we go. And notice here that I have called the table the plural form of what it is, so this is a common convention that you will have articles instead of article. You could go with singular naming convention if you wanted to, I'm just going to stick with the common practice here of using plural. Next up, you can see for an ID we use UUID, and if you have ever done other database work with something like Laravel or Rails, they typically just use an int or a serial, where it's ever increasing whenever you add a new row, so the first row will be 1, 2, 3, 4, 5, etc. I like to use UUIDs because they are globally unique, they prevent ID iteration attacks, and they also hide the size of the database from the users. Now most of these columns or fields here are just using standard data types, but I do want to touch upon our timestamp columns here, because they are going to be using this attribute here with timezone, and you could specify the timestamp without having this with timezone, so just say timestamp not null, which would simply just store the timestamps as UTC. Now we specify with timezone, we also internally store it as UTC, but it will have a timezone modifier so that the client accessing the database will have the timestamp adjusted to whatever timezone they're in, and what this means is that if I'm here in Europe creating a record at let's say 1pm, and then another user logs into the database, let's say from New York, then they would see this record being created relative to their timezone, so negative 4 or 5 hours, instead of 1pm. Both options are totally valid, they have both pros and cons. If you opt for going without the timezone, it's well suited for applications that are only in the same timezone, whereas if you specify with the timezones, it's really good when you have multiple users in different timezones, or if you need to account for data at saving times, the most important thing is just that you're consistent. So choose one option and then stick with it, so you don't mess up later on, because you can always, let's say if you go without the timezone modifier, you just need to modify the timestamp yourself, and that's a totally reasonable way to go about this. We are going to stick with timezone here for the block, so we do have this modifier if we need it. For the rest of the fields, it's really straightforward, we use VARCHAR for our title, file name and slog, we use text for excerpt, and the difference between this one and this one is basically that text can grow, whereas VARCHAR can only have 255 characters. We could go less than this, we could go 155 characters. The main important part here is that we only give it as much space as we think it's going to need, since once we have set the text field here, Postgres will automatically allocate enough space to have this field for each row. So if we say for the title, if we are only going to be using 100 characters each time, it will still allocate 255 characters, so it's just important to be aware of this when you create your database schema, so that your database don't grow in size unnecessarily. For the rest of them, we just use a boolean, set the default true, and then for the read time we just use an integer. So pretty straightforward, the down version we simply just delete the table, nothing that complicated going on here. So now we can jump out and we can go and say, Goose, please apply all of the up versions that we have in our migrations directory to this database where you can connect using this connection string. So very similar to how we created a migration, but just with a little, little small difference that we now specify the up version. So now we run up and we now have this table in our database. If we were to go down again, we made a mistake, whatever, we can just run down and now it's deleted. But we need it. This is how the table is going to look. And now we're basically ready to start adding all of the database interaction layer. And next episode, we're going to be focusing on that, where we're going to be adding all the Go code to start interacting with our database. We're going to be adding the configuration for using SQL C so that we can start pulling out records and creating records in a database from our application.