# Database Migrations Guide

YouMusic uses [Alembic](https://alembic.sqlalchemy.org/) for database schema migrations. This ensures safe schema changes without data loss.

## Quick Start

### View Migration Status
```bash
cd backend
./migrate.sh current
```

### Create a New Migration
When you modify database models in `app/models/models.py`:
```bash
cd backend
./migrate.sh create "Add new field description"
```

### Apply Migrations
```bash
cd backend
./migrate.sh upgrade
```

### Rollback a Migration
```bash
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:
```python
# app/models/models.py
class Music(Base):
    # ... existing fields ...
    new_field: Mapped[Optional[str]] = mapped_column(String, nullable=True)
```

2. Create migration:
```bash
./migrate.sh create "Add new_field to music table"
```

3. Review generated migration in `alembic/versions/`

4. Apply migration:
```bash
./migrate.sh upgrade
```

### Renaming a Field

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

```python
# 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)
```bash
# Migrations will run automatically on startup
# Or manually:
./migrate.sh upgrade
```

### Updating Existing Instance
```bash
# 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:
```bash
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:
```bash
kubectl logs -f deployment/youmusic -c migrations
```

## Troubleshooting

### "Target database is not up to date"
Your database is behind. Run:
```bash
./migrate.sh upgrade
```

### "Can't locate revision identified by 'xyz'"
Migration files are missing or database is inconsistent. Check:
```bash
./migrate.sh history
./migrate.sh current
```

### Rollback Failed Migration
```bash
# Downgrade to previous version
./migrate.sh downgrade

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

### Reset Database (Dev Only - DATA LOSS!)
```bash
# Delete database
rm data/youmusic.db

# Recreate with latest schema
./migrate.sh upgrade
```

### Manually Fix Database Version
If migrations got out of sync:
```bash
# 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)
```bash
source .venv/bin/activate
alembic revision -m "Manual migration"
# Edit the created file manually
```

### Offline SQL Generation
```bash
source .venv/bin/activate
alembic upgrade head --sql > migration.sql
# Review migration.sql before applying
```

### Branching and Merging
```bash
# 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

```bash
$ ./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