Skip to main content

​
Overview

The Muebles Roble system uses Flask-Migrate (built on Alembic) to manage database schema changes. Migrations provide version control for the database schema and enable safe, repeatable deployments.

​
Migration Setup

​
Extension Configuration

Flask-Migrate is initialized in the application factory:
app/__init__.py
from .extensions import db, migrate

def create_app():
    app = Flask(__name__)
    
    # Initialize extensions
    db.init_app(app)
    migrate.init_app(app, db)  # Initialize Flask-Migrate
    
    # Import models to register them
    from . import models
    
    return app
app/extensions.py
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

db = SQLAlchemy()
migrate = Migrate()

​
Migration Environment

The migration environment is configured in migrations/env.py:
migrations/env.py
import logging
from logging.config import fileConfig
from flask import current_app
from alembic import context

config = context.config
fileConfig(config.config_file_name)
logger = logging.getLogger('alembic.env')

def get_engine():
    try:
        return current_app.extensions['migrate'].db.get_engine()
    except (TypeError, AttributeError):
        return current_app.extensions['migrate'].db.engine

def get_metadata():
    if hasattr(target_db, 'metadatas'):
        return target_db.metadatas[None]
    return target_db.metadata

def run_migrations_online():
    """Run migrations in 'online' mode."""
    connectable = get_engine()
    
    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=get_metadata(),
            **conf_args
        )
        
        with context.begin_transaction():
            context.run_migrations()
The migration environment automatically detects the database connection from the Flask application configuration.

​
Migration Commands

​
Initialize Migrations

Run once to set up the migrations directory: 
flask db init
This creates the migrations/ directory structure: 
migrations/
├── alembic.ini
├── env.py
├── README
├── script.py.mako
└── versions/
    ├── 5aa5020316c5_create_colors_table.py
    ├── e57af1e0a8f8_create_roles_table.py
    └── ...

​
Generate a Migration

After modifying models, generate a migration: 
flask db migrate -m "Add description field to colors"
This auto-generates a migration file based on model changes: 
"""Add description field to colors

Revision ID: abc123def456
Revises: 5aa5020316c5
Create Date: 2026-03-04 10:30:00.123456
"""
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('colors', 
                  sa.Column('description', sa.String(255), nullable=True))

def downgrade():
    op.drop_column('colors', 'description')
Always review auto-generated migrations before applying them. Alembic may not detect all schema changes correctly.

​
Apply Migrations

Apply pending migrations to the database: 
flask db upgrade
This runs all migrations that haven’t been applied yet.

​
Rollback Migrations

Revert the last migration: 
flask db downgrade
Revert to a specific migration: 
flask db downgrade abc123def456

​
Check Migration Status

View current migration status: 
flask db current
View migration history: 
flask db history

​
Migration File Structure

​
Example Migration: Create Colors Table

migrations/versions/5aa5020316c5_create_colors_table.py
"""create colors table

Revision ID: 5aa5020316c5
Revises: 
Create Date: 2026-02-17 12:07:59.141977
"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = '5aa5020316c5'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('colors',
        sa.Column('id_color', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(length=50), nullable=False),
        sa.Column('active', sa.Boolean(), nullable=False),
        sa.Column('created_at', sa.TIMESTAMP(), 
                  server_default=sa.text('CURRENT_TIMESTAMP'), 
                  nullable=False),
        sa.Column('updated_at', sa.TIMESTAMP(), 
                  server_default=sa.text('CURRENT_TIMESTAMP'), 
                  nullable=False),
        sa.Column('deleted_at', sa.TIMESTAMP(), nullable=True),
        sa.Column('created_by', sa.String(length=100), nullable=True),
        sa.Column('updated_by', sa.String(length=100), nullable=True),
        sa.Column('deleted_by', sa.String(length=100), nullable=True),
        sa.PrimaryKeyConstraint('id_color'),
        sa.UniqueConstraint('name')
    )
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('colors')
    # ### end Alembic commands ###

​
Migration Components

Each migration has metadata for tracking:
revision = '5aa5020316c5'        # Unique ID for this migration
down_revision = None              # Previous migration ID
branch_labels = None              # Optional labels
depends_on = None                 # Dependencies on other migrations
Defines changes to apply:
def upgrade():
    op.create_table('colors', ...)
    op.add_column('table', ...)
    op.alter_column('table', 'column', ...)
Defines how to revert changes:
def downgrade():
    op.drop_table('colors')
    op.drop_column('table', 'column')

​
Common Migration Operations

​
Create Table

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table(
        'materials',
        sa.Column('id_material', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(100), nullable=False),
        sa.Column('active', sa.Boolean(), nullable=False, default=True),
        sa.Column('created_at', sa.TIMESTAMP(), 
                  server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.PrimaryKeyConstraint('id_material'),
        sa.UniqueConstraint('name')
    )

def downgrade():
    op.drop_table('materials')

​
Add Column

def upgrade():
    op.add_column('colors', 
                  sa.Column('hex_code', sa.String(7), nullable=True))

def downgrade():
    op.drop_column('colors', 'hex_code')

​
Modify Column

def upgrade():
    op.alter_column('colors', 'name',
                    existing_type=sa.String(50),
                    type_=sa.String(100),
                    nullable=False)

def downgrade():
    op.alter_column('colors', 'name',
                    existing_type=sa.String(100),
                    type_=sa.String(50),
                    nullable=False)

​
Add Foreign Key

def upgrade():
    op.add_column('furniture', 
                  sa.Column('id_color', sa.Integer(), nullable=True))
    op.create_foreign_key(
        'fk_furniture_color',
        'furniture', 'colors',
        ['id_color'], ['id_color']
    )

def downgrade():
    op.drop_constraint('fk_furniture_color', 'furniture', type_='foreignkey')
    op.drop_column('furniture', 'id_color')

​
Create Index

def upgrade():
    op.create_index('idx_colors_name', 'colors', ['name'])

def downgrade():
    op.drop_index('idx_colors_name', 'colors')

​
Data Migrations

Sometimes you need to modify data, not just schema: 
def upgrade():
    # Get connection
    conn = op.get_bind()
    
    # Execute raw SQL
    conn.execute(
        "UPDATE colors SET name = UPPER(name) WHERE active = 1"
    )
    
    # Or use SQLAlchemy Core
    from sqlalchemy.sql import table, column
    colors = table('colors',
                   column('name', sa.String),
                   column('active', sa.Boolean))
    
    conn.execute(
        colors.update()
        .where(colors.c.active == True)
        .values(name=sa.func.upper(colors.c.name))
    )
Data migrations should be carefully tested as they can’t always be automatically reversed.

​
Migration Best Practices

Always inspect migrations before applying:
# Generate migration
flask db migrate -m "Add description field"

# Review the generated file
cat migrations/versions/latest_migration.py

# Apply only after review
flask db upgrade
Migration messages should be clear and specific:
# Good
flask db migrate -m "Add hex_code field to colors table"
flask db migrate -m "Create materials table"

# Bad
flask db migrate -m "Update database"
flask db migrate -m "Changes"
Always test both upgrade and downgrade:
# Apply migration
flask db upgrade

# Verify it works
flask shell
>>> from app.models import Color
>>> Color.query.all()

# Test rollback
flask db downgrade

# Re-apply
flask db upgrade
Once a migration is applied to production, never modify it. Create a new migration instead:
# Don't modify existing migration
# Instead, create a new one
flask db migrate -m "Fix colors table schema"
Always backup the database before running migrations in production:
# Backup database
mysqldump -u user -p database > backup.sql

# Run migration
flask db upgrade

# If something goes wrong
mysql -u user -p database < backup.sql

​
Migration Workflow

1

Modify Models

Update your model definitions:
app/models/color.py
class Color(db.Model):
    # Add new field
    hex_code = db.Column(db.String(7), nullable=True)
2

Generate Migration

Create a migration from model changes:
flask db migrate -m "Add hex_code to colors"
3

Review Migration File

Check the generated migration in migrations/versions/
4

Test Migration

Apply and test in development:
flask db upgrade
flask db downgrade
flask db upgrade
5

Commit to Version Control

Add the migration file to git:
git add migrations/versions/abc123_add_hex_code_to_colors.py
git commit -m "Add hex_code field to colors"
6

Deploy to Production

Apply migration in production:
# Backup first!
mysqldump -u user -p database > backup.sql

# Apply migration
flask db upgrade

​
Troubleshooting

​
Migration Conflicts

If multiple developers create migrations simultaneously: 
# List all migrations
flask db heads

# Merge branches
flask db merge heads -m "Merge migration branches"

​
Reset Migrations

In development only, you can reset: 
# Drop all tables
flask db downgrade base

# Or delete migrations and start fresh
rm -rf migrations/
flask db init
flask db migrate -m "Initial migration"
flask db upgrade
Never reset migrations in production. This will result in data loss.

​
Manual Migration Editing

If auto-generation fails, edit manually: 
def upgrade():
    # Alembic didn't detect this change
    op.execute("""
        ALTER TABLE colors 
        ADD COLUMN hex_code VARCHAR(7) NULL
    """)

def downgrade():
    op.execute("ALTER TABLE colors DROP COLUMN hex_code")

​
Next Steps

Database Models

Learn about model definitions

Database Schema

View the complete database schema

Build docs developers (and LLMs) love

Get started for freeTalk to us