Run Database Migration

[00:00:00]
>> Brian Holt: So now we have a schema. A schema in and of itself isn't useful, it's just a definition of something. We need to go make something where it's actually like useful. So in this DB I'm going to create another file and I'm going to call it, what do I call it, index, yeah, index.ts. OK, here we're actually going to go set it up, so we're going to import Neon from @neon-database/serverless.

[00:00:31]
Import Drizzle from drizzle-orm/neon-http, there's a couple of different ways to do this. I did it over the HTTP one. This is the easiest to set up. There's also like a web socket way of doing it. Not every environment supports web sockets. That was one thought I had, and the other thing is that, yeah, this is just like, this works in every environment. And you have to like give it a web socket implementation, but this does not support transactions, which most people at some point will get to the point where they want to use transactions.

[00:01:12]
You can do that with the Neon serverless environment, it's just you have to switch to web sockets instead of HTTP. OK, I'm going to say import star as schema from @/db/schema. And import. Dot. No I'm doing this again, the reason why is like, now you can import this from anywhere of like a script or something like that, and it just always comes ready to go. And here we're going to say const SQL equals Neon.

[00:01:59]
Process.env.DATABASE_URL. We're going to have to do our assert trick here as well. So DB equals Drizzle. And then you give it the serverless driver. So here you could say like const results equals await SQL and select one, something like that, right? This SQL library allows you to run SQL statements, so we're just passing that to Drizzle. SQL and then schema like this. And now we're going to export default DB.

[00:02:48]
And now we can use this anywhere. Let's go ahead and assert. Process.env.DATABASE_URL. You need a database URL. Again, you might be wondering why do we have to do this in multiple places. The only reason is that now these become independently able to be imported. It's just mad that it's not sorted again. I can. Why is this? Why are you like this? Here we go. Questions so far?

[00:03:44]
So now we have this DB client, we can import this anywhere in our codebase and make database queries using Drizzle. But as of yet, we don't actually have anything in our database. We've created the schema, but we haven't applied it, right? So we're going to do. I'll just bring up the. Yeah, we'll do it here. We're going to do npx drizzle-kit generate. OK, it's going to look at our Drizzle config, I think it tells you that.

[00:04:24]
Yeah, it's like I looked at this Drizzle config, and I saw that you have some stuff in there that I don't have anything for, in this case it has nothing, so it creates everything for us. And if you go look in your folder, you see that you have Drizzle here now. We didn't write that, right? That just got generated. And you can see now it created the migration force, in this case, it's just creating something.

[00:04:52]
And then here it's adding some altered tables so that it adds the foreign key. Yep, that's it. It then creates a journal of what's been applied of the snapshot, blah blah blah, this is stuff that you should never modify by hand, this is just stuff that Drizzle will keep track for you. Now, again, we still actually haven't ran anything. If you go look at your Neon database and look at your tables, and we look at our public schema.

[00:05:24]
Nothing in there. So we have the SQL statements ready to run, but nothing has run them. So what do we have to do? Well, we come back over here and we say npx drizzle-kit migrate. So now it's run that. It's found all of the migrations that we have not run yet, and it runs them for us, or applies them, it applies the migrations. So the good news is like, let's say you have 30 migrations, you create a 31st one, and the first 30 have already been applied, it'll know that the first 30 have been applied and it'll just apply the one, right?

[00:06:05]
It's good at finding the diff. And the way that it does that, it has its own Drizzle schema, it keeps track of what ones have been applied and which ones have not been applied. OK. Again, you should never modify this directly. This is managed by Drizzle, and it's just saying like, I have seen these migrations and I have run these migrations and I have not run these other ones.

[00:06:25]
The other nice thing that it does for you is like, and the reason why you don't want to modify your migrations by hand is that it does this hash, so if you modify it by hand, it's going to be like, I don't know what this migration is, I'm not going to run it and just blows up, right? So make sure that you're running the same migrations across every computer. So, in other words, never modify migrations by hand.

[00:06:49]
And please tell your LLM coding assistant to not modify the migrations by hand. I've seen this way too many times. So I'm just, put that in your prompts. Do not modify the migrations by hand. Only use Drizzle. OK, but some of the story is we now have an articles table, ID text, slug, content, image URL published author ID created at, updated at, and this refers to the user sync table as well, so you can like, you can see them together, which is cool.

[00:07:27]
Good questions so far? So I like just doing the generate and migrate, but like if you're rapidly iterating and you're like trying different schemas and rapidly, like you don't want to like be deleting and unapplying migrations quickly, there is a feature called drizzle-kit push, which is basically like, hey, take what I have and push it, don't care about migrations, just YOLO let it go.

[00:07:48]
That's helpful if I'm like trying a bunch of different schemes quickly, but I always forget how to get back in the state of like, it's like properly migrated because it doesn't keep track of that in the migrations. So just so you know, it is out there, and I always have, I always have to look up, how do I get out of this situation that I put myself into.