Database migrations¶
Managing database migrations is done using Alembic, a database migration tool for usage with SQLAlchemy.
Supported database systems¶
We try to take care to fully support migrations on PostgreSQL, MySQL, and SQLite. Since our production deployments run PostgreSQL, migrations may be optimized only for that system.
Zero-downtime migrations¶
To support zero-downtime database migrations, all migrations must keep compatibility with the existing codebase. The assumption is that deployments always run database migrations first and update the code second.
This means some database changes may have to be broken down into several steps and completed over several deployments. For example, deleting a column from a table can be done as follows:
- Remove the code that uses the column.
- Deploy:
- Run migrations (nothing to be done).
- Update the code.
- Create a migration that removes the column.
- Deploy:
- Run migrations (column is removed).
- Update the code (nothing to be done).
On the other hand, adding a column is easier. Creating a migration that adds the column and adding code to use it can safely be done at the same time if we assume migrations are always run before updating the code.
To support smooth deployments, ideally a release is made between migrations and code updates that should be deployed separately. This way also external users can always safely upgrade one release at a time.
Note
Our automated deployment of Mutalyzer with Ansible runs database migrations before updating the code.
Setting up Alembic¶
Before you can use Alembic, the database needs to be stamped with the current
revision. This is done automatically when using the -c
argument with the
mutalyzer-admin setup-database
command (as is recommended in
Mutalyzer setup):
$ mutalyzer-admin setup-database --alembic-config migrations/alembic.ini
An existing database can also be stamped manually using Alembic:
$ alembic -c migrations/alembic.ini stamp head
Running migrations¶
Upgrading an existing database to the latest revision is done as follows:
$ alembic -c migrations/alembic.ini upgrade head
Downgrades are explicitely unsupported and some migrations may not have downgrades implemented at all.
Creating migrations¶
To create a new migration, first update the SQLAlchemy models in the Mutalyzer source code. Then, generate a migration with Alembic:
$ alembic -c migrations/alembic.ini revision --autogenerate -m 'Some descriptive comment'
Template code for the upgrade and downgrade paths are written to a new file in
the migrations/versions
directory. Alembic is smart enough to generate the
complete code for simple migrations, but please always have a look at the
generated code.
For more complex changes to the database schema, you’ll have to add some code manually. The same goes for any data migrations you might want to include. Consult the Alembic documentation and existing migrations for some common patterns.