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