Difficulty: Intermediate
Database migrations are the version control system for your database schema. Just as Git tracks changes to your code, Alembic (through Flask-Migrate) tracks changes to your database tables, columns, and indexes. Without migrations, modifying your schema in production means manually writing ALTER TABLE statements or dropping and recreating tables (losing data).
Flask-Migrate integrates Alembic with Flask and Flask-SQLAlchemy. Alembic compares your current model definitions with the actual database schema and generates migration scripts that describe the changes. These scripts can upgrade the schema (apply changes) or downgrade (revert changes).
The workflow is: modify your models, run 'flask db migrate' to generate a migration script, review the script, then run 'flask db upgrade' to apply it. Each migration is a Python file with upgrade() and downgrade() functions. The upgrade function applies the change (e.g., add a column), and the downgrade function reverses it (e.g., drop the column).
Setting up Flask-Migrate involves creating a Migrate instance, initializing it with the app and db, running 'flask db init' to create the migrations directory, and then using the migrate/upgrade/downgrade commands. The migrations directory should be committed to version control so all developers and deployment environments share the same migration history.
Auto-generated migrations are a starting point, not a final product. Alembic detects many changes automatically (added/removed columns, renamed tables, changed types), but it cannot detect everything. Column renames are detected as 'drop + add' (losing data), and data migrations require manual intervention. Always review generated migration scripts before running them.
Migration scripts are sequential and form a chain. Each script knows its revision ID and its predecessor ('down_revision'). Alembic tracks which migrations have been applied in an 'alembic_version' table. Running 'flask db upgrade' applies all pending migrations in order. Running 'flask db downgrade' reverts one migration.
For production deployments, always test migrations against a copy of the production database before applying them. Data loss, type mismatches, and constraint violations can occur if migrations are not carefully reviewed.
# app/extensions.py
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
db = SQLAlchemy()
migrate = Migrate()
# app/__init__.py
from flask import Flask
from app.extensions import db, migrate
def create_app():
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'
db.init_app(app)
migrate.init_app(app, db)
return app
# Terminal commands:
# flask db init # Create migrations/ directory (once)
# flask db migrate -m "Initial migration" # Generate migration
# flask db upgrade # Apply migration
# flask db downgrade # Revert last migrationInitialize Migrate with the app and db. 'flask db init' creates the migrations directory. 'flask db migrate' auto-generates migration scripts. 'flask db upgrade' applies them to the database.
"""Add email column to users
Revision ID: a1b2c3d4e5f6
Revises: 9z8y7x6w5v4u
Create Date: 2024-01-15 10:30:00
"""
from alembic import op
import sqlalchemy as sa
revision = 'a1b2c3d4e5f6'
down_revision = '9z8y7x6w5v4u'
def upgrade():
op.add_column('users',
sa.Column('email', sa.String(120), nullable=True)
)
# Backfill existing rows
op.execute("UPDATE users SET email = username || '@example.com'")
# Then make non-nullable
op.alter_column('users', 'email', nullable=False)
def downgrade():
op.drop_column('users', 'email')Migration scripts have upgrade() and downgrade() functions. This one adds an email column, backfills existing rows with default values, then makes it non-nullable. The downgrade simply drops the column.
from alembic import op
import sqlalchemy as sa
def upgrade():
# Add a column
op.add_column('users', sa.Column('bio', sa.Text))
# Create a new table
op.create_table('posts',
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('title', sa.String(200), nullable=False),
sa.Column('user_id', sa.Integer, sa.ForeignKey('users.id')),
)
# Create an index
op.create_index('ix_users_email', 'users', ['email'], unique=True)
# Alter a column type
op.alter_column('users', 'username',
type_=sa.String(150), # was String(80)
existing_type=sa.String(80)
)
def downgrade():
op.alter_column('users', 'username',
type_=sa.String(80),
existing_type=sa.String(150)
)
op.drop_index('ix_users_email', 'users')
op.drop_table('posts')
op.drop_column('users', 'bio')Alembic provides operations for all schema changes: add/drop columns, create/drop tables, create/drop indexes, and alter columns. The downgrade must reverse every change in the upgrade.
# 1. Modify your model
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80))
email = db.Column(db.String(120)) # NEW COLUMN
phone = db.Column(db.String(20)) # NEW COLUMN
# 2. Generate migration
# $ flask db migrate -m "Add email and phone to users"
# INFO: Generating migration script...
# migrations/versions/abc123_add_email_and_phone.py
# 3. Review the generated script (always!)
# $ cat migrations/versions/abc123_add_email_and_phone.py
# 4. Apply the migration
# $ flask db upgrade
# INFO: Running upgrade -> abc123
# 5. Check migration history
# $ flask db history
# abc123 -> (head), Add email and phone to users
# 987654 -> abc123, Initial migration
# 6. Rollback if needed
# $ flask db downgrade
# INFO: Running downgrade abc123 -> 987654The migration workflow is: change models, generate migration, review script, apply. Always review auto-generated scripts before applying. Use 'flask db history' to see the migration chain.
Flask-Migrate, Alembic, Migration Scripts, Upgrade, Downgrade, Schema Changes