#100DaysOfWeb in Python Transcripts
Chapter: Days 61-64: Database migrations
Lecture: Automatically detecting schema changes
0:00 Our goal is to have Alembic look
0:01 at our SQLAlchemy classes in our database
0:04 and make any changes that it needs to make
0:07 in order to migrate our current database schema
0:10 to be in sync with our class schema
0:12 our SQLAlchemy schema.
0:13 Now, in order to do that, we need
0:14 to tell Alembic a little bit more.
0:17 Here, we've already told it how to connect
0:19 to the database and we need to connect Alembic
0:21 to the other side of the story
0:23 our SQLAlchemy classes.
0:24 So, with things like our bill and whatnot.
0:27 So, we're going to go over here to this env.py
0:30 and we're going to set the target metadatas.
0:32 It says if you want to run auto-generate
0:34 and trust me you probably do
0:36 you're going to need to something like this here.
0:39 Okay, now there's a couple of things we have to do.
0:40 We have to import all of our models
0:42 and then we can set the metadata.
0:45 So, that should be easy because we've already set our code
0:49 over here, to have this, all models.
0:51 Of course, we named it like this.
0:53 So, let's import it like so.
0:55 We'll come over here and we'll say
0:57 From billtracker. Close but no.
1:00 from billtracker.data.models import *
1:04 Remember, the SQLAlchemyBase has to quote
1:06 see all of these and then we can say
1:09 from billtracker.data.modelbase import SqlAlchemyBase
1:15 And then finally, this is .metadata.
1:17 Whoo! And, this comes up as a warning but you
1:20 actually do not want that to go away.
1:23 Saying this stuff should be up at the top.
1:25 I'm kind of okay with that.
1:26 Here we go, everything's good.
1:28 So, we've imported all of our models.
1:30 We've imported the base.
1:31 And then we're going to say the meta base.
1:33 Metadata is that metadata.
1:35 That tells SQL out coming about all the models.
1:38 We've already told it about the database
1:39 so we should be able to connect those two.
1:41 Let's do that now.
1:43 So, remember, we still have this pending change
1:45 and if I try to run it, it's still not working.
1:48 Nope, there's still this last_payment.
1:50 Let's copy this.
1:51 So, the goal is to add that, right?
1:53 Let's go over to our terminal here.
1:55 And, we can run a new command.
1:56 alembic revision --autogenerate.
2:03 That's the nice part. And we can give it a comment.
2:05 So, the message will be
2:06 adds bills.last_payment column.
2:10 Something like that. Now we run this, it looks over it
2:13 and it says, we detected a new column was added
2:15 and a new index on that column was added
2:18 and it's generated this file, right there.
2:22 Now, that hasn't made any changes to the database.
2:25 Again, if we still run it.
2:27 Over here, it still doesn't have that
2:29 but we're almost there.
2:31 So, let's look at this and see what this is about.
2:33 So, what it's done is said
2:35 there's no previous migration that we've known about
2:38 but this is a new migration called this.
2:40 As we add additional ones they'll have, you know
2:42 this value will appear here and so on.
2:45 So, it says, if we're going to do an upgrade
2:46 we're going to go to the bills table
2:47 and we're going to add that column
2:49 and we're going to create an index.
2:50 If you want to roll this back
2:51 which is totally an option
2:53 it will drop the index, drop that column, great.
2:55 So, now that we have a change, we can apply that
2:59 to the database.
3:00 So in order to actually get the database
3:02 in the right shape. To use Alembic to fix our code, fix out system
3:06 so that it will run again, we say simply
3:08 alembic upgrade and then we can say, just
3:11 upgrade all the way, head.
3:13 To this, it says, we're running an upgrade from nothing
3:16 to this, uh, 219.
3:19 And, now we can ask Alembic, current
3:22 and it should say, your current version
3:23 is the latest, it's that.
3:26 Beautiful! Did it do the fix?
3:28 Is our code going to run again?
3:30 Oh, it's running, it's running!
3:32 Let's see, if I click here, what do we get?
3:34 Yes! It fixed it.
3:36 We no longer have that missing column error
3:38 we have our app running, and it's working again.
3:40 You can see it's reading lots of these bills right here.
3:42 Including that other column that was missing.
3:44 So, we've used Alembic and its auto-generating changes
3:48 to take our database and keep it in sync.
3:51 Let's add one more silly little change.
3:53 Let's just go over here, I'll just add another column.
3:56 Let's call it other.
3:57 sqlalchemy.Column, just go like that.
4:00 Then, let's say, default, 0.0
4:03 just so it doesn't crash.
4:04 All right, now again, we don't have the others
4:07 so let's go one more time through this.
4:09 What do we do?
4:10 We say alembic, we're going to pull up some stuff here
4:13 and we're going to generate a new one
4:14 and this will be new, other column.
4:16 See, it's found the other column, bills.other.
4:19 And, now we can apply.
4:20 If we ask, current, it says that this one
4:22 but it no longer says that it's head.
4:24 So, no we're going to ask to upgrade.
4:26 It's going to upgrade it to the new other column
4:29 and now again if I ask current now, is this other one
4:32 and that's the head. If we run it again
4:35 yes, it still works.
4:36 And, by the way, if I ran it in the middle
4:38 no, it would not work, it would be crashed.
4:40 And, here you can see we have out new other column.
4:42 Depends on the previous version.
4:43 Here's it's new one, and so on.
4:45 So, that's how Alembic works.
4:47 Takes a look at our SQLAlchemy classes
4:50 looks at our database and then it does it's best
4:52 to keep everything in sync.
4:53 These changes, these files here.
4:55 You can edit this, right?
4:57 This is now frozen in time.
4:59 It'll be stored in source control
5:00 and if you want this to operate differently
5:02 or do other work as it's making these upgrades
5:05 and downgrades, just write the code right there
5:07 and you'll be all set.