SQLite foreign key migration helper

[KitKety]

📌 Problem:
When working with more than one database in Laravel projects, we may encounter some migration issues when commands are not supported by all databases.

📝 Context:
Specifically here, I'm working with MySQL and SQLite (test database) and I always have problems when my migration wants to remove some FK, because it is not supported by SQLite databases.

The solution to drop a FK from an SQLite table is:

1. Rename your table to something like "old_table";
2. Create a new table with the original name, with all columns except the FK you want to remove;
3. Transfer all data from "old_table" to the new table;
4. Remove "old_table";

It works but is also a labor do that with table that has a lot of columns.

🎯 Solution
So, this is the solution I came up with: I wrote a helper class that performs all the above steps on its own, requiring only the table name and the foreign key column name.
It can be improved (obviously), but I'm posting it here to show everyone how I did it and discuss whether it could be a future improvement to the Blueprint $table->dropForeign() method.

💻 Code

<?php

namespace App\Helpers;

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

class SqLiteForeignKeysHelper
{
    /**
     * This method should delete fks from a SQLite table
     * 
     * @param string $tableName
     * @param array $fksColumnName
     * @return void
     * @throws Exception
     */
    public function dropForeignKeys(string $tableName, array $fksColumnName): void
    {
        $originalTableName = 'old_' . $tableName;
        DB::statement('PRAGMA foreign_keys = OFF;');

        Schema::rename($tableName, $originalTableName);
        $this->cloneTableWithoutFkColumns($originalTableName, $tableName, $fksColumnName);
        $this->populateNewTableWithOldData($originalTableName, $tableName);
        Schema::dropIfExists($originalTableName);

        DB::statement('PRAGMA foreign_keys = ON;');
    }

    /**
     * @param string $originalTableName
     * @param string $newTableName
     * @param array $fksToIgnore
     * @return void
     * @throws Exception
     */
    private function cloneTableWithoutFkColumns(string $originalTableName, string $newTableName, array $fksToIgnore): void
    {
        $pk = $tihs->extractPk($originalTableName);
        $filteredFks = $this->extractFks($originalTableName, $fksToIgnore);
        $filteredColumns = $this->extractColumns($originalTableName);

        Schema::create($newTableName, function (Blueprint $table) use ($pk, $filteredColumns, $filteredFks) {
            if ($pk) {
                $table->increments($pk->name);
            }
            foreach ($filteredColumns as $column) {
                if ($column->type === 'varchar') {
                    $column->type = 'string';
                }
                if ($column->dflt_value) {
                    $column->dflt_value = str_replace("'", '', $column->dflt_value);
                }
                $table
                    ->addColumn($column->type, $column->name)
                    ->nullable(! $column->notnull)
                    ->default($column->dflt_value);
            }
            foreach ($filteredFks as $fk) {
                $table->foreignId($fk->from)
                    ->nullable(! $fk->notnull)
                    ->default($fk->dflt_value)
                    ->references($fk->to)
                    ->on($fk->table)
                    ->onUpdate($fk->on_update)
                    ->onDelete($fk->on_delete);
            }
        });
    }

    /**
     * @param string $originalTable
     * @param string $newTable
     * @return void
     */
    private function populateNewTableWithOldData(string $originalTable, string $newTable): void
    {
        $columnsList = collect(Schema::getColumnListing($newTable))->implode(', ');
        DB::statement(sprintf(
            'INSERT INTO %s (%s) SELECT %s FROM %s;',
            $newTable,
            $columnsList,
            $columnsList,
            $originalTable
        ));
    }
    
    /**
     * This method get the fks from table, ignoring the fks that we want to delete
     * 
     * @param string $originalTableName
     * @param array $fksToIgnore
     */
    private function extractFks(string $originalTableName, array $fksToIgnore) {
        $fks = DB::select("PRAGMA foreign_key_list('" . $originalTableName . "');");
        $originalColumns = DB::select("PRAGMA table_info('" . $originalTableName . "');");
        
        $filteredFks = array_filter(array_map(function ($fk) use ($fksToIgnore, $originalColumns) {
            if (in_array($fk->from, $fksToIgnore)) {
                return null;
            }

            $column = current(array_filter($originalColumns, function ($column) use ($fk) {
                return $column->name === $fk->from;
            }));

            $fk->notnull = $column->notnull;
            $fk->dflt_value = $column->dflt_value;

            return $fk;
        }, $fks));

        if (count($filteredFks) === count($fks)) {
            throw new Exception('No FK found to delete');
        }

        return $filteredFks;
    }

    /**
     * This method get the pk from the table
     * 
     * @param string $originalTableName
     */
    private function extractPk(string $originalTableName) {
        $originalColumns = DB::select("PRAGMA table_info('" . $originalTableName . "');");
        
        $pks = array_filter($originalColumns, function ($pk) {
            return $pk->pk === 1;
        });

        if(count($pks) > 1) {
            throw new Exception('More than 1 primary key found on table ' . $originalTableName);
        }

        return current($pks);
    }

    /**
     * This method get the non pk and fk columns
     * 
     * @param string $originalTableName
     */
    private function extractColumns(string $originalTableName) {
        $fks = DB::select("PRAGMA foreign_key_list('" . $originalTableName . "');");
        $originalColumns = DB::select("PRAGMA table_info('" . $originalTableName . "');");
        
        $fkNames = array_map(function ($fk) {
            return $fk->from;
        }, $fks);

        return array_filter($originalColumns, function ($column) use ($fkNames) {
            return ! in_array($column->name, $fkNames) && $column->pk === 0;
        });
    }

}

[shaedrich]

Yeah, I always hated it when Laravel is like "well, it works as expected in one database engine and in the others … well, you'll see"

After all, Laravel is a framework that should provide convenience and not either swallow error messages and act as if nothing happened until much later strange errors emerge nor act like a petulant child, just throwing an error message as if the task wasn't resolvable by a little workaround.

[hafezdivandari]

Laravel 11+ supports dropping foreign keys on SQLite, you may check #51373

What am I missing here?

[KitKety]

I missed this feature update, thanks for responding