you-music/MIGRATIONS.md

Database Migrations Guide

YouMusic uses Alembic for database schema migrations. This ensures safe schema changes without data loss.

Quick Start

View Migration Status

cd backend
./migrate.sh current

Create a New Migration

When you modify database models in app/models/models.py:

cd backend
./migrate.sh create "Add new field description"

Apply Migrations

cd backend
./migrate.sh upgrade

Rollback a Migration

cd backend
./migrate.sh downgrade  # Go back one migration
# or
./migrate.sh downgrade <revision_id>  # Go to specific version

Migration Commands

Command	Description
./migrate.sh create "message"	Create new migration with autogenerate
./migrate.sh upgrade	Apply all pending migrations
./migrate.sh downgrade	Rollback last migration
./migrate.sh history	View migration history
./migrate.sh current	Show current database version
./migrate.sh stamp head	Mark database as current without running migrations

How It Works

1. Models - Define your schema in app/models/models.py
2. Generate Migration - Alembic compares models to current DB and creates migration script
3. Review Migration - Check alembic/versions/*.py file before applying
4. Apply Migration - Run upgrade to apply changes to database

Migration Workflow

Adding a New Field

1. Add field to model:

# app/models/models.py
class Music(Base):
    # ... existing fields ...
    new_field: Mapped[Optional[str]] = mapped_column(String, nullable=True)

2. Create migration:

./migrate.sh create "Add new_field to music table"

3. Review generated migration in alembic/versions/

4. Apply migration:

./migrate.sh upgrade

Renaming a Field

Alembic may detect this as drop + add. Manually edit the migration:

# Before (auto-generated)
def upgrade():
    batch_op.drop_column('old_name')
    batch_op.add_column(sa.Column('new_name', sa.String(), nullable=True))

# After (manual edit)
def upgrade():
    batch_op.alter_column('old_name', new_column_name='new_name')

Deleting a Field

1. Remove from model
2. Generate migration: ./migrate.sh create "Remove old_field"
3. Review - ensure data you want is preserved
4. Apply: ./migrate.sh upgrade

Production Deployment

First Deployment (New Instance)

# Migrations will run automatically on startup
# Or manually:
./migrate.sh upgrade

Updating Existing Instance

# 1. Backup database
cp data/youmusic.db data/youmusic.db.backup

# 2. Pull latest code
git pull

# 3. Install dependencies
uv sync

# 4. Apply migrations
./migrate.sh upgrade

# 5. Restart application

Docker Deployment

Migrations run automatically when the container starts (via startup script).

To run manually in container:

docker exec -it youmusic /app/backend/migrate.sh current
docker exec -it youmusic /app/backend/migrate.sh upgrade

Kubernetes Deployment

Migrations run as an init container before the main application starts.

Check migration status:

kubectl logs -f deployment/youmusic -c migrations

Troubleshooting

"Target database is not up to date"

Your database is behind. Run:

./migrate.sh upgrade

"Can't locate revision identified by 'xyz'"

Migration files are missing or database is inconsistent. Check:

./migrate.sh history
./migrate.sh current

Rollback Failed Migration

# Downgrade to previous version
./migrate.sh downgrade

# Or to specific version
./migrate.sh downgrade <revision_id>

Reset Database (Dev Only - DATA LOSS!)

# Delete database
rm data/youmusic.db

# Recreate with latest schema
./migrate.sh upgrade

Manually Fix Database Version

If migrations got out of sync:

# Stamp to specific version (doesn't run migrations)
./migrate.sh stamp <revision_id>

# Or to latest
./migrate.sh stamp head

Files and Directories

backend/
├── alembic/                    # Migration configuration
│   ├── versions/               # Migration scripts
│   │   └── *.py               # Individual migrations
│   ├── env.py                 # Alembic environment config
│   └── script.py.mako         # Migration template
├── alembic.ini                # Alembic settings
├── migrate.sh                 # Helper script
└── app/
    └── models/
        └── models.py          # SQLAlchemy models (source of truth)

Best Practices

1. Always Review - Check auto-generated migrations before applying
2. Test First - Run migrations on dev/staging before production
3. Backup Data - Always backup database before migrations in production
4. One Change Per Migration - Keep migrations focused and atomic
5. Descriptive Messages - Use clear migration messages
6. Version Control - Commit migration files to git
7. Never Edit Applied Migrations - Create new ones instead
8. Document Complex Changes - Add comments in migration files

Advanced Usage

Manual Migration (No Autogenerate)

source .venv/bin/activate
alembic revision -m "Manual migration"
# Edit the created file manually

Offline SQL Generation

source .venv/bin/activate
alembic upgrade head --sql > migration.sql
# Review migration.sql before applying

Branching and Merging

# Create branch-specific migration
alembic revision -m "Feature branch change" --branch-label feature

# Merge branches
alembic merge -m "Merge branches" head1 head2

SQLite Specific Notes

• Uses batch_alter_table for schema changes (required for SQLite)
• Some operations may require table recreation
• Foreign key constraints temporarily disabled during migrations
• render_as_batch=True in env.py handles this automatically

Migration History Example

$ ./migrate.sh history

01209c730b33 -> (head), Initial migration
<base> -> 01209c730b33, Initial migration

Getting Help

• Alembic Docs: https://alembic.sqlalchemy.org/
• SQLAlchemy Docs: https://docs.sqlalchemy.org/
• Project Issues: Check GitHub issues

See Also

• AGENTS.md - AI/LLM context about the project
• DEV_QUICK_START.md - Development setup guide
• DEPLOYMENT.md - Deployment instructions