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