Skip to content

Database Migrations (Alembic)

LibreFolio uses Alembic to manage database schema changes. Alembic is a lightweight database migration tool for SQLAlchemy that provides a versioning system for your database schema.

Workflow

The basic workflow for making a schema change is:

  1. Modify SQLAlchemy Models: Make changes to your db/models.py files (e.g., add a new column, create a new table).

  2. Generate a Migration Script: Run the dev.sh command to automatically generate a new migration script. 

    ./dev.sh db:migrate "Your descriptive message"
    This command compares your SQLAlchemy models to the current state of the database and generates a Python script in backend/alembic/versions/ that contains the necessary upgrade() and downgrade() functions.

  3. Review the Migration Script: Always review the generated script. Alembic is not perfect, especially with SQLite, and may require manual adjustments.

  4. Apply the Migration: Run the upgrade command to apply the changes to your database. 

    ./dev.sh db:upgrade

SQLite and Alembic Limitations

SQLite has limited support for ALTER TABLE statements, which can make schema migrations more complex than with other databases like PostgreSQL. Alembic works around this by using a "batch mode" that:

  1. Creates a new table with the desired schema.
  2. Copies the data from the old table to the new table.
  3. Drops the old table.
  4. Renames the new table to the original name.

The CHECK Constraint Problem

A significant limitation of this approach is that Alembic's autogenerate feature does not detect CHECK constraints in SQLite.

This means that if you add or modify a CHECK constraint in your SQLAlchemy model, you must manually add it to the generated migration script.

Example: Manually Adding a CHECK Constraint

If you add a CHECK constraint to the Transaction model:

class Transaction(SQLModel, table=True):
    # ...
    __table_args__ = (
        CheckConstraint("quantity >= 0", name="check_quantity_positive"),
    )

Alembic will not include this in the autogenerated script. You must edit the upgrade() function in the migration file and add it yourself:

# In the generated migration file: backend/alembic/versions/xxxx_add_check.py

def upgrade() -> None:
    # ... other operations ...

    with op.batch_alter_table('transaction', schema=None) as batch_op:
        batch_op.create_check_constraint(
            'check_quantity_positive',  # Constraint name
            'quantity >= 0'             # SQL expression
        )

def downgrade() -> None:
    # ... other operations ...

    with op.batch_alter_table('transaction', schema=None) as batch_op:
        batch_op.drop_constraint('check_quantity_positive', type_='check')

The dev.sh db:upgrade command includes a post-flight check that will warn you if CHECK constraints are missing after a migration, guiding you to fix the issue.