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

1. 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)

1. 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)

1. Compare indexes:

• PostgreSQL: Query pg_indexes for indexname, indexdef on each database
• MySQL: Query information_schema.STATISTICS for INDEX_NAME, COLUMN_NAME, NON_UNIQUE
• Flag missing, extra, or differently-defined indexes

1. Compare constraints (primary keys, foreign keys, unique, check):

• Query information_schema.table_constraints and information_schema.key_column_usage
• Detect missing foreign keys, changed constraint names, and altered check constraint expressions

1. Compare functions, stored procedures, and triggers:

• PostgreSQL: Query pg_proc for function signatures and pg_trigger for trigger definitions
• MySQL: