Skip to main content

Database Migrations with Flask-Migrate

Database Migrations with Flask-Migrate

Database migrations are essential for managing schema changes in Flask applications, especially in data-driven projects like dashboards, machine learning (ML) interfaces, or user management systems. Flask-Migrate, built on top of Alembic and integrated with Flask-SQLAlchemy, simplifies the process of creating, applying, and tracking database migrations. This guide covers database migrations with Flask-Migrate, including setup, creating and applying migrations, handling schema changes, and practical examples tailored to data-driven applications.

01. Overview of Database Migrations

Database migrations allow developers to evolve a database schema over time (e.g., adding tables, modifying columns) while preserving existing data. Flask-Migrate automates this process by generating migration scripts and applying them to the database, ensuring consistency across development, testing, and production environments.

  • Purpose: Manage database schema changes systematically and safely.
  • Key Components: Flask-SQLAlchemy, Flask-Migrate, Alembic, migration scripts.
  • Use Cases: Adding new fields for ML model metadata, updating user tables, or modifying analytics schemas.

1.1 Challenges and Risks

  • Data Loss: Schema changes (e.g., dropping columns) can delete data if not handled carefully.
  • Inconsistency: Mismatched schemas across environments can cause errors.
  • Downtime: Improper migrations can disrupt application availability.
  • Security: Migration scripts must avoid exposing sensitive data or credentials.

02. Setting Up Flask-Migrate

2.1 Installation

Install Flask-SQLAlchemy and Flask-Migrate, along with a database driver (e.g., psycopg2 for PostgreSQL or sqlite3 for SQLite, which is built into Python): 

pip install flask-sqlalchemy flask-migrate psycopg2-binary

2.2 Project Structure

project/
├── app.py
├── migrations/  # Auto-generated by Flask-Migrate
├── static/
│   ├── css/
│   │   └── style.css
├── templates/
│   ├── base.html
│   ├── users.html
│   └── add_user.html
└── instance/
    └── database.db  # SQLite database

2.3 Basic Configuration

Configure Flask-SQLAlchemy and Flask-Migrate, and initialize the migration environment.

Example: Setting Up Flask-Migrate with SQLite

File: app.py

from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField
from wtforms.validators import DataRequired
import os

app = Flask(__name__)
app.config['SECRET_KEY'] = 'your-secret-key'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///instance/database.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

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

# Define User model
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

# Form for adding users
class UserForm(FlaskForm):
    name = StringField('Name', validators=[DataRequired()])
    email = StringField('Email', validators=[DataRequired()])
    submit = SubmitField('Add User')

@app.route('/')
def index():
    users = User.query.all()
    return render_template('users.html', users=users)

@app.route('/add', methods=['GET', 'POST'])
def add_user():
    form = UserForm()
    if form.validate_on_submit():
        user = User(name=form.name.data, email=form.email.data)
        db.session.add(user)
        db.session.commit()
        return redirect(url_for('index'))
    return render_template('add_user.html', form=form)

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

File: templates/base.html

<!DOCTYPE html>
<html>
<head>
    <title>{% block title %}Flask Database Migrations{% endblock %}</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <link rel="stylesheet" href="{{ url_for('static', filename='css/style.css') }}">
</head>
<body>
    <div class="container mt-3">
        {% block content %}{% endblock %}
    </div>
</body>
</html>

File: static/css/style.css

body {
    font-family: Arial, sans-serif;
}
.form-group {
    margin-bottom: 15px;
}
.error {
    color: red;
    font-size: 0.9em;
}

File: templates/users.html

{% extends 'base.html' %}

{% block title %}Users{% endblock %}

{% block content %}
    <h1>Users</h1>
    <a href="{{ url_for('add_user') }}" class="btn btn-primary mb-3">Add User</a>
    <table class="table table-striped">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
            </tr>
        </thead>
        <tbody>
            {% for user in users %}
                <tr>
                    <td>{{ user.id }}</td>
                    <td>{{ user.name | escape }}</td>
                    <td>{{ user.email | escape }}</td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
{% endblock %}

File: templates/add_user.html

{% extends 'base.html' %}

{% block title %}Add User{% endblock %}

{% block content %}
    <h1>Add User</h1>
    <form method="post" novalidate>
        {{ form.hidden_tag() }}
        <div class="form-group">
            {{ form.name.label }}
            {{ form.name(class="form-control") }}
            {% for error in form.name.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        <div class="form-group">
            {{ form.email.label }}
            {{ form.email(class="form-control") }}
            {% for error in form.email.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        {{ form.submit(class="btn btn-primary") }}
    </form>
    <a href="{{ url_for('index') }}" class="btn btn-secondary mt-2">Back</a>
{% endblock %}

Steps to Initialize Migrations:

  • Initialize Migration Repository: 
    flask db init
    This creates the migrations/ folder with Alembic configuration.
  • Create Initial Migration: 
    flask db migrate -m "Initial migration"
    Generates a migration script based on the User model.
  • Apply Migration: 
    flask db upgrade
    Applies the migration to create the users table in the database.

Output (/):

  • GET /: Displays a table of users from the database.
  • GET /add: Shows a form to add a user.
  • POST /add (valid data): Adds a user and redirects to the user list.

Explanation:

  • Flask-Migrate: Integrates Alembic with Flask-SQLAlchemy.
  • flask db init: Sets up the migration environment.
  • flask db migrate: Generates migration scripts based on model changes.
  • flask db upgrade: Applies migrations to the database.

2.4 Connecting to PostgreSQL

For PostgreSQL, update the database URI and ensure the database server is running: 

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://username:password@localhost:5432/dbname'

Run the same migration commands (flask db init, flask db migrate, flask db upgrade) after setting up the PostgreSQL database.

03. Performing Schema Changes

Flask-Migrate makes it easy to update the database schema by modifying models and generating new migrations.

Example: Adding a New Column

Step 1: Update the User Model

Modify the User model in app.py to add a created_at column:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)

Step 2: Generate Migration

flask db migrate -m "Add created_at to users"

This generates a migration script in migrations/versions/, e.g.:

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('users', sa.Column('created_at', sa.DateTime(), nullable=False))

def downgrade():
    op.drop_column('users', 'created_at')

Step 3: Apply Migration

flask db upgrade

Step 4: Update Application Logic

Update templates to display the new column. Modify templates/users.html:

{% extends 'base.html' %}

{% block title %}Users{% endblock %}

{% block content %}
    <h1>Users</h1>
    <a href="{{ url_for('add_user') }}" class="btn btn-primary mb-3">Add User</a>
    <table class="table table-striped">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
                <th>Created At</th>
            </tr>
        </thead>
        <tbody>
            {% for user in users %}
                <tr>
                    <td>{{ user.id }}</td>
                    <td>{{ user.name | escape }}</td>
                    <td>{{ user.email | escape }}</td>
                    <td>{{ user.created_at | escape }}</td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
{% endblock %}

Output (/):

  • GET /: Displays the updated user table with the created_at column.

Explanation:

  • Model Change: Adding created_at updates the schema.
  • Migration Script: Alembic generates upgrade and downgrade functions.
  • flask db upgrade: Applies the new column to the database.

3.1 Rolling Back Migrations

To revert a migration (e.g., remove the created_at column): 

flask db downgrade

This executes the downgrade function in the latest migration script.

04. Migrations in Data-Driven Applications

Data-driven applications, such as ML platforms or analytics dashboards, often require frequent schema updates to store new data types or metadata.

Example: Managing ML Model Metadata with Migrations

File: app.py

from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from flask_wtf import FlaskForm
from wtforms import StringField, FloatField, SubmitField
from wtforms.validators import DataRequired
from datetime import datetime
import os

app = Flask(__name__)
app.config['SECRET_KEY'] = os.getenv('SECRET_KEY', 'your-secret-key')
app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv('DATABASE_URL', 'sqlite:///instance/database.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

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

# Define ModelMetadata model
class ModelMetadata(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    model_name = db.Column(db.String(100), nullable=False)
    accuracy = db.Column(db.Float, nullable=False)
    created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)

# Form for adding model metadata
class ModelForm(FlaskForm):
    model_name = StringField('Model Name', validators=[DataRequired()])
    accuracy = FloatField('Accuracy', validators=[DataRequired()])
    submit = SubmitField('Add Model')

@app.route('/')
def index():
    models = ModelMetadata.query.all()
    return render_template('models.html', models=models)

@app.route('/add', methods=['GET', 'POST'])
def add_model():
    form = ModelForm()
    if form.validate_on_submit():
        model = ModelMetadata(
            model_name=form.model_name.data,
            accuracy=form.accuracy.data
        )
        db.session.add(model)
        db.session.commit()
        return redirect(url_for('index'))
    return render_template('add_model.html', form=form)

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

File: templates/models.html

{% extends 'base.html' %}

{% block title %}ML Models{% endblock %}

{% block content %}
    <h1>ML Model Metadata</h1>
    <a href="{{ url_for('add_model') }}" class="btn btn-primary mb-3">Add Model</a>
    <table class="table table-striped">
        <thead>
            <tr>
                <th>ID</th>
                <th>Model Name</th>
                <th>Accuracy</th>
                <th>Created At</th>
            </tr>
        </thead>
        <tbody>
            {% for model in models %}
                <tr>
                    <td>{{ model.id }}</td>
                    <td>{{ model.model_name | escape }}</td>
                    <td>{{ model.accuracy }}</td>
                    <td>{{ model.created_at | escape }}</td>
                </tr>
            {% endfor %}
        </tbody>
    </table>
{% endblock %}

File: templates/add_model.html

{% extends 'base.html' %}

{% block title %}Add Model{% endblock %}

{% block content %}
    <h1>Add Model Metadata</h1>
    <form method="post" novalidate>
        {{ form.hidden_tag() }}
        <div class="form-group">
            {{ form.model_name.label }}
            {{ form.model_name(class="form-control") }}
            {% for error in form.model_name.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        <div class="form-group">
            {{ form.accuracy.label }}
            {{ form.accuracy(class="form-control") }}
            {% for error in form.accuracy.errors %}
                <span class="error">{{ error | escape }}</span>
            {% endfor %}
        </div>
        {{ form.submit(class="btn btn-primary") }}
    </form>
    <a href="{{ url_for('index') }}" class="btn btn-secondary mt-2">Back</a>
{% endblock %}

Migration Steps:

  • Initialize migrations: flask db init
  • Create initial migration: flask db migrate -m "Create model_metadata table"
  • Apply migration: flask db upgrade
  • Add a new column (e.g., version): 
    class ModelMetadata(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    model_name = db.Column(db.String(100), nullable=False)
    accuracy = db.Column(db.Float, nullable=False)
    created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    version = db.Column(db.String(20), nullable=True)  # New column
    Then run: 
    flask db migrate -m "Add version to model_metadata"
flask db upgrade

Output (/):

  • GET /: Displays a table of ML model metadata.
  • GET /add: Shows a form to add model metadata.
  • POST /add (valid data): Adds metadata to the database and refreshes the table.

Explanation:

  • ModelMetadata: Stores ML model metadata (e.g., name, accuracy).
  • Migrations: Handle schema updates like adding a version column.
  • FlaskForm: Secures input with CSRF protection.

05. Advanced Migration Techniques

5.1 Custom Migrations

For complex changes (e.g., data migrations), manually edit the generated migration script.

Example: Data Migration

Step 1: Add a Non-Nullable Column

Add a status column to ModelMetadata with a default value:

class ModelMetadata(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    model_name = db.Column(db.String(100), nullable=False)
    accuracy = db.Column(db.Float, nullable=False)
    created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    status = db.Column(db.String(20), nullable=False, default='active')

Step 2: Generate Migration

flask db migrate -m "Add status to model_metadata"

Step 3: Edit Migration Script

Modify the generated script to handle existing rows:

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('model_metadata', sa.Column('status', sa.String(length=20), nullable=False, server_default='active'))
    # Update existing rows
    op.execute("UPDATE model_metadata SET status = 'active' WHERE status IS NULL")

def downgrade():
    op.drop_column('model_metadata', 'status')

Step 4: Apply Migration

flask db upgrade

Explanation:

  • server_default: Sets a default for new rows.
  • op.execute: Updates existing rows to comply with the non-nullable constraint.

5.2 Managing Migration Conflicts

In team environments, multiple developers may generate conflicting migrations. Use flask db merge to resolve conflicts: 

flask db merge <revision_id_1> <revision_id_2> -m "Merge migrations"
flask db upgrade

5.3 Production Considerations

  • Backup Database: Always back up before applying migrations.
  • Test Migrations: Test on a staging environment first.
  • Minimize Downtime: Use zero-downtime strategies (e.g., add columns with nullable=True initially).

06. Best Practices for Database Migrations

6.1 Recommended Practices

  • Use Flask-Migrate Commands: Leverage init, migrate, upgrade, and downgrade.
  • Version Control Migrations: Commit migrations/ to your repository.
  • Write Descriptive Messages: Use clear -m messages for migrations.
  • Handle Data Migrations: Update existing data when adding non-nullable columns.
  • Test Migrations: Test migrations locally and in staging before production.
  • Secure Credentials: Store database URIs in environment variables.

6.2 Security Considerations

  • Avoid Sensitive Data in Scripts: Don’t hardcode sensitive data in migration scripts.
  • Restrict Migration Access: Limit who can run migrations in production.
  • Backup Before Migrating: Protect against data loss.

Example: Insecure Migration Practice

app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://admin:password@localhost/dbname'  # Hardcoded credentials
op.execute("DROP TABLE users")  # Dangerous without backup

Correct:

app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv('DATABASE_URL')
# Ensure backup before destructive operations
op.drop_table('users')  # Only after careful review

6.3 Practices to Avoid

  • Avoid Manual Schema Changes: Always use migrations to ensure consistency.
  • Avoid Skipping Version Control: Commit migration scripts to track changes.
  • Avoid Unreviewed Migrations: Review scripts to prevent data loss.

07. Conclusion

Database migrations with Flask-Migrate streamline schema management in Flask applications. Key takeaways:

  • Use Flask-Migrate with Flask-SQLAlchemy for automated migrations.
  • Generate and apply migrations with flask db migrate and flask db upgrade.
  • Handle complex changes with custom migration scripts and test thoroughly.
  • Secure credentials and back up data before applying migrations.

By following these practices, you can manage database schemas effectively, ensuring robust, scalable Flask applications for data-driven workflows like ML and analytics!

Comments

Post a Comment