Flask Migration Generator MCP Server

Generate MySQL migration SQL from Python Flask-SQLAlchemy model files using AI-powered analysis.

This MCP server analyzes Flask-SQLAlchemy models using Google's Gemini AI and automatically generates MySQL migration SQL with proper table structures, constraints, indexes, and relationships.

Features

Tools

• generate_migration - Generate complete MySQL migration SQL from a Flask-SQLAlchemy model file

  • Analyzes model structure using Gemini AI
  • Generates CREATE TABLE statements with proper column types
  • Includes primary keys, foreign keys, indexes, and constraints
  • Supports all common SQLAlchemy column types and relationships

• analyze_model - Analyze a Flask-SQLAlchemy model and return detailed table structure information

  • Extracts table name, columns, and relationships
  • Identifies column types, constraints, and defaults
  • Provides JSON structure for integration with other tools

Prompts

• generate_migration_prompt - Interactive prompt for generating migrations from Flask models
  • Guides users through the migration generation process
  • Provides context and usage instructions

Prerequisites

• GEMINI_API_KEY - Required environment variable for Google's Gemini AI
  • Get your API key from Google AI Studio
  • The server uses gemini-2.5-flash model for analysis

Development

Install dependencies:

npm install

Build the server:

npm run build

For development with auto-rebuild:

npm run watch

Installation for Roo Code

This server requires manual configuration in Roo Code's MCP settings since it needs to run locally and requires the GEMINI_API_KEY environment variable.

Manual Configuration

You need to manually configure the server in Roo Code's MCP settings. In Roo Code, go to Settings → MCP Servers and add:

Server Name: flask-migration-generator

Configuration:

{
  "command": "node",
  "args": ["/path/to/flask-migration-generator/build/index.js"],
  "env": {
    "GEMINI_API_KEY": "your-actual-api-key-here"
  }
}

Important: Replace /path/to/flask-migration-generator with the actual path to your project directory and your-actual-api-key-here with your real Gemini API key.

Environment Setup

Important: The GEMINI_API_KEY must be configured directly in the MCP server configuration (see Installation section above). Global environment variables will not be inherited by the MCP server.

1. Get your Gemini API key from Google AI Studio

2. Add the key to your MCP server configuration in Roo Code's settings (shown in Installation section above)

3. Verify the path to your flask-migration-generator project directory is correct in the MCP server configuration

Usage with Roo Code

Once manually configured in Roo Code's MCP settings (see Installation section above), you can use the tools directly in your conversations:

Generate Migration SQL

To generate a migration from a Flask model file:

Generate a MySQL migration for my User model in /path/to/models/user.py

Or use the specific tool syntax:

<use_mcp_tool>
<server_name>flask-migration-generator</server_name>
<tool_name>generate_migration</tool_name>
<arguments>
{
  "modelFile": "/path/to/models/user.py",
  "modelName": "User"
}
</arguments>
</use_mcp_tool>

Analyze Model Structure

To analyze a model without generating migration:

<use_mcp_tool>
<server_name>flask-migration-generator</server_name>
<tool_name>analyze_model</tool_name>
<arguments>
{
  "modelFile": "/path/to/models/product.py"
}
</arguments>
</use_mcp_tool>

Example Model File

from flask_sqlalchemy import db
from datetime import datetime

class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password_hash = db.Column(db.String(128), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    is_active = db.Column(db.Boolean, default=True)

    # Relationship
    posts = db.relationship('Post', back_populates='author', lazy=True)

    def __repr__(self):
        return f'<User {self.username}>'

class Post(db.Model):
    __tablename__ = 'posts'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text, nullable=False)
    published = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # Foreign Key
    author_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

    # Relationship
    author = db.relationship('User', back_populates='posts')

    def __repr__(self):
        return f'<Post {self.title}>'

Generated Migration Output

The generate_migration tool will produce output like:

-- Migration for users
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(80) NOT NULL UNIQUE,
  `email` VARCHAR(120) NOT NULL UNIQUE,
  `password_hash` VARCHAR(128) NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `is_active` TINYINT(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Migration for posts
CREATE TABLE IF NOT EXISTS `posts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `content` TEXT NOT NULL,
  `published` TINYINT(1) DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `author_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `posts_author_id_fk` FOREIGN KEY (`author_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Supported SQLAlchemy Features

• Column Types: String, Integer, Boolean, DateTime, Text, Float, etc.
• Constraints: Primary keys, foreign keys, unique constraints, nullable/non-nullable
• Defaults: Static values, function calls (func.now(), etc.)
• Indexes: Automatic index creation for indexed columns
• Relationships: One-to-many, many-to-one, one-to-one, many-to-many
• Table Arguments: Custom table names, schema definitions

Debugging

Since MCP servers communicate over stdio, debugging can be challenging. Use the MCP Inspector for debugging:

npm run inspector

The Inspector will provide a URL to access debugging tools in your browser.

Error Handling

The server includes comprehensive error handling for:

• Missing or invalid model files
• Unparseable model structures
• Missing environment variables
• API rate limits and errors

Make sure your GEMINI_API_KEY is properly configured and you have sufficient API quota for model analysis.