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".
npxskills add jeremylongshore/claude-code-plugins-plus-skills--skill comparing-database-schemasLoading…
Compare database schemas between two environments (development vs. staging, staging vs.
psql or mysql CLI configured to connect to both environmentsinformation_schema and pg_catalog (PostgreSQL) or information_schema (MySQL)pg_dump --schema-only for full schema extractionExtract the full schema from both databases for comparison:
pg_dump --schema-only --no-owner --no-privileges -f schema_source.sql source_db and repeat for target_dbmysqldump --no-data --routines --triggers source_db > schema_source.sqlinformation_schema directly for programmatic comparisonCompare 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'Compare columns for each shared table:
information_schema.columns from both databases for: column_name, data_type, character_maximum_length, is_nullable, column_default, ordinal_positionCompare indexes:
pg_indexes for indexname, indexdef on each databaseinformation_schema.STATISTICS for INDEX_NAME, COLUMN_NAME, NON_UNIQUECompare constraints (primary keys, foreign keys, unique, check):
information_schema.table_constraints and information_schema.key_column_usageCompare functions, stored procedures, and triggers:
pg_proc for function signatures and pg_trigger for trigger definitionsinformation_schema.ROUTINES and information_schema.TRIGGERSCompare enum types and custom types (PostgreSQL):
pg_type and pg_enum for enum label differencesGenerate a structured diff report categorizing differences as:
Generate migration SQL to synchronize the target database to match the source:
Validate the generated migration by applying it to a copy of the target database and re-running the diff. The second diff should report zero differences, confirming the migration produces the expected state.
| Error | Cause | Solution |
|---|---|---|
| Connection refused to one database | Network or credential issue on source or target | Verify connection strings; check firewall rules; confirm credentials work with direct psql or mysql connection |
Permission denied on pg_catalog queries | User lacks read access to system catalogs | Grant pg_read_all_settings role; or use pg_dump --schema-only which requires fewer privileges |
| False positive differences from default value formatting | PostgreSQL normalizes default expressions differently in different versions | Normalize default value strings before comparison; ignore whitespace differences; compare semantic equivalence |
| Enum type modification blocked | PostgreSQL does not support removing enum values or reordering | Create a new enum type, migrate the column, drop the old type; document this as a multi-step migration |
| Generated migration fails on target | Target has data that violates new constraints | Add data validation queries before constraint creation; backfill default values; handle edge cases in migration |
Detecting schema drift between staging and production: After 3 months without auditing, the diff reveals: 2 columns added to production manually (not in migrations), 1 index missing from staging, and 3 functions with different implementations. A migration script is generated to bring staging in sync, and the manual production changes are backported into migration files.
Pre-deployment schema validation: Before deploying a release with 5 migration files, run the diff between the post-migration staging schema and the expected schema. The diff catches a migration that accidentally dropped a constraint that a later migration depends on. The migration ordering is fixed before production deployment.
Comparing PostgreSQL schemas across major version upgrade: Schema extracted from PostgreSQL 14 and compared against PostgreSQL 16 after migration. Diff reveals function signature changes for built-in function calls, updated default values for new parameters, and deprecated syntax in stored procedures. Migration script updates function definitions for the new version.
Create or update AgentSkills. Use when designing, structuring, or packaging skills with scripts, references, and assets.
Create or update AgentSkills. Use when designing, structuring, or packaging skills with scripts, references, and assets.
Set up and use 1Password CLI (op). Use when installing the CLI, enabling desktop app integration, signing in (single or multi-account), or reading/injecting/running secrets via op.
CLI to manage emails via IMAP/SMTP. Use `himalaya` to list, read, write, reply, forward, search, and organize emails from the terminal. Supports multiple accounts and message composition with MML (MIME Meta Language).
Create or update AgentSkills. Use when designing, structuring, or packaging skills with scripts, references, and assets.
Create or update AgentSkills. Use when designing, structuring, or packaging skills with scripts, references, and assets.
Set up and use 1Password CLI (op). Use when installing the CLI, enabling desktop app integration, signing in (single or multi-account), or reading/injecting/running secrets via op.
CLI to manage emails via IMAP/SMTP. Use `himalaya` to list, read, write, reply, forward, search, and organize emails from the terminal. Supports multiple accounts and message composition with MML (MIME Meta Language).