Does dropColumn() deletes virtualAs column in sqlite?

I have an issue with PHPUnit/PEST test running with sqlite.

First I have the following migration, which creates and end_date column with the ->virtualAs() method:

public function up(): void
    {
        $isSqlite = \Illuminate\Support\Facades\DB::getDriverName() === 'sqlite';

        Schema::create('project_assignments', function (Blueprint $table) use ($isSqlite) {
            $table->uuid('id')->primary();

            $table->foreignUuid('user_id')->constrained()->cascadeOnDelete();
            $table->foreignUuid('project_id')->constrained()->cascadeOnDelete();

            $table->unsignedInteger('duration_in_days');
            $table->date('start_date');
            $isSqlite ?
                $table->date('end_date')->virtualAs("DATE(start_date, '+' || (duration_in_days-1) || ' days')") :
                $table->date('end_date')->virtualAs('DATE_ADD(start_date, INTERVAL duration_in_days - 1 DAY)');

            $table->text('note')->nullable();

            $table->timestamps();
        });
    }

Then I have a second migration, which drops the user_id column:

public function up(): void
    {
        $isSqlite = \Illuminate\Support\Facades\DB::getDriverName() === 'sqlite';
        
        Schema::table('project_assignments', function (Blueprint $table) use ($isSqlite) {
            if (! $isSqlite) {
                $table->dropForeign(['user_id']);
            }
            $table->dropColumn('user_id');
        });
    }

This results into end_date being always null/empty/not present when trying to access it in the code run by the tests.

Only if I run the following at the end of the migration, the end_date column is accessible again:

if($isSqlite){
            Schema::table('project_assignments', function (Blueprint $table) {
                $table->date('end_date')->virtualAs("DATE(start_date, '+' || (duration_in_days-1) || ' days')") ;
            });
        }

So my question is: Why does the dropColumn() method interfere with the virtualAs end_date column?

"laravel/framework": "10.34.2" "pestphp/pest": "2.26.0"

LaryAI

2 years ago

Best Answer

Level 58

SQLite has a limitation when it comes to altering tables. When you use the dropColumn() method, SQLite doesn't actually drop the column in the same way other databases like MySQL do. Instead, SQLite creates a new table without the dropped column and copies the data over. This process does not include copying over the virtual/generated columns as they are not actual data stored in the table but rather computed on the fly.

This is why when you drop a column in SQLite, your virtual column end_date seems to disappear. It's not that dropColumn() specifically targets virtual columns, but rather that the entire table is recreated without the virtual column definitions.

To work around this, you need to redefine the virtual column after dropping a column in SQLite, as you've discovered. Here's a more complete example of how you might handle this in your migration:

public function up(): void
{
    $isSqlite = \Illuminate\Support\Facades\DB::getDriverName() === 'sqlite';
    
    Schema::table('project_assignments', function (Blueprint $table) use ($isSqlite) {
        if (! $isSqlite) {
            $table->dropForeign(['user_id']);
        }
        $table->dropColumn('user_id');

        // SQLite requires redefining the virtual column after altering the table
        if ($isSqlite) {
            $table->date('end_date')->virtualAs("DATE(start_date, '+' || (duration_in_days-1) || ' days')");
        }
    });
}

This ensures that after the user_id column is dropped, the end_date virtual column is redefined for SQLite databases, maintaining the expected structure of your table.

Remember to always test your migrations thoroughly, especially when dealing with different database engines, as their behavior can vary significantly.

1 like

Marven

2 years ago

Level 17

Wow this is an awesome answer and makes a lot of sense, thanks :)

But the suggested migration by Lary does not work, as the end_date column still "exists" within the Schema::table method in which we drop the user_id column.

Therefore I had to call another Schema::table() method redefining the end_date.

Please or to participate in this conversation.