Index of every page on this site: llms.txt
All skills
jeremylongshore avatar

/comparing-database-schemas

@820dfc8
Run it yourself

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".

Use this Skill: https://skilld.dev/gh/jeremylongshore/tons-of-skills-marketplace/comparing-database-schemas

Nothing lands on disk. Nothing to clean up.

Fork this Skill

Edit a local copy. It keeps the author and licence.

SKILL.md

≈68 tokens for metadata: the name and description. ≈1.7k when used: this file. ≈191 more on demand in 3 files.

Description uses 3.4% of example budget

Before choosing a Skill, your Agent reads its name and description. All available Skills share that space.

  • A shorter description leaves more room for other Skills. This entry exceeds our 1% size suggestion.

In our Claude Code example, all Skill names and descriptions share 8,000 characters. This Skill uses ≈271 characters, or 3.4%.

The 1% threshold is a size suggestion. Longer descriptions can still fit.

Your model, settings, and other Skills decide how much text your Agent can read.

Example settings and source

The example uses a 200k-token context and default Claude Code settings. The count includes the name, description, separators, and when_to_use when present. Codex also counts local file paths.

Skit's source and limits: Codex 0.160.1, Claude Code 2.1.292.

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
    • Flag missing, extra, or differently-defined indexes
  5. 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
  6. Compare functions, stored procedures, and triggers:

    • PostgreSQL: Query pg_proc for function signatures and pg_trigger for trigger definitions
    • MySQL: Query information_schema.ROUTINES and information_schema.TRIGGERS
    • Compare function bodies for logical differences
  7. Compare enum types and custom types (PostgreSQL):

    • Query pg_type and pg_enum for enum label differences
    • Detect added or removed enum values (note: PostgreSQL only supports adding enum values, not removing)
  8. Generate a structured diff report categorizing differences as:

    • Added: Objects in source not present in target (require CREATE statements)
    • Removed: Objects in target not present in source (require DROP statements, confirm intentional)
    • Modified: Objects differing between source and target (require ALTER statements)
  9. Generate migration SQL to synchronize the target database to match the source:

    • CREATE TABLE for new tables, ALTER TABLE ADD COLUMN for new columns
    • ALTER TABLE ALTER COLUMN for type changes, ALTER TABLE DROP COLUMN for removed columns
    • CREATE INDEX / DROP INDEX for index differences
    • Include transaction wrapping and rollback-safe operations
  10. 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.

Output

  • Schema diff report listing all differences categorized by type (added, removed, modified)
  • Migration SQL script to synchronize target schema to match source
  • Rollback SQL script to reverse the migration if needed
  • Side-by-side comparison of differing object definitions
  • Drift detection summary highlighting changes not tracked in migration files

Error Handling

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

Examples

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.

Resources

Source: SKILL.md on GitHub

skilld matched fixed text patterns in SKILL.md and file names. Patterns miss obfuscated code.

skilld run checks every file with the same patterns. It asks for approval before it loads a Skill with a behavior marked Needs approval.

No alerts2mo3 checks · Risk SAFE
  • Gen Agent Trust Hub2mo

    The skill includes functionality to generate and make shell scripts executable, while processing external database schema data which could potentially be manipulated to influence the agent's behavior.

  • Socket2mo

    No alerts

  • Snyk2mo

    Risk: LOW · No issues

Signed by skilld at 820dfc8. This ties the file your Agent reads to that commit on GitHub. It does not review the instructions.

Last checked against GitHub 20 hours ago.

Activeupdated 3 months ago
What it can do
Reads files Edits files Runs commands
version
1.27.0
author
Jeremy Longshore <jeremy@intentsolutions.io>
tags
[
  "database",
  "comparing-database"
]
All 8 allowed tools
ReadWriteEditGrepGlobBash(psql:*)Bash(mysql:*)Bash(mongosh:*)
Other metadata
compatibility
Designed for Claude Code

README badge

README badge for jeremylongshore/tons-of-skills-marketplace/comparing-database-schemas