Building Data-Driven Web Apps with Flask 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
Now the first thing we have to do before we can use Alembic at all is to set up the sqlalchemy.url in the alembic.ini.
0:09
If we try to go over here and run something like Alembic current to ask what is the current version
0:16
It's going to go "Aaah, I have no idea what this thing is." Over here, this driver thing. So we need to give it the SQLAlchemy connection string.
0:24
Well, we basically already did that. Remember over here in our db_session? We came up with a connection string to be sqlite:///, then the file name.
0:35
It turns out that what we can put over here is exactly that. And then, we can put the relative path.
0:41
And it's a little, there's a lot of slashing going on here. So I'm going to make a space and then take it away.
0:45
So I'm going to say the current directory which is going to be here and then we want to have our database.
0:53
So we go over here and can say copy relative path and paste this there. That's what the path needs to be. Now, of course there's no spaces.
1:02
So like I said a lot of funkiness going on but that's the connection string. It's a SQLite database
1:07
and it's a relative path from the top level directory down to our database file. Let's see if we can run this again. Ah, there we go.
1:16
Well, what happened? Nothing happened. Nothing came out here. And like there's a couple of info tracing statements but effectively, nothing came out.
1:26
So rather than saying like there is no version num whatever which would be nice it doesn't, it just does nothing.
1:32
So when you see, normally you would see like the version is something and if you don't see that, it's not configured at all.
1:38
We can actually go look at the database here real quick. If we expand it out, by virtue of running that we now have this table here.
1:47
And we go over and say, jump to console and say select * from alembic. Run that, and notice there's zero rows. It just has a version number.
1:56
And when it's working it'll just have a single row and a single column which is the version number. So that's it.
2:03
That's all there's going to be to it to keep track of it. Right now there's no results so that's what we get. Now this is good enough
2:10
if we just want to make manual changes. If we want to generate various changes then they go hard code them write them up in Python by hand.
2:18
We don't want to do that. But before we see the next step that we have to take with Alembic let's just visualize the problem real quick.
2:26
So let's suppose we want to go over here and make a change to our database. Let's suppose that we, in addition to having a created_date on a package
2:35
we would like to have an updated_date or last_updated, something like that. Now, it could be basically the same thing here.
2:43
Initially, it's updated when it's created but then, right we can change it along the way. So this is cool, all right we want this last_updated date
2:50
and it's nice that it's here. So we're going to be able to do simpler queries potentially, you know, theoretically.
2:56
So let's go in runner app and see this awesome new change. Ooh yikes, not so awesome is it. That's not what we want.
3:07
What is the error? No such column last updated. I see. No such column last_updated. Okay, well what that means is
3:17
the database has gotten out of sync with SQLAlchemy. And the response is Aaah! We don't understand this anymore. Crash. That's unnerving in development
3:27
but it's really, really frustrating and nerveracking in production. You were deploy this code your website would be down, right?
3:35
500 site unavailable, really, really bad. So we don't want that and how do we solve it? We use Alembic to solve it.
3:43
So we could go and generate a change for this by hand or we could do this really cool thing and this is definitely the way I recommend it
3:51
is we can go over here, in say, alembic and we can say, I'd like to create a revision. I want to --autogenerate."
3:59
That means SQLAlchemy will look at its classes it will look at the table and go Oh, here are the things that are new.
4:04
Okay, and then this is a name to kind of just describe to basically name the file of what the update is. So we go run this, it's not going to work
4:14
because we have one more step to teach Alembic about our SQLAlchemy models. But let's see what happens. Failed, we cannot proceed with autogenerate
4:23
because the metadata object was not set. All right, so that's the last thing to do to make this work. So we come over here
4:30
and depending on the type of project you're working on this can be super easy or can be a little bit tricky.
4:36
So what we need to do is change this one line. We need to set it to SQLAlchemyBase base, remember that? And we need to set the metadata here.
4:50
So we're going to try to import. Here let's move this down you're going to see why I want it down here in a minute. Let's put this right here.
4:58
Well we're going to do this. Now, remember for this to mean anything we're also have to show SQLAlchemyBase all the other ones.
5:05
So we can also say, import pypi.org.data.__allmodels That's good enough. And tell PyCharm that this has meaning at the moment.
5:15
So looks like this will work. However, the context or location that Alembic runs it's not going to know about this directory structure.
5:25
So we got to do one quick little thing there. So let's try it again. Should get at least at a different error. Boom! No module pypi.org.
5:34
Well, that's frustrating because right there is pypi.org isn't it? It's okay, we can fix this. All we have to do is, we have to go
5:41
and for this particular execution of this script we have to add a folder, namely that folder to the Python path explicitly so Alembic knows about that.
5:54
Remember, that's what this is doing here as well right there okay? So we just have to do that manually and it's pretty easy to do.
6:03
And I think we've done it before as well. So what we're going to do is use os and then we're going to create the path based on this file
6:10
get the folder, so this file, this folder. Then we'll go up one, turn that into an absolute path
6:17
and it's going to have this right there so we can import it. Whew. Alright, so now when I run this now the magic is going to happen.
6:25
Are you ready? We'll be able to import this so we'll be able to get the models and set the metadata. And then, what's going to happen here
6:34
is Alembic is going to look at the database it's going to realize there's this last updated added and it's going to generate what we need
6:41
to make that change. Awesome. Look at that, generating this file right there in alembic/versions. Let's go see what was generated.
6:52
So, go in here. Alright, here we have it. So, this is the revision. There's no base revision. These chained together but right now since the first
7:02
this is like the end of that tree or whatever. And to upgrade, it's going to add a packages last updated and it's going to add an index to it.
7:09
Now if we want to downgrade then we're going to do the opposite we're going to take that away. Alright, so lets see if Alembic can help us.
7:18
Now notice, when we rerun this nothing is better. Our app is still broken. This command we ran over here it didn't actually change the database.
7:29
All it did, literally was to generate that single file right there. But what we can do is then apply that change over to the database.
7:37
So we say alembic upgrade head That means, just go to the latest and whatever steps you've had to take
7:47
if there's three or four migrations that have to happen that's great. Do it. So we do this. This is running an upgrade from nothing to five five etc.
7:56
Added last update. Updated. So now, if we go over here, to our packages folder and we do a refresh there's our last_updated
8:08
and if we refresh, most importantly if we refresh our app, the database should now be in sync with SQLAlchemy. Tada. We're back. Things are working.
8:17
Our app is back and running, just like it was before. So we made some changes to our SQLAlchemy code our classes, and then we generated automatically
8:26
an Alembic migration and then we applied it here. This works with taking away columns this works with other things
8:34
like if we say added a table, all sorts of stuff. Indexes, foreign key constraints all the kinds of things that you care about happen
8:42
happen when we do this. So this is really, really good. Now these should all be stored in version control as well
8:47
and obviously those changes we've made. So let's just do that right away. So we've got just some dictionary stuff that doesn't matter
8:53
but on version files, we want to make sure that goes into source control. And this will help us move, like say
9:00
upgrade the QA database, or upgrade production or upgrade all of the developers as we all make changes so our databases are always in sync.
9:08
There we have it. Our project has been brought back to life and evolved using SQLAlchemy and the auto generate feature. Super, super nice.