Database Schema Migrations with Flask-Migrate

In development, dropping and recreating database tables with db.drop_all() and db.create_all() wipes out all existing data. In production, destroying production data to add a new column or table is completely unacceptable.

Flask-Migrate wraps Alembic—SQLAlchemy's database migration engine—into the flask CLI, providing automated schema diffing and version-controlled migration scripts.

1. Installing and Configuring Flask-Migrate

Install the extension:

In app.py:

2. The Migration Lifecycle

Flask-Migrate adds the flask db command group to your terminal:

Step 1: Initializing Migrations Repository

Run this once when starting a new project:

This creates a migrations/ directory in your project root containing Alembic configuration files and an alembic_version tracking table.

Step 2: Generating a Migration Script

Whenever you add, modify, or delete columns or models in models.py, generate a revision:

Alembic inspects your SQLAlchemy models, compares them to the active database schema, and writes a Python migration script into migrations/versions/.

Step 3: Inspecting the Generated Script

Always inspect the generated file in migrations/versions/. Notice the upgrade() and downgrade() functions:

Step 4: Applying Migrations

Apply the revision to your database:

Step 5: Rolling Back a Migration

If a migration causes an issue, roll back to the previous revision:

3. Important Caveats & SQLite Limitations

1. 1
   SQLite Alter Column Restrictions: SQLite does not natively support dropping or altering columns. In Flask-Migrate, enable render_as_batch=True in Migrate(app, db, render_as_batch=True) so Alembic recreates the table under the hood during complex migrations on SQLite.
2. 2
   Commit Migrations to Git: The migrations/ directory must be checked into version control so team members and CI/CD deployment pipelines apply identical schema migrations.