Purpose of Migrations
Migrations manage the structure and evolution of a database, including tables, columns, and relationships. They allow controlled, sequential changes that can move forward or backward in time.
Why Use Migrations Instead of Manual Changes
Manual schema changes through a GUI become error-prone and unmanageable, especially in team environments. Migrations provide version control for the database and track incremental changes over time.
Sequential and Atomic Design
Migrations are applied in order and should be atomic, meaning each migration performs a single change. This ensures safe rollbacks if errors occur without affecting the entire schema.
Database Schemas and Default Usage
PostgreSQL supports multiple schemas, each containing multiple tables. In most cases, development occurs within the default public schema.
Creating a Migration File
A migration is generated with a descriptive name and timestamp for ordering. The file includes separate up and down sections to define forward and rollback behavior.
Defining the Articles Table
The migration creates an articles table with a UUID primary key, timestamp fields (created_at, updated_at), and text-based fields for title, excerpt, and content. Explicit constraints such as NOT NULL are applied for data integrity.
Use of UUIDs and Timestamps
UUIDs are used instead of auto-incrementing integers to prevent predictable IDs and improve security. Timestamps with time zones ensure accurate tracking of events across systems and regions.
Rollback Strategy
The down migration drops the table, enabling a clean rollback to the previous state. This reinforces safe schema version control.
Applying the Migration
Running the migration updates the database schema and sets the current version. With the schema in place, development can proceed with writing queries and generating application code.
We've talked about models and we talked about queries in part one. But now we're actually going to be wiring everything up. And to do that, we will need migrations. The TLDR of migrations is that they are used to manage the design and structure of a database. That means which table there are, what columns these tables contain, and how different tables relate to a job.
My graces are applied in sequential order so that we can go back and forth back and forth in time. All right, short version. Now let's look at our longer explanation. Let's take a little bit further. In PostQuest, we can have multiple schemas and each schema can hold many tables. Most of the time we will just be working with the default schema called public. Now,
Technically, we could just use our database GUI and manually create tables and columns and all of that, but that gets messy really, really fast, especially when we work in a team. That's why in real world projects, we use migrations. They represent the evolution of our database over time, and we have one small control change at a time. Each migration is applied in order.
Mistakes can happen. So it's really important to keep these migrations atomic, meaning that each migration should only do one thing or only change one thing. If we are changing multiple tables, it's important that we try our best to split it up into multiple migrations so that if anything goes wrong, we can undo a migration and go back to a place of safety without, you know, nuking our entire schema.
So our first migration will be the migration to create the articles table. So we just use our just command and say, just create my, no, create, create, just create migration, create articles table. And I'm giving the name here a descriptive naming so we can visually see what the migration does. So this will be the name of the SQL file that will be generated. And you can see we have a new SQL file on the database migrations.
where we have a timestamp so we can also do the sequential ordering. Great. Now let's look at this generated file. This is what Goose has generated for us. We can see we have some comments and then we have a select up and a select down SQL query. And we have Goose up, statement begin, statement end, Goose down, statement begin, statement end. And this is simply so that we can tell Goose to either migrate forward or migrate
back in time. So to create our table, we're gonna say create, table if not exists, articles. And in here we're gonna say ID, UID, not null. Gonna say primary key is ID. Then we need two columns to handle the time that something was created and then the time something was updated. So we're gonna say create it at,
to be timestamped with time zone, not null. Grab this and then create a updated add with again timestamp with a time zone and also not null. Then let's create a title. There's gonna be a VARJAR. There can only be a hundred characters long. Again, not null. Also we have an excerpt that can be VARJAR 255. That can also not be null.
255 is the default. We technically don't need to specify it. But again, I like being explicit. So it's nice to see exactly what is going on. Finally, we need to have the content field. That's just going to be a text field. So here, in the op statement, we're going to create the table. We are going to be using an ID of type UUID as the primary key instead of an auto incrementing integer.
Apart from UIDs being unique across systems, UIDs also make our records a lot harder to guess. This means that we cannot, our user cannot guess the next row in line. So let's say we had used an auto incrementing ID instead of the UID, and we had articles be one, two, three, four, five. The user could technically
potentially pull out data that they are not supposed to because they can get the next row. With UIDs, this becomes practically impossible since each ID is random and unpredictable. For the timestamp, we use this timestamp with time zone to make sure we don't lose track of when things happen and also when they happen across regions or servers. Finally, we have the title, we have the excerpt, and we have the content where we use two types of text
data types, where the VARJAS have a specific size and the text can basically just grow as much as you want, really in reason, but at least enough for our use case. Now, we also need a way to go back. So we need to go down here and say drop table, if exists, exists articles. Now, technically, we also don't really need this if exists since
we should really be able to run this down migration if something does not exist at this point. But it's just again being explicit and following some best practices. So the down statement is simply just delete the table and we are back to square one, which is our starting point from this state. Now we can jump out, clear and say just of migrations. And we can see we applied, they create articles migration and we
also can see that Goose specifies the version that we are currently at. Now, with this in place, we can start writing our queries and have SQL C generate the Go function and actually write our first problem model.
