Automated database migrations with Alembic

[Lukas Fleischer]

Hi Frédéric,

Thanks for the proposal!

On Fri, 07 Feb 2020 at 01:51:28, Frédéric Mangano-Tarumi wrote:
> We want to be able to deploy new versions of aurweb from Ansible without
> requiring manual intervention for data migration.

That is correct. We are still working closely with the DevOps team on
migrating everything to Ansible but expect completion by the end of this
week.

> Proposed solution: Alembic
> ==========================
> [...]

I like the idea of using SQLAlchemy and Alembic for just the schema and
migrations as a first step. It's important that nothing in our PHP code
breaks (at least nothing that cannot be fixed with some simple code
changes). I don't see where things could potentially break but it's
better to do some testing.

Following that, I guess we could also port the existing Python code to
use SQLAlchemy? There currently seem to be ~100 database queries that we
would need to work on.

> The migrations scripts are written in Python and are intended to be used
> with SQLAlchemy, but that\u2019s 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\u2019t we?

Both (a) and (b) sound great!

There have been proposals of doing a full rewrite of aurweb using Flask
and SQLAlchemy. However, nothing is set in stone yet. I expect this
rewrite to be a long-term project with a timeline of at least 12 months,
considering that we need a *lot* of testing, both automated and manual,
while backporting new changes at the same time. Doing a partial
migration to SQLAlchemy certainly won't hurt, unless for some reason, we
decide to use a different database toolkit for the rewrite.

> Alembic supports multi-database setups but don\u2019t 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\u2019s worth it.

It's likely that we need to migrate other things in the future but I
expect that to be pretty rare (you can check the text files in
upgrading/ for how often that happened in the past). As long as there is
some way of adding other types of migrations in the future, we should
focus on the database now and take care of other things as they come up.

> 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].

Sounds all good to me. We should leave this discussion open for a couple
of days for others to chime in before starting with an implementation.

Lukas