RomM Alembic Workflow for Database Migrations: A Complete Guide

RomM uses Alembic to automatically manage SQLAlchemy schema migrations, running alembic upgrade head on startup via backend/main.py while supporting manual CLI workflows for development.

RomM is an open-source game library manager that relies on SQLAlchemy for database operations. As the application evolves, schema changes are handled through Alembic, SQLAlchemy's migration tool. This article explains the complete workflow for managing database migrations in the RomM codebase, from automatic startup procedures to manual development commands.

Configuration and Environment Setup

Alembic Initialization

The migration system is configured through backend/alembic.ini, which specifies the script location and database connection parameters. The actual connection string is dynamically injected from the application's environment variables via ConfigManager.get_db_engine() in backend/config/config_manager.py (lines 42-60). This ensures that migrations always use the same database URL as the running application.

Environment Script

The backend/alembic/env.py file sets up the migration context by loading SQLAlchemy metadata from BaseModel.metadata. It defines an include_object hook (lines 35-42) that filters out virtual tables like SiblingRom and VirtualCollection, as well as certain indexes, preventing spurious changes during autogeneration. The script supports both offline and online modes, with the online mode creating an engine using the same connection string used by the application (lines 56-99).

Automatic Migration on Startup

RomM guarantees database consistency by automatically applying pending migrations when the server launches. In backend/main.py (lines 87-90), the application executes:

alembic.config.main(argv=["upgrade", "head"])

This runs before any other startup tasks, ensuring the database schema is always at the latest revision before the API becomes available. Whether you start the server with python -m uvicorn or python main.py, this migration check occurs automatically.

Manual CLI Workflow for Developers

While automatic migrations handle production deployments, developers use the Alembic CLI to create and manage migration scripts during development. All commands require the -c backend/alembic.ini flag to point to the correct configuration file.

Generate a new revision based on model changes:

alembic -c backend/alembic.ini revision --autogenerate -m "Add column is_verified to firmware"

Apply all pending migrations (equivalent to the automatic startup behavior):

alembic -c backend/alembic.ini upgrade head

Rollback migrations to a previous state:

# Downgrade by one revision

alembic -c backend/alembic.ini downgrade -1

# Or downgrade to a specific revision

alembic -c backend/alembic.ini downgrade 0001_initial_models

Inspect migration history:

alembic -c backend/alembic.ini history

Migration Script Structure

Individual migration scripts reside in backend/alembic/versions/ and follow standard Alembic revision format. The repository contains over 80 revisions (such as 0001_initial_models.py and 0094_track_meta_table.py), documenting every schema change since the project's inception.

A typical revision file contains upgrade() and downgrade() functions:

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('firmware', sa.Column('is_verified', sa.Boolean(), nullable=False, server_default=sa.false()))

def downgrade():
    op.drop_column('firmware', 'is_verified')

The --autogenerate flag inspects the current SQLAlchemy models and generates these operations automatically, though manual review is recommended for complex changes.

Handling SQLite Limitations

RomM supports SQLite as a database backend, which has limited ALTER statement support. To accommodate this, backend/alembic/env.py enables batch mode when rendering migration SQL offline (lines 70-77):

context.configure(
    connection=connection,
    target_metadata=target_metadata,
    render_as_batch=True  # Critical for SQLite support

)

This setting ensures that schema changes work correctly across all supported database backends, including SQLite's stricter constraints on column alterations.

Summary

• Automatic execution: backend/main.py runs alembic upgrade head on every startup to ensure schema consistency.
• Configuration: backend/alembic.ini and backend/alembic/env.py manage the connection string and metadata filtering.
• Version control: Over 80 migration scripts in backend/alembic/versions/ track schema evolution.
• Development workflow: Use alembic -c backend/alembic.ini revision --autogenerate to create new migrations based on model changes.
• Cross-platform support: Batch mode rendering ensures SQLite compatibility.

Frequently Asked Questions

How does RomM handle database migrations during deployment?

RomM automatically applies pending migrations when the application starts. The backend/main.py file invokes alembic.config.main(argv=["upgrade", "head"]) before initializing the API, ensuring the database schema matches the application code without manual intervention.

Where are the migration files stored in the RomM repository?

Migration scripts are stored in backend/alembic/versions/. Each file represents a revision with a unique identifier, such as 0001_initial_models.py or 0094_track_meta_table.py. The repository maintains over 80 revisions documenting the complete schema history.

Why does RomM exclude certain tables from autogenerated migrations?

The include_object hook in backend/alembic/env.py filters out virtual tables like SiblingRom and VirtualCollection because these are dynamically generated or managed by SQLAlchemy relationships rather than physical database tables. This prevents Alembic from generating unnecessary migration operations for non-existent schema objects.

Can I use Alembic commands manually with RomM?

Yes. Developers can run standard Alembic commands against the RomM configuration file using alembic -c backend/alembic.ini followed by standard subcommands like revision, upgrade, downgrade, or history. This is necessary when creating new migrations after modifying SQLAlchemy models in the development environment.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}