Check out a free preview of the full API Design in Node.js, v4 course:
The "Migrations" Lesson is part of the full, API Design in Node.js, v4 course featured in this preview video. Here's what you'd learn in this lesson:

Scott runs a migration that syncs the database structure with the Prisma schema. A migrations directory is also created which stores every executed migration. The benefit of having migration files is the ability to track them with source control and ensure every developer is aware of changes made to the database.

Get Unlimited Access Now

Transcript from the "Migrations" Lesson

>> Let's talk about migrations. So migrations are basically something that relational databases need to do in order to, they serve two purposes really. One purpose is you want to, on a relational database, the database itself actually stores the schema under it because it has to create a table.

[00:00:23] A fixed table that understands the shape of your data. And migration is when you teach your database about the shape of that data. So that's one reason you would do a migration is like, I need to teach my database what my data looks like, so I have to run a migration.

[00:00:39] Another reason you would do a migration, and I think this is where the name comes from, is sometimes if you already have a shape, you already have a schema and you need to make a change to it, let's say a breaking change. A breaking change would include, for instance, if we go look at our schema right now, a breaking change would be, I would come in here and say, now, users need an email and it's required and it's unique.

[00:01:05] Okay, we already have 100,000 users in our database, how is this going to work? If emails are all required, and they're all unique, and none of the users have emails, right, okay, you would have to run a migration. Well, the email was a lot harder cuz you gotta get email from them.

[00:01:22] But if it was something else that you could get, you'd have to migrate the data that you already have in the database out of the database and then back into the database with the new requirements on it. That's why it's called a migration. You're moving the data around back into the database, but you have to reinsert it against this new schema that has different validations on it.

[00:01:45] So that's why it's called a migration, you're already moving the data around. So those are the two typical use cases for migrations. They're painful, they usually cause the most problems in downtime and issues in companies, but Prisma makes it pretty easy for us to do. So, it's really awesome.

[00:02:03] And if you use a NoSQL database, you don't need migrations anyway. So this is mostly for relational databases. Well, let's get started with that because we actually have to make a migration, because we just created a database and we made a schema, but we didn't tell the database about our schema.

[00:02:16] So let's run the initial migration for that. So in order to do that, we actually need to install something. We need to install the Prisma client. I mentioned this a while ago. This is the actual SDK for the database. Prisma, the thing that we installed earlier, was the CLI that we're gonna use to run a migration.

[00:02:34] But inside of our code, we're gonna use Prisma client. This is the SDK, this is the ORM. This is how we talk to the database. So we want to install that. So be sure to install that. And the reason we have to install it now is because the migration is going to generate this SDK for us, because this SDK is based off of our schema.

[00:02:55] So every time we change our schema and every time we migrate, the SDK gets generated again. So if one of you all had a different schema than my schema, then your SDK will look different than my SDK, because it gets generated based off your own schema. So it's pretty cool, not too many MPN packages do that.

[00:03:13] If you and I installed the same version of a package, we're guaranteed to have the same thing, not in this case with Prisma, because it's going to generate based off whatever your schema is. Okay, so I'm gonna install that. Okay, and then you also, we already talked about this, but please make sure that you have database URL in your .env file pointing to your database.

[00:03:36] Whether it's the host database that you got to render or a local database, wherever your Postgres database is, make sure you're pointing to that. The next thing you wanna do is you wanna run the prisma migrate dev command, and you can give it a name flag. And the reason you give it a name flag is because migrations have to be saved somewhere so that you can rerun them later.

[00:04:04] So if we're all sharing the same database and some one person changed the schema and ran a migration, or sorry, if we all have different databases and some one person changed something on the schema, I too need that migration on my database. So hopefully they saved it, so we can give it a name.

[00:04:23] So we can have the migrations have names basically. So we know which migration is doing what, and then we can check them in the Git. And then we can reuse them for development of local staging and production, if that's the way your organization sets it up. So we need to run this command, we're going to npx because Prisma is not installed globally.

[00:04:42] I believe I already ran migration on this database, so either we had to create another one, or I'm gonna run a command that you don't need to run right now. I'm gonna reset my database, but I want you to see it anyway. So I'm going to run npx prisma migrate, I think --reset, let's see.

[00:05:01] Oops, nope, not that, migrate, just reset. Okay, yes, I gotta reset my database cuz I already added some stuff in there, so I reset mine. You don't need to do this. Okay, so now mine is just like yours. So now the command you wanna run is going to be npx prisma migrate dev --name, and you can give it a name.

[00:05:29] I'm just gonna call mine init, for initialize, and hit Enter. Think this should work. I don't know if the reset got rid of the name for me or not, I might have to pick a different name, but you should be good. So what this is gonna do is it's gonna do exactly what I said.

[00:05:44] It's going to sync the schema that we just created to the database that you're pointing it to. So now your database is gonna know about the schema, which for relational database is absolutely necessary. So because this database isn't local, it's running on the cloud, it took a little longer.

[00:05:59] If it was local, it pretty much happened instantly, you don't have any data in there. Once it's done, you'll see right here it says, hey, look, I created a migrations folder. I put a time stamp on it, underscore the name you told me, and then I created a migration SQL file.

[00:06:15] You're gonna actually run migrations in SQL and I'm gonna show you what it looks like, just so you can see how much work Prisma has saved you, I want you to see that. And then other thing here is that it confirms that our database is now in sync with our schema.

[00:06:28] And then this last one is that it generated the client. Remember I told you Prisma's client is generated based off our schema, so it does that every time we run a migration and that's how we had to install it. So if we go look at these migrations now, You can see that there's a migrations folder, has this.

[00:06:46] And then this is what we would have had to write ourselves if we did at Prisma.