Building Data-Driven Web Apps with Pyramid and SQLAlchemy Transcripts
Chapter: Database migrations with Alembic
Lecture: Our first change

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