Laravel command to drop all tables and re-run migrations while preserving specified tables.

MigrateFreshPreserve.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Schema;

class MigrateFreshPreserve extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'migrate:fresh:preserve
                            {--seed : Seed the database after migrating}
                            {--force : Force the operation to run when in production}
                            {--overwrite : Overwrite existing dump files}
                            {--dry-run : Show what would be preserved without actually doing it}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Drop all tables and re-run migrations while preserving specified tables';

    /**
     * Directory to store table dumps.
     */
    private string $dumpDir;

    /**
     * Execute the console command.
     */
    public function handle(): int
    {
        // Check database driver
        $connection = config('database.default');
        $driver     = config("database.connections.{$connection}.driver");

        if ($driver !== 'mysql') {
            $this->error("This command only supports MySQL databases. Current driver: {$driver}");

            return Command::FAILURE;
        }

        // Check production confirmation
        if ($this->getLaravel()->environment('production') && ! $this->option('force')) {
            $this->warn('Application is in production!');
            if (! $this->confirm('Do you really wish to run this command?')) {
                $this->info('Command cancelled.');

                return Command::FAILURE;
            }
        }

        $this->dumpDir = config('migrations.dump_directory', storage_path('app/preserved-tables'));

        // Ensure dump directory exists
        if (! File::exists($this->dumpDir)) {
            File::makeDirectory($this->dumpDir, 0755, true);
        }

        $beforeSeedTables = config('migrations.preserve_before_seed', []);
        $afterSeedTables  = config('migrations.preserve_after_seed', []);

        if ($this->option('dry-run')) {
            $this->info('Dry run mode - showing what would be preserved:');
            $this->showTablesInfo($beforeSeedTables, 'Before Seed');
            $this->showTablesInfo($afterSeedTables, 'After Seed');

            return Command::SUCCESS;
        }

        // Check for existing dump files
        $allTables     = array_merge($beforeSeedTables, $afterSeedTables);
        $existingDumps = [];
        foreach ($allTables as $table) {
            $dumpFile = $this->dumpDir . '/' . $table . '.sql';
            if (File::exists($dumpFile)) {
                $existingDumps[] = $table;
            }
        }

        if (! empty($existingDumps) && ! $this->option('overwrite')) {
            $this->warn('Existing dump files found for: ' . implode(', ', $existingDumps));
            $this->warn('These tables will be skipped. Use --overwrite to replace existing dumps.');
            $this->newLine();
        }

        try {
            // Step 1: Dump tables to preserve
            $this->info('Preserving tables...');
            $beforeDumps = $this->dumpTables($beforeSeedTables);
            $afterDumps  = $this->dumpTables($afterSeedTables);

            if (empty($beforeDumps) && empty($afterDumps)) {
                $this->warn('No tables found to preserve. Continuing with normal migrate:fresh...');
            }

            // Step 2: Run migrate:fresh
            $this->newLine();
            $this->info('Running migrate:fresh...');
            $this->call('migrate:fresh', [
                '--force' => true, // Always force since we already confirmed
            ]);

            // Step 3: Restore before-seed tables
            if (! empty($beforeDumps)) {
                $this->newLine();
                $this->info('Restoring tables (before seed)...');
                $this->restoreTables($beforeDumps);
            }

            // Step 4: Run seeders if requested
            if ($this->option('seed')) {
                $this->newLine();
                $this->info('Seeding database...');
                $this->call('db:seed', ['--force' => true]);
            }

            // Step 5: Restore after-seed tables
            if (! empty($afterDumps)) {
                $this->newLine();
                $this->info('Restoring tables (after seed)...');
                $this->restoreTables($afterDumps);
            }

            $this->newLine();
            $this->info('Migration completed successfully!');
            $this->comment("Preserved table dumps are stored in: {$this->dumpDir}");

            return Command::SUCCESS;
        } catch (\Exception $e) {
            $this->error('Migration failed: ' . $e->getMessage());
            $this->error('Preserved table dumps are available in: ' . $this->dumpDir);

            return Command::FAILURE;
        }
    }

    /**
     * Show information about tables that would be preserved.
     */
    private function showTablesInfo(array $tables, string $label): void
    {
        $this->newLine();
        $this->line("<fg=cyan>{$label} Tables:</>");

        if (empty($tables)) {
            $this->line('  <fg=gray>None</>');

            return;
        }

        foreach ($tables as $table) {
            $exists = Schema::hasTable($table);
            $status = $exists ? '<fg=green>✓</>' : '<fg=red>✗</>';
            $count  = $exists ? DB::table($table)->count() : 0;

            $this->line("  {$status} {$table} " . ($exists ? "({$count} rows)" : '(does not exist)'));
        }
    }

    /**
     * Dump specified tables to SQL files.
     */
    private function dumpTables(array $tables): array
    {
        $dumps = [];

        foreach ($tables as $table) {
            if (! Schema::hasTable($table)) {
                $this->warn("  Skipping '{$table}' - table does not exist");

                continue;
            }

            $rowCount = DB::table($table)->count();
            $dumpFile = $this->dumpDir . '/' . $table . '.sql';

            // Check if dump file already exists
            if (File::exists($dumpFile) && ! $this->option('overwrite')) {
                $this->warn("  Skipping '{$table}' - dump file already exists (use --overwrite to replace)");

                continue;
            }

            $this->line("  Dumping '{$table}' ({$rowCount} rows)...");

            // Get database connection info
            $connection = config('database.default');
            $config     = config("database.connections.{$connection}");

            // Build mysqldump command (suppress password warning)
            $command = sprintf(
                'mysqldump --host=%s --port=%s --user=%s --password=%s %s %s > %s 2>/dev/null',
                escapeshellarg($config['host']),
                escapeshellarg($config['port'] ?? 3306),
                escapeshellarg($config['username']),
                escapeshellarg($config['password']),
                escapeshellarg($config['database']),
                escapeshellarg($table),
                escapeshellarg($dumpFile)
            );

            exec($command, $output, $returnCode);

            if ($returnCode !== 0 || ! File::exists($dumpFile) || File::size($dumpFile) === 0) {
                $this->error("  Failed to dump '{$table}'");
                if (File::exists($dumpFile)) {
                    File::delete($dumpFile);
                }

                continue;
            }

            $dumps[] = [
                'table' => $table,
                'file'  => $dumpFile,
            ];
        }

        return $dumps;
    }

    /**
     * Restore tables from SQL dump files.
     */
    private function restoreTables(array $dumps): void
    {
        // Disable foreign key checks
        DB::statement('SET FOREIGN_KEY_CHECKS=0');

        foreach ($dumps as $dump) {
            $table = $dump['table'];
            $file  = $dump['file'];

            if (! File::exists($file)) {
                $this->warn("  Skipping '{$table}' - dump file not found");

                continue;
            }

            $this->line("  Restoring '{$table}'...");

            // Get database connection info
            $connection = config('database.default');
            $config     = config("database.connections.{$connection}");

            // Build mysql restore command (suppress password warning)
            $command = sprintf(
                'mysql --host=%s --port=%s --user=%s --password=%s %s < %s 2>/dev/null',
                escapeshellarg($config['host']),
                escapeshellarg($config['port'] ?? 3306),
                escapeshellarg($config['username']),
                escapeshellarg($config['password']),
                escapeshellarg($config['database']),
                escapeshellarg($file)
            );

            exec($command, $output, $returnCode);

            if ($returnCode !== 0) {
                $this->error("  Failed to restore '{$table}'");
            }
        }

        // Re-enable foreign key checks
        DB::statement('SET FOREIGN_KEY_CHECKS=1');
    }
}

migrations.php

<?php

return [

    /*
    |--------------------------------------------------------------------------
    | Preserve Tables on Fresh Migration
    |--------------------------------------------------------------------------
    |
    | When running migrate:fresh:preserve, these tables will be preserved
    | and restored. Tables are split into two groups: before and after seeding.
    |
    */

    /**
     * Directory to store table dumps.
     */
    'dump_directory'       => storage_path('app/preserved-tables'),

    /**
     * Tables to restore BEFORE running seeders.
     * Use this for tables that seeders might depend on.
     */
    'preserve_before_seed' => [
        'users',
    ],

    /**
     * Tables to restore AFTER running seeders.
     * Use this for tables with data that should not be overwritten by seeders.
     */
    'preserve_after_seed'  => [

    ],

];