Flask-Migrate - Database Migrations, Alembic

Introduction

Flask-Migrate manages database schema migrations using Alembic. Migrations allow you to modify your database schema incrementally while preserving existing data.

Setup and Initialization

# Initialize migration repository
# flask db init

# Create initial migration
# flask db migrate -m "Initial migration"

# Apply migrations
# flask db upgrade

# app.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'

db = SQLAlchemy(app)
migrate = Migrate(app, db)

# models.py
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

Creating Migrations

When you change your models, generate a new migration to update the database schema.

# After modifying models, create migration
# flask db migrate -m "Add user profile fields"

# This creates a migration file like:
# migrations/versions/abc123_add_user_profile.py

def upgrade():
    op.add_column('users', sa.Column('bio', sa.String(500)))
    op.add_column('users', sa.Column('avatar_url', sa.String(200)))
    op.add_column('users', sa.Column('is_active', sa.Boolean(), default=True))

def downgrade():
    op.drop_column('users', 'bio')
    op.drop_column('users', 'avatar_url')
    op.drop_column('users', 'is_active')

Migration Commands

# Check current migration status
flask db current

# Show migration history
flask db history

# Rollback one migration
flask db downgrade -1

# Migrate to specific revision
flask db upgrade [revision]

# Stamp current database version
flask db stamp head

Advanced Migration Operations

# Multiple databases
# config.py
SQLALCHEMY_DATABASE_URI = {
    'default': 'postgresql://localhost/maindb',
    'analytics': 'postgresql://localhost/analytics'
}
SQLALCHEMY_BINDS = SQLALCHEMY_DATABASE_URI

# migrations/env.py
def get_url():
    return current_app.config['SQLALCHEMY_DATABASE_URI']

# migrations for specific database
flask db upgrade -- database analytics

# Using Alembic directly
# alembic upgrade head
# alembic downgrade -1
# alembic revision --autogenerate -m "Add tables"

Data Migrations

def upgrade():
    # Add new column with default value
    op.add_column('users', 
        sa.Column('status', sa.String(20), default='active'))
    
    # Migrate existing data
    op.execute("UPDATE users SET status = 'active' WHERE status IS NULL")

def downgrade():
    op.drop_column('users', 'status')

# Using bulk operations
def upgrade():
    from app.models import User
    from app import db
    
    op.add_column('users', sa.Column('role', sa.String(20)))
    
    # Migrate data
    users = User.query.all()
    for user in users:
        user.role = 'user'
    db.session.commit()

Practice Problems

1. Add a new table for posts and create the corresponding migration
2. Modify an existing table to add a new column with a default value
3. Create a migration that renames a column and maintains data
4. Implement a rollback strategy for a critical schema change
5. Set up migrations for multiple database environments (development, production)