Building Data-Driven Web Apps with Flask and SQLAlchemy Transcripts
Chapter: Database migrations with Alembic
Lecture: More database changes
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.