Building data-driven web apps with Flask and SQLAlchemy Transcripts
Chapter: Database migrations with Alembic
Lecture: Introducing database migrations
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.