staudenmeir/laravel-migration-views

Laravel database migrations with SQL views

v1.10 2024-10-21 18:26 UTC

Requires

• php: ^8.2
• illuminate/database: ^11.0

Requires (Dev)

• orchestra/testbench: ^9.0
• phpstan/phpstan: ^1.10
• phpunit/phpunit: ^11.0

MIT

• Jonas Staudenmeir

README

This Laravel extension adds support for SQL views in database migrations.

Supports Laravel 5.5+.

Installation

composer require staudenmeir/laravel-migration-views:"^1.0"

Use this command if you are in PowerShell on Windows (e.g. in VS Code):

composer require staudenmeir/laravel-migration-views:"^^^^1.0"

Versions

Usage

• Creating Views
• Renaming Views
• Dropping Views
• Checking For View Existence
• Listing View Columns
• Materialized Views

Creating Views

Use createView() to create a view and provide a query builder instance or an SQL string:

use Staudenmeir\LaravelMigrationViews\Facades\Schema;

$query = DB::table('users')->where('active', true);

Schema::createView('active_users', $query);

You can provide the view's columns as the third argument:

use Staudenmeir\LaravelMigrationViews\Facades\Schema;

$query = 'select id from users where active = 1';

Schema::createView('active_users', $query, ['key']);

Use createOrReplaceView() to create a view or replace the existing one:

use Staudenmeir\LaravelMigrationViews\Facades\Schema;

$query = DB::table('users')->where('active', true); 

Schema::createOrReplaceView('active_users', $query);

View Processing Algorithm

On MySQL and MariaDB, you can specify the view processing algorithm:

use Staudenmeir\LaravelMigrationViews\Facades\Schema;

$query = DB::table('users')->where('active', true);

Schema::createView('active_users', $query, algorithm: 'TEMPTABLE');

Renaming Views

Use renameView() to rename a view:

use Staudenmeir\LaravelMigrationViews\Facades\Schema;

Schema::renameView('active_users', 'users_active');

Dropping Views

Use dropView() or dropViewIfExists() to drop a view:

use Staudenmeir\LaravelMigrationViews\Facades\Schema;

Schema::dropView('active_users');

Schema::dropViewIfExists('active_users');

If you are using php artisan migrate:fresh, you can drop all views with --drop-views (Laravel 5.6.26+).

Checking For View Existence

Use hasView() to check whether a view exists:

use Staudenmeir\LaravelMigrationViews\Facades\Schema;

if (Schema::hasView('active_users')) {
    //
}

Listing View Columns

Use getViewColumnListing() to get the column listing for a view:

use Staudenmeir\LaravelMigrationViews\Facades\Schema;

$columns = Schema::getViewColumnListing('active_users');

Materialized Views

On PostgreSQL, you can create a materialized view with createMaterializedView():

use Staudenmeir\LaravelMigrationViews\Facades\Schema;

$query = DB::table('users')->where('active', true);

Schema::createMaterializedView('active_users', $query);

Use refreshMaterializedView() to refresh a materialized view:

Schema::refreshMaterializedView('active_users');

Contributing

Please see CONTRIBUTING and CODE OF CONDUCT for details.