Automated database migrations with Alembic

[Frédéric Mangano-Tarumi]

Hello everyone,

After discussing with Lukas about automation and database migrations, he
suggested I write a proposal. Here it is.

By the way, I’m a new contributor, and nothing has yet been decided on
who would implement this proposal if we do decide to follow it. Feedback
and contributions welcome!

Context
=======

We want to be able to deploy new versions of aurweb from Ansible without
requiring manual intervention for data migration.

Many all-in-one web frameworks provide tools for database migrations,
but aurweb being a multi-language project, we can’t afford to make a
switch to such a framework in the short term.

Proposed solution: Alembic
==========================

Disclaimer: I don’t have any practical experience with SQLAlchemy or
Alembic in production. I’m suggesting it entirely because it looks like
the best fit for our use case among the existing tools, but I could be
missing something.

Alembic[1] is a tool for managing database migrations. It is a sister
project of the Python database library SQLAlchemy. Both Alembic and
SQLAlchemy are flexible enough to collaborate with other database
connectors. I suggest we use SQLAlchemy only for the migrations and the
schema definition, without porting the existing Python and PHP codebase
for now.

The migrations scripts are written in Python and are intended to be used
with SQLAlchemy, but that’s not a requirement. Regardless, I do suggest
we migrate the whole schema to SQLAlchemy because (a) it takes care of
the SQLite/MySQL compatibility for us, (b) Alembic is able to
automatically generate migrations from SQLAlchemy schemas[2], and (c) we
did plan to do that eventually, didn’t we?

Alembic supports multi-database setups but don’t technically support
migrating other data files like INI configurations, though it is
something we might need to migrate in the future. We can still write
custom code in the migrations, but with the risk that the files may get
out of sync with their migration metadata in the database, which may
happen after manual interventions. I think it’s worth it.

To convert our current SQL schema, sqlacodegen[3] can generate the
SQLAlchemy code for us. Alembic can also create migrations to migrate
from zero to the full database, but I suggest we keep the SQLAlchemy
schema up-to-date in the code. Migrations would only run on existing
database, as described in the Alembic cookbook[4].

[1]: https://alembic.sqlalchemy.org/en/latest/tutorial.html
[2]: https://alembic.sqlalchemy.org/en/latest/autogenerate.html
[3]: https://pypi.org/project/sqlacodegen/
[4]: https://alembic.sqlalchemy.org/en/latest/cookbook.html#building-an-up-to-date-database-from-scratch

Alternative solutions
=====================

Flyway and Liquibase are two popular tools for database version control.
They’re both commercial with opensource editions, and written in Java.
While not bad in themselves, Alembic is a better match for us with its
licensing and its Python ecosystem.

We could roll our own migration tools, but it would certainly require
more effort than using an existing tool, and error handling is tricky
and may cause data damage if not done right.