A Laravel artisan command to migrate database tables from MySQL to Postgres and back. See https://silvanhagen.com/talks/meetup-laravel-cloud/

MigrateDatabaseCommand.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use Symfony\Component\Console\Helper\ProgressBar;

class MigrateDatabaseCommand extends Command
{
    protected $signature = 'app:migrate-database {source} {destination} {table} {--chunk=50} {--from=}';

    private ProgressBar $bar;

    public function handle(): void
    {
        $source = $this->argument('source');
        $destination = $this->argument('destination');
        $table = $this->argument('table');
        $chunkSize = $this->option('chunk');

        $this->info("Importing table {$table}");

        // Get the primary key of the table
        $primaryKey = $this->getPrimaryKey($source, $table);

        $query = DB::connection($source)->table($table);

        if ($primaryKey && $this->option('from')) {
            $query->where($primaryKey, '>', $this->option('from'));
        }

        $total = $query->count();
        $this->bar = $this->output->createProgressBar($total);

        $this->bar->start();

        if ($primaryKey) {
            // Use chunkById if table has a primary key
            $this->migrateWithPrimaryKey($source, $destination, $table, $primaryKey, $chunkSize);
        } else {
            // Use regular chunk if no primary key
            $this->migrateWithoutPrimaryKey($source, $destination, $table, chunkSize: -1);
        }

        // Update sequence if it's a PostgreSQL destination and table has an integer id
        if ($this->shouldUpdateSequence($destination, $table)) {
            $this->newLine()->info("Updating sequence for {$table}");
            DB::connection($destination)->statement("SELECT setval('{$table}_id_seq', (SELECT MAX(id) FROM {$table}))");
        }

        $this->bar->finish();
    }

    protected function getPrimaryKey(string $connection, string $table): ?string
    {
        $schema = DB::connection($connection)->getSchemaBuilder();
        $columns = $schema->getColumnListing($table);

        // Check if 'id' exists as it's Laravel's default primary key
        if (in_array('id', $columns)) {
            return 'id';
        }

        // For MySQL/PostgreSQL, we can query the information schema
        $driver = DB::connection($connection)->getDriverName();

        if ($driver === 'mysql') {
            $result = DB::connection($connection)
                ->select("SHOW KEYS FROM {$table} WHERE Key_name = 'PRIMARY'");

            return $result[0]->Column_name ?? null;
        }

        if ($driver === 'pgsql') {
            $result = DB::connection($connection)->select("
                SELECT a.attname
                FROM pg_index i
                JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
                WHERE i.indrelid = '{$table}'::regclass
                AND i.indisprimary
            ");

            return $result[0]->attname ?? null;
        }

        return null;
    }

    protected function migrateWithPrimaryKey(string $source, string $destination, string $table, string $primaryKey, int $chunkSize): void
    {
        DB::connection($source)
            ->table($table)
            ->when($this->option('from'), fn ($query) => $query->where($primaryKey, '>', $this->option('from')))
            ->orderBy($primaryKey)
            ->chunk($chunkSize, function ($sourceRows) use ($table, $source, $destination, $primaryKey) {
                $sourceIds = Arr::pluck($sourceRows, $primaryKey);
                $destinationRows = DB::connection($destination)
                    ->table($table)
                    ->whereIn($primaryKey, $sourceIds)
                    ->get()
                    ->pluck($primaryKey)
                    ->toArray();

                $missingIds = array_diff($sourceIds, $destinationRows);

                if (count($missingIds)) {
                    $missing = DB::connection($source)
                        ->table($table)
                        ->whereIn($primaryKey, $missingIds)
                        ->get();

                    DB::connection($destination)
                        ->table($table)
                        ->insert($missing->map(fn ($row) => (array) $row)->toArray());
                }

                $this->bar->advance(count($sourceRows));
            });
    }

    protected function migrateWithoutPrimaryKey(string $source, string $destination, string $table, int $chunkSize): void
    {
        // Get first column to use as ordering, because chunk needs to be ordered
        $firstColumn = DB::connection($source)
            ->getSchemaBuilder()
            ->getColumnListing($table)[0];

        DB::connection($source)
            ->table($table)
            ->orderBy($firstColumn)
            ->chunk($chunkSize, function ($sourceRows) use ($table, $destination) {
                DB::connection($destination)
                    ->table($table)
                    ->insert($sourceRows->map(fn ($row) => (array) $row)->toArray());

                $this->bar->advance(count($sourceRows));
            });
    }

    protected function shouldUpdateSequence(string $connection, string $table): bool
    {
        $schema = DB::connection($connection)->getSchemaBuilder();

        return DB::connection($connection)->getDriverName() === 'pgsql'
            && $schema->hasColumn($table, 'id')
            && Str::of($schema->getColumnType($table, 'id'))->contains('int');
    }
}