Flask Essentials: Schema Changes with Migrations

What migrations are (and why you want them)

A migration is a versioned, repeatable change to your database schema (and sometimes data) that can be applied forward (upgrade) or reversed (downgrade). In a Flask service using SQLAlchemy, migrations are the safe workflow for evolving tables, columns, indexes, and constraints without manually editing production databases.

Instead of “change models and hope the database matches,” migrations give you:

• Traceability: every schema change is a committed file with an ID and timestamp.
• Repeatability: the same steps run in dev, CI, staging, and prod.
• Reviewability: you can inspect the generated SQL operations before they run.
• Rollback: a downgrade path for most changes (with caveats for destructive operations).

In practice, you typically use Flask-Migrate (Alembic under the hood). The workflow is: change SQLAlchemy models → generate a migration diff → review/edit → apply to the database.

Tooling overview: Flask-Migrate + Alembic

Flask-Migrate integrates Alembic with Flask. Alembic tracks migration history in a table (usually alembic_version) inside your database. Each migration file contains two functions:

• upgrade(): apply changes
• downgrade(): reverse changes

Autogeneration compares your current database schema to your SQLAlchemy model metadata and produces operations like op.add_column, op.create_index, etc. Autogeneration is helpful, but not infallible—always review.

Continue in our app.

You can listen to the audiobook with the screen off, receive a free certificate for this course, and also have access to 5,000 other free online courses.

Or continue reading below...

Download the app

Step-by-step: create and apply a schema migration

1) Ensure migrations are initialized

If your project already has a migrations/ directory, you can skip this. Otherwise, initialize once:

flask db init

This creates the Alembic environment and configuration under migrations/.

2) Make a model change

Example: add a last_login_at column to a users table.

# models.py (example only; adapt to your project layout)  class User(db.Model):     __tablename__ = "users"     id = db.Column(db.Integer, primary_key=True)     email = db.Column(db.String(255), unique=True, nullable=False)     last_login_at = db.Column(db.DateTime, nullable=True)

3) Generate a migration (autodiff)

flask db migrate -m "add last_login_at to users"

This creates a new file in migrations/versions/. Autogeneration will attempt to detect the new column.

4) Review the generated migration

Open the new migration file and verify it matches your intent. A typical autogenerated migration might look like:

from alembic import op import sqlalchemy as sa  revision = "8b1c2f3a9c1d" down_revision = "1a2b3c4d5e6f"  def upgrade():     op.add_column("users", sa.Column("last_login_at", sa.DateTime(), nullable=True))  def downgrade():     op.drop_column("users", "last_login_at")

Review checklist for autogenerated diffs:

• Is the table name correct?
• Is the column type correct (e.g., DateTime vs String)?
• Are nullable, unique, defaults, and server defaults correct?
• Did it accidentally detect unrelated changes (e.g., index naming differences)?

5) Apply the migration

flask db upgrade

This runs all pending migrations up to the latest revision.

6) Verify the database state

At minimum, confirm the revision and inspect the schema:

flask db current flask db history --verbose

In addition, validate in your database client that the column exists and behaves as expected.

When autogenerate is not enough: custom migration steps

Autogenerate is primarily schema-aware, not business-data-aware. You often need custom steps for:

• Data migrations: transforming existing rows to match new schema expectations.
• Backfills: populating new columns based on existing data.
• Complex constraint transitions: adding constraints after cleaning data.
• Database-specific operations: partial indexes, concurrent index creation, triggers.

Pattern: add nullable column → backfill → enforce NOT NULL

Suppose you add users.timezone and want it to be required. Doing it in one step (add NOT NULL immediately) can fail if existing rows don’t have a value. A safer approach is a multi-migration rollout.

Migration A: add the column as nullable

def upgrade():     op.add_column("users", sa.Column("timezone", sa.String(length=64), nullable=True))  def downgrade():     op.drop_column("users", "timezone")

Migration B: backfill data

For backfills, you can use Alembic’s connection and execute SQL. Keep it deterministic and idempotent where possible.

from alembic import op import sqlalchemy as sa  def upgrade():     conn = op.get_bind()     # Example: set default timezone for existing users     conn.execute(sa.text("UPDATE users SET timezone = :tz WHERE timezone IS NULL"), {"tz": "UTC"})  def downgrade():     # Downgrade may be lossy; decide whether to revert or leave as-is     conn = op.get_bind()     conn.execute(sa.text("UPDATE users SET timezone = NULL WHERE timezone = :tz"), {"tz": "UTC"})

Notes for data migrations:

• Prefer SQL updates over ORM usage inside migrations to avoid importing application code and to keep migrations stable over time.
• For large tables, consider batching (e.g., updating by primary key ranges) to reduce locks and transaction time.
• Be explicit about which rows you modify (WHERE timezone IS NULL).

Migration C: enforce NOT NULL and add constraints

def upgrade():     op.alter_column("users", "timezone", existing_type=sa.String(length=64), nullable=False)  def downgrade():     op.alter_column("users", "timezone", existing_type=sa.String(length=64), nullable=True)

This staged approach reduces deployment risk and makes failures easier to diagnose.

Writing safe custom migrations: practical techniques

Use server defaults carefully

SQLAlchemy model defaults (Python-side) are not the same as database server defaults. If you need existing rows to get a value at the database level during migration, use a server default temporarily.

Example: add a non-nullable boolean with a default, without rewriting the whole table in a risky way (database-dependent):

def upgrade():     op.add_column(         "users",         sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("true"))     )     # Optional: remove server_default after backfill if you don't want it long-term     op.alter_column("users", "is_active", server_default=None)  def downgrade():     op.drop_column("users", "is_active")

Always confirm how your database handles adding columns with defaults (some engines rewrite the table; some optimize it).

Handle indexes and constraints explicitly

Autogenerate may miss or mis-detect certain index/constraint changes, especially naming differences. Prefer explicit operations:

def upgrade():     op.create_index("ix_users_email", "users", ["email"], unique=True)  def downgrade():     op.drop_index("ix_users_email", table_name="users")

For foreign keys and check constraints, ensure you name them (consistent naming helps diffing and rollbacks):

def upgrade():     op.create_foreign_key(         "fk_orders_user_id_users",         source_table="orders",         referent_table="users",         local_cols=["user_id"],         remote_cols=["id"],         ondelete="CASCADE",     )  def downgrade():     op.drop_constraint("fk_orders_user_id_users", "orders", type_="foreignkey")

Be cautious with destructive changes

Dropping columns/tables is irreversible in terms of data. If you must remove a column:

• First deploy code that no longer reads/writes it.
• Wait for a safe window (and confirm no consumers rely on it).
• Then drop it in a later migration.

For rollbacks, consider whether downgrade should recreate the column (empty) or whether you intentionally make downgrade a no-op with a clear comment.

Structured migration checklist (before you merge)

Rollout patterns: expand/contract for safer deploys

For services that deploy frequently, a reliable pattern is:

• Expand: add new schema elements in a backward-compatible way (new nullable columns, new tables, new indexes).
• Migrate: backfill data and dual-write if needed (application writes both old and new fields).
• Contract: remove old schema elements only after all code paths no longer use them.

This avoids “deploy order” incidents where one instance runs new code against an old schema (or vice versa).

Keeping migrations readable and maintainable

• One intent per migration: avoid mixing unrelated changes (e.g., “add column” plus “rename table”).
• Use descriptive messages: "add timezone to users" is better than "update".
• Prefer explicit names for indexes and constraints to reduce environment drift.
• Comment non-obvious steps: especially for data fixes, batching, or irreversible operations.
• Split risky operations into multiple migrations (schema first, data second, constraints third).

Testing migrations in CI

Migrations should be tested like code. A practical CI strategy:

• Create a fresh database.
• Run flask db upgrade to head.
• Optionally run a smoke test that exercises basic queries.
• Run flask db downgrade (at least one step) when feasible to validate downgrade paths.

Example CI commands (conceptual):

# Start DB service (e.g., Postgres) # Set DATABASE_URL for CI  flask db upgrade  pytest -q  flask db downgrade -1  flask db upgrade

Also consider a “migration drift” check: compare model metadata to the database after upgrading. If your team relies on autogenerate, this helps catch missed operations (like indexes or constraints not represented in models).

Aligning migration history across environments

Environment mismatches happen when:

• Someone ran manual SQL in staging/prod.
• Migrations were squashed or rebased incorrectly.
• Two branches generated migrations from the same base revision and were merged without resolving heads.

Detecting multiple heads

If Alembic reports multiple heads, list them:

flask db heads flask db history

To resolve, create a merge migration:

flask db merge -m "merge heads" <head1> <head2>

This produces a migration that reconciles divergent histories (it doesn’t apply schema changes by itself; it ties the graph together).

Stamping (use sparingly)

flask db stamp marks the database as being at a given revision without running migrations. This is useful only when you are absolutely sure the schema already matches (for example, restoring from a snapshot). Misuse can permanently desynchronize environments.

# Mark DB as at head without applying migrations (dangerous if wrong) flask db stamp head

Operational discipline

• Never edit applied migrations: once a migration has run in any shared environment, treat it as immutable.
• Always commit migration files with the code change that requires them.
• Run migrations the same way everywhere: same commands, same entrypoint, same config.
• Track schema changes through PR review: reviewers should read the migration file, not only the model diff.