comparing-database-schemas

name	comparing-database-schemas
description	Process use when you need to work with schema comparison. This skill provides database schema diff and sync with comprehensive guidance and automation. Trigger with phrases like "compare schemas", "diff databases", or "sync database schemas".
allowed-tools	Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*), Bash(mongosh:*)
version	1.0.0
author	Jeremy Longshore <[e-mail]>
license	MIT
compatible-with	claude-code, codex, openclaw
tags	["database","comparing-database"]

Database Diff Tool

Overview

Compare database schemas between two environments (development vs. staging, staging vs.

Prerequisites

• Connection credentials to both source and target databases
• psql or mysql CLI configured to connect to both environments
• Read access to information_schema and pg_catalog (PostgreSQL) or information_schema (MySQL)
• Permission to run pg_dump --schema-only for full schema extraction
• Understanding of which environment is the "source of truth" (typically the migration-managed environment)

Instructions

1. Extract the full schema from both databases for comparison:

   • PostgreSQL: pg_dump --schema-only --no-owner --no-privileges -f schema_source.sql source_db and repeat for target_db
   • MySQL: mysqldump --no-data --routines --triggers source_db > schema_source.sql
   • Alternatively, query information_schema directly for programmatic comparison

2. Compare tables present in each database:

   • SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_catalog = 'source_db' EXCEPT SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_catalog = 'target_db'
   • This reveals tables that exist in source but not in target (and vice versa)

3. Compare columns for each shared table:

   • Query information_schema.columns from both databases for: column_name, data_type, character_maximum_length, is_nullable, column_default, ordinal_position
   • Flag differences in data type, nullability, default values, and column ordering
   • Detect added columns (in source, not target) and dropped columns (in target, not source)

4. Compare indexes:

   • PostgreSQL: Query pg_indexes for indexname, indexdef on each database
   • MySQL: Query information_schema.STATISTICS for INDEX_NAME, COLUMN_NAME, NON_UNIQUE