Best real-world examples of Flask-Migrate database migration examples

Quick setup before the examples

All of the examples of Flask-Migrate database migration examples below assume a baseline project that looks roughly like this:

demo_app/
  app.py
  models.py
  migrations/        # auto-created by Flask-Migrate
  venv/

Minimal setup in 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"  # swap for Postgres in real life
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

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

from models import *  # noqa: E402,F401

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

Initialize migrations once:

flask db init

From here, every flask db migrate creates a new migration script in migrations/versions, and flask db upgrade applies it to the database.

Example of adding your first table with Flask-Migrate

Let’s start with the most common case: introducing your first model. This is the baseline example of Flask-Migrate database migration examples that everything else builds on.

models.py:

from app import db

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True, nullable=False)
    created_at = db.Column(db.DateTime, server_default=db.func.now())

Generate and apply the migration:

flask db migrate -m "Create user table"
flask db upgrade

Auto-generated migration (simplified):

from alembic import op
import sqlalchemy as sa

revision = "001_create_user"
down_revision = None


def upgrade():
    op.create_table(
        "user",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("email", sa.String(length=255), nullable=False),
        sa.Column("created_at", sa.DateTime(), server_default=sa.func.now()),
        sa.UniqueConstraint("email"),
    )


def downgrade():
    op.drop_table("user")

This is the simplest example of a forward-only change: you define the model, let Flask-Migrate detect it, then inspect and commit the generated migration.

Real examples of adding and backfilling a non-nullable column

Reality check: most schema changes are not this clean. A very common pattern in real examples of Flask-Migrate database migration examples is adding a non-nullable column to a table that already has data.

Say your User model grows a status field:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True, nullable=False)
    status = db.Column(db.String(20), nullable=False, server_default="active")
    created_at = db.Column(db.DateTime, server_default=db.func.now())

If you just run:

flask db migrate -m "Add status to user"

the generated migration might look like this:

from alembic import op
import sqlalchemy as sa

revision = "002_add_status_to_user"
down_revision = "001_create_user"


def upgrade():
    op.add_column(
        "user",
        sa.Column("status", sa.String(length=20), nullable=False, server_default="active"),
    )


def downgrade():
    op.drop_column("user", "status")

On a production database with thousands of users, that server_default is doing real work: it keeps the migration from failing when existing rows are updated. After deployment, you can optionally drop the default in a later migration if you want stricter behavior.

This pattern—add column with a default, backfill, then tighten constraints—is one of the best examples of Flask-Migrate database migration examples that scales from toy apps to serious systems.

Examples of Flask-Migrate database migration examples for renaming a column safely

Column renames are where people get burned. Databases don’t like losing track of column names while your app still expects the old ones. A safer pattern is a two-step, backward-compatible migration.

Imagine you want to rename email to primary_email.

Step 1: Add the new column and keep both

Update models.py temporarily:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True, nullable=False)
    primary_email = db.Column(db.String(255), unique=True)
    status = db.Column(db.String(20), nullable=False, server_default="active")
    created_at = db.Column(db.DateTime, server_default=db.func.now())

Generate migration:

flask db migrate -m "Add primary_email column"

Edit the migration to copy data:

from alembic import op
import sqlalchemy as sa

revision = "003_add_primary_email"
down_revision = "002_add_status_to_user"


def upgrade():
    op.add_column("user", sa.Column("primary_email", sa.String(length=255), nullable=True))

#    # Copy existing values
    conn = op.get_bind()
    conn.execute(sa.text("UPDATE "user" SET primary_email = email"))


def downgrade():
    op.drop_column("user", "primary_email")

Deploy this. Update your application code to start reading from primary_email while still writing both email and primary_email for a while.

Step 2: Drop the old column once traffic is migrated

After you’re confident no code depends on email, change the model:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    primary_email = db.Column(db.String(255), unique=True, nullable=False)
    status = db.Column(db.String(20), nullable=False)
    created_at = db.Column(db.DateTime, server_default=db.func.now())

Generate another migration:

flask db migrate -m "Drop legacy email column"

Edit the migration:

from alembic import op
import sqlalchemy as sa

revision = "004_drop_email"
down_revision = "003_add_primary_email"


def upgrade():
    op.drop_column("user", "email")
    op.alter_column("user", "primary_email", nullable=False)


def downgrade():
    op.add_column(
        "user",
        sa.Column("email", sa.String(length=255), nullable=False),
    )
#    # Best effort reverse copy
    conn = op.get_bind()
    conn.execute(sa.text("UPDATE "user" SET email = primary_email"))

This two-step approach is one of the best examples of Flask-Migrate database migration examples for avoiding downtime during renames.

Examples include adding indexes and constraints for performance

As your traffic grows, you’ll start tuning queries. That means adding indexes and constraints, which is another category where real examples of Flask-Migrate database migration examples matter.

Suppose you’re seeing slow lookups by status and created_at. Add an index in a migration without touching your model:

from alembic import op
import sqlalchemy as sa

revision = "005_add_status_created_idx"
down_revision = "004_drop_email"


def upgrade():
    op.create_index(
        "ix_user_status_created_at",
        "user",
        ["status", "created_at"],
    )


def downgrade():
    op.drop_index("ix_user_status_created_at", table_name="user")

This is a good example of a migration that exists purely for performance. Your SQLAlchemy model doesn’t need to know about it, but your database—and your users—definitely care.

For background on why indexes matter for query performance, the PostgreSQL documentation is a solid reference.

Example of many-to-many relationships with an association table

Let’s say you add a Role model and a many-to-many relationship between User and Role.

models.py:

user_roles = db.Table(
    "user_roles",
    db.Column("user_id", db.Integer, db.ForeignKey("user.id"), primary_key=True),
    db.Column("role_id", db.Integer, db.ForeignKey("role.id"), primary_key=True),
)


class Role(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True, nullable=False)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    primary_email = db.Column(db.String(255), unique=True, nullable=False)
    status = db.Column(db.String(20), nullable=False)
    created_at = db.Column(db.DateTime, server_default=db.func.now())
    roles = db.relationship("Role", secondary=user_roles, backref="users")

Generate the migration:

flask db migrate -m "Add roles and user_roles"

The migration (trimmed) will include both the role table and the user_roles association table. This is another one of those practical examples of Flask-Migrate database migration examples where the tool keeps the boilerplate under control while you focus on model design.

Examples of Flask-Migrate database migration examples in CI and multiple environments

In 2024–2025, most teams are running tests and migrations in CI before anything touches production. Flask-Migrate plays nicely with that workflow.

A typical pattern in GitHub Actions or similar:

## In CI script
flask db upgrade        # run against test DB
pytest                  # run your test suite

On deployment, you might run:

flask db upgrade        # run migrations against staging
## smoke tests here
flask db upgrade        # finally run against production

The same migration history runs in dev, CI, staging, and production. That consistency is one of the best examples of how migration tooling keeps you out of trouble.

If you want to get more formal about database change management, the NIST Software Assurance guidelines and the US Digital Services Playbook provide broader context for controlled releases and change review, even though they’re not Flask-specific.

Example of data migrations alongside schema migrations

Sometimes you need to massage data, not just the schema. Flask-Migrate (via Alembic) lets you write data migrations that run as part of the same flask db upgrade command.

Imagine you add a display_name column and want to initialize it based on the email prefix.

models.py:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    primary_email = db.Column(db.String(255), unique=True, nullable=False)
    display_name = db.Column(db.String(255))
    status = db.Column(db.String(20), nullable=False)
    created_at = db.Column(db.DateTime, server_default=db.func.now())

Generate migration:

flask db migrate -m "Add display_name"

Edit the migration to include a data step:

from alembic import op
import sqlalchemy as sa

revision = "006_add_display_name"
down_revision = "005_add_status_created_idx"


def upgrade():
    op.add_column("user", sa.Column("display_name", sa.String(length=255), nullable=True))

    conn = op.get_bind()
#    # Simple example: everything before '@' becomes display_name
    conn.execute(
        sa.text(
            "UPDATE "user" SET display_name = split_part(primary_email, '@', 1) "
            "WHERE display_name IS NULL"
        )
    )


def downgrade():
    op.drop_column("user", "display_name")

This is a realistic example of Flask-Migrate database migration examples where schema and data changes ship together, and you keep the logic versioned in Git.

Handling breaking changes: examples include staged rollouts

The hardest migrations are the ones that break old code: dropping columns, changing data types, or tightening constraints. The better examples of Flask-Migrate database migration examples all share the same pattern: stage the change.

A common three-phase flow:

• Phase 1: Add new columns or tables in a backward-compatible way. Application code writes to both old and new structures.
• Phase 2: Switch reads to the new structure, keep writing both for a while, and monitor.
• Phase 3: Once you’re confident, remove the old structure in a final migration.

You saw that pattern in the email → primary_email rename. You can reuse the same idea for type changes (string to integer IDs), moving data into a new table, or enforcing new constraints.

For general thinking about staged rollouts and risk reduction, the Harvard Kennedy School’s digital governance materials offer good reading on controlled change, even though they’re not about Flask directly.

FAQ: common questions about Flask-Migrate database migration examples

Q: Can you show a very simple example of using Flask-Migrate from scratch?
Yes. Create a Flask app, configure SQLALCHEMY_DATABASE_URI, initialize db and Migrate, then run flask db init, flask db migrate, and flask db upgrade. The first example above, where we create a User table, is the minimal example of going from zero to a migrated database.

Q: How do I avoid conflicts when two developers generate migrations at the same time?
Treat migrations like code. Each developer runs flask db migrate, commits the generated file, and pushes. If branches diverge, you may need to edit down_revision or merge migration files manually. The key is to run flask db upgrade locally before pushing, so you don’t ship a migration that fails on your own machine.

Q: Are there examples of using Flask-Migrate with PostgreSQL specifically?
Yes. All the examples of Flask-Migrate database migration examples here work with PostgreSQL; you just change the SQLALCHEMY_DATABASE_URI to something like postgresql+psycopg2://user:pass@host/dbname. For PostgreSQL-specific features (JSONB, partial indexes, etc.), you’ll usually edit the generated migrations by hand using SQLAlchemy types or raw SQL.

Q: Do I need a migration for every small change?
If it changes the schema or persistent data, yes. The best examples of production-ready Flask-Migrate database migration workflows show one migration per logical change set. That makes rollbacks, debugging, and code review much easier.

Q: How do I roll back a bad migration?
Use flask db downgrade <revision> to step back to a previous revision. This is why every migration script in these examples includes both upgrade() and downgrade() functions. Be careful, though: if a migration dropped data, downgrading won’t magically restore it.

If you treat these examples of Flask-Migrate database migration examples as patterns—add with defaults, stage breaking changes, keep migrations in version control—you’ll be far less likely to wake up to a broken production database. And that’s the real goal here: boring, predictable schema changes that let you focus on shipping features, not firefighting.