Building Data-Driven Web Apps with Flask and SQLAlchemy Transcripts
Chapter: Database migrations with Alembic
Lecture: Introducing database migrations
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Relational databases are very powerful.
0:02
But one of the problems that you have
0:04
when working with relational databases
0:06
and this is exacerbated by when you're working with ORMs
0:11
is this ability to have say SQLAlchemy
0:13
create all the tables and the structures
0:16
and relationships automatically is awesome
0:18
except when you need to change things over time.
0:23
Once it's already created those tables
0:24
we saw it will not change them
0:26
your database is somewhat calcified
0:28
and it becomes hard to change.
0:30
So we're going to talk about a subject
0:32
and the set of tools and techniques
0:34
to keep your database evolving as your code evolves.
0:38
And this, I think, is one of the biggest challenges
0:41
of running a relational database
0:43
and is why some of the NoSQL databases
0:46
with their more flexible schema
0:48
has some advantages in this situation.
0:51
But you'll see the tooling for SQLAlchemy
0:54
to actually evolve your database is pretty sweet.
0:57
So let's get started. What is the problem?
1:00
Here's what happens.
1:01
We have a wonderful running site.
1:04
Here's our fake PyPI, it's doing its PyPI thing
1:07
running working in this database
1:09
then we decide oh, you know, things would be nicer
1:12
if we could track something else on the package
1:15
if we had a last updated field.
1:17
So we could do simpler queries and maybe make this page
1:20
a little bit faster, who knows something like that.
1:22
We make that change in SQLAlchemy.
1:24
And then we want to run our code.
1:26
Well, what happens, something that will send shivers
1:29
down your spine, if you see this in production
1:31
OperationalError, no such column
1:34
package keywords in this case, we added the database schema
1:40
doesn't exactly match the schema of SQLAlchemy
1:44
at least if the SQLAlchemy schema is not a subset
1:47
of that and will contain all the required fields
1:51
you're going to get something like this
1:52
and it's going to be super bad.
1:54
So here's the thing, you make some changes
1:56
you push your code out to production
1:58
instead of your site becoming more awesome.
2:01
It just goes 100% offline, this is any page
2:05
that touches anything to do with packages
2:07
will never ever run. That's a problem.
2:11
How do we fix it?
2:12
We keep our database exactly in sync
2:15
with our SQLAlchemy code
2:16
at least two ways to do this.
2:18
One way is to manually create an update script
2:21
every time we need to make a change to the database
2:23
we'll do that update script
2:25
and we'll apply it to production
2:26
or staging or some other developer's database, right?
2:30
It's not terribly hard when using SQLite
2:31
like we are because that's actually just a file.
2:34
If you have live data going into it
2:36
you have to stay in sync with that.
2:38
And that can be a challenge.
2:39
But the real problem is if you have separate databases
2:43
like Postgres or something, and different people
2:46
are connecting to different databases that are out of sync
2:49
right or different, you know, production versus development
2:51
things like that. So this is a big problem.
2:53
So the one way we can do is manual.
2:55
Another one is we can use something called Alembic.
2:59
Alembic is from Mike Bayer, the same guy
3:02
who created SQLAlchemy and maintains it himself.
3:06
So these are quite closely tied together.
3:08
And Alembic is a database migration tool
3:11
that will track the versions of databases.
3:15
So maybe your production database is the latest
3:17
maybe you're staging databases, some intermediate thing
3:20
and maybe production is the most behind
3:23
and long as you continually to evolve it with Alembic.
3:26
You can have Alembic catch it up to whatever version of code
3:30
that you ship with, it's really, really nice
3:32
it doesn't solve every single problem
3:35
but it solves most of the problems
3:37
and it gives you some plugins for sort
3:39
of adding the final bits.
3:40
And we're going to use Alembic
3:42
to help evolve our database over time as we evolve our code.
