Laravel Artisan Migrate Drop Column using Sqlite throws exception

Question

I'm trying to set up some unit tests in a legacy Laravel project. This project has a lot of migration scripts creating and updating the database. This migrations work fine on a MySQL database, however I don't want to persist a test database so I'm using the standard .env.testing configuration of a Sqlite database:

One of the migration files contains the following:

public function up()
{
    Schema::table('product_variants', function (Blueprint $table) {
        $table->dropColumn(['sku']);
    });
}

While this migration works in MySQL, when I try to run the migration in test mode (i.e. in Sqlite) I get the following message:

Reading the Laravel Docs, it's not supported to remove multiple columns from a Sqlite connection, so I converted the array param to a string (['sku'] into 'sku') but that did not change anything.

How do I get the migration to work in Sqlite?

Edit

Following the comment by BkiD (the column is indexed), I have attempted to identify what the index name of the column is using the following code:

$sm = Schema::getConnection()->getDoctrineSchemaManager();
$indexesFound = $sm->listTableIndexes('product_variants');
var_dump($indexesFound);die;

I was expecting an array of generated names, specifically I was expecting to see product_variants_sku_unique. What I actually got was an empty array: array(0){}

Edit2

Tried manually dropping the index anyway:

Schema::table('product_variants', function (Blueprint $table) {
    $table->dropUnique('product_variants_sku_unique');
});

Any other name throws a message that the index does not exist. Since this is not throwing an error, I assume the index exists, even if the previous code does not return it. So. I'm "dropping" the index then trying to drop the sku column, but still getting the same message.

Answer 1

Have you tried something like this?

Schema::table('products', function (Blueprint $table) {
    $table->dropColumn('name', 'description', 'price');
});

or this, seem's like not the best practice but hopefully works:

collect([
['old_a', 'new_a'],
['old_b', 'new_b'],
['old_c', 'new_c']])->map(function ($old_new) {
Schema::table('thetable', function (Blueprint $table) use ($old_new) {
   $table->renameColumn($old_new[0], $old_new[1]);
});

});

Answer 2

So from what I understand, you're using Laravel 9.x and running sqlite 3.37.2 and you definitely have the Doctrine/dbal package installed.

My first question is - is this the complete migration? If not, please post the entire thing here.

If yes - please check your other migrations to look for references to this particular column.

If you look at the error, the error is coming from Index::_addColumn() , which means you probably have - an index declared for usage in mysql.

The doctrine/dbal package cannot rename/handle/drop SQLite columns that have an index

So your solution could be to either -

Drop the index in the laravel migration (provided you don't need it for MySQL as well - probably the case since you're dropping the column)

OR

Use a condition like this (in the migration file where the index was first created - incase you want to be able to rename or continue using the index in mysql) -

if (config('DB_CONNECTION') === 'mysql') {
    $table->index('sku');
}

PS: The doctrine/dbal github repo has issues like these that have since been closed since the package has fixed this in newer versions - I'm guessing you probably aren't on the latest by default.

Answer 3

There is a warning in the laravel doc's that:

Dropping or modifying multiple columns within a single migration while using a SQLite database is not supported.

So, try to split your migrations.

public function up()
{
    Schema::table('product_variants', function (Blueprint $table) {
        $table->dropColumn('sku');
    });

Schema::table('product_variants', function (Blueprint $table) {
        $table->dropColumn('vku');
    });
}

I know that it is a bad idea, but this can be a possible solution for your current issue. Hope, it works for you.