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 to make Alembic do changes to our database. We have the SQLAlchemy class based changes
0:09 and just straight up manual changes. I didn't show you the manual changes but I did tell you about them. I'll show them to you now
0:15 so you can see what the whole set of steps are and appreciate a little bit better what the SQLAlchemy version the AutoGen version is doing for you.
0:23 If we're going to make a change we're going to record our revision. The manual way we're going to get started by creating the pieces by running
0:30 Alembic revision and then give it a comment -m, add key words to the column. This is important, it's going to show up
0:36 when you apply it. It's also going to show up in the file name that stores it. So, we do that and it'll generate some
0:42 hash and then a file name friendly version of your comment. Done. Then we go and open that file and we see
0:49 it looks like this. It has a revision, the first one will have no down revision, no relationship, but as you build upon them, of course, they do.
0:57 And then it has an empty upgrade and an empty pass. So, your job in the manual version is to actually implement those things.
1:04 Okay, that may or may not be something you want to do. It's not too hard. Here's another one we can do. We can come and say we'd like to do op
1:12 add column to the packages table and just passing a column like you would in your SQLAlchemy class, right? I say, the column, keywords, string
1:21 nullable, and so on, then downgrade of course is drop column. This is pretty straight forward but it can be a little bit tricky.
1:28 One thing you may want to do is double check that this column does not exist before you try to add it. If it does, this migration will fail.
1:35 And everything that depends upon it will subsequently fail as well. And that won't be great. Similarly for dropping it.
1:41 I talked a little bit earlier about the alembic_helpers. There's this function that I got called table has column. And give it a table and a column
1:51 it'll say yes, I have it. So, it'll go and do some funky introspection on the table and tell you whether or not that it's there.
1:58 The way you import it is a little weird. Because where we run the code from and where it lives is not exactly
2:05 the same place. The way Alembic runs these it's a little bit funky. So, you got to do this weird import to get it, but once you do then you can ask
2:14 questions like, if the table doesn't have a keyword's column, then add it. Things like that. So, this may be helpful to you.
2:21 We could also ask alembic current. By default it'll just say nothing which means there's no version. Although it does when you run that create table
2:30 just there's no version put into it. And then we say upgrade and it's going to apply whatever upgrade. So, in this case, we ran it on the database
2:38 that had revision 708 and we upgraded a 689. And that was adding the keywords column. Now, it you ask Alembic.current
2:47 it'll say, we're on that 689 version that we just upgraded to and by the way that is the latest. And of course, as we saw, this version
2:54 is stored in your database so you have an Alembic.version and you go do a query on it, you'll see 689, the one column
3:01 with one row that is just that string. Super easy to check. Might just open it up in your tools and going what version is this in?
3:08 Even if you can't Alembic directly on it.