Cannot alter table with a dependant view when using SQLite

[AJenbo]

Laravel Version

11.20.0

PHP Version

8.3.6

Database Driver & Version

SQLite 3.45.1

Description

In Laravel 10 it was possible to alter tables even if they had dependent views as long as the doctrine/dbal package was installed. After upgrading to Laravel 11 all migrations that alter tables after a view has been defined that uses the table will fail with the following error.

Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 error in view users_view: no such table: main.users (Connection: testing, SQL: alter table "__temp__users" rename to "users")

Steps To Reproduce

Create a view:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        DB::statement($this->dropView());
        DB::statement($this->createView());
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        DB::statement($this->dropView());
    }

    private function createView(): string
    {
        return <<<'SQL'
            CREATE VIEW `users_view` AS
            SELECT `id`, `name`
            FROM `users`
            SQL;
    }

    private function dropView(): string
    {
        return <<<'SQL'
                DROP VIEW IF EXISTS `users_view`;
            SQL;
    }
}

Alter the dependent table:

<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('users', function (Blueprint $table) {
            $table->string('profile_picture')->nullable()->after('name');
        });
    }

    public function down(): void
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn('profile_picture');
        });
    }
}

[hafezdivandari]

No altering table is occurring on your provided example (steps to reproduce). However, the issue is valid when modifying a column on a table that has associated views.

Solutions

Solution 1:

You may drop the views associated with the table before altering, and recreate them after that:

$view = collect(Schema::getviews())->firstWhere('name', 'users_view');

DB::statement('drop view '.$view['name']);

Schema::table('users', function (Blueprint $table) {
    $table->string('profile_picture')->nullable()->change();
});

DB::statement($view['definition']);

Solution 2:

You may enable and disable legacy alter table:

DB::statement('pragma legacy_alter_table = 1');

Schema::table('users', function (Blueprint $table) {
    $table->string('profile_picture')->nullable()->change();
});

DB::statement('pragma legacy_alter_table = 0');

Why not fixing this?

There is no easy way to list the views associated with a table (the table being altered).

Why it works on Doctrine DBAL?

Laravel follows the steps on SQLite docs to make arbitrary changes to the schema design of a table. These steps require listing views associated with the table, not easily possible as I said. On the other hand, Laravel doesn't officially supports DB views!

Doctrine DBAL uses a different approach / steps to make arbitrary changes to the schema design of a table, that doesn't follow SQLite docs instructions!

[driesvints]

Guess this sort of ties into #51318 which is another incompatibility after we moved to our own way of doing things. This is the first time in over half a year that v11 was released that this has come up though.

@AJenbo does any of the two workarounds from @hafezdivandari work for you (thanks for that Hafez). I'd prefer not to start dwelling too deeply in framework workarounds if there's user land solutions.

[AJenbo]

Thank you for the replies, for context we have been in the process of moving away from views sine about the time laravel 9 was released (as part of cleaning up general legacy in our code base), the fact views works at all has been a life saver here.

Yeah we already went with a work around similar to suggestion 1, we did this by including the migration that creates the view and call it's down/up methods inside the migrations that would otherwise fail, a bit ugly to have them intertwined but it works. As a shortcut we also simply deleting migrations for views that where dropped or altered by later migrations (and not needed by others), the downside of this is that we don't have a fully valid state if only run some migrations, but we likely won't have to roll back to older states and could just get the old laravel 10 based migrations from git if we really needed to.

To summerize I'm fine if nothing is done to support this, my only suggestion is to make a note about this difference somewhere (maybe this issue is enough) since it was a bit hard to figure out what was causing the migrations to fail compared to previously even after looking in the docs 🙃

P.s. Sorry the example code didn't fully work.

[AJenbo]

P.s.s. The new implementation is generally better then the old one and let's us remove several workarounds in code that actually matter to nice work in general.

[renatofrota]

@AJenbo , I did not test but I am pretty sure you can use https://github.com/calebporzio/sushi with

public function getRows(): array
{
    // either ORM:
    return YourModel::all()->toArray();

    // or Query Builder
    return DB::('your_model')->get()->toArray();
    
    // adapting to obtain the records you desire, of course...
}

instead creating a regular view at SQLite level.