Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 72 ยท Databases

Migrations with Flask-Migrate

Learn how database migrations work with Flask-Migrate and Alembic: the flask db commands, upgrade and downgrade, and how schema evolution works in plain SQL.

Your app is live, people have created accounts, and now you want to add a bio column to the users table. If you call db.create_all() again, nothing happens: it only creates tables that are missing and never alters existing ones. If you delete the database and start over, you lose everything. You need a way to change the structure of a database that already holds data, and to do it the same way on your laptop, in your tests and in production.

That is what migrations are. A migration is a small, versioned script that moves the schema one step forward (and, ideally, one step back). In the Flask world the tool is Flask-Migrate, a thin wrapper around Alembic, which is the migration tool of SQLAlchemy.

Flask-Migrate and the flask db command need a real project on disk, a terminal and usually a real database file, so they cannot run in the browser. The commands below are read-only. The runnable examples in this lesson reproduce the same ideas with plain SQL, so you can see exactly what a migration tool does for you.

The migration workflow

flowchart LR
    A["1. Edit a model"] --> B["2. flask db migrate<br/>(generate a script)"]
    B --> C["3. Review and fix<br/>the script"]
    C --> D["4. flask db upgrade<br/>(apply it)"]
    D --> E["5. Commit the script<br/>to Git"]

Models describe the schema you want; migration scripts are the recorded steps that get any database there.

The key idea is that the scripts, not the models, are the history. Everybody applies the same ordered list of scripts, so a new teammate, your CI server and your production database all end up with an identical schema.

Setting up Flask-Migrate

Install it with pip install Flask-Migrate, then connect it to your app and your db object. With the application factory from the next lesson it looks like this:

Python
from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()
migrate = Migrate()


def create_app():
    app = Flask(__name__)
    app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///app.db"
    db.init_app(app)
    migrate.init_app(app, db)
    return app

Importing Migrate adds a db command group to the flask CLI. All of the following run in a terminal, in your project folder, with your virtual environment active (add --app yourmodule if Flask cannot find the app on its own):

Terminal
flask db init                      # once: creates the migrations/ folder
flask db migrate -m "add bio"      # compare models with the database, write a script
flask db upgrade                   # apply all scripts that have not run yet
flask db downgrade                 # undo the most recent script
flask db current                   # which revision is the database at?
flask db history                   # list all revisions

flask db init creates a migrations/ folder with an env.py (how Alembic connects to your app) and a versions/ folder that will hold one Python file per migration. Commit this whole folder to Git.

What a migration script looks like

Each file in versions/ has a unique revision id, a pointer to its parent, and two functions:

Python
"""add bio to users

Revision ID: 3f2a1b9c7d10
Revises: a81c0e5d4b22
"""
from alembic import op
import sqlalchemy as sa

revision = "3f2a1b9c7d10"
down_revision = "a81c0e5d4b22"
branch_labels = None
depends_on = None


def upgrade():
    with op.batch_alter_table("users") as batch_op:
        batch_op.add_column(sa.Column("bio", sa.String(length=200), nullable=True))


def downgrade():
    with op.batch_alter_table("users") as batch_op:
        batch_op.drop_column("bio")

upgrade() moves the schema forward; downgrade() undoes it. Because every script names its parent through down_revision, the scripts form a chain, and Alembic stores the id of the last applied one in a one-row table called alembic_version inside your database.

flowchart LR
    A["(empty database)"] --> B["a81c0e5d4b22<br/>create users"]
    B --> C["3f2a1b9c7d10<br/>add bio"]
    C --> D["head"]
    T[("alembic_version<br/>3f2a1b9c7d10")] -. "current position" .-> C

Revisions form a chain. The alembic_version table records where this particular database stands, so upgrade knows what is left to run.

The batch_alter_table wrapper matters for SQLite, which can only do a few ALTER TABLE operations natively. For anything else, Alembic's batch mode rebuilds the table behind the scenes. Recent Flask-Migrate versions turn batch mode on by default, but look at the generated env.py to be sure about your version.

Simulating a migration tool in plain SQL

A migration tool is not magic. Its heart is a list of ordered steps, a record of how far this database has got, and code that runs the missing steps in order. This example builds exactly that with sqlite3, using a schema_version table in place of alembic_version. Each step has an up and a down statement.

Python A tiny migration runner

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Follow the output. Step 3 added a NOT NULL column with a DEFAULT, so the existing row for Ada received active = 1 automatically. That default is not decoration: adding a required column without one fails as soon as the table has any rows. Real migrations on populated tables need this kind of care. The downgrade then peels the steps off in reverse order until only the bookkeeping table remains.

A downgrade that drops a column or table destroys the data in it. flask db downgrade is a development convenience, not a safe production undo. For production, back up first and prefer a new forward migration that fixes the mistake.

When ALTER TABLE is not enough

SQLite cannot change an existing column's type, add a constraint to a column or make it required. The standard workaround, which Alembic's batch mode automates, is to rebuild the table: create a new one with the desired shape, copy the rows over (transforming them on the way), drop the old table and rename the new one. Do it inside a transaction so a failure leaves the original untouched.

Python Rebuilding a table to change column rules

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The text prices became real numbers, the old rows survived, and the new CHECK rule is enforced. MySQL and PostgreSQL can alter columns in place, so on those databases the generated scripts are shorter, but the thinking is the same: describe the change, copy the data, keep it reversible when you can.

How autogenerate decides what to write

flask db migrate compares two things: the tables described by your models, and the tables that actually exist in the database. Whatever differs becomes an operation in the new script. The next example does that comparison for columns using SQLAlchemy's inspector, so you can see both what autogenerate finds and where it is blind.

Python A mini autogenerate: models vs the live database

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Here you renamed nickname to bio in the model. The comparison cannot know that: it sees one column that disappeared and one that appeared, so it proposes a drop plus an add, which would throw the data away. Alembic behaves the same way. This is why you must always read the generated script before running it. When a change is really a rename, replace the generated pair with batch_op.alter_column("nickname", new_column_name="bio"), or write the data copy yourself.

Autogenerate also misses some things entirely, such as changed server defaults on some databases and table or column renames, so treat its output as a draft written by a helpful but literal assistant.

Good habits

  • One concern per migration. Small scripts are easier to review, test and reverse.
  • Never edit a migration that others have already applied. Add a new one instead.
  • Add required columns in steps on a populated table: add them as nullable (or with a default), backfill the data, then tighten the constraint.
  • Run flask db upgrade as part of deployment, before the new code starts serving requests, and keep the new code compatible with the old schema for a moment if you deploy without downtime. See Deployment.
  • Back up production before migrating. Test the whole chain on a copy of real data.
  • Use flask db stamp head only to tell Alembic that a database is already at the latest revision, for example when adopting migrations for a schema that was created by hand.

In tests you usually skip migrations and build the schema with db.create_all() on a throwaway in-memory database, as shown in Testing Flask Apps with pytest. Keep one separate check, perhaps in CI, that runs the full upgrade chain on an empty database, so a broken migration is caught before release.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You add a new column to a model and call db.create_all() on a database that already has the table. What happens?

    create_all() skips tables that already exist. Changing an existing table is the job of a migration.
  2. 2. What does the alembic_version table store?

    It holds the id of the last applied revision, so flask db upgrade knows which scripts still have to run.
  3. 3. You rename a model attribute from nickname to bio and run flask db migrate. What does autogenerate most likely write?

    It compares names, not intent. A rename looks like one column removed and another added, so review and correct the script.
  4. 4. Why do migrations for SQLite often use batch_alter_table?

    SQLite cannot alter many column properties in place. Batch mode creates a new table, copies the rows and swaps it in.
  5. 5. Which command applies every migration that has not run yet?

    migrate only generates a script. upgrade runs the pending scripts against the database.

Summary

  • db.create_all() never changes existing tables; migrations evolve a live schema safely and repeatably.
  • Flask-Migrate wraps Alembic: flask db init once, then migrate -m "..." to generate a script and upgrade to apply it.
  • Each revision file has upgrade() and downgrade() and points to its parent; the alembic_version table records the current position.
  • Always review autogenerated scripts: renames look like drop plus add, and some changes are not detected.
  • SQLite needs batch mode (table rebuild) for many changes; required columns on populated tables need a default or a backfill.
  • Commit the migrations/ folder, run upgrade during deployment, and back up before touching production data.

Next: The Application Factory, the structure that makes extensions like Flask-Migrate easy to wire up.