Graphile-Migrate MCP Server - Database Migration Management & Validation

Overview

Create an MCP server that provides comprehensive integration with Graphile-Migrate, enabling AI assistants to manage database migrations, validate idempotency, analyze schema changes, and assist with database evolution workflows.

Use Cases

Primary Use Cases

Migration Management: Create, validate, and apply database migrations
Idempotency Validation: Ensure migrations can be safely run multiple times
Schema Analysis: Compare schemas and analyze migration impact
Shadow Database Testing: Validate migrations against shadow databases
CI/CD Integration: Automate migration validation in development workflows

Specific Scenarios

Idempotent Migration Development: Ensuring all migrations are safely repeatable
Schema Evolution Planning: Analyzing impact of proposed changes
Migration Troubleshooting: Diagnosing and fixing migration issues
Team Coordination: Managing migration conflicts and dependencies
Production Deployment: Validating migrations before production deployment

Ideal API Structure

Tools (MCP Functions)

Migration Management

// Create new migration
create_migration(description: string, migration_sql: string): Promise<MigrationResult>

// Apply pending migrations
apply_migrations(target?: string): Promise<ApplyResult>

// Reset to specific migration
reset_to_migration(migration_id: string): Promise<ResetResult>

// Rollback last migration
rollback_migration(steps?: number): Promise<RollbackResult>

// Get migration status
get_migration_status(): Promise<MigrationStatus>

Idempotency & Validation

// Test migration idempotency
test_idempotency(migration_id?: string, iterations?: number): Promise<IdempotencyResult>

// Validate current.sql for idempotency issues
validate_current_migration(): Promise<ValidationResult>

// Suggest idempotency fixes
suggest_idempotency_fixes(migration_sql: string): Promise<IdempotencyFixes>

// Run comprehensive migration validation
validate_all_migrations(): Promise<ComprehensiveValidation>

Shadow Database Management

// Create shadow database
create_shadow_db(name?: string): Promise<ShadowDbResult>

// Test migrations against shadow database
test_against_shadow(migration_sql?: string): Promise<ShadowTestResult>

// Compare main and shadow schemas
compare_with_shadow(): Promise<SchemaComparison>

// Clean up shadow databases
cleanup_shadow_databases(): Promise<CleanupResult>

Schema Analysis

// Analyze current database schema
analyze_schema(schema_names?: string[]): Promise<SchemaAnalysis>

// Compare two database states
compare_schemas(from_db: string, to_db: string): Promise<SchemaComparison>

// Generate migration from schema differences
generate_migration_from_diff(target_schema: string): Promise<MigrationGeneration>

// Analyze migration dependencies
analyze_migration_dependencies(): Promise<DependencyAnalysis>

Development Workflow

// Watch for migration file changes
watch_migrations(callback: (changes: MigrationChange[]) => void): Promise<WatchResult>

// Commit current migration (move current.sql to committed)
commit_migration(message?: string): Promise<CommitResult>

// Uncommit migration (move back to current.sql for editing)
uncommit_migration(migration_id: string): Promise<UncommitResult>

// Validate project configuration
validate_config(): Promise<ConfigValidation>

Resources (MCP Data Sources)

Migration Information

// Current migration status
graphile-migrate://status/current -> MigrationStatus

// Migration history
graphile-migrate://history/migrations -> Migration[]

// Current.sql contents
graphile-migrate://current/sql -> string

// Migration configuration
graphile-migrate://config/settings -> GraphileMigrateConfig

Schema Information

// Database schema structure
graphile-migrate://schema/current -> SchemaStructure

// Schema differences from base
graphile-migrate://schema/diff -> SchemaDiff

// Shadow database status
graphile-migrate://shadow/status -> ShadowDbStatus

Validation Reports

// Idempotency test results
graphile-migrate://validation/idempotency -> IdempotencyReport

// Migration validation results
graphile-migrate://validation/migrations -> ValidationReport

Prompts (MCP Templates)

// Common migration workflows
"create-idempotent-migration" -> Guide for writing idempotent SQL migrations
"fix-idempotency-issues" -> Template for fixing common idempotency problems
"setup-shadow-database" -> Configure shadow database for testing
"migration-best-practices" -> Best practices for database migrations
"troubleshoot-migration-failure" -> Debugging guide for migration issues
"prepare-production-deployment" -> Production migration deployment checklist

Implementation Details

Core Technologies

Language: TypeScript (for consistency with MCP ecosystem)
Graphile-Migrate Integration: Direct integration with graphile-migrate CLI and APIs
PostgreSQL: Direct database connections for schema analysis
SQL Parsing: SQL AST parsing for migration analysis
File Watching: Monitor migration file changes

Required Graphile-Migrate Integrations

import { migrate, reset, status, watch } from 'graphile-migrate/cli';
import { readFileSync, writeFileSync } from 'fs';
import { Pool } from 'pg';

// Core integrations needed:
// - Migration execution and status tracking
// - Shadow database creation and management
// - SQL parsing and idempotency analysis
// - Configuration file management
// - Migration file manipulation

Configuration Options

interface GraphileMigrateMcpConfig {
  // Database connection URLs
  database_url?: string;
  shadow_database_url?: string;
  root_database_url?: string;
  
  // Migration settings
  migrations_folder?: string;
  current_sql_path?: string;
  
  // Shadow database settings
  shadow_db_prefix?: string;
  auto_cleanup_shadow?: boolean;
  
  // Idempotency testing
  idempotency_test_iterations?: number;
  enable_comprehensive_validation?: boolean;
}

Error Handling Requirements

Database Connection Issues: Clear diagnostics for connection failures
Migration Failures: Detailed SQL error analysis with line numbers
Idempotency Violations: Specific guidance on making migrations idempotent
Schema Conflicts: Conflict resolution suggestions
Shadow Database Issues: Shadow DB setup and cleanup error handling

Example Usage Scenarios

Creating Idempotent Migration

// AI assistant helping create safe migration
const validation = await tools.validate_current_migration();
if (!validation.is_idempotent) {
  const fixes = await tools.suggest_idempotency_fixes(validation.sql);
  // Apply suggested fixes
}
await tools.test_idempotency(undefined, 3); // Test 3 iterations

Migration Troubleshooting

// AI helping debug migration failure
const status = await tools.get_migration_status();
const shadowTest = await tools.test_against_shadow(status.current_sql);
if (shadowTest.has_errors) {
  // Analyze errors and suggest fixes
  const analysis = await tools.analyze_schema(["app_public", "app_private"]);
}

CI/CD Integration

// AI validating migrations in CI pipeline
await tools.create_shadow_db("ci-test");
const validation = await tools.validate_all_migrations();
const idempotency = await tools.test_idempotency();
if (validation.is_valid && idempotency.is_idempotent) {
  // Safe to deploy
}

Schema Evolution Planning

// AI helping plan schema changes
const current = await tools.analyze_schema();
const comparison = await tools.compare_schemas("current", "proposed");
const migration = await tools.generate_migration_from_diff("proposed-schema");

Testing Requirements

Unit Tests

SQL parsing and idempotency detection
Migration file manipulation
Schema comparison algorithms
Shadow database lifecycle management
Configuration validation

Integration Tests

Real database migration scenarios
Idempotency testing with actual PostgreSQL
Shadow database creation and testing
Multi-migration dependency scenarios
Error recovery and rollback testing

Mock Test Data

Sample migration files (idempotent and non-idempotent)
PostgreSQL schema dumps for comparison
Migration failure scenarios and error messages
Configuration file examples
Shadow database test scenarios

Documentation Requirements

User Documentation

Graphile-Migrate + MCP integration setup
Idempotent migration writing guide
Shadow database configuration
CI/CD integration examples
Troubleshooting common migration issues

Developer Documentation

API reference with TypeScript types
SQL parsing and analysis implementation
Custom validation rule development
Integration patterns with other MCP servers

Success Criteria

Core Functionality ✅

Complete migration lifecycle management
Comprehensive idempotency validation and testing
Shadow database integration for safe testing
Schema analysis and comparison capabilities

Advanced Features ✅

Intelligent migration generation from schema diffs
Real-time migration validation during development
CI/CD pipeline integration with validation reports
Migration dependency analysis and conflict resolution

Quality Standards ✅

Robust error handling with actionable guidance
Full TypeScript type coverage
Comprehensive test suite covering edge cases
Integration with PostgreSQL-specific features

Related Resources

Critical Migration Patterns to Support

Idempotent Patterns

-- Table creation
CREATE TABLE IF NOT EXISTS app_public.users (...);

-- Index creation  
CREATE INDEX IF NOT EXISTS idx_users_email ON app_public.users(email);

-- Function creation
CREATE OR REPLACE FUNCTION app_public.current_user() ...;

-- Custom type creation with exception handling
DO $$ BEGIN
    CREATE TYPE app_public.jwt AS (role TEXT, user_id UUID);
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- Column addition
ALTER TABLE app_public.users ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT NOW();

-- Constraint addition with existence check
ALTER TABLE app_public.users 
  ADD CONSTRAINT IF NOT EXISTS users_email_unique UNIQUE (email);

Common Anti-Patterns to Detect

-- ❌ Non-idempotent patterns to flag
CREATE TABLE app_public.users (...); -- Missing IF NOT EXISTS
ALTER TABLE users ADD COLUMN name TEXT; -- Missing IF NOT EXISTS
CREATE INDEX idx_users_name ON users(name); -- Missing IF NOT EXISTS
CREATE TYPE jwt AS (...); -- No exception handling

Implementation Priority: HIGH

This server directly addresses critical pain points in database migration management, particularly the idempotency challenges that many PostgreSQL + PostGraphile projects face. It would significantly improve development velocity and deployment confidence.