Building Data-Driven Web Apps with Flask and SQLAlchemy Transcripts
Chapter: Database migrations with Alembic
Lecture: More database changes
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Let's make one more change so we can see the changes chaining together as you will appear as we are going. So let's add a new table.
0:09
Let's suppose we want to do some auditing like, this user logged in or whatever. So we're going to go copy and paste one of these model files.
0:18
It's usually the easiest, so we'll just say audit. And what we'll want to do is just tell it, your name is Audit
0:27
your table name is Auditing, or something like that and id and date is probably good and then description, for now that's good enough.
0:35
You know, we probably want the user, the IP address the action, things like this but, for now
0:41
just for this example, this is going to be more than enough. So we want to have Alembic detect this table's added and create it.
0:51
Now you need to be really careful. If you run this app now, run the web app it's going to actually
0:57
SQLAlchemy will see this table doesn't exist and create it. But if you want to make sure that Alembic is managing it, don't run the app
1:03
or it's already going to exist and it'll think there's no changes, everything is totally in sync. And this gets even a little trickier
1:10
in debug mode for Flask because Flask will notice this file had changed and it will restart the process.
1:16
And the restarting the process will generate that table. So be super, super careful about the web app running.
1:22
Basically, have it off if this is something that you want to record into Alembic, right? Maybe there's relationships between
1:29
the auditing table and the user's table and you want to make sure that also gets set up right like, foreign keys and whatnot.
1:35
So just turn off the web app, or the auto reload may actually create it which Alembic will not then detect it. All right so let's go over here.
1:43
You can ask the Alembic what is your current version? It'll say it's this one which is actually the head, the latest.
1:51
Now let's go and then ask it to autogenerate adding the auditing table. Now this is not going to work, I don't think. Maybe, maybe not, let's see.
2:03
So we run it, it's generated the file, no doubt. But let's see what's in it. We go in here, notice it says pass. Well, why did it say pass?
2:13
Let's double check over here that our table does not exist. Nope, no table. So, is Alembic broken? SQLAlchemy broken? No. What's going on?
2:25
Remember our convention here, in this __all_models this is how we're telling SQLAlchemy here, we're telling it all the models
2:33
that we're going to be using exist in here. So we're just going to go and have to add it. Don't forget, when you add a new one of these
2:41
always do something like that. Okay so this actually did nothing, let's delete it. And we'll just generate it again.
2:48
All right, so current is still this, generate the vision. Now it's generated it, let's see what we got. There we go, now we got something meaningful.
2:58
So it says that revision is the one it created and its down revision or the one that needs to be run before it is a 55, etc., etc.
3:08
What it's going to do is create a table it's going to create a bunch of columns in there it's going to create a primary key, and an index.
3:14
And to get rid of it, it drops the index and drops the table, okay? So it's super simple. Now though, if we go over here to our terminal
3:23
and we ask Alembic current, it's still the same version but that's no longer the head. So we want to get the new one.
3:31
We can easily do that again by just going, upgrade to head. Run that, we ask what current is. It's the new one, now it's head.
3:41
And of course, over here in the database if we refresh, we now have our auditing table exactly like you would expect. How cool is that?
3:49
So Alembic let's us just keep evolving our database; however, our SQLAlchemy classes evolve we're just going to make sure the database
3:57
does that same thing, which is pretty cool. And again, I want to make sure this is in version control so let's right away just do an Add
4:04
so we don't forget to commit it. We definitely don't want to lose one of those. All right, well, now we can just keep going
4:10
with this process, over and over. Remember, adding classes, which add the add models, which add the tables don't forget to add them to all models
4:18
in our convention or find another convention and, you know, just keep saving the changes. Now, there's one other little utility
4:25
I want to make sure you have access to. We're not going to use it, but it could turn out to be helpful.
4:30
We'll talk a tiny bit about it in the concept videos. So over here I'm going to drop a file called Alembic Helpers
4:35
and it just for now has one item, but you can have more. So basically it gives you a function that let's you answer the question
4:43
does a particular table have a particular column? And you can see it's using the reflection from the engine to go look at it, and things like that.
4:53
Now go look at the tables, and the idea is that you know, maybe over here one of these revisions you would like to ask
5:00
let's pick the other one where we have a column you'd like to ask, I only really want to do this operation if the column doesn't exist.
5:07
Maybe you want to just ask first, like hey, does this column exist? Then I'm going to add it. Because what happens if a column does exist
5:14
and you try to run this, it's going to crash. It's going to go, ah, I can't create the column it already exists, et cetera, et cetera.
5:21
So, you know, some little utilities like this can go a long way to make your migrations a little more durable and fault tolerant.