When building a Flask application with a MySQL database, you’ll often need to evolve your database schema — adding tables, changing columns, or updating constraints.
Instead of running raw SQL manually, you can use Flask-Migrate, which integrates Alembic to manage migrations automatically.

In this tutorial, you’ll learn how to:

  • Set up Flask with MySQL
  • Install and configure Flask-Migrate
  • Create and apply migrations
  • Handle common issues

Prerequisites

Make sure you have:

  • Python installed
  • MySQL server running
  • MySQL database created
  • Basic Flask app

Also, install the necessary Python packages:

pip install Flask Flask-SQLAlchemy Flask-Migrate pymysql

pymysql is the driver that SQLAlchemy will use to connect to MySQL.

Step 1: Set Up Flask App with MySQL

Create a new Python file (e.g., app.py) and add the following code:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__)

# MySQL database configuration
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://username:password@localhost/db_name'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

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

# Example model
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), unique=True, nullable=False)

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

Don’t forget to:

  • Replace username, password, and db_name with your actual MySQL credentials.

Step 2: Initialize Migrations

First, you need to set the Flask application environment variable:

On Linux/macOS:

export FLASK_APP=app.py

On Windows (CMD):

set FLASK_APP=app.py

Now, initialize the migration environment:

flask db init

This will create a migrations/ directory in your project.

Step 3: Create Migration Script

Once your models are defined, generate the initial migration:

flask db migrate -m "Initial migration."

Flask-Migrate will scan your models and create a migration script in the migrations/versions/ directory.

Step 4: Apply the Migration to MySQL

Now apply the migration to your MySQL database:

flask db upgrade

Done! Your MySQL database now has the User table created.

Step 5: Make Changes and Migrate

Let’s say you want to add an email column to the User model:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)  # New column

Now, repeat:

flask db migrate -m "Added email column to User table."
flask db upgrade

Flask-Migrate will handle generating and applying the necessary ALTER TABLE commands.

Step 6: Rollback Changes (Optional)

If you need to undo the last migration:

flask db downgrade

You can also step through revisions:

flask db downgrade -1

Step 7: Check Migration History

To see your migration history:

flask db history

To see the current state:

flask db current

Common Issues with MySQL (and Fixes)

  • Problem: No changes detected
    Fix: Make sure your models are imported in your main app file!
  • Problem: Can't connect to MySQL server
    Fix: Double-check your SQLALCHEMY_DATABASE_URI and ensure MySQL server is running.
  • Problem: Collation or charset issues
    Fix: Set your database and tables to use utf8mb4 for better compatibility:
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • Problem: Data loss during type change
    Fix: Review migration scripts carefully, especially when altering existing columns.

Final Tips

  • Always review the generated migration scripts.
  • Keep your migrations/ directory under version control.
  • Test migrations in a dev or staging environment first!

Conclusion

Flask-Migrate makes handling database schema changes with MySQL smooth and reliable.
No more manual ALTER TABLE scripts — you can manage your database evolution directly from your Flask app.

Now you’re ready to build and grow your Flask + MySQL application confidently!

Categorized in:

Flask

Tagged in:

, , ,