Skip to content

Schema Analysis & Database Comparison Tools

Overview

Implement comprehensive schema analysis and database comparison tools that help Claude agents understand database structure, track changes, and compare different database states. These tools are essential for understanding migration impact and planning schema evolution.

Claude Agent Success Criteria

A Claude agent should be able to:

  • ✅ Analyze current database schema structure and metadata
  • ✅ Compare schemas between main and shadow databases
  • ✅ Generate migration diffs between different database states
  • ✅ Understand migration dependencies and relationships

Detailed Acceptance Criteria

1. Schema Analysis Tool

Implement analyze_schema MCP tool:

{
  name: "analyze_schema",
  description: "Analyze database schema structure and metadata",
  inputSchema: {
    type: "object",
    properties: {
      projectPath: {
        type: "string",
        description: "Path to the Graphile Migrate project"
      },
      database: {
        type: "string",
        enum: ["main", "shadow"],
        description: "Which database to analyze (default: main)"
      },
      includeConstraints: {
        type: "boolean",
        description: "Include constraint information (default: true)"
      },
      includeIndexes: {
        type: "boolean",
        description: "Include index information (default: true)"
      },
      includeFunctions: {
        type: "boolean", 
        description: "Include function definitions (default: true)"
      }
    },
    required: ["projectPath"]
  }
}

Return Structure:

interface SchemaAnalysis {
  success: boolean;
  data: {
    database: {
      name: string;
      version: string;
      schemas: string[];
      totalObjects: number;
    };
    tables: TableInfo[];
    indexes: IndexInfo[];
    constraints: ConstraintInfo[];
    functions: FunctionInfo[];
    types: TypeInfo[];
    triggers: TriggerInfo[];
    sequences: SequenceInfo[];
    views: ViewInfo[];
    extensions: ExtensionInfo[];
    permissions: PermissionInfo[];
  };
}

interface TableInfo {
  name: string;
  schema: string;
  columns: ColumnInfo[];
  rowCount?: number;
  sizeBytes?: number;
  tablespace?: string;
}

interface ColumnInfo {
  name: string;
  dataType: string;
  nullable: boolean;
  defaultValue?: string;
  isPrimaryKey: boolean;
  isForeignKey: boolean;
  references?: string;
}
  • Connect to PostgreSQL database using project configuration
  • Query information_schema and pg_catalog tables
  • Extract comprehensive schema metadata
  • Support both main and shadow database analysis
  • Include performance statistics where available

2. Schema Comparison Tool

Implement compare_schemas MCP tool:

{
  name: "compare_schemas", 
  description: "Compare schemas between two database states",
  inputSchema: {
    type: "object",
    properties: {
      projectPath: {
        type: "string",
        description: "Path to the Graphile Migrate project"
      },
      sourceDatabase: {
        type: "string",
        enum: ["main", "shadow"],
        description: "Source database for comparison (default: shadow)"
      },
      targetDatabase: {
        type: "string", 
        enum: ["main", "shadow"],
        description: "Target database for comparison (default: main)"
      },
      includeData: {
        type: "boolean",
        description: "Compare data in addition to schema (default: false)"
      },
      outputFormat: {
        type: "string",
        enum: ["diff", "table", "json"],
        description: "Format for comparison output (default: diff)"
      }
    },
    required: ["projectPath"]
  }
}

Comparison Categories:

interface SchemaComparison {
  success: boolean;
  data: {
    summary: {
      identical: boolean;
      differences: number;
      additionsCount: number;
      deletionsCount: number;
      modificationsCount: number;
    };
    differences: {
      tables: SchemaDifference[];
      indexes: SchemaDifference[];
      constraints: SchemaDifference[];
      functions: SchemaDifference[];
      types: SchemaDifference[];
      triggers: SchemaDifference[];
    };
    recommendations: string[];
  };
}

interface SchemaDifference {
  type: 'addition' | 'deletion' | 'modification';
  object: string;
  objectType: string;
  details: string;
  impact: 'low' | 'medium' | 'high';
  migrationSql?: string;
}
  • Compare schema objects between databases
  • Detect additions, deletions, and modifications
  • Generate SQL statements for differences
  • Assess impact levels for changes
  • Support multiple output formats

3. Migration Impact Prediction Tool

Implement predict_migration_impact MCP tool:

{
  name: "predict_migration_impact",
  description: "Analyze potential impact of a migration before applying",
  inputSchema: {
    type: "object",
    properties: {
      projectPath: {
        type: "string",
        description: "Path to the Graphile Migrate project"
      },
      migrationFile: {
        type: "string",
        description: "Migration file to analyze (defaults to current.sql)"
      },
      includePerformanceEstimate: {
        type: "boolean",
        description: "Include performance impact estimates (default: true)"
      }
    },
    required: ["projectPath"]
  }
}

Impact Analysis:

interface MigrationImpact {
  success: boolean;
  data: {
    migration: {
      file: string;
      operations: OperationInfo[];
    };
    impact: {
      tablesAffected: string[];
      indexesAffected: string[];
      functionsAffected: string[];
      estimatedDuration?: number;
      lockLevel: 'none' | 'share' | 'exclusive';
      downtime: boolean;
    };
    risks: {
      dataLoss: boolean;
      performanceImpact: 'low' | 'medium' | 'high';
      rollbackDifficulty: 'easy' | 'medium' | 'hard';
      warnings: string[];
    };
    recommendations: string[];
  };
}

interface OperationInfo {
  type: string;
  target: string;
  sql: string;
  impact: string;
  reversible: boolean;
}
  • Parse SQL to identify operations
  • Analyze impact on existing schema objects
  • Estimate performance implications
  • Assess rollback difficulty
  • Provide risk warnings and recommendations

4. Migration Dependency Analysis

Implement analyze_migration_dependencies MCP tool:

{
  name: "analyze_migration_dependencies",
  description: "Analyze dependencies between migrations and schema objects",
  inputSchema: {
    type: "object",
    properties: {
      projectPath: {
        type: "string",
        description: "Path to the Graphile Migrate project"
      }
    },
    required: ["projectPath"]
  }
}

Dependency Analysis:

interface DependencyAnalysis {
  success: boolean;
  data: {
    migrations: MigrationDependency[];
    objects: ObjectDependency[];
    graph: DependencyNode[];
    conflicts: DependencyConflict[];
    recommendations: string[];
  };
}

interface MigrationDependency {
  migration: string;
  dependsOn: string[];
  dependedBy: string[];
  creates: string[];
  modifies: string[];
  deletes: string[];
}

interface DependencyConflict {
  type: 'circular' | 'missing' | 'ordering';
  description: string;
  affected: string[];
  resolution: string;
}
  • Analyze migration files for object creation/modification
  • Build dependency graph between migrations
  • Detect circular dependencies and conflicts
  • Suggest optimal migration ordering
  • Identify missing dependencies

5. Database Schema Utilities

Create supporting utilities in src/schema/:

Schema Inspector:

interface SchemaInspector {
  connect(connectionString: string): Promise<void>;
  disconnect(): Promise<void>;
  getTables(schema?: string): Promise<TableInfo[]>;
  getIndexes(schema?: string): Promise<IndexInfo[]>;
  getConstraints(schema?: string): Promise<ConstraintInfo[]>;
  getFunctions(schema?: string): Promise<FunctionInfo[]>;
  getTypes(schema?: string): Promise<TypeInfo[]>;
  getTriggers(schema?: string): Promise<TriggerInfo[]>;
}

Schema Comparator:

interface SchemaComparator {
  compare(source: SchemaAnalysis, target: SchemaAnalysis): Promise<SchemaComparison>;
  generateMigrationSql(differences: SchemaDifference[]): Promise<string>;
  assessImpact(differences: SchemaDifference[]): Promise<ImpactAssessment>;
}
  • Implement database connection management
  • Create schema inspection queries
  • Build comparison algorithms
  • Generate SQL from differences
  • Handle PostgreSQL-specific features

Testing Requirements

Unit Tests

  • Test schema analysis queries and parsing
  • Test comparison algorithms with mock data
  • Test SQL generation from differences
  • Test dependency graph construction

Integration Tests

  • Test with real PostgreSQL databases
  • Test with complex schema structures
  • Test performance with large databases
  • Test with various PostgreSQL versions

Test Fixtures

Create test databases with:

  • Complex table structures with relationships
  • Various PostgreSQL types and extensions
  • Functions, triggers, and custom types
  • Multiple schemas and namespaces
  • Performance test scenarios

Claude Agent Guidance

Typical Usage Workflow

// 1. Analyze current schema
const analysis = await tools.analyze_schema({
  projectPath: "/path/to/project",
  database: "main"
});

// 2. Compare with shadow database
const comparison = await tools.compare_schemas({
  projectPath: "/path/to/project",
  sourceDatabase: "shadow",
  targetDatabase: "main"
});

// 3. Predict migration impact
const impact = await tools.predict_migration_impact({
  projectPath: "/path/to/project",
  includePerformanceEstimate: true
});

// 4. Analyze dependencies
const dependencies = await tools.analyze_migration_dependencies({
  projectPath: "/path/to/project"
});

How to Verify Success

# Test schema analysis
node -e "
const analysis = require('./dist/tools/analyze-schema');
analysis.analyzeSchema('/path/to/project').then(console.log);
"

# Test comparison
node -e "
const compare = require('./dist/tools/compare-schemas'); 
compare.compareSchemas('/path/to/project').then(console.log);
"

Expected Behavior

  • Schema analysis should capture comprehensive metadata
  • Comparisons should detect all meaningful differences
  • Impact predictions should be accurate and helpful
  • Dependency analysis should prevent conflicts

Error Handling Examples

  • Connection Failed: "Cannot connect to database. Check connection string and permissions."
  • Schema Not Found: "Schema 'app_public' not found in database."
  • Comparison Error: "Cannot compare: target database connection failed."

Definition of Done

  • All schema analysis tools implemented correctly
  • Database comparison provides accurate results
  • Migration impact prediction covers major scenarios
  • Dependency analysis prevents common conflicts
  • Integration tests pass with real databases
  • >80% test coverage maintained
  • Performance optimized for large schemas
  • CLAUDE.md updated with schema analysis examples

Estimated Effort

3-4 days

Dependencies

Technical Notes for Claude Agents

Key Files to Create

  • src/tools/analyze-schema.ts - Schema analysis tool
  • src/tools/compare-schemas.ts - Schema comparison tool
  • src/tools/predict-impact.ts - Impact prediction tool
  • src/tools/analyze-dependencies.ts - Dependency analysis tool
  • src/schema/inspector.ts - Database inspection utilities
  • src/schema/comparator.ts - Schema comparison logic

Database Connection Strategy

  • Use connection pooling for performance
  • Support multiple simultaneous connections
  • Handle connection timeouts and retries
  • Implement proper connection cleanup
  • Support SSL and advanced connection options

PostgreSQL-Specific Features

  • Handle PostgreSQL extensions correctly
  • Support custom types and domains
  • Include pg_catalog system tables
  • Handle schemas and search paths
  • Support PostgreSQL-specific index types