Do you need to alter your database? Migrations provide a great way to do this, but the approach you'll need to use differs depending on what's already in production. In this lesson, we'll discuss two different flows you can use to alter your database. One, when working completely locally and another when a migration has already been executed on your production database.

👍 If You Enjoy This Video, Consider Hitting The Like Button!
🥁 Subscribe To Stay Notified For New Lessons
📺 View on YouTube
👨‍💻 Find The Code For This Lesson Here

📚 Chapters:
00:00 - Intro
00:46 - Altering A Local-Only Column
02:05 - Altering A Column Already In Production
04:30 - Adding A New Column To A Local-Only Table
06:28 - Adding A New Column To A Table Already In Production
07:22 - Seeding A New Column With Data After Migrating
09:52 - Outro

📜 Transcript:

0:00

in this lesson we're going to talk about

0:01

the flow of migrations and how that

0:02

differs whenever you're working with

0:03

just a local database versus a database

0:06

that already has production data on it

0:07

so the reason that this differs is

0:09

because whenever you roll back

0:10

migrations that's going to get rid of

0:12

any tables columns or whatever within

0:14

your database that that migration

0:15

changed as well as any data that that

0:18

table column or what have you might be

0:21

storing and it's very vital that you

0:23

never run the risk of accidentally

0:24

purging production data from your

0:26

database so let's say both our roles and

0:28

users migrations have already run on

0:30

production we would not want to make any

0:32

changes to either of these migrations

0:34

and instead create a separate migration

0:36

to alter either of these two tables if

0:38

we needed to so in this lesson we're

0:40

going to take a look at that in

0:41

particular so first let's start with the

0:43

easy stuff and say that we're still just

0:45

working locally for example if we wanted

0:47

to go about adding in a default value to

0:49

our role ID column here at present point

0:52

in time and we can confirm this by

0:53

taking a look at our database structure

0:55

the column default for our role ID is

0:57

null so if we do not explicitly set this

0:59

whenever you user is created it's just

1:01

going to be set to null and at the

1:03

database level we can set that via a

1:04

default to value and then specify a

1:07

value for this we can do roles off of

1:08

our enum and specify it as member now

1:11

since we're working locally and this

1:12

migration hasn't actually hit a

1:14

production database we're free to go

1:15

ahead and make the change directly

1:17

within our user's migration and in the

1:19

way that we would go about getting this

1:20

change actually applied to our database

1:22

is by jumping into our terminal here and

1:25

running node Ace migration and you can

1:27

run refresh to rowback and then run

1:29

again or you can actually roll back and

1:32

then node Ace migration run individually

1:35

so refresh would just run both of those

1:38

commands within one go but there you can

1:39

see it individually so we rolled back so

1:41

we reverted both of these migrations and

1:43

then we just re-ran them so that would

1:45

pick up that default to Value change

1:47

that we applied to our role ID so now if

1:49

we go take a look at our column default

1:51

for our roll ID let's give it a refresh

1:53

here back into the structure you can see

1:55

now it's set to one so that change did

1:57

get applied successfully just by

1:58

changing our existing migration however

2:00

if we were to undo that change roll back

2:02

and rerun so now we're back to that

2:04

default value being null and now let's

2:06

say that this has already run in

2:08

production so our users table and our

2:10

roles table already exist within

2:12

production at that point in time we

2:14

would not want to make that alteration

2:15

directly within this migration file and

2:17

instead we would want to create a

2:19

separate migration file to make that

2:21

change so we can jump back into our

2:23

terminal here and run node Ace make

2:25

migration to create a separate migration

2:28

and you can name this whatever you've

2:30

deemed applicable I'm going to call it

2:31

users underscore add underscore role ID

2:36

default and then we can also specify the

2:39

table that this should get applied to so

2:40

we can do hyphen hyphen table equals and

2:43

then specify it for our users and what

2:45

that will do is if we actually jump into

2:46

that migration file here it will default

2:49

the table name to users and give us an

2:51

alter table up and down method call so

2:54

if we contrast that with our previous

2:55

two migrations these were set with

2:57

create table by default this one will be

2:59

set to alter table and now you might be

3:02

thinking okay so all that I need to do

3:04

is reassign that column within this

3:07

alter and then reapply the default to or

3:10

whatever change it is that you want to

3:12

make and you should be okey dokey to go

3:14

ahead and just rerun your migration so

3:16

let's go ahead and try that out and see

3:18

what happens so node Ace migration run

3:20

and you'll see that we're greeted with

3:22

an error because it's trying to alter

3:23

the table by adding the column roll ID

3:25

with that default value so it's

3:27

attempting to add in another column

3:28

called roll ID and it's running into an

3:30

area because that column obviously

3:31

already exists from our previous

3:33

migration so if I was adding in an

3:35

additional column that did not exist on

3:37

my users table you would just Define

3:38

whatever column it is that you want to

3:40

exist within your database now and it

3:41

would run just fine but if you're

3:43

altering an existing column all you need

3:45

to do is Define what column it is that

3:47

you want to Target so in this case table

3:48

dot integer role underscore ID specify

3:51

that you want to alter that column and

3:54

then make the alteration so now if we

3:55

give this a save jump back into our

3:57

terminal here I'm going to clear that

3:58

out and let's run node migration run

4:00

again that will run successfully because

4:02

now it knows that it needs to alter a

4:04

column instead of creating a column so

4:06

if we jump back into our database here

4:08

refresh you can see now our default is

4:10

set back to one just as we'd like it so

4:12

that's the approach that you would want

4:13

to take if you're working with

4:15

migrations that have already run on

4:17

production because had we made the

4:18

change within our users migration we

4:20

would have had to have rolled back and

4:21

then rerun the migration in order to

4:22

pick that change up which would have

4:24

purged our data from this user's table

4:26

and obviously we don't want to delete

4:27

all of our users so next let's take a

4:29

look at the use case where maybe we want

4:30

to add in an additional column so for

4:33

example on our roles table here let's

4:35

say maybe we wanted an additional

4:36

description column or something of the

4:38

sort again if we were working locally

4:40

all that we'd need to do is table that

4:42

string description give it a limit and

4:45

set it to nullable or not nullable again

4:47

the default is going to be nullable so

4:49

if you want it to be knowledgeable you

4:50

can just leave that off and then we

4:51

would need to roll back so node Ace

4:54

migration roll back now let's pause here

4:57

for a second and take a look at our

4:59

Adana schema here let's take a look at

5:00

the actual data within it and let's note

5:02

the batch column here so our role and

5:05

users tables were both created within

5:07

batch one whereas our user roll ID

5:09

default value was created within batch

5:11

two remember a batch is incremented

5:13

every time that you run so whenever we

5:15

first ran that was creating our role and

5:17

users table and then whenever we second

5:19

ran that was adding in our default roll

5:21

ID value and the rollback command we'll

5:24

just go back a single batch so if we

5:26

were to just roll back by the default we

5:28

would just be undoing our default rule

5:29

ID setting if we take a look at the help

5:31

options for this you can see that we

5:33

have within here an option to specify

5:35

the batch number that we want to go back

5:36

to so we could do node Ace migration

5:40

rollback hyphen hyphen batch and you can

5:42

see in the a note here use zero to roll

5:44

back to the initial state so we could do

5:46

zero so in order to run all the way back

5:48

we can specify 0 for that and now we'll

5:49

undo everything there is an alternative

5:51

command that you can do for this as well

5:52

and that is migration reset that will

5:55

just roll back all the way to zero so

5:57

you can take advantage of both of those

5:58

options if you don't need to go all the

6:00

way back then you would want to use

6:01

migration row back and then specify the

6:03

specific batch number that you want to

6:04

go back to within your Adana schema

6:07

batches back to the point at hand though

6:09

in order to pick up the change of adding

6:10

our description onto our rules migration

6:12

here we would need to go ahead and just

6:14

node Ace migration run to recreate all

6:17

of these and now one last thing to note

6:19

here is now if we refresh our Dyna

6:20

schema all of these are within batch one

6:22

because now they're all created within

6:24

the same run but if we take a look at

6:26

our rules now we have a description

6:27

within here as well again let's go ahead

6:30

and run through the use case of that

6:31

being production so let's do node Ace

6:33

migration roll back get rid of that

6:36

change that we added to our roles

6:37

migration and now let's create another

6:39

migration to actually apply that change

6:41

because now we're taking a look at what

6:43

this use case would look like if roles

6:44

had already run within our production

6:46

database so node is make migration and

6:49

we can call this roles underscore add

6:52

description or whatever you feel is

6:54

applicable there hyphen hyphen table

6:56

roles and let's dive into there so since

6:59

it does not exist when they're in a

7:00

database all that we need to do is

7:01

Define it so table.string description

7:04

255 nullable and that should do it so

7:07

now we should be good to go ahead and do

7:09

node Ace migration run so now if we

7:12

refresh our roles and you'll see that

7:14

our description was added in despite it

7:16

being all the way in the back it does

7:17

indeed exist let's go ahead and do node

7:19

Ace migration roll back again and now

7:22

let's say that our roles already exist

7:24

within our production database we have

7:25

our member and our admin and now we want

7:27

to add the description column but we

7:28

also want to seed that with some data

7:30

for both our member and our ad so we can

7:32

make use of this defer again to do that

7:35

async DB and how you do this is up to

7:38

you and again this might look forward

7:39

since we haven't covered querying or

7:41

inserting or anything on the sort yet

7:42

beyond what we did within our previous

7:44

migration lesson but let's go ahead and

7:46

just await DB from this dot table name

7:50

dot where ID is roles Dot and let's

7:54

specify admin first I suppose and then

7:56

we can call update description and say

7:58

our

8:00

application

8:02

super user and we can do await DB from

8:05

this table name where ID roles dot

8:10

member update description our

8:13

application

8:15

default user and now after the

8:17

alterations made on a Rolls table to add

8:19

in the description column it will run

8:21

this with the fur method which will then

8:23

query our admin record and update it

8:25

with a description value and then it

8:27

will do the same for our member as well

8:29

and then lastly would also be good to

8:31

fill in our down method So within our

8:33

down method all that we would want to do

8:34

is undo whatever we're doing with an up

8:37

we don't need to worry about getting rid

8:38

of what we did within our this dot defer

8:40

method since getting rid of the column

8:42

as a whole would get rid of that value

8:43

there as well so we can do table dot

8:46

drop column and specify it to drop our

8:49

description we can do the same for our

8:51

default value as well this would look a

8:53

little bit different since we need to

8:54

alter it so table dot integer row ID dot

8:59

alter

9:00

default to

9:02

null so since we don't want to get rid

9:04

of the role ID column altogether we

9:06

still want it to exist and it would be

9:07

our user's migration that would get rid

9:09

of it all together if we needed to all

9:11

that we want to do within this migration

9:12

is just alter it back to having a

9:14

default value of null should this

9:16

migration get rolled back so now we can

9:18

give this a save I'm going to check node

9:19

Ace migration status to see where we

9:22

left off looks like none of them have

9:23

run so we can clear that and then run

9:25

node Ace migration run all right now

9:28

that all of those have run let's verify

9:30

that our roles now have a description

9:32

value so let's jump back into our roles

9:34

and there we go we can see our

9:35

application default user and our

9:37

application super user for our member

9:39

and admin have been populated now let's

9:41

go ahead and try roll back it's a node

9:43

Ace migration

9:45

roll back and all of those successfully

9:48

reverted so it looks like both of these

9:50

ran without a hitch as well so the

9:52

important takeaway from this lesson is

9:54

if you have tables or migrations that

9:57

have already executed within your

9:58

production environment don't alter those

10:00

migrations at all create separate

10:02

migrations to make those alterations

10:04

tables if you're working locally and

10:07

nothing has hit production yet then

10:08

you're A-Okay to make whatever changes

10:10

you need just roll it back rerun them

10:12

and you'll be good to go

10:14

[Music]

10:22

thank you

10:24

[Music]