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.
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.