Configuring Knex js Migrations for Database Management Scripts

[BLANK_AUDIO] Introducing Next.js. Next.js is a lightweight SQL query builder that makes it easy to create, read, update, and delete data in databases from within a Node.js application. We'll use Next for this purpose in our Express app, but it also has a key feature called migrations. Migrations can be thought of as source control for your database. They allow you to define sets of schema changes that you can apply to any database and bring it up to date, e.g. migrate it to the desired state. By using migrations, we'll keep a running history of all changes to our database in a set of source migration files. Migrations can be used for all database structural changes, such as adding and dropping tables and columns. And the next migration tooling automatically tracks and applies migrations that are set up in the migration files. And so in this lesson, we'll use Next Migrations to create tables in our new Postgres database. To get started, we need to install Next. And we're going to install it both globally and in the local project. So the dash G flag will install it globally. We'll get started with that one. So back on the command line, npm install Next-G. And then we also need to install Next locally, and then we'll install the Postgres npm library. So you can install multiple libraries in one command, just by separating them with a space. So copy that and we'll run that one next. And so in order for Next to be able to connect to a database, it needs some config, such as the host name and password. We're going to store this information in a file called NextFile.js. So we need to create that file in the backend root directory. So I'm going to go over to VS code here. And in the backend root, new file, NextFile.js. And then we'll copy these contents. And we need to use the super user password that we used when we installed Post gres. So make sure to change that. Mine was svelte. Nextclamation mark. Now this file has secrets in it, so you don't really want to check this into source control. If, for example, you're using Git, you would want to ignore this file. So I'll show you a quick feature in VS code. If you're using Git VS code, what has an integrated Git interface. And what you can do is just easily come over to the Git tab, right click, and choose Add to Git Ignore. And then that'll make sure that that file doesn't get checked into your Git repository. Okay. So now that we've got that NextFile set up, it'll allow us to connect to our local database. Then we can start working on migrations. So using migrations is a three-step process. First of all, you run NextMigrateCreate, and that'll stub out basically an empty migration file. Then you'll edit that file, and then you run NextMigrateUp, which will actually apply the migration. So let's get started, and we'll run NextMigrateMakeCreateLunchWeekTable. So from the backend root project here, I think take away the .js. I don't think we actually want that. Okay. So that creates a migration, and it gives it a timestamp, and it puts it in the migrations directory in the backend project. So now we can open it up in VS code. So there'll be a new directory called migrations. And so here's our empty migration file. And the up function is where we define what we want the migration to do. The down function is where we define a rollback/undo action. For this migration, we want to create the lunchweek table. So we'll add a create table migration. And our lunchweek table is going to have a lunchweek ID property, and a week of property, and an is published property. And so this is the migration code to create a table with those three columns on it. The lunchweek ID will be an auto numbering primary key column. And then under the down function, if we wanted to undo this migration, so after we've created the table, if we wanted to then undo it or back it out, we would run a drop table lunchweek table. So go ahead and copy this into the migration file. Take it over to VS code and paste it in. And save that. And then we just need to run next migrate up. So we'll do that from the command line. And so you can see that it says it ran the following migrations. Now, next automatically creates a special metadata table in the database, and that's how it tracks the migrations. And you can actually run them over and over again, and it won't cause any harm. So this is a concept known as item potent code. So doing it once does the thing that you want to do, doing it over and over again doesn't repeat that thing. It's just good for one run. And so it's kind of smart enough to know whether your database is up to date or not. Okay. So we've run that migration and that created the lunchweek table in the local database. And there's a couple more things that you should know about migrations. So the timestamps and file names are important. So that tracking table uses those file names to determine what migrations need to be run. So don't change the file names. And then like we just said, migrations will only be run once. So they're item potent. So if you were to change that, if you were to run a migration and then say you forgot something and you wanted to change something in that file, if you ran migrate up again, it wouldn't work. It wouldn't do anything the second time. And so under normal circumstances, if you need to change something you did in an earlier migration, just create a new one with the changes that you need to make. So this is called rolling forward with a new migration. Okay. So now let's come over to PG admin. So before we had just created our school lunch database, and we didn't have any tables. So now that we've run our migration, if we right click here and choose a refresh, now we can see the two next migration tables and then our lunch week table. And we can right click here and go to query tool. And this is where we can run SQL queries. So we can say select star from lunch week. And then run that query. So when we run that query, we don't have any rows in our new table yet. So we don't get any rows back. But what we want to do next is put some rows in that table. And we'll use those for testing and building our backend API. So we can run an insert statement to insert some test data into that table. So copy this insert statement and take it back to PG admin. Paste that in there. And if you have several statements, just highlight the one you want to run and then run it. And then now if we run this select statement again, now we can see that we've got those five rows there. So we've got some test data on our table now. So if you remember our data model, we have our lunch week, which is kind of an overarching parent record. And then it will have lunch day records underneath it. And so we're going to create a lunch day table. While we're working in the database, we may as well go ahead and create that table. And that table is going to use a foreign key that references a lunch week ID from the lunch week table. And so that'll be a formal relationship. So a lunch day table won't be able to exist without being related to a lunch week table, the kind of the parent to it or the owner of it. So we'll go through these steps again. This time we're going to do next migrate make create lunch day table. So that'll stub out an empty migration file. And then we'll paste this code in there this time. So back to VS code. Here's our empty lunch day migration. And so we're going to create a lunch day table. It's going to have a lunch day ID. So that'll be the local primary key for that table. And then it's going to have a lunch week ID. And that's going to be the foreign key to the lunch week table. Then we'll have a day a day column, which is just the day, the given day. And then we'll have a string column that's the menu details for that day. And then this last bit of code right here sets up the formal foreign key to the parent lunch week table. So it's going to be based on lunch week ID. It's going to reference lunch week ID in the lunch week table. And if the parent lunch week row is deleted, we'll do a cascading delete and automatically delete any child rows on the lunch day table. That way we don't leave orphan data laying around. And then same thing on the down command, we would drop the lunch day lunch day table. So save that. And then we run next migrate up. And that's going to apply the latest migrations any that are missing from the database. And so let's go give that a look in PG admin. Okay, so now we've got our lunch day table and our lunch week table. And that's it for this lesson. So we learned a little bit about next and we created two tables in our database and we seeded the lunch week table with a little bit of data. And so in the next lesson, we're going to take our lunch week in point and where we're returning this hard coded list, we 're going to actually fetch the data out of our new table and we'll return that directly from the end point and get rid of this hard coded list. So that's it. Thank you very much. And we'll see you next time.