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