Introducing and Defining Database Migrations
In this lesson, we'll introduce database migrations. We'll learn what they are, why they're great to have, and how we can create and use them to define our database tables and their columns.
- Author
- Tom Gobich
- Published
- Feb 24
- Duration
- 18m 35s
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
Introducing and Defining Database Migrations
-
[music]
-
So we have our schema defined as a PNG.
-
We know what tables we want.
-
We know what columns we want those tables to have.
-
What we need to do now is take this schema definition
-
and turn it into an actual database with full tables and columns defined.
-
The way that we do that is through migrations.
-
Migrations provide a way for us to create tables and their columns
-
in a way that we can easily build them up and tear them back down
-
so that as we're developing our application, if we build them up,
-
we find out that something's slightly incorrect,
-
we can tear it back down, make the change, and then build it back up again.
-
This build up and tear down system not only allows us to easily make changes
-
as we need to while we're still developing out our application,
-
but it also allows for others outside of the realms of our computer
-
to clone down our project, spin up a database on their own machine,
-
build up the database so that they have access to work with the application
-
on their system as well.
-
Okay, so here's our schema, right?
-
We have our various tables and their column definitions.
-
What we need to do is first define the order with which we need these tables
-
to be defined in, and the relationships are what dictates that.
-
In order for our users table to exist, we first need our roles table to exist
-
because the users is dependent on a particular role being defined.
-
The same thing is applied with our watch lists.
-
In order for that to be defined, we need both our user and movie table
-
because it's dependent on that relationship data.
-
I'm going to go ahead and condense our schema down.
-
Looks like it won't let me go any smaller than that.
-
That's unfortunate.
-
Okay, let's drag this over slightly then,
-
and we'll work here on the left-hand side of our screen.
-
I'll go ahead and pull this in so that we're not overlapping.
-
Okay, cool. Let's clear out our terminal,
-
and we can get started by making our migrations.
-
So node, ace, list to see the available commands.
-
We're going to be making something, so we'll look underneath the make section.
-
Within here, we should see under the M section here, make migration,
-
which allows us to create a new migration file.
-
As we saw a couple of lessons ago,
-
our migration files will be held within our database migrations directory,
-
and they're going to use a natural sort, which we saw in the configuration.
-
Now, the natural sort is going to prefix the file name for the migration itself
-
with the current timestamp when the file was created.
-
So that's going to allow us to order all of our migrations
-
in the order with which we created them.
-
And we'll allow our schema here to dictate the order
-
by following our relationships to determine requirements for a particular table.
-
So for the most part, the way that we have this defined,
-
we can start from the left-hand side of our schema and work our way right.
-
The one exception is going to be our synists.
-
Although it is technically a little bit more left,
-
we will need to define that before we define our crew movies and our cast movies
-
because both of those tables rely on the synist ID.
-
Therefore, it relies on their synist table being defined.
-
Now, one important distinction, and the reason why our tables are reliant
-
on each other based on the relationships
-
is because we're going to be defining foreign keys for the relationships itself.
-
Foreign keys are essentially a data integrity protection system
-
to where we cannot refer, say, within our users table
-
on a role ID that doesn't actually exist in the role table.
-
So we can't assign a user a role that doesn't actually exist in the roles table.
-
Same thing with movie statuses, same thing with our crew movies
-
and cast movies and our watch lists as well.
-
The actual relationships that we have defined with these IDs
-
needs to exist in the related table itself.
-
So we would actually have to have a user with an ID of one
-
if we want to create a record in our watch lists with a user ID of one, for example.
-
So we'll work our way from left to right here, starting with our roles migration.
-
So within our terminal, we can go ahead and clear this out.
-
We can run node ace make migration roles.
-
Hit enter on that, and that's going to create a file for us within database migrations.
-
There's that timestamp that we referenced for the current date and time
-
with which we actually created this migration file.
-
And then it's going to call the migration create roles table
-
because we're going to be creating the roles table specifically with this migration.
-
So it's defining the actual purpose for the migration here as well.
-
Now let's actually pause here for a second because whenever we created
-
our web structure application with Adana JS,
-
it started us out with a users migration.
-
I don't think we've taken a look at it yet, but we will here now.
-
So let's go ahead and dive back into our project here, dive into our database directory.
-
So here's our create users table migration and our create roles table migration there underneath it.
-
As our schema has defined, our roles are going to need to be created prior to our users table being created
-
because the users table is reliant on that relationship for the role ID existing
-
before we actually create the users table as a whole.
-
So we need our roles to run before our users.
-
So we're going to need to adjust the timestamp so that the natural sort of this migration
-
lists these files in the order with which we want them to run.
-
So we can copy the timestamp for our create roles migration right here.
-
Just give that a copy. We can adjust our create users table migration.
-
We just increment that by one.
-
And now our create roles table migration is prior to our create users table migration.
-
Now let's actually look at the actual underlying content of the migration itself.
-
You can see that we're exporting a default class that extends a base schema here.
-
We have a protected property of the actual table name for the table that we want to create with this migration.
-
Then we have an up method. This is in charge of the build up that we mentioned earlier.
-
And then we have a down method, which is in charge of the tear down that we also mentioned.
-
The up method is where we'll be creating tables or altering tables with things that we want forward looking.
-
So it's the changes that we want to apply and have stick to our database.
-
The down method then is in charge of undoing whatever we do in the up method.
-
So if we create a table in the up method as we are here, we want to drop that table in the down method,
-
essentially allowing us to build it up as well as tear it back down.
-
And note that we didn't type any of this out.
-
This is how AdonisJS will start our migration for us whenever we create it.
-
So we're actually already the vast majority of the way there for our roles migration.
-
If we take a look at the underlying schema here, our roles migration has an ID, a name created at and updated at.
-
Whereas our migration itself already has an ID and a created at and updated at.
-
This increments column type is the column type where our database will automatically apply an ID for us whenever we insert into the database.
-
As we mentioned, whenever we're going over our scheme as a whole.
-
So for our IDs, we'll always be using increments here because that will allow for the ID itself to auto increment.
-
And we can almost essentially ignore the ID altogether whenever we're creating our records.
-
If we take a look at the table property, we have a number of different column types that we can define using this chainable method tree here.
-
If we take a look at our schema here, we want our name to be a Varchar with a max character length of 50.
-
So as we discussed within the last lesson, Varchar is essentially just a string.
-
So we can define this as a string type with the column name of name and the max character length here of 50.
-
I'm going to go ahead and crunch this down a little bit so that we can see both our schema and our text editor here at the same time.
-
And we'll hide our explorer tree away by hitting control or command B.
-
I don't have this actually defined on our schema itself, but we also want this name to be not nullable,
-
meaning the underlying value that we insert into the database cannot be null.
-
It needs to have some form of a value.
-
So we can do that by chaining off of our string here in not nullable method, just like so.
-
Cool. So we are actually done with our rules table migration.
-
We can now move into our users migration, which AdonisJS created for us whenever we created our project.
-
So hit control or command B to open up our file tree again, and we'll jump over to the users file.
-
OK, so they started us out with a couple of different things.
-
We have an increments for our ID of not nullable.
-
We actually want this increments to be not nullable as well as our created at an updated at.
-
So we'll do not nullable on those there as well so that we force each of these columns here to have a value.
-
OK, cool. Let's go ahead and jump over to our create users table migration.
-
Now, AdonisJS started us out with this file whenever we created our project.
-
So we do want to change a little bit of it.
-
So we already have our ID, our full name, our email password created and updated at.
-
But we are missing our role ID as well as our avatar URL.
-
So we'll go ahead and add those in now. This is just a preference.
-
But what I like to do is always put my IDs up at the top.
-
So I'll put my table dot and then we can do integer because the ID for our roles table is an integer.
-
Give it a column name of role underscore ID.
-
Now, you might also note that we are using the snake case naming strategy for our database columns.
-
This is the default for AdonisJS. And there's a reason why we're doing it,
-
because it will make it a little bit easier to integrate directly with our models whenever we come time to introduce those.
-
In addition to being an integer, our role ID is also unsigned because by default, increments will define the ID as unsigned.
-
Unsigned essentially means that it just can't be negative.
-
So it's going to take all of those out of the equation and just focus on positive numbers.
-
And then we can add a foreign key onto our role ID by doing references.
-
And you can either do this all within the references. So we could do roles dot ID to say that our role ID references the roles table and the ID column inside of there.
-
Or you can just specify the column name here and then chain off of this in table roles, whatever your preferences.
-
So we'll stick with roles dot ID here. And we also want this to be not nullable. All users need to have a role.
-
Lastly, we can also define a default value for this as well if we wanted to.
-
So that whenever we create a user, they're automatically assigned the particular role that we define.
-
So we can do default to and we'll just set that to one. Next up, we have our avatar URL.
-
We'll keep this in the same order that we have it here within our schema. So we'll place this underneath our full name.
-
This will just be a table string and we define the column name as avatar underscore URL.
-
And we can use the default length there for that column. This one will be nullable.
-
We're not going to require a user to have an avatar URL much the same as we're not going to require them to have a full name.
-
So we now have this migration ready to go as well. But before we move on from it on our email column,
-
you'll notice that it also has chained off of it unique. This is going to require each row within our users table to have a unique value inside of the email.
-
So if I were to come along to an application and sign up with test to test dot com, and then later on at some point in time,
-
you were to come along and try to sign up with test to test dot com.
-
This unique constraint would prevent you from actually being able to sign up because the email that you're trying to insert would not be unique.
-
So it's going to throw an error for that particular case. So we have our users ready to go there.
-
Now, instead of doing watch lists, we're actually going to skip this altogether so that we can circle back to it later on in the series.
-
But we would not want to insert this at this point in time working left to right because it also requires our movie ID to exist.
-
So we would have to have our movies table created prior to our watch list table being created.
-
So instead of jumping over here, we instead want to jump down to our movie statuses as our movies requires our movie statuses.
-
So it's our next entry point. So we jump back into our terminal here and run node ace make migration movie underscore statuses.
-
We can enter on that and that's going to create a movie statuses table migration.
-
While we're here within our terminal, let's go and just create out the rest of the migrations that we're going to need.
-
Again, noting that we're going to skip our watch lists and save that for later on in the series.
-
So we'll have our movies table next to node ace make migration movies.
-
OK, node ace make migration cynists.
-
And the next we could pick either crew movies or cast movies. It doesn't really matter too much because they both rely on the same data.
-
So node ace make migration. We'll do crew movies first, I guess, because it's on top.
-
Hit enter on that and then node ace make migration cast movies just like so.
-
So we can clear that out and we're ready to go ahead and continue populating the migration columns for these tables.
-
So we'll open our Explorer back up and jump down to movie statuses migration.
-
Within here, all that we need to do is add in a not nullable name.
-
So table string name, set the max length of that to 50 characters and set that to not nullable.
-
Same to with our ID, our created at and our updated at.
-
Again, noting that our down is already ready to go because it's just going to drop the table,
-
which will take care of deleting all of our columns in addition to our table as a whole.
-
All right. Next up is our movies. So we'll jump down to that one next.
-
This one has a little bit more going on. So we have a status ID, writer ID, director ID.
-
Well, uh oh, look at that. Our writer ID and director ID is on the movie as well.
-
So we actually need our cynists table to exist prior to our movies table existing.
-
That's no real biggie. So all that we need to do is open back up our file explorer.
-
I'm going to expand this out a little bit so that we can actually see the file names here.
-
We'll go ahead and copy our create movies table timestamp right here.
-
And we'll alter our create cynists table timestamp.
-
So we'll just paste in the copied value from our create movies table migration,
-
just as we did with our users and our roles. And let's subtract one from it.
-
That'll move our cynists above our movies. And now we have the correct order.
-
So instead of taking care of our movies migration next, let's instead jump into our cynists.
-
Hopefully that flow is starting to become a little bit more clear to you.
-
OK, I'll leave it to where we can at least read it a little bit. There we go.
-
OK, so we'll start with our cynists next. So we have a first name, last name, headshot URL that we need to add in.
-
So table dot string first underscore name max length. We'll do 100 characters.
-
And that's going to be not nullable because we're going to need a first name and a last name in order to actually show them.
-
So we'll do table string last name, 100 characters, not nullable there as well.
-
Not nullable on our ID. And we'll copy that and apply that to our created at and updated at there as well.
-
We also need the headshot to table string headshot URL.
-
And that's a max length 255. So we can leave that as default. And that one can be nullable.
-
So we'll note that there as well. We're not going to require them to have a headshot URL.
-
We can have a default image there in the cases where they don't.
-
Now we're ready to jump over to our movies. So let's jump down to our movies migration next.
-
And here we have quite a bit going on. So we have our status ID, writer ID, director ID, as well as a bunch of columns specific to this table.
-
So we'll start out with our IDs. So table integer status ID, that's going to be unsigned so that we don't allow negative numbers.
-
And in case you were wondering that unsigned is also needed in order for our foreign key to work.
-
Both the column types have to match. So our status ID needs to match the same type as our movie statuses ID in order for that foreign key to be created successfully.
-
Therefore, the unsigned is required because increments will assign the column type as unsigned itself.
-
OK, so we're going to have this references our movie underscore statuses dot ID just like so.
-
And we also want this to be not nullable for right now. We'll leave a default out of here, but we could assign a default if we wanted to by doing default to just as we did with our roles.
-
Let's do our writer ID next. So table integer is going to follow much the same as our status ID.
-
We'll have a writer underscore ID that'll be unsigned references our sinists dot ID.
-
And we'll have that be not nullable there as well. Every movie has to be written by somebody just as every movie most likely will need to be directed by somebody.
-
So we'll have our director ID that too, just like all the others is going to be unsigned and it's going to reference our sinists dot ID.
-
This will give us a great example where we have two different columns referencing the same table. We'll make this not nullable as well.
-
OK, then we move into all of the actual columns specific to a movie. So we have our title.
-
So we'll do table string movie title. That'll be 100 characters, not nullable. And then we have the slug. So table string slug.
-
We'll want that to be double just in case because we're going to generate a slug from our title and you never know.
-
It could be every other character needs escaped. So we'll make that double just in case. And this will be not nullable to now, just like with the file names that we're currently working with.
-
Our slug is going to uniquely identify a movie inside of our database.
-
So just as we are with our email, we also want this to be unique as well so that we only have one particular slug for any one movie inside of our database.
-
OK, cool. Then we have our summary. So string summary max length of that is 255.
-
So we just leave that out, not nullable. And I'm going to set this to default to just an empty string.
-
So we'll make this optional. And if it's not provided, then we'll just default it to an empty string as opposed to making it nullable as a whole.
-
Next, we have our abstract. That one's a little bit different. This one is a type of text, which is a long form string.
-
For that, we have a text column type here that we could define within our migration.
-
And just like the others, we just provide it a column name, which in this case is abstract.
-
And we'll leave that one as is. Then we have our poster URL. So table, we're back to string poster underscore URL.
-
That one, too, is 255. So we'll leave that as is. We'll set this one here to not nullable to and we'll default it to an empty string.
-
Let's do that exact same thing with our headshot as well on our synapse table.
-
But before we do that, we have one more timestamp to add in. So table timestamp.
-
And this is our release that so released underscore at just like so.
-
This one's going to be nullable because some of these just we don't know when it's going to be released quite yet.
-
And then we'll switch our created and updated that to not nullable here as well.
-
Just like so. Let's hop over to our synapse table real quick and let's switch our headshot URL from nullable to not nullable and set it to a default empty string.
-
OK, cool. So the reason why we're doing this is that it will just simplify our checks just a little bit more in terms of working with our model.
-
OK, so we have our movies table ready to go. And now we're ready to move into our crew movies or cast movies pivot tables.
-
So let's move into those migrations next. So here's our crew movie, I believe.
-
Yep. Crew movie. And we need to bind our synapse ID as well as our movie ID and add in an optional title for the crew member on that particular movie.
-
So do table integer, synapse ID, unsigned references, synapse ID, not nullable.
-
And then we'll have a table integer, movie ID, unsigned references, movies ID.
-
And that one there will be not nullable as well. And then we'll have a table string title with a max length of 100.
-
And we'll do the same thing here, not nullable with the default value of an empty string.
-
And then we'll jump down here and do not nullable on our created and updated as well. And we'll do that on our ID as well.
-
Just for clarity's sake. Since our cast movies is going to be relatively similar, we can go ahead and copy our title, synapse ID and movie ID declarations and jump into that file next.
-
And then we can go ahead and paste that in our synapse ID and movie ID columns will remain exactly the same.
-
However, our title will switch from title to character underscore name and the length of that can be up to 200 characters.
-
Let's also switch these to not nullable as well. So not nullable there, with our created at and updated at there as well.
-
And let's run through real quick and do a sanity check on everything else. So we'll expand this back out.
-
So we're viewing our cast movies, crew movies. Looks good there. Movies.
-
Let's see, we have nullable on our release that everything else looks good there.
-
We'll go ahead and add a not nullable here. By default, the increments here should be not nullable, but we'll add it there for explicit sake.
-
There's our cynists. That one looks good. Movie statuses. That one looks good. Users looking good there too.
-
And finally, our role. Awesome.
-
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! (6 Comments)
Please sign in or sign up for free to join in on the dicussion.
frp
which Queen City is yours? Cincinnati?
Please sign in or sign up for free to reply
tomgobich
Yep, Cincinnati Ohio! :)
Please sign in or sign up for free to reply
nerdysurfer
Tom, I live in OTR. Would be great to grab a beer sometime!
Please sign in or sign up for free to reply
tomgobich
Oh hey, small world! Yeah, that would be cool! :)
Please sign in or sign up for free to reply
nerdysurfer
I sent you a request on linked in. You can also get in touch with me via www.nerdysurfer.com
Please sign in or sign up for free to reply
tomgobich
Alrighty cool, sounds good! :)
Please sign in or sign up for free to reply