# Database Migrations with Alembic

This guide explains how to set up and run database migrations using Alembic.

## Prerequisites

1. Database must be running (use Docker Compose)
2. Alembic must be installed: `poetry install` (already included in pyproject.toml)

## Quick Start

### 1. Start the Database

```bash
cd backend
docker-compose up -d db
```

### 2. Initialize Alembic (First Time Only)

```bash
# Install alembic if not already installed
poetry add alembic

# Initialize alembic
poetry run alembic init alembic
```

### 3. Configure Alembic

Update `alembic.ini`:
```ini
sqlalchemy.url = postgresql://postgres:postgres@localhost:5432/wealthwise
```

Update `alembic/env.py`:

```python
import asyncio
from sqlalchemy.ext.asyncio import create_async_engine, AsyncEngine

# Import your models
from app.models import User, Portfolio, Transaction
from app.models.base import BaseModel

# Set target metadata
target_metadata = BaseModel.metadata

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

async def run_migrations_online():
    """Run migrations in 'online' mode with async engine."""
    configuration = config.get_section(config.config_ini_section)
    # Convert sync URL to async URL
    url = configuration['sqlalchemy.url'].replace(
        'postgresql://', 'postgresql+asyncpg://'
    )
    connectable = create_async_engine(url)
    
    async with connectable.connect() as connection:
        await connection.run_sync(do_run_migrations)
    
    await connectable.dispose()

if context.is_offline_mode():
    run_migrations_offline()
else:
    asyncio.run(run_migrations_online())
```

### 4. Create Initial Migration

```bash
# Generate migration script
poetry run alembic revision --autogenerate -m "Initial migration with user, portfolio, transaction"

# Apply migration
poetry run alembic upgrade head
```

## Common Commands

### Create a New Migration

```bash
# Auto-generate from model changes
poetry run alembic revision --autogenerate -m "Add new field to user"

# Create empty migration
poetry run alembic revision -m "Manual migration"
```

### Apply Migrations

```bash
# Upgrade to latest
poetry run alembic upgrade head

# Upgrade specific number of revisions
poetry run alembic upgrade +2

# Upgrade to specific revision
poetry run alembic upgrade abc123
```

### Rollback Migrations

```bash
# Downgrade one revision
poetry run alembic downgrade -1

# Downgrade to specific revision
poetry run alembic downgrade abc123

# Downgrade all the way
poetry run alembic downgrade base
```

### View Migration Status

```bash
# Current revision
poetry run alembic current

# History
poetry run alembic history --verbose
```

## Migration Best Practices

1. **Always review auto-generated migrations** before applying them
2. **Test migrations** on a copy of production data before running in production
3. **Never modify** existing migration files after they've been applied
4. **Use descriptive names** for migration messages
5. **One logical change per migration** (don't bundle unrelated changes)

## Troubleshooting

### Async Issues

If you encounter async-related errors, ensure you're using `create_async_engine` and `AsyncEngine` in `env.py`.

### Import Errors

Make sure all models are imported in `env.py`:
```python
from app.models import User, Portfolio, Transaction
```

### Connection Issues

Ensure the database is running and accessible:
```bash
docker-compose ps
# or
pg_isready -h localhost -p 5432 -U postgres
```