Database Migration Best Practices

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.

What This Skill Does

This skill enforces a strict migration workflow that:

Critical Rules

**NEVER:**

**ALWAYS:**

Step-by-Step Instructions

1. Assess Schema Changes

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

2. Generate Migration Locally

Before generating a migration, ensure:

```bash

Set up local environment (if not already done)

python3 -m venv venv

source venv/bin/activate

pip install -r requirements.txt

Configure environment variables

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"

```

3. Review Generated Migration

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

4. Ensure Data Safety

For each new column being added to an existing table:

**Check if nullable or default is set:**

```python

SAFE - Existing rows won't break

new_field = db.Column(db.String(255), nullable=True)

SAFE - Provides default value

new_field = db.Column(db.Integer, default=0)

UNSAFE - Will fail if table has existing rows

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

5. Commit to Git

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.

6. Deploy with Migration

Deployment should run migrations automatically:

```bash

Typical deployment flow

flask db upgrade # Applies all pending migrations

```

Verify the deployment configuration includes `flask db upgrade` or equivalent before starting the application.

Examples

Example 1: Adding Optional Field

**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*

Example 2: Adding Required Field (Two-Step Process)

**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*

Important Constraints

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.

Troubleshooting

**If migration generation hangs or fails:**

**If migration fails on deployment:**

**If you need to fix a bad migration:**

When to Use This Skill

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.