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