Database Migrations

[00:00:00]
>> Speaker 1: We all know what version control is, right Git, right We all use Git or some version of that, okay Databases have their own version control They're called migrations, and they suck They're really bad I'll just tell you right now they're really bad

[00:00:00]
About two years ago, it finally clicked for me how migrations actually work and like I finally understood them This was two years ago I've been writing code for 15 years It was two years ago it finally clicked for me, okay, so maybe you're a genius or maybe I'm slow or maybe it's both

[00:00:00]
But they suck, but I'm gonna make it really easy for you, okay I simplified it I'll bring it down to my simple level of how migrations work, when you use them, and why to use them So basically, migrations are how you version your database

[00:00:00]
You might ask, why do I need to version my database Well, let's think about what a database is It's a contract It's essentially a contract like, "Hey, I promise that all my data is gonna look like this Promise, guarantee it go ahead Build your apps, do whatever you want

[00:00:00]
I promise it's always gonna look like this." And the clients are like, "You sure?" And you're like, "Yeah, yeah, I promise, for sure." So they go build their apps And then you come back and you're like, "Actually, I'm gonna change the shape to look like this now." It's not that big of a deal

[00:00:00]
I know this was this, but I'm gonna change it, and then all the clients are like, "But wait, we already made all these changes to look like this That was the contract Why did you change it?" Oh, because I wanted to So, do we have to do it

[00:00:00]
Yeah, we have to do it Okay, well then, how do I make sure that my app doesn't break Because I'm expecting it to look like this and you're telling me it looks like this Do I just need to fix that on my end and do if statements and figure out the history

[00:00:00]
That would be gross So, no, definitely not That's where migrations come in migrations' job is to migrate the old state of a database to the suggested state of a database Whatever state of database that you're suggesting, whatever that change is, not only do you need to migrate the schema—the contract—you're also trying to migrate the data that's already there, that's abiding by the previous contract, over to this new contract

[00:00:00]
So it's a schema migration and a data migration That's where this gets really bad, okay But essentially, the workflow goes like this Let's assume you already have a database out in production with data and people are using it First, you'll make schema changes in the code

[00:00:00]
You'll create some migration files, either by hand or through some tool Your ORM might allow you to do that You might write raw SQL or something in between You should inspect the generated plan or SQL before attempting to apply it, like a PR

[00:00:00]
Before you merge this code into the main database, you want to review it and make sure it looks good Then you want to run the migrations against the database—merge it and migrate all those changes over, all the index changes, all the data that now needs to be changed because you've got a new contract

[00:00:00]
Then you want to deploy that in production, but you probably want to test it outside of production first That's typically how most people would do it I'm not doing it like that That's not how I do it Most people do that workflow, but there are ways around this

[00:00:00]
There's no way of avoiding it entirely, but there are definitely ways to get close to zero where you have to do what I just described Without migrations, we lead to inconsistencies As soon as the database version bumps up, your app is expecting something else

[00:00:00]
When your code looks at the data it's getting back from the API, it's gonna break—whether on the server querying the database or on the client requesting from the server, somewhere it's going to break I promise you, your code is not generic enough not to break

[00:00:00]
Schema migrations are where you change fields on objects and arrays—tables, columns, rows You want to add a new index, rename something, add a new field, remove a field, change the type of a field The key here is not all changes require a migration

[00:00:00]
There are destructive and non-destructive changes If you can find a way to only do non-destructive changes, your life will be so much easier Non-destructive changes would be adding a new optional field A destructive change would be renaming a field, which is actually deleting a field and adding a new one with a different name

[00:00:00]
Instead, you could add the new field with a different name, make it optional, migrate the code to use this optional field in another PR, and then slowly migrate the data away from the old field to the new field This causes no downtime Data migrations occur when schema migrations affect your data

[00:00:00]
If you add a required field, rename a field, or delete a field, you'll need to migrate data The scary part about migrations is you're messing with real people's data, and if you mess up badly without backups, there's no recovering it We're using Drizzle over Prisma because, in my opinion, it's easier to write in TypeScript than learning a new language

[00:00:00]
Prisma has its own DSL based on GraphQL, and it's not flexible for migrations Drizzle allows pure TypeScript, no DSL, no code generation, with predictable queries Here's a sketch of our data with five tables PK means Primary Key, Foreign Key associates the relation

[00:00:00]
We have relationships between users and habits (one-to-many), habits and entries (one-to-many), and a many-to-many relationship between habits and tags using a separate habit_tags join table.