thibaud-dauce/migrations

Migrations' helpers to create SQL views, relationships' columns and more!

3.8.0 2023-02-28 07:16 UTC

Requires

• php: ^7.0|^8.0
• illuminate/console: ^5.8|^6.0|^7.0|^8.0|^9.0|^10.0
• illuminate/database: ^5.8|^6.0|^7.0|^8.0|^9.0|^10.0
• illuminate/support: ^5.8|^6.0|^7.0|^8.0|^9.0|^10.0

Requires (Dev)

• orchestra/testbench: ^8.0
• phpunit/phpunit: ^9.0

MIT

• Thibaud Dauce

README

This package solves multiples problems:

• Defining the relationships in our migrations is tedious and error prone with a lot of copy-pasting
• A lot of going back and forth between our models and our migrations to check the naming of the columns
• How to create and refresh views with our migrations and Eloquent QueryBuilder?

Installation

composer require thibaud-dauce/migrations

Usage

Creating tables

Instead of using Illuminate\Database\Migrations\Migration, use ThibaudDauce\Migrations\Migration and define a protected $model attribute in your migration.

<?php

use ThibaudDauce\Migrations\Migration;
use App\Comment;

class CreateCommentsTable extends Migration
{
    protected $model = Comment::class;
}

Next, you need to define your table schema in your model in the schema method. If you prefer to keep your table schema in the migration, you can instead define the schema in your migration.

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Schema\Blueprint;

class Comment extends Model
{
    public function schema(Blueprint $table)
    {
        $table->increments('id');
        $table->string('author');
        $table->text('body');
        $table->timestamps();
    }
}

Creating views

It can be useful to create SQL views for your application. Did you know you can create an Eloquent Model for your views and use it as any other Eloquent Model? Check the very good blog post of @Brendt: Eloquent MySQL views.

To create a view, the process is similar. Instead of using Illuminate\Database\Migrations\Migration, use ThibaudDauce\Migrations\ViewMigration and define a protected $model attribute in your migration.

<?php

use ThibaudDauce\Migrations\ViewMigration;
use App\Search;

class CreateCommentsTable extends ViewMigration
{
    protected $model = Search::class;
}

Next, you need to define your view query in your model in the schema method. If you prefer to keep your view query in the migration, you can instead define the schema in your migration. The schema method must return a Illuminate\Database\Query\Builder.

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Search extends Model
{
    public function schema()
    {
        $titles = DB::table('posts')->select('title as term', 'id as searchable_id', Post::class . ' as searchable_type');
        $bodies = DB::table('posts')->select('body as term', 'id as searchable_id', Post::class . ' as searchable_type');
        $comments = DB::table('comments')->select('body as term', 'id as searchable_id', Comment::class . ' as searchable_type');

        return $titles->union($bodies)->union($comments);
    }
}

Refreshing views

php artisan migrate:refresh-view database/migrations/2014_10_15_000000_create_searches_view.php

Using the relation helper

This package define a macro in the Blueprint class to quickly create relationship's columns.

In your schema definition, use $table->relation('post') to generate the column and the foreign key. For the following BelongsTo relationship:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Schema\Blueprint;

class Comment extends Model
{
    public function schema(Blueprint $table)
    {
        $table->increments('id');
        $table->string('author');
        $table->text('body');
        $table->relation('post');
        $table->timestamps();
    }
    
    public function post()
    {
        return $this->belongsTo(Post::class);
    }
}

These two columns are generated:

$table->integer('post_id')->unsigned();
$table->foreign('post_id')->references('id')->on('posts');

Before and after hooks

You can define before_schema_migration and after_schema_migration inside your model. These methods are called before and after the table creation.

For example, you can use before_schema_migration to create some PostgreSQL enums types before creating the table.

Known issues

• PDO forbid you to pass variables when creating a view. You should use the raw version of the methods to bypass this restriction (like whereRaw instead of where).
• CREATE VIEW works with PostgreSQL and SQLite. I didn't test it with other database engine. We should use something better to work with other databases if needed.

Migrate to 2.0.0

• In your migrations, rename protected $class to protected $model
• In your view migrations, extends ThibaudDauce\Migrations\ViewMigrations instead of ThibaudDauce\Migrations\Migration
• In your view models, rename the view method to schema.

TODO

I'm open to pull requests :-)

• Add ManyToMany relationships
• Add the auto-increment ID automatically
• Add better support for other SQL dialect when creating a view