Extending Illuminate Eloquent Models to query multiple databases

We have a customer application based on Slim PHP framework where we use Eloquent Models from the Illuminate Database package from the makers of Laravel. During the migration of the application built with CodeIgniter (version 3), we required a solution for a class where synchronizing is done between 2 administrations, that is also 2 different databases (MySQL).

How to deal with multiple databases

As said, we are using Eloquent models for tables and relations in our database(s). I know that some of you might think that it comes at the cost of performance, but for me Eloquent models are simply a great way to centralize functionality where it in my opinion belongs.

The challenge

In our sample we have 2 databases (on the same host, but that doesn't matter) and some information is synchronized between tables in these databases.

We solved this by making student synchronisation possible between institutes. The administration could easily synchronise students between institutes (so called "corona" imports). In that way students could be registered for seminars in either institute.

Extending models

Since both institutes share the same structure of tables it was quite easy to solve. Only a few tables were involved in the synchronisation process. We created "partner" models which were extended classes with the "parent" institute, or base institute.

With just a few lines of code the problem could be solved.

Why extending?

We defined extended the base model classes where something like "UsersModel" was extended to "PartnerUsersModel". In that way it is always clear in our code which model we are querying or updating.

Base Model

<?php
namespace Db\Models;

use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\HasOne;

class UsersModel extends Model
{
    protected $table = 'users';

    protected $fillable = [
        'id',
        'hash'
        // ... all other fields ...
    ];

    /**
     * @function user_functions
     * @return HasMany
     */
    public function user_functions()
    {
        return $this->HasMany(UsersFunctionModel::class, 'hash_user', 'hash')
            ->where('active', 1);
    }

    // any other relations and functions .....

}

Partner Model

<?php
namespace Db\Models\Partner;

use Db\Models\UsersModel;

class PartnerUsersModel extends UsersModel
{
    protected $table = 'users';
    protected $connection = 'partner';
}

Database settings to make this work

You define your database for Eloquent in the following way. Use your own database and login values.

return [
    'db' => [
        'hosts' => [
            'default' => [
                'driver' => 'mysql',
                'host' => 'localhost',
                'charset' => 'utf8mb4',
                'collation' => 'utf8mb4_unicode_ci',
                'prefix' => '',
                'database' => 'database_one',
                'username' => 'somedbuser',
                'password' => 'somepassword'
            ],
            'partner' => [
                'driver' => 'mysql',
                'host' => 'localhost',
                'charset' => 'utf8mb4',
                'collation' => 'utf8mb4_unicode_ci',
                'prefix' => '',
                'database' => 'database_two',
                'username' => 'someotheruser',
                'password' => 'anotherpassword'
            ]
        ]
    ]
];

How to configure Illuminate Capsule (database manager)

We defined so called factory classes which are dependency injected as a singleton in our application. You might have to adapt following code to your own situation.

<?php
namespace App\Factory;

use Illuminate\Container\Container;
use Illuminate\Database\Capsule\Manager;
use Illuminate\Events\Dispatcher;
use Psr\Container\ContainerInterface;
use Noodlehaus\Config as Config;

class DatabaseManagerFactory
{
    public $capsule;

    public function __construct(ContainerInterface $container)
    {
        $settings = $container->get(Config::class);

        $dbSettingsDefault = $settings->get('db.hosts.default');
        $dbSettingsPartner = $settings->get('db.hosts.partner');

        $manager = new Manager;

        /* institut (default) */
        $manager->addConnection($dbSettingsDefault, 'default');

        /* partner institut */
        $manager->addConnection($dbSettingsPartner, 'partner');

        $manager->getConnection()->disableQueryLog();

        $manager->setEventDispatcher(new Dispatcher(new Container()));

        $manager->setAsGlobal();
        $manager->bootEloquent();

        $this->capsule = $manager;
    }
}

Some practice

There are no actual database relations between the databases. But we would like to update some information from the "partner" into our base UsersModel. Here is an example on how we did it. Maybe not the best solution performance wise, but it is effective though.

For this we use a trait.

...
class UsersModel extends BaseModel
{
    use PartnerInfoAttributesTrait;
...    

The trait to get partner information

<?php
namespace Db\Traits;

use Db\Models\Partner\PartnerUsersModel;
use Illuminate\Database\Eloquent\ModelNotFoundException as NotFound;

trait PartnerInfoAttributesTrait
{
    public function getPartnerInfoAttribute()
    {
        $hash = $this->hash;

        $partnerUsersModel = null;
        $inSync = true;
        $found = true;

        try {
            $partnerUsersModel = PartnerUsersModel::where('hash', $this->partnerHash)->where('active', 1)->firstOrFail();
        } catch (NotFound $e) {
            $found = false;
        }

        $ourSyncDate = $this->syncdate;
        $partnerSyncDate = $found ? $partnerUsersModel->getAttribute('syncdate') : null;

        if (
            (empty($ourSyncDate) && !empty($partnerSyncDate)) ||
            (!empty($ourSyncDate) && empty($partnerSyncDate)) ||
            ($ourSyncDate !== $partnerSyncDate)
        ) {
            $inSync = false;
        }

        return [
            'hash' => $hash,
            'import' => $found,
            'isInSync' => $inSync,
            'O' => [
                'created' => $this->getAttribute('pcdate') ?? null,
                'updated' => $this->getAttribute('pmdate') ?? null,
                'syncdate' => $this->getAttribute('syncdate') ?? null
            ],
            'P' => [
                'hash' => $found ? $partnerUsersModel->getAttribute('hash') : null,
                'created' => $found ? $partnerUsersModel->getAttribute('pcdate') : null,
                'updated' => $found ? $partnerUsersModel->getAttribute('pmdate') : null,
                'syncdate' => $found ? $partnerUsersModel->getAttribute('syncdate') : null
            ]
        ];
    }
}

Dynamically assigning the connection

In this article you see another approach to use multiple databases in the same application. It is using dynamic setting the connection, rather than extending models.

Sneak preview from this article

<?php

class SomeController extends BaseController {

    public function someMethod()
    {
        $someModel = new SomeModel;
        $someModel->setConnection('mysql2');
        $something = $someModel->find(1);
        return $something;
    }
}

Conclusion

I hope you got some inspiration for your own development. I know that this article gives just a start to apply it to your own situation.