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