Managing the database
Databases and version control
To prevent corrupting our production data, our code and our database need to have the same understanding of what the data looks like: what our tables are called, what columns they have, and so on.
But although our code lives in a version-controlled system, our database doesn’t. And since our database contains our precious user data, it’s something we can’t afford to corrupt.
The usual fix for this problem is to define database migrations. Each migration defines how we want to modify the database and how to undo those changes if we decide not to keep them.
In Ambuda, we manage our database schemas with SQLAlchemy, and we manage our migrations with a companion library called Alembic.
This guide will show you how to use Alembic for common development tasks.
Alembic cheatsheet
Alembic is analogous to Git. We can see the current migration ID:
alembic current
See the migration history:
alembic history
Move up to a newer migration:
# Move up one migration
alembic upgrade +1
# Move up to specific migration
alembic upgrade <migration_id>
# Move to latest migration
alembic upgrade head
And downgrade to the earliest migration:
# Move down one migration
alembic downgrade -1
# Move down to specific migration
alembic downgrade <migration_id>
# Move to earliest migration
alembic downgrade base
We can also modify the database by creating a new migration:
alembic revision --autogenerate -m "Add my cool column"
Then applying it:
alembic upgrade head
Initializing the migration system
We initialize Alembic as part of make install. The specific commands we run here are:
# Create Alembic's migrations table. This tracks the current migration
# status.
alembic ensure_version
# Set the most recent revision as the current one.
alembic stamp head
How to apply schema changes
Usually, all you’ll need to do is upgrade to the latest migration:
alembic upgrade head
How to create schema changes
Alembic can create common changes for you. Just run the following command:
alembic revision --autogenerate -m "Add my cool column"
Sometimes, custom changes are needed on top. For details and pointers, ask on the #backend channel on our Discord server.
Fixing a broken database
In development, it’s often just easiest to delete the database and recreate it from scratch.
Before we merge in a migration, we must verify that we can successfully upgrade to it and downgrade from it.
For more information
See the Alembic tutorial.