Skip to main content

Database Migrations

Tool: Alembic (Python)​

File Location​

backend/alembic/versions/
├── 001_initial_schema.py
├── 002_add_avatar_and_lessons.py
├── 003_add_user_is_active.py
├── 004_add_containers_table.py
├── 005_add_sandbox_templates.py
├── 006_add_is_visible_to_templates.py
├── 007_add_sections_themes.py
├── 008_drop_old_lessons_tables.py
├── 009_add_laboratory_system.py
└── ...

Naming Convention​

{number}_{description}.py

009_add_laboratory_system.py     # GOOD
009AddLaboratorySystem.py        # BAD
migration9.py                    # BAD

Numbers are zero-padded to 3 digits: 001, 002, ..., 009, 010.

Creating a Migration​

# Step 1: Modify backend/app/db/models.py with new model/field

# Step 2: Generate migration file
docker-compose exec backend alembic revision -m "add_notifications"

# Step 3: Edit the generated file in backend/alembic/versions/
# Add upgrade() and downgrade() functions

# Step 4: Apply migration
docker-compose exec backend alembic upgrade head

# Step 5: Verify
docker-compose exec backend alembic current

Migration File Template​

"""add notifications table

Revision ID: abc123def456
Revises: xyz789uvw012
Create Date: 2026-03-01 10:00:00.000000
"""
from alembic import op
import sqlalchemy as sa


# revision identifiers
revision = 'abc123def456'
down_revision = 'xyz789uvw012'
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'notifications',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('user_id', sa.Integer(), sa.ForeignKey('users.id', ondelete='CASCADE'), nullable=False),
        sa.Column('type', sa.String(50), nullable=False),
        sa.Column('title', sa.String(200), nullable=False),
        sa.Column('message', sa.Text(), nullable=True),
        sa.Column('is_read', sa.Boolean(), default=False),
        sa.Column('created_at', sa.TIMESTAMP(timezone=True), server_default=sa.func.now()),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_notifications_user_id', 'notifications', ['user_id'])


def downgrade():
    op.drop_index('idx_notifications_user_id', table_name='notifications')
    op.drop_table('notifications')

Commands​

# Apply all pending migrations
docker-compose exec backend alembic upgrade head

# Rollback last migration
docker-compose exec backend alembic downgrade -1

# Show current version
docker-compose exec backend alembic current

# Show migration history
docker-compose exec backend alembic history

# Production (same commands with prod compose file)
docker-compose -f docker-compose.prod.yml exec backend alembic upgrade head

Rules​

  1. Every upgrade has a downgrade — rollbacks must always be possible
  2. Never edit merged migrations — create a new migration instead
  3. One logical change per migration — don't mix unrelated schema changes in one file
  4. Include indexes in the same migration as the table they belong to
  5. Use TIMESTAMP(timezone=True) with server_default=sa.func.now() for timestamp columns
  6. Use ondelete='CASCADE' on foreign keys where child rows should be deleted with parent
  7. Migrations run before deploy — never embedded in application startup

Model → Migration Workflow​

1. Edit backend/app/db/models.py
   └── Add new Column, Table, or relationship

2. docker-compose exec backend alembic revision -m "description"
   └── Creates new file in backend/alembic/versions/

3. Edit the generated file
   └── Implement upgrade() and downgrade()

4. docker-compose exec backend alembic upgrade head
   └── Test locally

5. Commit both models.py and the migration file

6. Deploy to production:
   └── git pull && alembic upgrade head

Timestamp Convention​

# Always use timezone-aware timestamps with server default
created_at = Column(TIMESTAMP(timezone=True), server_default=func.now())
updated_at = Column(TIMESTAMP(timezone=True), server_default=func.now(), onupdate=func.now())