Altering Tables with Migrations

In This Lesson

We'll learn how to alter our database tables using migrations both while in development, where we can delete our data, and once our data has already hit production where we need to persist all data.

Created by: @tomgobich
Published: 1 year ago

Join the Discussion 2 comments

@christoxz
3 months ago

In case you already using ace migration:refresh- —seed like me, you would face an error with current code, this is because fake_seeder will run before start_seeder, because it will run in alphabetic order.
You can solve this by renaming start seeder to 00_start_seeder.
Tom will implement the same solution is lesson 4.15.
Hope this tip might save someone some time, because I lost some :D

2
1. Responding to christoxz
  
  @tomgobich
  3 months ago
  
  Terribly sorry you lost some time on that christoxz!! I've written down a note to circle back to lesson 4.8, where we introduce the fake_seeder, and add a note about the seeder order of execution.
  
  1

Transcript

So every once in a while, project scopes change, additional things come up out, and sometimes you just forget stuff. For example, we had defined on our database
schema, if I pull this back over, on our cast movies a sort order. Well, it would probably be great to also have that on our crew movies, so that we can specify
a specific order for the crew as well. However, if we take a look at those migrations, so if we dive back into our database directory, migrations, take a look at crew movies,
we don't have a sort defined here, and we don't have one defined on our cast movies either, despite it being on our diagram. There's two approaches that we could take to add this order
into both of our tables. We could either add it into the migration as it exists right now, roll back, delete everything inside of our database, re-migrate to
rebuild up our database with those columns added in, or we could create a separate migration, keep all of the data that we have inside of our database,
and then just migrate forward again to add that change into those tables. Which approach you use depends on where you are in your application cycle.
If you already have production data inside of your database, the only way that you want to go forward from there on is forward. So we would want to
create an additional migration to add the change into our database. If we're just working in development as we are here, it doesn't hurt to delete everything
that we have inside of our database and just rebuild it up again, because none of that is production data. We don't need to keep it, it's just fake data. So we're going to cover both
of those approaches. So first, let's add it into the migration as we have it currently. So to do that, since the down method is just dropping the table, we don't
need to worry about that in any way. All that we need to do is add it in. So for our crew movies, let's go about adding it specifically into the migration here.
So let's do table, integer, sort_order, and let's specify this is not nullable and default it to 0. Remember,
since these are both pivot tables, we don't have models for them, so we don't currently have to make this change inside of a model, too. It's just here within our migration
that it currently exists. So we can save this, and now in order to apply this change into our database, what we would want to do is dive back into our terminal
and let's remind ourselves what we have available. So let's do node.ace.list, and underneath migration, we could roll back and then rerun, or we could run
refresh to just roll back and then re-migrate. So let's do that one. So let's do node.ace. migration.refresh. That will roll back our migrations
one batch and then re-migrate that batch. In this case, a batch is all of our tables. If we go ahead and dive back into PgAdmin, right-click
on our Adonis 6 database and refresh it. Scroll down to our tables, and that was our crew movies that we were working with, so we'll take a look at the columns there, and there is our new
sort_order column. It's been applied. Awesome. So that's how we can go about changing our database structure while we're still in development. You just make the change, roll back,
re-migrate, and it'll be applied, but it will also delete all of your data. So next, let's cover how we can approach this if we want to keep all of the data
inside of our database, because now all of our tables are empty. So let's first run node.ace.dbseed to reseed our database to get data inside of there.
Whoops. Looks like cannot define released_at on movie_model since it is not defined on the model property. Okay. Looks like we forgot to define that on the model
itself. So let's scroll back up and take care of that. So movie, scroll down here. Sure enough, it's not there. So we'll do @column, declare, released_at,
date, time, and that's also nullable, I believe. And we also want that to be converted into a lux and date_time, so we'll do date_time there.
But we don't want it auto-created or auto-updated. We want to manually specify it, so we'll leave those out. Okay, cool. Let's jump back into our terminal. It
completed our start seeder, but it failed on the fake seeder. So let's go ahead and just try to run our fake seeder again. So node.ace.dbseed - - files
= database/seeders/fake/seeder. Hash is not a function. Okay, so I did update to the latest packages,
which is the first time I've updated this since the release of AdonisJS, version 6. So I bet you something changed here. Let's take a look at our user model. Hash
- - has the matching signature. Ah, we need to provide that as a function. So let's take care of that, just like so. That should make that happy. There we go.
So now, hash.use needs to be returned back as a result of a callback function. So let's hide that back away. Hide our terminal back away. Hide our text editor back away.
And let's try that one more time. Hey, there we go. Okay, so now we have data back inside of our database. Let's say we want to keep that data in there, but still make a
change to our database. So let's clear our terminal out. That's where we would want to create a new migration to make the change to the database itself. So here we can do node.ace
makeMigration, and we'll give this a name to say its purpose. So we'll say alterCastMoviesAddSortOrder
and we'll do - - alter to tell the ACLI that we're creating this migration to alter our database. Hit enter there, and we
have now created our migration, alterCastMoviesAddSortOrder. Okay, they automatically added alter on there, but - so now we have alter, alter, but that's fine.
Let's jump back into our text editor. We haven't run it yet, so I'm just going to fix the name real quick. It's good to know that it will prefix that with alter. We'll jump back over to here.
Just get rid of one of those two. There we go. And let's update the table name to castMovies, and within the up method, we want to make
our change, and then within the down method, we want to revert that change. So what we're looking to do is add in a column. So just as we have so far with our
migrations, we could do table.integer to find our sortOrder column, and just like we did with the crew movies, we'll do not nullable
and default to zero there. So that takes care of our up method. Now in the down method, we want to revert that. So we'll do table.dropColumn
to now delete the column out of the database, and we want to specify that we want to drop the sortOrder column. Cool. So in the up, we're going
forward and creating our sortOrder column, and in the down, we're going backwards and deleting the sortOrder column from our castMovies table. Awesome. So we can give that
a save, jump back into our terminal, and run node.aceMigration.run. We only have forward to go. We don't need to worry about going back.
So we can just run it here, and there we go. It's now run our migration. So if we jump back into pgAdmin, scroll up, refresh our Adana 6 database,
scroll back down, and take a look at our castMovies table columns, we should now see sortOrder here as well. Awesome. Additionally though, if we
right-click our Adana schema, let's view data, query all rows, there we go. If we scroll down to the bottom, once our toast goes away, we'll see that our batch
integer is now incremented for this new alter castMovies add sortOrder table migration from 1 to 2, meaning we now have
two different batches. So whenever we run the rollback command now, so nodeaceMigrationRollback, by default, we're just going
to go back one batch. So that's just going to undo our alter castMovies migration. So we hit enter there, there we go. It just undid that
one. nodeaceMigrationRun again, and it will reapply it. If we were to want to go back all the way, we could do nodeaceMigrationRollback
- - batch =, and then we can either specify 1 or 0 to go back all the way, and if we were to run that, it would delete everything out of our database.
So we'll go ahead and bypass that for right now. [ austen rieger https://austenrieger.com/ ]

Let's Learn AdonisJS 6

113 Lessons

14h 15m

Introduction

Fundamentals

Building Views with EdgeJS

Database and Lucid ORM Basics

Lucid ORM Relationships

Working With Forms

Authentication & Middleware

Filtering and Paginating Queries

User Watchlist

User Profiles

Admin Panel

Stop losing your best ideas!

Use our integrated note feature to capture and organize your key insights. Take a note at any timestamp and jump instantly back to that moment in the video with a single click.