Change primary key in Laravel migration with SQLite

Question

I have the following migration:

Schema::create('items', function(Blueprint $table) {
    $table->uuid('id')->primary();
    // more columns ...
});

Now, we want to add an additional auto-increment column:

Schema::table('items', function(Blueprint $table) {
    $table->dropPrimary('id');
    $table->rename('id', 'SystemId')->change();
    $table->id();
});

Problem: SQLite doesn't allow changing the primary key
Solution: It's recommended to delete the table and create it with the changed schema

Of course, that works in theory but it is anything but DRY to copy the code from our first migration to our second. So my question is: Is there another way to achieve this?

Answer 1

So, I finally came up with a solution that is generic enough to be reusable. Would be great to be included into Laravel, but a package is probably more likely.

use Doctrine\DBAL\Schema\Table;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

class ExtendedSQLiteAlterTableMigration extends Migration
{
    public function extendedAlterTable(string $tableName, callable $callback)
    {
        /** @var \Doctrine\DBAL\Schema\AbstractSchemaManager */
        $schemaManager = DB::connection()->getDoctrineSchemaManager();
        /** @var \Doctrine\DBAL\Schema\Table */
        $table = $this->getTempTable($schemaManager, $tableName);
        call_user_func($callback, $table);
        $tempName = $table->getName();
        //$schemaManager->renameTable($tableName, $tempName);
        $schemaManager->createTable($table);
        $schemaManager->dropTable($tableName);
        $schemaManager->renameTable($tempName, $tableName);
    }

    private function getTempTable($schemaManager, string $name)
    {        
        $columns     = $schemaManager->listTableColumns($name);
        $foreignKeys = [];

        //if ($this->_platform->supportsForeignKeyConstraints()) {
            $foreignKeys = $schemaManager->listTableForeignKeys($name);
        //}

        $indexes = $schemaManager->listTableIndexes($name);

        return new Table("temp_$name", $columns, $indexes, [], $foreignKeys);
    }
}

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AddAutoIncrementPrimaryKeyToTestTable extends ExtendedSQLiteAlterTableMigration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $this->extendedAlterTable('test', function(Table $table) {
            $table->dropPrimaryKey();
            $table->addColumn('id', 'bigint', [
                'autoincrement' => true,
            ]);
            $table->setPrimaryKey([ 'id' ]);
        });
    }
}

This follows the instructions on the SQLite website