How to work with Database and Models in Laravel ?

We had this big Laravel project, in which one we had to maintain a complex Database Schema, shared with different Laravel projects, and we tried our best to make easy !

So firstly ! Laravel… so PHP… so MySQL ! it’s simple, it works, and it’s clearly robust. 70% of web is running using this.

So how can we handle a complex MySQL schema ?

MySQL Workbench

MySQL Workbench is a MySQL database management and administration software created in 2004. Via an intuitive graphical interface, it allows, among other things, to create, modify or delete tables, user accounts, and carried out all the operations inherent in the management of a database. To do this, it must be connected to a MySQL server.

To resume, it’s free, very intuitive, and let’s say powerful. You can use it on Windows or Mac.

The aim here is to centralize your schema, you will have a lot of modifications during your project, so you want to keep a graphic updated version to share with your colleagues.

This tools allows you to maintain this graphic schema, you have to edit this schema firstly, then you can use Laravel and migrations.
You can even put colors ! It’s amazing !

Let’s me show you an exemple of a simple User table :

In this table, I use the ID as a Bigint (default on Laravel migrations), and I have created all timestamps fields (created_at, updated_at, deleted_at). This will create SoftDelete, and allows timestamps for the Model. Simple no ?

Migrations, how ?!

Then, what you want from your schema ? it’s clearly a migration file ! or let’s a lot of migration files. Why ? Because you will need it for your CI (continuous integration).

You want to be able to easily create and fill Database in a big project, you will have several envs, Docker, CI, you need to be able to create a database and the tables with only 1 command.

I spent a lot of time to find a good way… I found a tool to create Yaml Doctrine 2 schemas from my Mysql Workbench tool, and I tried to adapt a plugin I found for Laravel to import those Doctrine 2 schemas, but fairly, it was a bad idea.

And then I found this : https://github.com/beckenrode/mysql-workbench-export-laravel-5-migrations

A simple Python script to install in Workbench to have the possibility to export directly Laravel 5 migrations files. (see more informations about Laravel migrations here)

Yes, it was so perfect ! I couldn’t believe it, but we can ! I took a look to the script, and it handles Soft Deletes, Laravel timestamps, foreign Keys … Once you have installed it, you can from Workbench just use :

It’s written Laravel 5, but it works for Laravel 7 as well.

So let’s export the migrations files to the folder : /laravelproject/database/migrations. And that’s it ? Yes !

Now you have in your project all your migration files, ready to used to create your MySQL Database.

And then ?

So, you have your migrations, let’s use them simply :

php artisan migrate

This will create your MySQL schema on the database you have configurated for your Laravel project.

Ok, so now, I have a Laravel project, a complete Database, what’s next ? We need to create our Models to use them !

Again, I will not try to create something which already exists, on Symfony, you have Doctrine, and Doctrine has a lot of functionalities including reverse-engineering, on Laravel, you have this amazing package : https://github.com/krlove/eloquent-model-generator

Using this package, you can generate a Model Class using :

php artisan krlove:generate:model User --table-name=user

From the table « user », I want to create the Model User. It’s simple. And very nice, because your Model class will contains all comments to easily use it in your favorite IDE.

Ok, something I don’t like it’s the default Laravel architecture, let’s use a folder Models to store our Models. For this :

php artisan krlove:generate:model User--table-name=user --output-path=models --namespace=App\Models

It will create a Models folders in your app folder, and use the correct namespace.

Ok, but I have dozen of tables, do I need to do it for all ? No, let’s create a simple command to do it for us:

php artisan make:command DatabaseToModels

And in the generated file :

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class DatabaseToModels extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'database:generate:models';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Generate all models from database';

    /**
     * Create a new command instance.
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $tables = DB::select('SHOW TABLES');

        DB::getDoctrineSchemaManager()->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');

        $databaseName = config('database.connections.'.config('database.default').'.database');
        $tables = collect($tables);
        $tables = $tables->pluck('Tables_in_'. $databaseName);

        foreach($tables as $table) {
            if ($table === 'migrations') {
                continue;
            }
            $className = Str::camel(Str::singular($table));
            $className = ucfirst($className);

            $this->info('Genarating model '. $className .' for table '. $table);

            Artisan::call('krlove:generate:model', [
                'class-name' => $className,
                '--table-name' => $table,
                '--output-path' => '/models',
                '--namespace' => 'App\\Models',
            ]);
        }
    }
}

So now, you can use this command :

php artisan databse:generate:models

And all your models will be created. And believe me, you want them updated to easily develop your controllers.

Laravel Models, do I need to know something ?

Yes, for me, the aim of all of this is to automate all this process. You have a table to edit ? Edit it on Workbench, update the migrations, migrate and generate your Model.

But sometimes, you want to add some code in your Models. For example, to define a specific attribute, add a Scope ….

For me the best way is to create a new Model Class and extends your Model. For example, I want to use my User Model as an Authentificable one with JWT Token, you can create a new folder « override » in your models folder, and use :

<?php

namespace App\Models\Override;

use Illuminate\Contracts\Auth\Authenticatable as ContractAuthenticatable;
use Models\User as UserMain;
use Tymon\JWTAuth\Contracts\JWTSubject;
use Illuminate\Notifications\Notifiable;
use Illuminate\Auth\Authenticatable;
use Illuminate\Auth\MustVerifyEmail;
use Illuminate\Auth\Passwords\CanResetPassword;
use Illuminate\Foundation\Auth\Access\Authorizable;

class User extends UserMain implements JWTSubject, ContractAuthenticatable
{
    use Notifiable, Authenticatable, Authorizable, CanResetPassword, MustVerifyEmail;

    /**
     * Get the identifier that will be stored in the subject claim of the JWT.
     *
     * @return mixed
     */
    public function getJWTIdentifier()
    {
        return $this->getKey();
    }

    /**
     * Return a key value array, containing any custom claims to be added to the JWT.
     *
     * @return array
     */
    public function getJWTCustomClaims()
    {
        return [];
    }
}

In this way, if I have to add some attributes to my model, I can generate my new migration, and generate the main model without loosing any modification.
The package krlove/eloquent-model-generator replaces the content of the Model file.

Then you juste have to use this overrided User Model in your logic.

Laissez un commentaire