Skip to main content

Database Migrations

Tellus uses Alembic for database schema versioning and migrations.

Quick Reference​

# Generate new migration
alembic revision --autogenerate -m "description"

# Apply all pending migrations
alembic upgrade head

# Rollback one version
alembic downgrade -1

# View current version
alembic current

# View migration history
alembic history

Migration Workflow​

1. Make Model Changes​

Edit SQLAlchemy models in app/db/models/:

# app/db/models/my_model.py
class MyModel(Base):
    __tablename__ = "my_table"

    id = Column(UUID, primary_key=True)
    name = Column(String(255), nullable=False)
    # Add new column
    description = Column(Text, nullable=True)

2. Generate Migration​

cd tellus-ehs-hazcom-service
alembic revision --autogenerate -m "add description to my_table"

This creates a file in alembic/versions/:

alembic/versions/20240101_123456_add_description_to_my_table.py

3. Review Migration​

Always review the generated migration!

def upgrade():
    op.add_column('my_table',
        sa.Column('description', sa.Text(), nullable=True))

def downgrade():
    op.drop_column('my_table', 'description')

Check for:

  • Correct table and column names
  • Proper data types
  • Foreign key relationships
  • Index creation

4. Apply Migration​

# Development
alembic upgrade head

# Test rollback
alembic downgrade -1
alembic upgrade head

5. Commit Migration​

git add alembic/versions/20240101_*.py
git commit -m "Add description column to my_table"

Best Practices​

Naming Conventions​

Use descriptive migration messages:

# Good
alembic revision --autogenerate -m "add_hazard_categories_to_product_catalog"
alembic revision --autogenerate -m "create_sds_sections_table"

# Bad
alembic revision --autogenerate -m "update"
alembic revision --autogenerate -m "fix"

Data Migrations​

For migrations that transform data:

def upgrade():
    # Add new column
    op.add_column('chemicals',
        sa.Column('status', sa.String(20), nullable=True))

    # Populate with default value
    op.execute("UPDATE chemicals SET status = 'active' WHERE status IS NULL")

    # Make non-nullable
    op.alter_column('chemicals', 'status', nullable=False)

Large Table Migrations​

For tables with millions of rows:

def upgrade():
    # Add column without lock
    op.execute("""
        ALTER TABLE large_table
        ADD COLUMN new_column TEXT
    """)

    # Create index concurrently (PostgreSQL)
    op.execute("""
        CREATE INDEX CONCURRENTLY idx_large_table_new_column
        ON large_table (new_column)
    """)

Common Issues​

Head Mismatch​

# Check current state
alembic current

# If out of sync, stamp to current
alembic stamp head

Failed Migration​

# Rollback failed migration
alembic downgrade -1

# Fix the migration file, then retry
alembic upgrade head

Missing Migration​

If autogenerate misses a change:

# Create empty migration
alembic revision -m "manual migration"

# Edit the file manually

Production Deployment​

  1. Never run autogenerate in production
  2. Test migrations on staging first
  3. Take database backup before migrating
  4. Run during low-traffic periods
# Production deployment
alembic upgrade head --sql > migration.sql  # Preview
alembic upgrade head                        # Apply