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