Automated database migrations with Alembic

[Frédéric Mangano-Tarumi]

Lukas Fleischer [2020-02-07 11:11:35 +0100]
> 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.

Absolutely. Maybe we should wait until we have a few successful
deployments with Alembic though, just in case.

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

SQLAlchemy is our safest bet considering its popularity, but if we do
end up with a different toolkit it’s gonna be a mess. If you have other
candidates in mind, it might be worth looking at them before migrating.

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

Nobody has expressed dissent so far, so here’s a plan:

1. Migrate the schema to SQLAlchemy.

   The new schema will unify both SQLite and MySQL, but since the SQLite
   schema is generated from the MySQL one, that should go smoothly.

   Naming-wise, where should it go? aurweb.schema, aurweb.db.schema?
   We’ll need an init script too: aurweb.db.initdb, aurweb.scripts.initdb?

2. Set up Alembic.

   With its env.py, we’ll be able to use the common aurweb configuration
   rather than duplicate the database connection settings in Alembic’s
   INI. We should also link it to the schema created in step 1.

I’m up for doing both tasks. If you’d like me to do them, just tell me
when I should start.