Building Data-Driven Web Apps with Pyramid and SQLAlchemy Transcripts
Chapter: Database migrations with Alembic
Lecture: Concept: Alembic - the manual version
Login or
purchase this course
to watch this video and the rest of the course contents.
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.