Flask Migrations

When developing web applications with Flask and a database, you'll inevitably need to modify your database schema as your application evolves. Adding columns, changing data types, or creating new tables manually becomes tedious and error-prone, especially in production environments. This is where database migrations come into play.

Introduction to Database Migrations

Database migrations are a controlled way to evolve your database schema over time. They allow you to:

• Track changes to your database schema in version control
• Apply schema changes consistently across different environments
• Roll back changes if something goes wrong
• Collaborate effectively with other developers

Flask-Migrate is an extension that provides migration support for Flask applications using SQLAlchemy. It's built on top of Alembic, a database migration tool for SQLAlchemy.

Setting Up Flask-Migrate

Installation

First, you need to install Flask-Migrate:

bash

pip install Flask-Migrate

Basic Setup

Here's how to set up Flask-Migrate in your Flask application:

python

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'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

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

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

    def __repr__(self):
        return f'<User {self.username}>'

Migration Commands

Flask-Migrate adds a set of migration commands to Flask's command-line interface. Here are the most important ones:

Initialize Migrations

To start using migrations, you need to initialize the migrations directory:

bash

flask db init

This creates a migrations folder in your project with the necessary files to track and manage migrations.

Create a Migration

After initializing or when you need to make changes to your models, generate a migration script:

bash

flask db migrate -m "Initial migration"

This command:

1. Examines the differences between your database models and the current database schema
2. Creates a new migration script in the migrations/versions folder with the changes needed

Apply Migrations

To apply the pending migrations to the database:

bash

flask db upgrade

This executes all migration scripts that haven't been run yet.

Rolling Back

If you need to undo the last migration:

bash

flask db downgrade

Real-World Example: Evolving a Blog Application

Let's go through a practical example of using migrations in a simple blog application.

Initial Setup

python

# 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:///blog.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

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

# Initial model
class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)

@app.route('/')
def index():
    return 'Blog Home Page'

if __name__ == '__main__':
    app.run(debug=True)

Initialize migrations and create the first migration:

bash

flask db init
flask db migrate -m "Create Post model"
flask db upgrade

Adding a Category Relationship

Now, let's say we want to add categories to our blog posts:

python

# Updated models
class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)
    # New columns
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    category = db.relationship('Category', backref=db.backref('posts', lazy=True))

Generate a new migration:

bash

flask db migrate -m "Add category relationship"

The migration script will contain the changes needed:

python

# migrations/versions/xxxxxxxxxxxx_add_category_relationship.py
"""Add category relationship

Revision ID: xxxxxxxxxxxx
Revises: xxxxxxxxxxxx
Create Date: 2023-05-20 12:34:56.789012

"""
# ... Alembic imports ...

def upgrade():
    # Create category table
    op.create_table('category',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(length=50), nullable=False),
        sa.PrimaryKeyConstraint('id')
    )
    # Add category_id to post
    op.add_column('post', sa.Column('category_id', sa.Integer(), nullable=True))
    op.create_foreign_key(None, 'post', 'category', ['category_id'], ['id'])

def downgrade():
    # Remove foreign key
    op.drop_constraint(None, 'post', type_='foreignkey')
    # Remove category_id column
    op.drop_column('post', 'category_id')
    # Drop category table
    op.drop_table('category')

Apply the migration:

bash

flask db upgrade

Adding Publication Date

Later, we decide to add a publication date to posts:

python

from datetime import datetime

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    category = db.relationship('Category', backref=db.backref('posts', lazy=True))
    # New column
    pub_date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)

Generate and apply the migration:

bash

flask db migrate -m "Add publication date"
flask db upgrade

Best Practices

1. Always review migration scripts: Alembic generates migration scripts automatically, but it's important to review them before applying changes to ensure they do what you expect.

2. Commit migrations to version control: Migration scripts should be part of your version control system to ensure all developers and environments use the same migrations.

3. Test migrations: Before applying migrations to a production database, test them in a development or staging environment.

4. Don't modify existing migrations: Once a migration has been applied and shared, create new migrations for additional changes instead of modifying existing ones.

5. Handle data migrations separately: For complex data transformations, you might need to write custom scripts to handle data migration alongside schema changes.

Advanced Migration Techniques

Custom Migration Scripts

Sometimes you need to perform operations that can't be detected automatically. You can write custom migration scripts:

bash

flask db revision -m "Custom migration"

This creates an empty migration script that you can edit:

python

def upgrade():
    # Your custom migration logic here
    op.execute("UPDATE users SET status = 'active' WHERE status IS NULL")

def downgrade():
    # Logic to reverse the upgrade
    op.execute("UPDATE users SET status = NULL WHERE status = 'active'")

Multiple Database Support

Flask-Migrate supports multiple databases:

python

app = Flask(__name__)
app.config['SQLALCHEMY_BINDS'] = {
    'users': 'sqlite:///users.db',
    'posts': 'sqlite:///posts.db'
}
db = SQLAlchemy(app)
migrate = Migrate(app, db)

To run migrations for a specific database:

bash

flask db migrate --multidb

Troubleshooting Common Issues

Head Mismatch

If you get a "Target database is not up to date" error, it means there's a mismatch between the current and head revisions. This often happens when migrations are applied manually or out of order. Fix it by:

bash

flask db stamp head  # Mark the current database as up to date

Autogenerate Not Detecting Changes

Sometimes Alembic doesn't detect all changes. Common cases include:

• Changes to constraints or indexes
• Table or column name changes
• Changes to server defaults

For these cases, you may need to write manual migration scripts.

Migration Error Recovery

If a migration fails halfway through:

1. Fix the issue that caused the failure
2. Run flask db upgrade again
3. If needed, manually fix data inconsistencies

Summary

Flask-Migrate is an essential tool for managing database schema changes in Flask applications. It allows you to:

• Track and version control database changes
• Safely apply schema modifications
• Roll back problematic changes
• Maintain consistency across development, testing, and production environments

By following good practices with database migrations, you can evolve your database schema with confidence, even in complex applications.

Additional Resources

1. Official Flask-Migrate Documentation
2. Alembic Documentation
3. SQLAlchemy Documentation

Exercises

1. Create a Flask application with SQLAlchemy models for a simple e-commerce system (products, orders, customers) and set up Flask-Migrate.

2. Add a new field to an existing model and create a migration for it.

3. Create a migration that adds a new model and relates it to existing models.

4. Write a custom migration script that populates a new column with data derived from existing columns.

5. Simulate a failed migration and practice recovering from it.