Db Diff

Implement production-grade database schema comparison for PostgreSQL and MySQL that detects all schema differences (tables, columns, indexes, constraints, triggers), generates safe migration scripts with transaction safety, validates changes before deployment, and provides rollback procedures. Essential for CI/CD pipelines and environment synchronization with zero-downtime deployments.

When to Use This Command

Use /db-diff when you need to:

• Compare development and production schemas to identify drift
• Generate migration scripts for deploying schema changes
• Validate that staging environment matches production before cutover
• Detect unauthorized schema changes in production databases
• Synchronize multiple database instances or read replicas
• Audit schema evolution over time for compliance

DON'T use this when:

• Schemas are managed by ORM migrations (use migration tools like Alembic, Flyway)
• Database has no versioned schema baseline (establish first)
• You need to compare data, not just schema (use data diff tools)
• Real-time schema synchronization needed (use logical replication instead)
• Databases use different RDBMS (PostgreSQL → MySQL requires manual conversion)

Design Decisions

This command implements comprehensive schema analysis with safe migration generation because:

• Detects all schema objects (tables, indexes, constraints, triggers, sequences)
• Generates idempotent migration scripts (safe to re-run)
• Validates dependencies before dropping objects (prevents cascading failures)
• Provides transaction-wrapped migrations with rollback capability
• Outputs human-readable diff reports for code review

Alternative considered: Manual SQL diff

• Simple for small schemas (<10 tables)
• Error-prone for complex changes (missed dependencies)
• No validation or safety checks
• Recommended only for one-off comparisons

Alternative considered: Database migration frameworks (Flyway, Liquibase)

• Tracks migration history automatically
• Requires all changes via migration files (no ad-hoc changes)
• Better for greenfield projects with strict change control
• Recommended when starting new projects with version control from day 1

Prerequisites

Before running this command:

1. Read access to both source and target databases
2. Understanding of schema dependencies (foreign keys, views, triggers)
3. Backup of target database before applying migrations
4. Testing environment to validate migrations before production
5. Rollback plan documented for each migration

Implementation Process

Step 1: Connect to Source and Target Databases

Establish connections with appropriate read-only permissions.

Step 2: Extract Schema Metadata

Query information_schema for tables, columns, indexes, constraints, and triggers.

Step 3: Compare Schemas

Identify additions, deletions, and modifications for each object type.

Step 4: Generate Migration Script

Create SQL statements in correct dependency order with transaction safety.

Step 5: Validate and Test

Review generated script, test in staging, then apply to production.

Output Format

The command generates:

• schema_diff_report.md - Human-readable diff report with impact analysis
• migration_forward.sql - Migration script to apply changes
• migration_rollback.sql - Rollback script for quick revert
• schema_validation.log - Pre-flight validation checks
• dependency_graph.dot - Graphviz visualization of object dependencies

Code Examples

Example 1: PostgreSQL Comprehensive Schema Diff

#!/usr/bin/env python3
"""
Production-ready PostgreSQL schema comparison tool with automated
migration generation and dependency analysis.
"""

import psycopg2
from psycopg2.extras import RealDictCursor
from typing import Dict, List, Set, Tuple
from dataclasses import dataclass
from datetime import datetime
import logging

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)
…

Example 2: CI/CD Integration for Schema Validation

# .github/workflows/schema-validation.yml
# Validates that database schema matches expected state before deployment

name: Database Schema Validation

on:
  pull_request:
    paths:
      - 'migrations/**'
      - '.github/workflows/schema-validation.yml'

jobs:
  schema-diff:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:15
…