DB Migration via Alembic

[LeonWehrhahn]

Is your feature request related to a problem?

Currently Athena lacks a robust mechanism for handling database schema changes. As Athena evolves, changes to the database schema are inevitable. Without a proper migration tool, this will lead to schema inconsistencies, deployment issues, and synchronization problems across different environments.

Describe the solution you'd like

Integrate Alembic into Athena to manage database migrations effectively. Alembic is a lightweight database migration tool for SQLAlchemy-supported databases. It can help us version control our database schema changes and apply migrations smoothly across different instances of Athena services.

Two possible approaches:

• Option A: Integrate Alembic directly into the athena package. The challenge here is ensuring that when multiple services (modules) start, we prevent race conditions where multiple instances attempt to run migrations simultaneously.
• Option B: Create a separate service dedicated to running migrations before the other modules start. This could be a good production solution but complicates local testing and development since locally we create a SQLite db separately in every module.