Enforce safe database migration workflows with local generation, git versioning, and schema compatibility rules for Flask-SQLAlchemy projects
Enforce critical database migration safety rules for Flask applications using Flask-Migrate/Alembic. This skill ensures migrations are properly generated locally, versioned in git, and never auto-generated on production servers.
This skill enforces a strict migration workflow that:
**NEVER:**
**ALWAYS:**
When the user wants to modify database models:
1. Read the current model definitions (typically in `src/models.py` or similar)
2. Identify what fields/tables are being added, modified, or removed
3. Check if changes affect existing data
4. Verify if `migrations/` directory exists in the repository
Before generating a migration, ensure:
```bash
python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt
export FLASK_APP=src/app.py # or appropriate app entry point
export DATABASE_URL="sqlite:///local.db" # Use local database for generation
```
Then generate the migration:
```bash
flask db migrate -m "Clear description of changes"
```
1. Locate the new migration file in `migrations/versions/`
2. Read the entire migration file to verify:
- `upgrade()` function contains expected schema changes
- `downgrade()` function properly reverses changes
- No unintended side effects or extra alterations
- Column additions use `nullable=True` or `server_default` when adding to existing tables
3. If the migration looks incorrect:
- Delete the generated file
- Adjust the model definitions
- Regenerate the migration
For each new column being added to an existing table:
**Check if nullable or default is set:**
```python
new_field = db.Column(db.String(255), nullable=True)
new_field = db.Column(db.Integer, default=0)
new_field = db.Column(db.String(255), nullable=False)
```
If the user requires non-nullable fields:
1. First migration: Add field as nullable
2. Data migration: Populate values for existing rows
3. Second migration: Alter column to non-nullable
Once the migration is verified:
```bash
git add migrations/versions/<new_migration_file>.py
git commit -m "Add migration: <description>"
```
**IMPORTANT:** The migration MUST be committed before deployment. Never deploy code changes that modify models without the corresponding migration file.
Deployment should run migrations automatically:
```bash
flask db upgrade # Applies all pending migrations
```
Verify the deployment configuration includes `flask db upgrade` or equivalent before starting the application.
**User Request:** "Add a 'bio' field to the User model"
**Response:**
```
I'll add a bio field to the User model. Since this is being added to an existing table, I'll make it nullable to avoid breaking existing user records.
First, let me update the model definition, then generate a migration locally.
```
*Updates model with `bio = db.Column(db.Text, nullable=True)`*
*Generates migration using flask db migrate*
*Reviews migration file*
*Commits migration to git*
**User Request:** "Add a required 'role' field to the User model"
**Response:**
```
Since 'role' should be required (non-nullable) and the User table likely has existing records, I'll use a two-step migration process:
1. First migration: Add 'role' as nullable
2. We'll need to populate default values for existing users
3. Second migration: Make 'role' non-nullable
Let me start with step 1.
```
*Creates first migration adding nullable role*
*Commits first migration*
*Asks user about default role value for existing users*
*Creates data migration or manual SQL to populate defaults*
*Creates second migration to set nullable=False*
*Commits second migration*
1. **Never Skip the Review:** Always read the generated migration file. Alembic sometimes generates unexpected operations.
2. **Local Database for Generation:** Always use a local database URL when generating migrations to avoid accidentally modifying production data.
3. **One Logical Change Per Migration:** Keep migrations focused. Don't mix unrelated schema changes in a single migration.
4. **Test Rollbacks:** Ensure the `downgrade()` function works. Test it locally when possible.
5. **Version Control is Source of Truth:** The migrations directory in git is the canonical schema history. Never manually edit the production database without a corresponding migration.
**If migration generation hangs or fails:**
**If migration fails on deployment:**
**If you need to fix a bad migration:**
Use this skill whenever:
This skill ensures database schema changes follow a safe, reviewable, version-controlled workflow that protects production data and maintains deployment reliability.
Leave a review
No reviews yet. Be the first to review this skill!
# Download SKILL.md from killerskills.ai/api/skills/database-migration-best-practices/raw