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.

Talk Python's Mastodon Michael Kennedy's Mastodon