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.
