Process use when you need to work with schema comparison.
ReadWriteEditGrepGlobBash(psql:*)Bash(mysql:*)Bash(mongosh:*)
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
informationschema and pgcatalog (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
- Extract the full schema from both databases for comparison:
- PostgreSQL:
pgdump --schema-only --no-owner --no-privileges -f schemasource.sql sourcedb and repeat for targetdb
- MySQL:
mysqldump --no-data --routines --triggers sourcedb > schemasource.sql
- Alternatively, query
information_schema directly for programmatic comparison
- Compare tables present in each database:
SELECT tablename FROM informationschema.tables WHERE tableschema = 'public' AND tablecatalog = 'sourcedb' EXCEPT SELECT tablename FROM informationschema.tables WHERE tableschema = 'public' AND tablecatalog = 'targetdb'- This reveals tables that exist in source but not in target (and vice versa)
- Compare columns for each shared table:
- Query
informationschema.columns from both databases for: columnname, datatype, charactermaximumlength, isnullable, columndefault, ordinalposition
- 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)
- Compare indexes:
- PostgreSQL: Query
pg_indexes for indexname, indexdef on each database
- MySQL: Query
informationschema.STATISTICS for INDEXNAME, COLUMNNAME, NONUNIQUE
- Flag missing, extra, or differently-defined indexes
- Compare constraints (primary keys, foreign keys, unique, check):
- Query
informationschema.tableconstraints and informationschema.keycolumn_usage
- Detect missing foreign keys, changed constraint names, and altered check constraint expressions
- Compare functions, stored procedures, and triggers:
- PostgreSQL: Query
pgproc for function s
