Building data-driven web apps with Pyramid 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
0:03 of the problems that you have when working
0:05 with relational databases, and this is exacerbated
0:08 by when you're working with ORMs, is this ability
0:12 to have say SQLAlchemy create all the tables
0:15 and the structures and the relationships automatically
0:18 is awesome 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, so we're going
0:31 to talk about a subject and a set of tools
0:33 and techniques to keep your database evolving
0:37 as your code evolves and this, I think
0:40 is one of the biggest challenges
0:41 of running a relational database and is why some
0:45 of the NoSQL databases 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. We have a wonderful running site.
1:04 Here's our fake PyPI and it's doing its PyPI thing running
1:07 working with this database.
1:09 Then we decide, oh you know, things would be nicer
1:12 if we could track something else
1:14 on the package if we had a last updated field
1:17 so we could do simpler queries
1:19 and maybe make this page a little bit faster.
1:21 Who knows, something like that.
1:22 We make that change in SQLAlchemy.
1:25 Then we want to run our code, well what happens?
1:28 Something that will send shivers down your spine
1:30 if you see this in production.
1:31 Operational error, no such column, packet, keywords
1:36 in this case we added.
1:39 The database schema doesn't exactly match
1:43 the schema of SQLAlchemy.
1:44 At least if the SQLAlchemy schema is not a subset
1:48 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.
2:03 This is any page that touches anything
2:06 to do with packages will never, ever run.
2:10 That's a problem. 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
2:23 to the database, we'll do that update script
2:25 and we'll apply it to production or staging
2:27 or some other developer's database, right?
2:30 It's not terribly hard when using SQLite like we are
2:32 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
2:44 and different people are connecting
2:47 to different databases that are out of sync
2:49 right, or production versus development
2:51 things like that. So this is a big problem.
2:53 So the one way we could do it is manual.
2:55 Another one is we can use something called Alembic.
2:59 Alembic is from Mike Bayer
3:01 the same guy who created SQLAlchemy
3:04 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 your staging database is some intermediate thing
3:20 and maybe production is the most behind
3:23 and long as you continue to evolve with Alembic
3:27 you can have Alembic catch it up to whatever version
3:30 of code that you ship with.
3:31 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
3:39 for sort 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.