Skip to content

Database migrations

Guide to managing database migrations using Alembic.

Overview

graph LR
    DEV["Developer"] --> |alembic revision| NEW["🆕 New Migration"]
    NEW --> |alembic upgrade| DB[("Database")]
    DB --> |alembic downgrade| PREV["⏪ Previous State"]

    style NEW fill:#f9f,stroke:#333
    style DB fill:#bbf,stroke:#333

Configuration

alembic.ini

INI
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# alembic.ini
[alembic]
script_location = alembic
prepend_sys_path = src

# Database URL z environment variable
sqlalchemy.url = 

[post_write_hooks]
hooks = ruff
ruff.type = exec
ruff.executable = ruff
ruff.options = format REVISION_SCRIPT_FILENAME

env.py

Python
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
# alembic/env.py
import asyncio
from logging.config import fileConfig

from sqlalchemy import pool
from sqlalchemy.ext.asyncio import async_engine_from_config
from alembic import context

from app.config import settings
from app.models.base import Base

# Importovať všetky modely
from app.models import *  # noqa

config = context.config
config.set_main_option("sqlalchemy.url", settings.DATABASE_URL)

if config.config_file_name is not None:
    fileConfig(config.config_file_name)

target_metadata = Base.metadata


def run_migrations_offline() -> None:
    """Run migrations in 'offline' mode."""
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

    with context.begin_transaction():
        context.run_migrations()


def do_run_migrations(connection):
    context.configure(connection=connection, target_metadata=target_metadata)
    with context.begin_transaction():
        context.run_migrations()


async def run_async_migrations():
    connectable = async_engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    async with connectable.connect() as connection:
        await connection.run_sync(do_run_migrations)

    await connectable.dispose()


def run_migrations_online() -> None:
    """Run migrations in 'online' mode."""
    asyncio.run(run_async_migrations())


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

Basic commands

Creating a migration

Bash
1
2
3
4
5
# Automatická detekcia zmien v modeloch
alembic revision --autogenerate -m "add_team_description"

# Prázdna migrácia (manuálna)
alembic revision -m "custom_data_migration"

Applying migrations

Bash
1
2
3
4
5
6
7
8
# Najnovšia verzia
alembic upgrade head

# Konkrétna verzia
alembic upgrade 003_multi_tenant

# Relatívne (2 kroky dopredu)
alembic upgrade +2

Rollback

Bash
1
2
3
4
5
6
7
8
# O jeden krok späť
alembic downgrade -1

# Na konkrétnu verziu
alembic downgrade 002_add_communication

# Na začiatok (pozor!)
alembic downgrade base

Information

Bash
1
2
3
4
5
6
7
8
# Aktuálna verzia
alembic current

# História migrácií
alembic history --verbose

# Pending migrácie
alembic heads

Existing migrations

Migration Description
001_core_schema Basic tables (16 tables, courses, teams, users, events)
002_teaching_tools Communication metrics, mrs_template_compliant
003_scale_and_telemetry Scale optimizations, pilot telemetry, partitioned webhook table
004_user_avatar avatar_url column in users table
005_membership_sync Lifecycle columns for team_members (is_active, left_at, membership_source, last_synced_at)
006_team_member_teacher is_teacher column in team_members
007_check_configs JSONB check_configs column in course_settings
008_custom_checks custom_checks table for dynamic checks
009_user_attribution triggered_by_username, created_by_username columns
010_dynamic_feedback_forms feedback_forms table, form_id/responses in pilot_feedback
011_add_missing_indexes Missing performance indexes
011_bg_job_fk_cascade Foreign-key cascade for background jobs
012_dynamic_docker_ci_config Docker lint and CI configuration in course_settings
013_ci_auto_rollback CI auto-rollback configuration
014_ci_scenario_resolution CI scenario resolution tracking
015_course_memberships Course membership table
016_fix_weight_and_nullable Weight/default and nullable-column fixes
017_hash_webhook_secret_and_cleanup Hashed webhook secrets and cleanup
018_mr_note_type_position MR note type and position columns
019_activity_monitor_indexes Activity monitor indexes
020_fix_schema_drift Schema drift fix
021_llm_course_settings Course-level LLM settings
022_llm_custom_prompt Custom LLM prompts
023_user_role_locked User role locking
024_member_exclusion Member exclusion from scoring
025_relax_default_checks Relaxed default checks
026_add_r13_commit_quality R13 commit quality metrics
027_secret_rotation_and_user_tokens Secret rotation and user API tokens
028_unify_roles_drop_legacy Role unification and legacy cleanup
029_rename_is_instructor_to_is_teacher Rename is_instructor to is_teacher
030_feedback_links Feedback links
031_support_ticket_enhancements Support ticket enhancements
032_submitted_by submitted_by column
033_email_log Email logging
034_is_mentor is_mentor column in team_members
035_semester_end_date Semester-end auto-archive settings
036_skip_auto_archive Manual auto-archive override
037_webhook_secret_lookup HMAC lookup columns for webhook secrets
038_relax_branch_pattern_default Relaxed default branch pattern
039_lower_min_review_words Lowered default min_review_word_count
040_group_webhook_audit Group webhook audit fields
041_webhook_configurations Multi-scope webhook configurations
042_user_is_bot users.is_bot for service accounts
043_backfill_email_alias_bots Bot backfill from email plus-aliases
044_source_branch_deleted source_branch_deleted for merge requests
045_user_tour_seen Per-user tour completion state
046_drop_weight_commit_references Remove legacy weight_commit_references

Writing migrations

Autogenerated migration

Python
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
# alembic/versions/011_add_user_preferences.py
"""add user preferences

Revision ID: 011_add_user_preferences
Revises: 010_ci_failure_generator
Create Date: 2024-11-15 10:00:00.000000

"""
from typing import Sequence

from alembic import op
import sqlalchemy as sa


# revision identifiers
revision: str = '011_add_user_preferences'
down_revision: str | None = '010_ci_failure_generator'
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    # ### autogenerated ###
    op.create_table(
        'user_preferences',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('theme', sa.String(20), nullable=False, default='light'),
        sa.Column('notifications_enabled', sa.Boolean(), default=True),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.Column('updated_at', sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_user_preferences_user_id', 'user_preferences', ['user_id'])
    # ### end autogenerated ###


def downgrade() -> None:
    # ### autogenerated ###
    op.drop_index('ix_user_preferences_user_id', 'user_preferences')
    op.drop_table('user_preferences')
    # ### end autogenerated ###

Data migration

Python
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
# alembic/versions/012_populate_default_rubrics.py
"""populate default rubrics

Revision ID: 012_populate_default_rubrics
Revises: 011_add_user_preferences
Create Date: 2024-11-15 11:00:00.000000

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.sql import table, column


revision = '012_populate_default_rubrics'
down_revision = '011_add_user_preferences'


def upgrade() -> None:
    # Definícia tabuľky pre insert
    rubrics = table(
        'rubrics',
        column('id', sa.Integer),
        column('name', sa.String),
        column('description', sa.Text),
        column('is_default', sa.Boolean),
    )

    # Batch insert
    op.bulk_insert(rubrics, [
        {
            'id': 1,
            'name': 'Default DevOps Rubric',
            'description': 'Standard rubric for DevOps course',
            'is_default': True,
        },
        {
            'id': 2,
            'name': 'Advanced Rubric',
            'description': 'Rubric with gaming detection',
            'is_default': False,
        },
    ])


def downgrade() -> None:
    op.execute("DELETE FROM rubrics WHERE id IN (1, 2)")

Complex migration

Python
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
# alembic/versions/013_split_events_table.py
"""split events table for better performance

Revision ID: 013_split_events_table
Revises: 012_populate_default_rubrics
Create Date: 2024-11-15 12:00:00.000000

"""
from alembic import op
import sqlalchemy as sa


revision = '013_split_events_table'
down_revision = '012_populate_default_rubrics'


def upgrade() -> None:
    # 1. Vytvoriť novú tabuľku
    op.create_table(
        'commit_events',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('project_id', sa.Integer(), sa.ForeignKey('projects.id')),
        sa.Column('sha', sa.String(40), nullable=False),
        sa.Column('message', sa.Text()),
        sa.Column('author_id', sa.Integer(), sa.ForeignKey('users.id')),
        sa.Column('committed_at', sa.DateTime(), nullable=False),
        sa.Column('lines_added', sa.Integer(), default=0),
        sa.Column('lines_removed', sa.Integer(), default=0),
    )

    # 2. Migrovať dáta
    op.execute("""
        INSERT INTO commit_events (id, project_id, sha, message, author_id, committed_at, lines_added, lines_removed)
        SELECT id, project_id, data->>'sha', data->>'message', author_id, created_at, 
               COALESCE((data->>'additions')::int, 0),
               COALESCE((data->>'deletions')::int, 0)
        FROM events
        WHERE event_type = 'commit'
    """)

    # 3. Vytvoriť index
    op.create_index('ix_commit_events_project_committed', 'commit_events', ['project_id', 'committed_at'])

    # 4. Odstrániť staré dáta (voliteľné)
    # op.execute("DELETE FROM events WHERE event_type = 'commit'")


def downgrade() -> None:
    # Migrovať dáta späť
    op.execute("""
        INSERT INTO events (project_id, event_type, data, author_id, created_at)
        SELECT project_id, 'commit', 
               jsonb_build_object('sha', sha, 'message', message, 'additions', lines_added, 'deletions', lines_removed),
               author_id, committed_at
        FROM commit_events
        WHERE NOT EXISTS (
            SELECT 1 FROM events e WHERE e.id = commit_events.id
        )
    """)

    op.drop_table('commit_events')

Best practices

Recommendations

  1. Always test up and down

    Bash
    1
2
3
    alembic upgrade head
alembic downgrade -1
alembic upgrade head

  2. Atomic Changes

    Python
    1
2
    # Jedna migrácia = jedna logická zmena
# Nie: pridať tabuľku + zmeniť inú + migrovať dáta

  3. Backup before production migration

    Bash
    1
2
    pg_dump -Fc gitpulse > backup_before_migration.dump
alembic upgrade head

  4. Review autogenerated code

    Python
    1
2
3
    # Alembic nedetekuje všetko správne
# - Premenovanie stĺpca -> drop + add
# - Zmena typu -> môže stratiť dáta

Anti-patterns

Python
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# 1. Hardcoded dáta v upgrade
def upgrade():
    op.execute("INSERT INTO users VALUES (1, 'admin', 'admin@example.com')")
    # Čo ak user už existuje?

# 2. DROP bez zálohy dát
def upgrade():
    op.drop_column('users', 'legacy_field')  # Dáta stratené navždy

# 3. Chýbajúci downgrade
def downgrade():
    pass  # Nemožné vrátiť späť!

Production deployment

Migration in CI/CD

YAML
1
2
3
4
5
6
7
8
# .gitlab-ci.yml
migrate:
  stage: deploy
  script:
    - alembic upgrade head
  only:
    - main
  when: manual  # Manuálne spustenie pre bezpečnosť

Docker entry point

Bash
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
#!/bin/bash
# docker-entrypoint.sh

# Čakať na databázu
while ! pg_isready -h $DB_HOST -p $DB_PORT; do
    echo "Waiting for database..."
    sleep 2
done

# Spustiť migrácie
alembic upgrade head

# Spustiť aplikáciu
exec uvicorn app.main:app --host 0.0.0.0 --port 8000

Troubleshooting

"Target database is not up to date"

Bash
1
2
3
4
5
# Označiť aktuálny stav ako migrated
alembic stamp head

# Alebo špecifická verzia
alembic stamp 010_ci_failure_generator

"Can't locate revision"

Bash
1
2
3
4
5
6
# Zobraziť históriu
alembic history

# Opraviť broken chain
alembic stamp --purge
alembic stamp head

Conflict migrations

Bash
1
2
3
# Ak dva vývojári vytvorili migrácie súčasne:
# 1. Premenovať jednu (zmeniť revision ID)
# 2. Upraviť down_revision reťaz

Further reading

Created at TUKE in Kosice as a diploma thesis project.