Building data-driven web apps with Pyramid and SQLAlchemy Transcripts
Chapter: Database migrations with Alembic
Lecture: Concept: Alembic - the manual version
0:00 We've seen there are two ways
0:01 to make Alembic do changes 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
0:14 so you can see what the whole set
0:15 of steps are and appreciate a little
0:17 bit better what the SQLAlchemy version
0:19 the AutoGen version is doing for you.
0:22 If we're going to make a change
0:23 we're going to record our revision.
0:25 The manual way we're going to get started
0:27 by creating the pieces by running
0:29 Alembic revision and then give it a comment
0:31 -m, add key words to the column.
0:34 This is important, it's going to show up
0:35 when you apply it. It's also going to show up in the file name
0:38 that stores it. So, we do that and it'll generate some
0:41 hash and then a file name friendly version of your comment.
0:45 Done. Then we go and open that file and we see
0:48 it looks like this. It has a revision, the first one will have no
0:50 down revision, no relationship, but as you
0:53 build upon them, of course, they do.
0:56 And then it has an empty upgrade
0:58 and an empty pass. So, your job in the manual version
1:01 is to actually implement those things.
1:03 Okay, that may or may not be something
1:05 you want to do. It's not too hard.
1:07 Here's another one we can do.
1:09 We can come and say we'd like to do op
1:11 add column to the packages table and just passing
1:15 a column like you would in your SQLAlchemy class, right?
1:18 I say, the column, keywords, string
1:20 nullable, and so on, then downgrade of course
1:23 is drop column. 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
1:28 double check 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.
1:38 And that won't be great.
1:39 Similarly for dropping it.
1:40 I talked a little bit earlier about
1:42 the alembic_helpers.
1:43 There's this function that I got called
1:46 table has column.
1:47 And give it a table and a column
1:50 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
1:56 or not that it's 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
2:04 the same place. The way Alembic runs these
2:08 it's a little bit funky.
2:09 So, you got to do this weird import
2:11 to get it, but once you do then you can ask
2:13 questions like, if the table doesn't have
2:15 a keyword's column, then add it.
2:18 Things like that.
2:19 So, this may be helpful to you.
2:20 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 create table
2:29 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 the database
2:37 that had revision 708 and we upgraded a 689.
2:41 And that was adding the keywords column.
2:44 Now, it you ask Alembic.current
2:46 it'll say, we're on that 689 version
2:48 that we just upgraded to and by the way
2:50 that is the latest.
2:51 And of course, as we saw, this version
2:53 is stored in your database so you have
2:55 an Alembic.version and you go do a query
2:57 on it, you'll see 689, the one column
3:00 with one row that is just that string.
3:02 Super easy to check.
3:03 Might just open it up in your tools
3:05 and going what version is this in?
3:07 Even if you can't Alembic directly on it.