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:
This creates theflask db init
migrations/
folder with Alembic configuration. - Create Initial Migration:
Generates a migration script based on theflask db migrate -m "Initial migration"
User
model. - Apply Migration:
Applies the migration to create theflask db upgrade
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
anddowngrade
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
):
Then run: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
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
, anddowngrade
. - 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
andflask 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