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.
- Author
- Tom Gobich
- Published
- Mar 05
- Duration
- 7m 6s
Developer, dog lover, and burrito eater. Currently teaching AdonisJS, a fully featured NodeJS framework, and running Adocasts where I post new lessons weekly. Professionally, I work with JavaScript, .Net C#, and SQL Server.
Adocasts
Burlington, KY
Transcript
Altering Tables with Migrations
-
[music]
-
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/ ]
-
Introduction
-
Fundamentals
-
2.0Routes and How To Create Them5m 23s
-
2.1Rendering a View for a Route6m 29s
-
2.2Linking Between Routes7m 51s
-
2.3Loading A Movie Using Route Parameters9m 17s
-
2.4Validating Route Parameters6m 6s
-
2.5Vite and Our Assets6m 38s
-
2.6Setting Up Tailwind CSS9m 5s
-
2.7Reading and Supporting Markdown Content4m 32s
-
2.8Listing Movies from their Markdown Files8m 51s
-
2.9Extracting Reusable Code with Services7m 4s
-
2.10Cleaning Up Routes with Controllers4m 52s
-
2.11Defining A Structure for our Movie using Models9m 38s
-
2.12Singleton Services and the Idea of Caching6m 11s
-
2.13Environment Variables and their Validation4m 16s
-
2.14Improved Caching with Redis10m 44s
-
2.15Deleting Items and Flushing our Redis Cache6m 46s
-
2.16Quick Start Apps with Custom Starter Kits6m 28s
-
2.17Easy Imports with NodeJS Subpath Imports8m 40s
-
-
Building Views with EdgeJS
-
3.0EdgeJS Templating Basics8m 49s
-
3.1HTML Attribute and Class Utilities6m 9s
-
3.2Making A Reusable Movie Card Component10m 24s
-
3.3Component Tags, State, and Props4m 53s
-
3.4Use Slots To Make A Button Component6m 56s
-
3.5Extracting A Layout Component5m 13s
-
3.6State vs Share Data Flow2m 59s
-
3.7Share vs Global Data Flow6m 7s
-
3.8Form Basics and CSRF Protection6m 13s
-
3.9HTTP Method Spoofing HTML Forms3m 3s
-
3.10Easy SVG Icons with Edge Iconify7m 57s
-
-
Database and Lucid ORM Basics
-
4.0Configuring Lucid and our Database Connection4m 3s
-
4.1Understanding our Database Schema9m 35s
-
4.2Introducing and Defining Database Migrations18m 35s
-
4.3The Flow of Migrations8m 28s
-
4.4Introducing Lucid Models5m 43s
-
4.5Defining Our Models6m 49s
-
4.6The Basics of CRUD11m 56s
-
4.7Defining Required Data with Seeders11m 11s
-
4.8Stubbing Fake Data with Model Factories13m 48s
-
4.9Querying Our Movies with the Query Builder15m 30s
-
4.10Unmapped and Computed Model Properties3m 24s
-
4.11Altering Tables with Migrations7m 6s
-
4.12Adding A Profile Model, Migration, Factory, and Controller2m 57s
-
4.13SQL Parameters and Injection Protection9m 19s
-
4.14Reusable Query Statements with Model Query Scopes8m 11s
-
4.15Tapping into Model Factory States9m 15s
-
4.16Querying Recently Released and Coming Soon Movies4m 59s
-
4.17Generating A Unique Movie Slug With Model Hooks7m 59s
-
-
Lucid ORM Relationships
-
5.0Defining One to One Relationships Within Lucid Models5m 49s
-
5.1Model Factory Relationships2m 54s
-
5.2Querying Relationships and Eager Vs Lazy Loading5m 17s
-
5.3Cascading and Deleting Model Relationships5m 16s
-
5.4Defining One to Many Relationships with Lucid Models6m 56s
-
5.5Seeding Movies with One to Many Model Factory Relationships5m 24s
-
5.6Listing A Director's Movies with Relationship Existence Queries8m 41s
-
5.7Listing and Counting a Writer's Movies8m 41s
-
5.8Using Eager and Lazy Loading to Load A Movie's Writer and Director5m 18s
-
5.9Defining Many-To-Many Relationships and Pivot Columns9m 48s
-
5.10Many-To-Many Model Factory Relationships4m 50s
-
5.11A Deep Dive Into Relationship CRUD with Models18m 5s
-
5.12How To Create Factory Relationships from a Pool of Data13m 55s
-
5.13How To Query, Sort, and Filter by Pivot Table Data9m 47s
-
-
Working With Forms
-
6.0Accepting Form Data12m 15s
-
6.1Validating Form Data with VineJS9m 29s
-
6.2Displaying Validation Errors and Validating from our Request7m 16s
-
6.3Reusing Old Form Values After A Validation Error2m 3s
-
6.4Creating An EdgeJS Form Input Component5m 28s
-
6.5Creating A Login Form and Validator5m 1s
-
6.6How To Create A Custom VineJS Validation Rule9m 7s
-
-
Authentication & Middleware
-
The Flow of Middleware7m 49s
-
Authenticating A Newly Registered User4m 14s
-
Checking For and Populating an Authenticated User2m 10s
-
Logging Out An Authenticated User2m 24s
-
Logging In An Existing User6m 54s
-
Remembering A User's Authenticated Session6m 55s
-
Protecting Routes with Auth, Guest, and Admin Middleware5m 36s
-
-
Filtering and Paginating Queries
Join The Discussion! (0 Comments)
Please sign in or sign up for free to join in on the dicussion.
Be the first to Comment!