Alembic

# Alembic Migration Examples for Customer Support Systems

This document provides 15+ practical, runnable examples for common database migration scenarios in customer support environments. Each example includes complete code that you can adapt for your own use cases.

## Table of Contents

1. [Initial Database Setup](#example-1-initial-database-setup)
2. [Adding Columns to Existing Table](#example-2-adding-columns-to-existing-table)
3. [Creating Performance Indexes](#example-3-creating-performance-indexes)
4. [Adding Tables with Foreign Keys](#example-4-adding-tables-with-foreign-keys)
5. [Modifying Column Types Safely](#example-5-modifying-column-types-safely)
6. [Data Migration for Status Values](#example-6-data-migration-for-status-values)
7. [Autogenerate from SQLAlchemy Models](#example-7-autogenerate-from-sqlalchemy-models)
8. [Complex Manual Migration](#example-8-complex-manual-migration)
9. [Downgrade Procedures](#example-9-downgrade-procedures)
10. [Creating Migration Branches](#example-10-creating-migration-branches)
11. [Merging Migration Branches](#example-11-merging-migration-branches)
12. [Online Migration with Minimal Downtime](#example-12-online-migration-with-minimal-downtime)
13. [Testing Migrations with Pytest](#example-13-testing-migrations-with-pytest)
14. [Rolling Back Failed Migrations](#example-14-rolling-back-failed-migrations)
15. [Production Deployment Workflow](#example-15-production-deployment-workflow)
16. [Batch Data Migration](#example-16-batch-data-migration)
17. [Adding Enums and Constraints](#example-17-adding-enums-and-constraints)
18. [Multi-Table Data Migration](#example-18-multi-table-data-migration)

---

## Example 1: Initial Database Setup

**Scenario**: Setting up the initial schema for a customer support ticketing system.

**Command**:
```bash
alembic revision -m "create initial support schema"
```

**Migration File** (`versions/001_create_initial_support_schema.py`):

```python
"""create initial support schema

Revision ID: 001_initial
Revises:
Create Date: 2025-01-15 10:00:00.000000
"""

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision = '001_initial'
down_revision = None
branch_labels = None
depends_on = None


def upgrade() -> None:
    # Create users table
    op.create_table(
        'users',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('email', sa.String(255), nullable=False, unique=True),
        sa.Column('full_name', sa.String(200), nullable=False),
        sa.Column('role', sa.String(50), nullable=False, server_default='agent'),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default='true'),
        sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.text('NOW()')),
        sa.Column('updated_at', sa.DateTime(), nullable=False, server_default=sa.text('NOW()'))
    )

    # Create customers table
    op.create_table(
        'customers',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('email', sa.String(255), nullable=False, unique=True),
        sa.Column('name', sa.String(200), nullable=False),
        sa.Column('company', sa.String(200), nullable=True),
        sa.Column('phone', sa.String(50), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.text('NOW()')),
        sa.Column('updated_at', sa.DateTime(), nullable=False, server_default=sa.text('NOW()'))
    )

    # Create tickets table
    op.create_table(
        'tickets',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('customer_id', sa.Integer(), nullable=False),
        sa.Column('assigned_user_id', sa.Integer(), nullable=True),
        sa.Column('subject', sa.String(500), nullable=False),
        sa.Column('description', sa.Text(), nullable=False),
        sa.Column('status', sa.String(50), nullable=False, server_default='open'),
        sa.Column('priority', sa.String(20), nullable=False, serv