Building Data-Driven Web Apps with Pyramid and SQLAlchemy Transcripts
Chapter: Database migrations with Alembic
Lecture: Our first change
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
So, we've initialized Alembic, and everything's working. So, let's go over to PyCharm. I've already opened up this project
0:06
and here you can see, there's our Alembic folder that we've set, and all of these things should go in a source control, so we can
0:12
go ahead and add those straightaway. Alright. So everything's up to date we've added tem in source control, remember:
0:22
we're going to get things starting to pile up in here but first of all, to get started we have to edit alembic.ini.
0:29
There's a lot of stuff going on in here but the part we care about is down here in this SQLAlchemy URL.
0:36
So, what we need to do is tell SQLAlchemy how it talks to the database. Remember, we already did that, we did that
0:44
here in our init_db if we track this down we see, we say sqlite:/// and then the path that we worked out.
0:55
And we can actually hard-code a relative path in here. So, what we're going to do is we're going to say, SQLite
1:00
and then the URL, it's kind of confusing I'll put some spaces then take them away... The URL's going to be ./pypi
1:08
that's this folder, and then into there, and then into here. We can actually say copy relative path, I suspect. Let's try that. Oo, perfect! Okay.
1:19
So, then, obviously there's no spaces here but there's just a lot of /. things going on so that should tell SQLAlchemy how to talk to our database.
1:30
Make sure you save that. So, we can run Alembic down here we can say Alembic current and ask what this current database version is.
1:39
Now, it's not super obvious but what it tells you here by having no other statements is no version. This is not being tracked by Alembic
1:49
and you can even see that if we go over here. Now that I touched it, it has a version. Let's see if there's anything that's in here.
2:02
Okay, so, by way of interacting with it with Alembic it's created this table that's tracking it but you can see that there's no, 0 rows.
2:10
When it's actually been migrated and tracked you'll see there's actually only one row with only one column and it's going to be a version number
2:18
but if you can look at this in your database and get a sense of what Alembic thinks is going on there. We could actually use just this information
2:27
just setting the connection string and use Alembic to generate the scaffolding the skeleton pieces that we would need to implement
2:35
to do these migrations, but there's a better way to do it. We can let it automatically infer that
2:41
from our SQLAlchemy models and that's what we're going to do. So, first, let's see the problem. Let's run it and make sure everything's working.
2:53
Good. Everything is working. Now, if we make some minor change here, like let's suppose I talked about there being a last updated
3:03
let's do last updated date here. datetime.now, that seems decent. What happens if I run this? Well, or you run it, rather.
3:14
Bad things happen. So, imagine we deployed a new version we're excited to show off how amazing it is and tell everyone about it
3:20
and instead, boom, last updated does not exist! Ooh boy. What now? That's not good. SQLAlchemy will create new tables
3:31
but let's just go ahead and create a new table really quick to show that Alembic can also notice there are new tables
3:36
and create them. So, I'll just make a quick copy here and we'll just call this "auditing". So, let's suppose, this is an auditing table
3:49
it's going to have a created date and a description, that's fine, right? We're just going to describe operations that happened.
3:54
Now, obviously that's not really enough, but that's fine. Now, do not run your site. If you run your site, it's going to create this table
4:01
and that's going to cause some challenges. So, we could do this all at once or we could do it in one step.
4:07
So let me hold off on this, just for a second, and remember: for this to work, I have to go up here and import.
4:18
So, right now, we're just going to sort of record this one change right there. We have our app. Not super happy, because it's not running.
4:27
So, what do we do? Come down to our terminal, and we're going to tell Alembic to generate a new version, a new revision. So we say, alembic revision.
4:38
And here's the part for the automatic stuff. We also have to integrate one more thing. We can say, autogenerate -m
4:47
I think we're going to run into a couple problems. New, say, last updated on package. So, if we run this, there's going to be two problems.
4:56
I'm not sure what error message we're going to get. One problem is we have not told Alembic where our SQLAlchemy models live.
5:02
The other one is I think it's going to complain that we're not yet managing the database so it doesn't know where to start from.
5:07
Let's try. Can't proceed because it has not been configured. So, how do we configure it to know what our models are?
5:17
That's over here. Notice this: target metadata. So we're going to say, where's our data from
5:25
We're going to say, from pypi.data.modelbase import SQLAlchemyBase. Remember this guy? And then it has a .metadata on it.
5:36
So we just have to do that one line in order to connect these things. However, it is very likely, remember
5:43
you have to load all of these in order for this to work. So, what we're going to do, is we're also going to say... this. from pypi.data import *.
5:59
Tell PyCharm: no, no, this time, it's okay. This is going to load up all the models so that the SQLAlchemyBase has actually seen them.
6:08
In which case, when this thing starts to work with it all will be good. Okay, so I've done that, and I've just pressed save.
6:15
I don't need to rerun the app because Alembic runs independently. Now, let's try again. Okay, we're going to tell it to auto-generate a change
6:25
for last updated on package. Boom! It did it. It said, "we have detected a new column." I guess it realized there was just nowhere to start from
6:35
which is fine, and then check this out: this created Alembic/versions and then this funky weirdo wrap thing but it's fine.
6:43
There's a Python file with a hash type thing at the front. It's created. Now, this has done nothing to our database.
6:50
If I go over here, and I look at it, even if I rerun it we should get the same not-very-nice outcome. Because all it's done is it's sort of
7:01
computed what needs to be done in order to put the database in the right place. So, over here in versions, we now have this
7:08
and it says, okay, here's the revision. This is what we're going to take it to. There's nowhere that it started so this is the first upgrade.
7:17
It says, alright, we're going to do a couple things. If you upgrade the database we're going to add to the package table
7:24
we're going to add last updated and you can add a bunch of other details about it there and if you say, I would like to roll back this change
7:30
you're going to drop that column. And you can write whatever stuff you want here. So, let's go ahead and apply this change that we've already put here.
7:39
So, we would like this, 2FE, whatever changed to create that column in packages so that our code works. If we look over here, it's still not working.
7:50
So how do we fix it? We go over here and we say, alembic upgrade head. Just the latest. So, it will keep track of all the versions in here
8:01
and figure out how to get to the end and what various steps in what order need to be applied. So we save this, now it's upgraded
8:08
and it's applied that change which should have added that column. I should have expanded that before.
8:14
Oh, great, it doesn't have it because I didn't refresh it. So, let's see, there's no last updated, but if I refresh or synchronize, boom.
8:23
There it is. Now does out code run? Ta-daa! It does. So Alembic saves the day.