Framework: migrate:fresh should also drop all views

[cat5inthecradle]

• Laravel Version: 5.5
• PHP Version: 7.1
• Database Driver & Version: mysql 5.7

Description:

We are writing a reporting app, and we have a model that is backed by a database View.

php artisan migrate:fresh doesn't drop views, so your 'up' will break.

I understand that our backing of a model with a view is not standard (and not documented, as far as I can tell), but I think this improvement to 'migrate:fresh' is still worthwhile.

Steps To Reproduce:

Make a migration such as:

public function up() {
    DB::statement('CREATE VIEW my_view AS SELECT name FROM users');
}
public function down() {
    DB::statement('DROP VIEW my_view');
}

Then make a model with the view as it's table:

protected $table = 'my_view';

Now, run php artisan migrate:fresh

Or, go about your business of testing that model, and use the testing trait Illuminate\Foundation\Testing\RefreshDatabase

When that trait tries to run migrate:fresh then you'll get the following error:

[Illuminate\Database\QueryException]
SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'my_view' already exists

Workaround

Write your migration like this, with 'CREATE OR REPLACE':

DB::statement('CREATE OR REPLACE VIEW my_view AS SELECT name FROM users');

[Dylan-DPC]

I don't think is "standard" enough to be part of core. You can define a down method as you did and run php artisan migrate:rollback. Or implement some logic in and put it in a package if you end up using it frequently

[antonkomarev]

@Dylan-DPC yes, but what bad will happen if migrate:fresh will drop all the database table views before migration process will start?

[devcircus]

This was mentioned when discussing the original pr, but was never implemented. Someone said it was out-of-scope. Maybe put together a PR that handles this for all database drivers that laravel supports and see if it gets pulled in.

[Dylan-DPC]

@a-komarev views are something very few people use. Making migration-fresh slower to check if views exist, etc isn't something that is really needed

[cat5inthecradle]

@Dylan-DPC looks like migrate:fresh was written the way it was so that it would be faster than rolling back. Adding in a drop view would still be a whole lot faster than that.

The benefit to be gained by having the framework drop views is admittedly small, but I also think the cost is small. Hard for me to say without any testing to compare though.

Other options for me include extending FreshCommand and overriding migrate:fresh, or maybe extending the RefreshDatabases trait.

[themsaid]

Maybe put together a PR that handles this for all database drivers that laravel supports and see if it gets pulled in.

Yes please feel free to propose a PR, or use the https://github.com/laravel/internals repo.

[vesper8]

I use views in one project and I am facing this problem now... would love to see migrate:fresh take care of views too

[nowak-ninja]

+1

[amylashley]

+1

[helenaEngstrom]

+1

[EddieB0711]

+1

[taha-moghaddam]

+1

[naneri]

+1

[CyrilMazur]

+1

[ghost]

+1

[dyp2000]

+1

[Nemesis19]

+1

[functioneelwit]

+1

[kenng]

in case someone stumbled onto this thread, in laravel 5.6, you can now do
php artisan migrate:fresh --drop-views

[papoteur-mga]

in case someone stumbled onto this thread, in laravel 5.6, you can now do
php artisan migrate:fresh --drop-views

Thanks for this info.
Is there also means to specify to dropviews inside a test method ?
the recommended way is to add:
use RefreshDatabase;
in the class extending TestCase.
And is there a method to drop also events?

[colemvn]

in case someone stumbled onto this thread, in laravel 5.6, you can now do
php artisan migrate:fresh --drop-views

Thanks for this info.
Is there also means to specify to dropviews inside a test method ?
the recommended way is to add:
use RefreshDatabase;
in the class extending TestCase.
And is there a method to drop also events?

I'm currently meeting this problem. Did you find a fix?

[monaye]

@colemvn I am using version 6.x so not sure when this was added but you can add protected $dropViews = true; property in your test TestCase.