README

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 MySQL schema before execution. It provides comprehensive schema diff analysis, destructive change detection, automatic data export, and rollback simulation.

Release highlights: v4.7.0 adds optional full-database backup before safe migrations (migrate:safe --backup), schema-lens.backup configuration, and schema:restore for restore hints. v4.6.0 adds schema:diff across two MySQL connections. v4.5.0 adds Laravel 13 support. Details: CHANGELOG.md.

Features

🔍 Schema Diff Analysis: Compare migration operations against current MySQL schema
⚠️ 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 MySQL connections (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

composer require zaeem2396/schema-lens
php artisan schema:preview database/migrations/your_migration.php
# Compare two MySQL connections (optional): php artisan schema:diff mysql mysql_staging
# Optional full SQL backup before safe migrate (MySQL client tools required): php artisan migrate:safe --backup

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

Installation

composer require zaeem2396/schema-lens

The package supports:

PHP 8.1+
Laravel 10.x through 13.x

Schema introspection (diff analysis, destructive detection) requires MySQL. The schema:preview command connects to your database to compare the migration against the current schema. If you use SQLite or another driver locally, use schema:preview migration.php --sql to generate SQL without connecting, or run full preview against a MySQL database (e.g. in CI).

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):

php artisan vendor:publish --tag=schema-lens-config

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

return [
    'export' => [
        'row_limit' => env('SCHEMA_LENS_EXPORT_ROW_LIMIT', 1000),
        'storage_path' => 'app/schema-lens/exports',
        'compress' => env('SCHEMA_LENS_COMPRESS_EXPORTS', true),
    ],
    'output' => [
        'format' => env('SCHEMA_LENS_OUTPUT_FORMAT', 'cli'),
        'show_line_numbers' => env('SCHEMA_LENS_SHOW_LINE_NUMBERS', true),
    ],
    'sql' => [
        'engine' => env('SCHEMA_LENS_SQL_ENGINE'), // e.g. InnoDB, MyISAM; falls back to DB connection engine
    ],
    'backup' => [
        'auto' => env('SCHEMA_LENS_BACKUP_AUTO', false),
        'driver' => env('SCHEMA_LENS_BACKUP_DRIVER', 'mysqldump'),
        'directory' => env('SCHEMA_LENS_BACKUP_DIRECTORY', 'app/schema-lens/backups'),
        'retention_days' => (int) env('SCHEMA_LENS_BACKUP_RETENTION_DAYS', 7),
        'mysqldump_binary' => env('SCHEMA_LENS_MYSQLDUMP_PATH'),
    ],
];

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:

php artisan schema:preview database/migrations/2024_01_01_000000_create_users_table.php

Or use a relative path from the migrations directory:

php artisan schema:preview 2024_01_01_000000_create_users_table.php

SQL Preview

Generate raw SQL statements that would be executed:

# Display SQL in terminal
php artisan schema:preview database/migrations/2024_01_01_000000_create_users_table.php --sql

# Save SQL to file
php artisan schema:preview database/migrations/2024_01_01_000000_create_users_table.php --sql --output=migration.sql

# Or use format option
php artisan schema:preview database/migrations/2024_01_01_000000_create_users_table.php --format=sql

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):

# Default: ASCII tree (uses database/migrations)
php artisan schema:graph

# Custom path
php artisan schema:graph --path=database/migrations

# JSON output
php artisan schema:graph --format=json

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):

Migration Dependency Graph

├── 2024_01_01_000000_create_users_table
│   └── 2024_01_06_000000_create_posts_with_foreign_key
└── 2024_01_06_000000_create_posts_with_foreign_key

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.

php artisan schema:diff mysql mysql_staging

# Named options (same as positional arguments)
php artisan schema:diff --from=mysql --to=mysql_staging

# Machine-readable output
php artisan schema:diff mysql mysql_staging --format=json

# Suggested migration-style hints for gaps (review before using)
php artisan schema:diff mysql mysql_staging --stubs

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):

Schema differences: mysql → mysql_staging
(Reference = mysql; missing below means absent on mysql_staging)

MISSING TABLES ON mysql_staging:
  ✗ Table: user_preferences

TYPE MISMATCH:
  ⚠ posts.body: text (mysql) vs longtext (mysql_staging)

Example output:

╔══════════════════════════════════════════════════════════════╗
║               📄 GENERATED SQL STATEMENTS                    ║
╚══════════════════════════════════════════════════════════════╝

🟢 [1] table::create
CREATE TABLE `users` (...) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

🟢 [2] column::add
ALTER TABLE `users` ADD COLUMN `name` VARCHAR(255);

─────────────────────────────────────────────────────────────────
📊 Summary:
   Total statements: 2
   Operations: 🟢 1 create, 🟢 1 add

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:

php artisan schema:preview database/migrations/2024_01_01_000000_create_users_table.php --format=json

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):

php artisan schema:preview database/migrations/2024_01_01_000000_create_users_table.php --no-export

Safe Migration (with auto-backup)

Run migrations with automatic destructive change detection and data backup:

php artisan migrate:safe

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:

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

Single Migration File

Run a specific migration file instead of all pending migrations:

# Using relative path
php artisan migrate:safe database/migrations/2024_01_15_drop_column.php

# Using absolute path
php artisan migrate:safe /var/www/app/database/migrations/2024_01_15_drop_column.php

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:

# Single file with interactive mode
php artisan migrate:safe database/migrations/2024_01_15_drop_column.php --interactive

# Single file without backup
php artisan migrate:safe database/migrations/2024_01_15_drop_column.php --no-backup

# Single file with pretend mode (just show SQL)
php artisan migrate:safe database/migrations/2024_01_15_drop_column.php --pretend

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:

php artisan migrate:safe --interactive

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

📋 Migration: 2024_01_15_drop_email_column.php
   Destructive changes:
   🔴 [CRITICAL] column::drop
      Tables: users
      Columns: users.email

   Approve '2024_01_15_drop_email_column.php'? [y/n/a/s/q]

Options during review:

Key	Action
`y`	Approve this migration
`n`	Skip this migration
`a`	Approve all remaining migrations
`s`	Skip all remaining migrations
`q`	Quit and cancel everything

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:

php artisan migrate:safe --backup
php artisan migrate:safe --backup --backup-path=/var/backups/app-pre-migrate.sql

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:

php artisan schema:restore /path/to/dump.sql
php artisan schema:restore storage/app/schema-lens/backups/schema-lens-db-2026-04-02_120000.sql --connection=mysql

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:

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

Export Structure

storage/app/schema-lens/exports/
└── 2024_01_01_000000_create_users_table_2024-01-15_10-30-45_v0001/
    ├── users.json
    ├── users.csv
    ├── users.zip (if compression enabled)
    └── restore_metadata.json

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

╔══════════════════════════════════════════════════════════════╗
║          Schema Lens - Migration Preview Report            ║
╚══════════════════════════════════════════════════════════════╝

📊 SUMMARY
────────────────────────────────────────────────────────────
Tables:        1
Columns:       5
Indexes:       2
Foreign Keys:  1
Engine:        0
Charset:       0
Collation:     0

⚠️  DESTRUCTIVE CHANGES: 1

⚠️  DESTRUCTIVE CHANGES DETECTED
════════════════════════════════════════════════════════════

  Risk Level: HIGH
  Operation:  column:drop
  Line:       45
  Tables:     users
  Columns:    users.email

📋 DETAILED CHANGES
────────────────────────────────────────────────────────────

📦 TABLES:
  ➕ [Line 12] Will create new table 'users'

📝 COLUMNS:
  ➕ [Line 15] Will add new column 'users.id'
  ➕ [Line 16] Will add new column 'users.name'
  🔴 [Line 45] Will DROP column 'users.email' (DESTRUCTIVE)

🔄 ROLLBACK SIMULATION
────────────────────────────────────────────────────────────
  Risk Level: HIGH
  Columns Affected: users.email

JSON Output

{
    "timestamp": "2024-01-15T10:30:45+00:00",
    "summary": {
        "tables": 1,
        "columns": 5,
        "indexes": 2,
        "foreign_keys": 1,
        "destructive_changes_count": 1,
        "has_destructive_changes": true
    },
    "diff": {
        "tables": [...],
        "columns": [...],
        "indexes": [...],
        "foreign_keys": [...]
    },
    "destructive_changes": [...],
    "rollback": {...},
    "exports": [...]
}

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+
Access to information_schema database

Environment Variables

You can configure Schema Lens using environment variables:

SCHEMA_LENS_EXPORT_ROW_LIMIT=1000
SCHEMA_LENS_COMPRESS_EXPORTS=true
SCHEMA_LENS_OUTPUT_FORMAT=cli
SCHEMA_LENS_SHOW_LINE_NUMBERS=true
SCHEMA_LENS_BACKUP_AUTO=false
SCHEMA_LENS_BACKUP_DRIVER=mysqldump
SCHEMA_LENS_BACKUP_DIRECTORY=app/schema-lens/backups
SCHEMA_LENS_BACKUP_RETENTION_DAYS=7
SCHEMA_LENS_MYSQLDUMP_PATH=

CI/CD Integration

GitHub Actions Example

- name: Preview Migration
  run: |
    php artisan schema:preview database/migrations/2024_01_01_000000_create_users_table.php --format=json
    cat storage/app/schema-lens/report.json | jq '.destructive_changes'

GitLab CI Example

migration-preview:
  script:
    - php artisan schema:preview database/migrations/2024_01_01_000000_create_users_table.php --format=json
    - |
      if [ $(cat storage/app/schema-lens/report.json | jq '.summary.has_destructive_changes') = "true" ]; then
        echo "⚠️ Destructive changes detected!"
        exit 1
      fi

Troubleshooting

"Schema Lens schema introspection requires MySQL" — Use schema:preview migration.php --sql to generate SQL without connecting, or run the command against a MySQL database (e.g. in CI).
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 requires two MySQL connections — Add both to config/database.php; SQLite or other drivers are rejected for this command.
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
Currently supports MySQL/MariaDB only
Requires direct database connection (no cloud services)
Schema introspection uses information_schema tables
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.

zaeem2396 / schema-lens

Maintainers

Package info

Statistics

Security

README

Features

Quick Start

Installation

Configuration

Usage

Basic Usage

SQL Preview

Migration Dependency Graph

Schema diff between environments

JSON Output

Skip Data Export

Safe Migration (with auto-backup)

Single Migration File

Interactive Mode

Full database backup (mysqldump)

Restore hint (schema:restore)

What It Detects

Schema Changes

Destructive Operations

Data Export

Export Structure

Restore Metadata

Output Examples

CLI Output

JSON Output

Rollback Simulation

Requirements

Environment Variables

CI/CD Integration

GitHub Actions Example

GitLab CI Example

Troubleshooting

Limitations

Contributing

License

Author

Support

Full database backup (`mysqldump`)

Restore hint (`schema:restore`)