Building Data-Driven Web Apps with Flask and SQLAlchemy Transcripts
Chapter: Database migrations with Alembic
Lecture: Concept: Alembic - the manual version
0:00 We've seen there are two ways to make Alembic do changes
0:03 to our database.
0:05 We have the SQLAlchemy class-based changes
0:08 and just straight-up manual changes.
0:09 I didn't show you the manual changes
0:11 but I did tell you about them.
0:12 I'll show them to you now so you can see
0:15 what the whole set of steps are
0:16 and appreciate a little bit better
0:17 what the SQLAlchemy version, the autogen version
0:21 is doing for ya.
0:22 If we're going to make change
0:23 we're going to record a revision
0:25 the main way we're going to get started by creating the pieces
0:28 by running alembic revision
0:30 and then give it a comment -m add keywords to the column.
0:34 This is important.
0:35 It's going to show up when you apply it
0:36 it's also going to show up in the file name
0:38 that stores it.
0:39 So, we do that and it'll generate some hash
0:41 and then a file name friendly version of your comment.
0:45 Done. Then we go and open that file
0:47 and we see it looks like this.
0:48 It has a revision, the first one will have no down revision
0:52 no relationship but as you build upon them of course
0:55 they do and then it has an empty upgrade
0:58 and an empty pass.
0:59 So, your main job in the manual version
1:01 is to actually implement those things.
1:04 That may or may not be something you want to do.
1:06 It's not too hard. Here's another one we can do.
1:09 We can come and say we'd like to do the op.add_column
1:12 to the packages table
1:14 and just pass in a column
1:15 like you would in your SQLAlchemy class, right?
1:18 sa.Column, keyword string nullable and so on.
1:21 Then downgrade of course is drop column.
1:24 This is pretty straight forward
1:25 but it can be a little bit tricky.
1:27 One thing you may want to do is double check
1:29 that this column does not exist
1:30 before you try to add it.
1:32 If it does, this migration will fail
1:34 and everything that depends upon it
1:36 will subsequently fail as well and that won't be great
1:39 so I'm going to leave for dropping it.
1:40 I talked a little bit earlier
1:41 about the Alembic helpers.
1:43 There's this function that I got called table_has_column
1:47 and give it a table
1:49 and a column and it'll say yes, I have it.
1:51 So, it'll go and do some funky introspection
1:54 on the table and tell you whether or not that is there.
1:57 The way you import it is a little weird
1:59 because where we run the code from
2:02 and where it lives is not exactly the same place
2:05 so the way Alembic runs these
2:08 it's a little bit funky
2:09 so you got to do this weird import to get it
2:12 but once you do, then you can ask questions
2:14 like if the table doesn't have a keyword column
2:17 then add it, things like that
2:19 so this may be helpful to you.
2:21 We could also ask Alembic current.
2:23 By default it'll just say nothing
2:25 which means there's no version
2:27 although it does when you run that
2:28 create the table, just there's no version put into it.
2:32 And then we say Upgrade
2:33 and it's going to apply whatever upgrade
2:35 so in this case, we ran it on a database
2:37 that had revision 078 and we upgraded a 689
2:42 and that was adding the keywords column.
2:44 Now if you ask Alembic.current
2:46 it'll say we're on that 689 version
2:48 that we just upgraded to
2:49 and by the way, that is the latest.
2:52 And of course as we saw
2:53 this version is stored in your database
2:54 so you have an Alembic.version
2:56 and if you go do a query on it
2:57 you'll see 689
2:59 the one column with one row
3:00 which is just that string
3:02 super easy to check
3:03 you might just open it up in your tools
3:05 and go what version is this in
3:07 even if you can't run Alembic directly on it.