Database Migrations

Schema changes to the firmware MySQL database are managed with Alembic. Migration scripts are generated automatically from the SQLAlchemy ORM models in src/firmware_orm/ and stored in alembic/versions/.

Applying migrations (automatic)

You do not need to run alembic upgrade head manually to bring a database up to date. The db_migrations service in docker_compose_dir/docker-compose.yaml is a one-shot container that runs alembic upgrade head and then exits. Every application service depends_on its successful completion (service_completed_successfully), so on each docker compose up the database is migrated to the latest revision before the app starts:

docker compose -f docker_compose_dir/docker-compose.yaml up -d

This applies to both the production stack and the development stack (compose.dev.yaml extends the same base compose file, so db_migrations runs there too).

The manual setup and commands below are only needed when authoring migrations during development — generating a new revision, reviewing it, and testing upgrade/downgrade against a local database.

Prerequisites (development only)

• The MySQL container must be running:

  docker compose -f docker_compose_dir/docker-compose.yaml up -d mysql_db
  caution

  Starting only mysql_db skips the db_migrations container, so the schema is not brought up to date automatically. You then have to either apply migrations yourself with alembic upgrade head (see below) or also start db_migrations (e.g. docker compose -f docker_compose_dir/docker-compose.yaml up -d mysql_db db_migrations). Bringing up the full stack runs db_migrations for you.

• The Python environment must be active and dependencies installed:

  python3 -m venv venv
  source venv/bin/activate  # On Windows: venv\Scripts\activate
  pip install -r requirements.txt
• All alembic commands must be run from the project root (where pyproject.toml lives).

The virtual environment needs to be activated in every new terminal session using source venv/bin/activate (or venv\Scripts\activate on Windows) before running any following commands, so that the correct Python environment and dependencies are used.

Environment Variables

Alembic reads the database connection from environment variables. Export them from docker_compose_dir/.env before running any command:

set -a && source docker_compose_dir/.env && set +a

Variable	Default	Description
DATABASE_HOST	localhost	MySQL host
DATABASE_PORT	3306	MySQL port
DATABASE_USER	scraper	MySQL user
DATABASE_PASSWORD	password	MySQL password
DATABASE_SCHEMA	firmware	Database / schema name

caution

Forgetting to export these variables is the most common source of Access denied errors when running Alembic locally. The .env file is only loaded automatically by Docker Compose — not by your shell.

Common Commands

These are run locally during development. Remember that deployments apply migrations automatically through the db_migrations container (see Applying migrations).

Apply all pending migrations against a local database

alembic upgrade head

Roll back the last migration

alembic downgrade -1

Show the current revision of the live database

alembic current

Show the full migration history

alembic history --verbose

Adding a Migration

Follow these steps whenever a schema change is needed:

1. Update the ORM model in src/firmware_orm/.

2. Generate the migration script. Alembic compares the models against the live database and writes the diff:

   alembic revision --autogenerate -m "short description of change"

3. Review the generated file in alembic/versions/. Autogenerate is not perfect — always check that the upgrade() and downgrade() functions are correct and complete before committing.

4. Apply the migration locally to test it:

   alembic upgrade head

5. Commit the changed ORM model and the new migration script together in the same commit. Once merged, the db_migrations container applies it automatically on the next docker compose up — no manual upgrade step is needed in deployment.

How It Works

alembic/env.py builds the database URL from the environment variables listed above and passes Base.metadata to Alembic as target_metadata. All ORM models are imported at the top of env.py so their tables are visible to the autogenerate comparison.

The [tool.alembic] section in pyproject.toml points script_location to the alembic/ directory, which is why no path needs to be set in alembic.ini.

On deployment, the db_migrations container (src/Dockerfile_migrations) runs migrate_db() from src/firmware_orm/init_db.py, which is the programmatic equivalent of alembic upgrade head. It resolves the alembic/ script location relative to the package, so it works regardless of the working directory. Because the container reads DB_CONTAINER/MYSQL_*/DB_SCHEMA/DB_PORT from the compose environment, no shell export step is needed there — that is only required when running Alembic by hand on the host.

Initial Schema

The first migration (alembic/versions/83812b476ccf_initial_schema.py) captures the baseline schema. On a fresh database the MySQL container's entrypoint runs docker_compose_dir/sql_scripts/init.sql to create the tables, and Alembic's migration history starts from there. Going forward, migrations are the authoritative source of truth for all schema changes — init.sql is only used for bootstrapping a brand-new data volume.