Laravel: Hotfix for SQLite Drop Foreign Exception

By convention, you can not use ALTER TABLE sql statement to drop a foreign key in sqlite. To do that, you will have to rename the table, create a new table without the foreign key, and then copy the data into the new table.

via GIPHY

This behaviour has been baked in to Laravel in the form of an exception since Laravelv5.7. Unfortunately, this removes the possibility to simply ignore foreign keys using the same migrations across different drivers. This has been a real bottleneck for me because I mostly use in-memory sqlite database for my tests and real world databases schema definitions are messy with lots of changes and droppings through out the lifetime of your project.

If you are like me, you are in luck because I will be sharing with you a solution I found that does not involve you updating any migration.

via GIPHY

Note:
This example is for when you are using in-memory sqlite for tests!

Inside the TestCase class, create a function that calls the Connection class resolverFor method. It takes the driver's name as the first parameter and a callback as the second.

The callback will return a new class extending SQLiteConnection and override the getSchemaBuilder method:

public function hotfixSqlite()
{
    \Illuminate\Database\Connection::resolverFor('sqlite', 
        function ($connection, $database, $prefix, $config) {
        return new class($connection, $database, $prefix, $config) 
            extends SQLiteConnection {
            public function getSchemaBuilder()
            {
                if ($this->schemaGrammar === null) {
                    $this->useDefaultSchemaGrammar();
                }

                return new class($this) extends SQLiteBuilder {
                    protected function createBlueprint($table, Closure $callback = null)
                    {
                        return new class($table, $callback) extends Blueprint {
                            public function dropForeign($index)
                            {
                                return new Fluent();
                            }
                        };
                    }
                };
            }
        };
    });
}

The final step is to call the newly created function in the TestCase's constructor class. Your final TestCase class should look like the following:

abstract class TestCase extends BaseTestCase
{
    public function __construct(?string $name = null, array $data = [], string $dataName = '')
    {
        $this->hotfixSqlite();
        parent::__construct($name, $data, $dataName);
    }

    /**
     *
     */
    public function hotfixSqlite()
    {
        \Illuminate\Database\Connection::resolverFor('sqlite', 
            function ($connection, $database, $prefix, $config) {
            return new class($connection, $database, $prefix, $config) 
                extends SQLiteConnection {
                public function getSchemaBuilder()
                {
                    if ($this->schemaGrammar === null) {
                        $this->useDefaultSchemaGrammar();
                    }

                    return new class($this) extends SQLiteBuilder {
                        protected function createBlueprint($table, Closure $callback = null)
                        {
                            return new class($table, $callback) extends Blueprint {
                                public function dropForeign($index)
                                {
                                    return new Fluent();
                                }
                            };
                        }
                    };
                }
            };
        });
    }
}

Important:
For PHPUnit 9, it is important that you call the hotfix
method before the parent constructor in the constructor method.

via GIPHY

This post appeared here first.

Blog

Laravel: Hotfix for SQLite Drop Foreign Exception

Faruk Nasir

Join Our Newsletter. No Spam, Only the good stuff.

Related