Download the PHP package zaeem2396/schema-lens without Composer

Preview Laravel migrations before execution with destructive change detection

A Laravel package that extends the default Artisan CLI with commands to preview a single migration file against the current database schema before execution. It provides comprehensive schema diff analysis, destructive change detection, automatic data export, and rollback simulation.

Release highlights: v1.8.0 adds PostgreSQL schema introspection and paired pgsql support for schema:diff. v1.7.0 adds optional full-database backup before safe migrations (migrate:safe --backup), schema-lens.backup configuration, and schema:restore for restore hints. v1.6.0 adds schema:diff across two MySQL connections. Details: CHANGELOG.md.

Features

• 🔍 Schema Diff Analysis: Compare migration operations against the current database schema (MySQL/MariaDB or PostgreSQL when connected)
• ⚠️ Destructive Change Detection: Automatically flags dangerous operations
• 🔄 Interactive Mode: Step-by-step confirmation for destructive changes
• 📄 Single Migration Support: Run a specific migration file with full analysis
• 💾 Automatic Data Export: Exports affected data to CSV/JSON when destructive changes are detected
• 🔄 Rollback Simulation: Preview rollback impact and SQL statements
• 📊 Line-by-Line Mapping: Maps each database change back to exact lines in migration file
• 🎨 Clean CLI Output: Human-readable formatted output
• 📄 SQL Preview: Generate raw SQL statements from migrations
• ⚙️ Configurable SQL engine: Set table engine (InnoDB, MyISAM, etc.) for generated SQL via config
• 📊 Migration Dependency Graph: Visualize migration dependencies (foreign keys) as ASCII tree or JSON
• 🔀 Schema diff between environments: Compare two Laravel connections (mysql/mariadb or pgsql pairs; missing tables/columns, type mismatches)
• 📦 Full database backup: Optional mysqldump before migrate:safe (--backup, --backup-path, config auto backup and retention)
• 📄 JSON Export: Optional JSON report for CI/CD integration
• 🗜️ Compression: Automatic compression of exported data
• 📦 Versioning: Automatic versioning of exports with restore metadata

Quick Start

📖 For detailed usage instructions, testing scenarios, and examples, see USAGE.md

Installation

The package supports:

• PHP 8.1+
• Laravel 10.x through 13.x

Schema introspection (schema:preview, migrate:safe, destructive detection, schema:diff) supports MySQL / MariaDB and PostgreSQL using each engine’s catalogs (information_schema and pg_catalog where needed). SQLite and other Laravel drivers cannot run introspection-only flows here; use schema:preview migration.php --sql to inspect generated SQL offline, or run tests against MySQL or PostgreSQL (see CI workflow).

Error output: When a command fails, only the error message is shown by default. Use -v / --verbose to see the full stack trace (e.g. for debugging).

Configuration

Publish the configuration file (optional):

This will create config/schema-lens.php with the following options:

The SQL engine (schema-lens.sql.engine or SCHEMA_LENS_SQL_ENGINE) is used in generated CREATE TABLE statements when using schema:preview --sql. If not set, the default database connection's engine is used (typically InnoDB).

The backup block configures optional logical backups before migrate:safe runs: SCHEMA_LENS_BACKUP_AUTO runs a dump automatically when destructive changes are detected (unless --no-backup), SCHEMA_LENS_BACKUP_DRIVER is mysqldump (default) or spatie (placeholder when spatie/laravel-backup is present), SCHEMA_LENS_BACKUP_DIRECTORY is relative to storage_path(), SCHEMA_LENS_BACKUP_RETENTION_DAYS prunes old schema-lens-db-*.sql files (0 disables pruning), and SCHEMA_LENS_MYSQLDUMP_PATH points to the mysqldump binary if it is not on PATH.

Usage

Basic Usage

Preview a migration file:

Or use a relative path from the migrations directory:

SQL Preview

Generate raw SQL statements that would be executed:

The table engine in generated SQL (e.g. ENGINE=InnoDB) is configurable via config/schema-lens.php → sql.engine or the SCHEMA_LENS_SQL_ENGINE env variable.

Migration Dependency Graph

Visualize which migrations depend on others (e.g. foreign key relationships):

The graph is derived from CREATE TABLE and foreign key operations in each migration. Edges are deduplicated (at most one edge per migration pair). Circular dependencies are detected and reported.

Exit codes: If you pass --path and that directory is empty or contains no migration files, the command exits with code 1. With the default path, an empty directory yields a warning but exit code 0.

Options: --path — custom migrations directory; --format=json — machine-readable graph. See TESTING-SCENARIOS.md scenario 21 for manual verification steps.

Example output (CLI):

Schema diff between environments

Compare live MySQL schemas from two Laravel database connections (for example local vs staging). Both connections must use the mysql driver and exist in config/database.php.

Exit codes: The command exits with code 1 when any structural difference is found (missing/extra tables or columns, type or nullable mismatches). Use --exit-zero if you only need output in scripts without a failing exit code. It exits 0 when schemas match or when --exit-zero is set.

Example output (CLI):

Example output:

When using --output, the SQL file includes:

• Header comments with migration name and timestamp
• SET FOREIGN_KEY_CHECKS=0/1 wrappers
• Operation comments for each statement

JSON Output

Generate a JSON report for CI/CD:

The JSON report will be saved to storage/app/schema-lens/report.json by default.

Skip Data Export

If you want to preview without exporting data (even if destructive changes are detected):

Safe Migration (with auto-backup)

Run migrations with automatic destructive change detection and data backup:

Arguments:

• path - (Optional) Path to a specific migration file to run

Options:

• --force - Force the operation to run in production
• --seed - Run seeders after migration
• --step - Run migrations one at a time
• --pretend - Dump the SQL queries that would be run
• --no-backup - Skip data backup for destructive changes (row exports and full mysqldump when applicable)
• --interactive - Confirm each destructive change individually
• --backup - Always create a full database SQL dump via mysqldump before migrations (skipped with --pretend)
• --backup-path= - Write the dump to this path (otherwise uses schema-lens.backup.directory)

This command:

1. Analyzes all pending migrations for destructive changes
2. Automatically backs up affected data before proceeding
3. Asks for confirmation if destructive changes are detected
4. Runs the actual migration

Single Migration File

Run a specific migration file instead of all pending migrations:

This is useful when you:

• Want to analyze and run just one migration
• Need fine-grained control over which migration to execute
• Are testing a specific migration before deploying

You can combine it with other options:

The command validates that:

• The file exists
• It has a .php extension
• It hasn't already been executed

Interactive Mode

For granular control over destructive migrations, use interactive mode:

This prompts you to review each migration with destructive changes individually:

Options during review:

Only approved migrations will be executed, giving you full control over which destructive changes to apply.

Full database backup (mysqldump)

In addition to per-table CSV/JSON exports for destructive operations, you can take a full logical backup of the default MySQL database before migrations run:

With SCHEMA_LENS_BACKUP_AUTO=true (or schema-lens.backup.auto), a dump is created automatically when destructive changes are detected, unless you pass --no-backup. --pretend never writes a dump file.

Dumps default to storage_path() + schema-lens.backup.directory, with filenames like schema-lens-db-YYYY-mm-dd_His.sql. Old files matching schema-lens-db-*.sql in that directory are pruned according to retention_days.

Restore hint (schema:restore)

Schema Lens does not execute restores for you. After generating a .sql file (from this package or any mysqldump), print the suggested mysql client invocation:

What It Detects

Schema Changes

• Tables: Create, modify, drop
• Columns: Add, modify, drop, rename
• Indexes: Add, drop
• Foreign Keys: Add, drop
• Engine: Changes
• Charset: Changes
• Collation: Changes

Destructive Operations

The following operations are flagged as destructive:

• dropTable() / dropIfExists()
• dropColumn() — single dropColumn('col') or multiple dropColumn(['col1','col2'])
• dropIndex()
• dropForeign()
• renameColumn()
• Constraint removals

Data Export

When destructive changes are detected, Schema Lens automatically:

1. Exports affected table/column data to CSV and JSON
2. Compresses exports (if enabled)
3. Versions the export with metadata
4. Creates restore instructions

Export Structure

Restore Metadata

Each export includes a restore_metadata.json file with:

• Export version and timestamp
• Migration file reference
• Affected tables and columns
• Restore instructions
• File paths for all exported data

Output Examples

CLI Output

JSON Output

Rollback Simulation

Schema Lens analyzes the down() method of migrations to:

• Show rollback SQL statements
• Identify dependency break risks
• Warn about foreign key constraints
• Highlight affected tables and columns

Requirements

• PHP 8.1+
• Laravel 10.x–13.x (Laravel 13 requires PHP 8.3+)
• MySQL 5.7+ or MariaDB 10.2+, or PostgreSQL 13+, for commands that introspect the live schema
• Catalog access (information_schema / pg_catalog as implemented)

Environment Variables

You can configure Schema Lens using environment variables:

CI/CD Integration

GitHub Actions Example

GitLab CI Example

Troubleshooting

• “Schema Lens schema introspection requires MySQL, MariaDB, or PostgreSQL” — Point the default Laravel DB connection (or --connection flows) at MySQL/MariaDB/PostgreSQL, or use --sql-only preview on unsupported drivers locally.
• Debugging command failures — Use -v or --verbose to see the full stack trace.
• Custom table engine in generated SQL — Set SCHEMA_LENS_SQL_ENGINE or config/schema-lens.sql.engine (e.g. MyISAM) to override the engine in CREATE TABLE output.
• schema:diff — Both connections must be mysql/mariadb or both pgsql (same driver family). Define them in config/database.php. For PostgreSQL, set 'schema' (e.g. public) on each connection when not using defaults.
• schema:diff exits 1 on drift — Use --exit-zero in CI if you only want logs without failing the job.
• mysqldump not found — Install MySQL client tools on the host or set SCHEMA_LENS_MYSQLDUMP_PATH to the full path of the mysqldump binary.

Limitations

• schema:diff compares structure only (tables/columns/types), not row data or triggers
• SQL preview (--sql) and table ENGINE= hints remain MySQL-oriented; PostgreSQL is supported for live introspection, schema:diff, and safer rollback hints when connected to Postgres
• migrate:safe --backup / mysqldump applies to MySQL-compatible connections only (not PostgreSQL dumps in this release)
• Requires direct database connection (no cloud proxies that hide catalog access)
• Migration parser supports standard Laravel migration syntax

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

This package is open-sourced software licensed under the MIT license.

Author

zaeem2396

GitHub: @zaeem2396

Support

For issues, questions, or contributions, please open an issue on GitHub.