Is there another way to “setConnection” on an Eloq

Question

I am currently handling a "multi db on the fly swap connections" sort of project.

So what I end up doing is the following:

$connectionName = uniqid();
\Config::set('database.connections.' . $connectionName, [/** db options **/]);
\Artisan::call('migrate', ['--database' => $connectionName]);

or

$connectionName = uniqid();           
\Config::set('database.connections.' . $connectionName,[/** db options **/]);

$user = new User();
$user->setConnection($connectionName);
$user->first_name = 'Daisy';
$user->last_name = 'Demo';
$user->is_not_being_ignored_by_santa_this_year = 0;
$user->email = //and so so on
$user->save();

For the Artisan call I sort of understand why Laravel needs to refer to a connection in a string, saved in a config array.

However on the Eloquent Model itself I find it somehow cumbersome to have to write my DB connection into a config array. So it can be picked up by the "Singleton approach" \Config::get().. in the Model.

Is there something more elegant, where I can inject a configuration directly without having to write it into some super global ?

Or am I missing something ?

Answer 1

You would probably be better off creating a configuration array for each connection then you could switch between connections pretty easily by specifying which connection to use.

If you need to use multiple connections on the same model you can use the on method:

so it would be something like User::on('dbconnection2')->find(1)

If you just want to use different connections for different models, you can set the protected $connection property on the model:

class User extends Model
{
    protected $connection = 'dbconnection2';
}

Hope that helps.

Answer 2

You could create a factory for your models and pass it the connection on bootstrapping your app:

<?php

class ModelFactory
{
    private $db;

    public function __construct($dbConnection)
    {
        $this->db = $dbConnection;
    }

    public function createNewModel($class)
    {
        $object = new $class();
        $object->setConnection($this->db);
        return $object;
    }
}

Then in your code:

$user = $factory->createModel(User::class);

Something like this! Good luck! :-)

Answer 3

I build a multitenant laravel app and was surprised that there is no out-of-the-box way of doing that.

I have one app available via different subdomains and the subdomain should be the key to different configs, like the database connection.

You can easily adapt that to whatever criteria you need instead of the subdomain.

Via dynamically Config::set()

So my first attempt was to always use the "default" connection and to create a middleware that dynamically calls Config::set("database.connection.default.host", "1.3.5.7"); and so on for all the other settings.

But there are some downsides. For example it was pretty slow because I read all the values from the database and later on from redis. But the much bigger problem was to set the connection to the redis cache for example, because the redis connection is already established before the middleware it called to override the config setting.

Via own config files

So my second and current approach is to make it all via config files. So I created the following file structure:

• config/_myapp.php
• config/subdomain1/_myapp.php

Note: The underscore is important because the files are read in alphabetic order and our new file has to be read at first to use it in other config files.

The goal is to use config('_myapp.DB_HOST') to retrieve the DB_HOST value from config/[subdomain]/_myapp.php and use that as value in config/database.php. So config/_myapp.php only returns the content of _myapp.php for the specific subdomain.

in config/_myapp.php:

// get the current subdomain from $_SERVER['HTTP_HOST']
$subdomain = \App\Helper::getSubDomain();

define('SUBDOMAIN', $subdomain);

if(!empty($subdomain) && file_exists($file = __DIR__."/$subdomain/".basename(__FILE__)))
    return require($file);

return [

];

and in config/_myapp.php:

return [
    ...
    'DB_HOST' => '1.3.5.7',
    ...
];

Use this for example in config/database.php or whereever you need domain specific config:

...
   'host' => config('_myapp.DB_HOST'),
...

Please ask if something doesn't work, took me quite some time to figure that stuff out.

Answer 4

Its all depends on how you handling your multiple connections.I have worked on similar requirement project.

• we have master slave/tenant database connection. with configuration https://gist.github.com/safoorsafdar/c6c623f9ec7b440f563d76995faf7aec#file-database-php
• tenant database/migration/seeds create on the fly with create new account in the system
• can soft delete database from the root control.
• on the user login, we check if its master, connect to master connection otherwise get tenant connection, store information in the session for web access. check Tenant Setup after login.
• Database connection related operation central class. View DatabaseConnection.
• center tenant session handler class to help with resolve tenant id from the session. View TenantContextSession
• we have separate models with abstract class for tenant and master to get set the model connection. Check
• You might also need to handle migration in your system, have look into this https://gist.github.com/safoorsafdar/cc9252a2f14301c3da942ca7bec7d66e

Tenant Model Abstract Class

<?php

namespace App\Models\Abstracts;

use App\Models\Abstracts\AbstractBaseModel;
use App\Models\Picklist\PicklistValue;

class TenantAbstractBaseModel extends AbstractBaseModel
{
    function __construct(array $attributes = array())
    {
        parent::__construct($attributes);
        if ( ! is_null(app('tenant.context')->getConnectionName())) {
            $this->setConnection(app('tenant.context')->getConnectionName());
        }

        //todo; should be dynamic
        if (is_null(app('tenant.context')->getConnectionName())
            && app()->runningInConsole()
        ) {
            //todo; need to resolve database connection through terminal and application.
            //dd(config('tenant.tenant_connection'));
            //$this->setConnection(config('tenant.tenant_connection'));
        }

    }
}

Tenant Setup after login

$connection = config('tenant.tenant_connection');
                //config()->set('database.default', config('tenant.tenant_connection'));
            app('tenant.context')->setConnectionName($connection);
            app('tenant.context')->setTenantId($company_id);

             //$database = config('database.connections.' . $connection . '.database') . $company_id;
             $company_system_name
                    = $this->auth->user()->company->system_name;
             config()->set('database.connections.'.$connection.'.database',
                    $company_system_name);
             //config()->set('database.connections.' . $connection . '.database', $database);
           config()->set('database.default', $connection);

DatabaseConnection

<?php

namespace App\Tenancy\Tenant;

use Config;
use DB;
use App\Tenancy\Exceptions\TenantDatabaseException;
use App\Tenancy\Models\Tenant;

/**
 * Class DatabaseConnection
 *
 * Helps with tenant database connections
 */
class DatabaseConnection
{
    /**
     * See the multi-tenant configuration file. Configuration set
     * to use separate databases.
     */
    const TENANT_MODE_SEPARATE_DATABASE = 'database';

    /**
     * See the multi-tenant configuration file. Configuration set
     * to use prefixed table in same database.
     */
    const TENANT_MODE_TABLE_PREFIX = 'prefix';
    /**
     * Current active global tenant connection.
     *
     * @var string
     */
    protected static $current;
    /**
     * @var string
     */
    public $name;
    /**
     * @var Tenant
     */
    protected $tenant;
    /**
     * @var \Illuminate\Database\Connection
     */
    protected $connection;

    public function __construct(Tenant $tenant)
    {
        $this->tenant = $tenant;

        $this->name = "tenant.{$this->tenant->hash_id}";

        $this->setup();
    }

    /**
     * Sets the tenant database connection.
     */
    public function setup()
    {
        Config::set("database.connections.{$this->name}", $this->config());
    }

    /**
     * Generic configuration for tenant.
     *
     * @return array
     */
    protected function config()
    {
        $clone             = Config::get(sprintf('database.connections.%s',
            static::tenantConnectionName()));
        $clone['database'] = $this->tenant->system_name;

        return $clone;
    }

    /**
     * Central getter for system connection name.
     *
     * @return string
     */
    public static function systemConnectionName()
    {
        return Config::get('tenant.master_connection', 'mysql');
    }

    /**
     * Checks whether current connection is set as global tenant connection.
     *
     * @return bool
     */
    public function isCurrent()
    {
        return $this->name === static::getCurrent();
    }

    /**
     * Loads the currently set global tenant connection name.
     *
     * @return string
     */
    public static function getCurrent()
    {
        return static::$current;
    }

    /**
     * Sets current global tenant connection.
     */
    public function setCurrent()
    {
        static::$current = $this->name;
        Config::set(sprintf('database.connections.%s',
            static::tenantConnectionName()), $this->config());

        DB::purge(static::tenantConnectionName());
    }

    /**
     * Central getter for tenant connection name.
     *
     * @return string
     */
    public static function tenantConnectionName()
    {
        return Config::get('tenant.tenant_connection', 'tenant_mysql');
    }

    /**
     * Loads connection for this database.
     *
     * @return \Illuminate\Database\Connection
     */
    public function get()
    {
        if (is_null($this->connection)) {
            $this->setup();
            $this->connection = DB::connection($this->name);
        }

        return $this->connection;
    }

    /**
     * @return bool
     */
    public function create()
    {
        $clone = $this->config();

        return DB::connection(static::systemConnectionName())
            ->transaction(function () use ($clone) {
                if ( ! DB::connection(static::systemConnectionName())
                    ->statement("create database if not exists `{$clone['database']}`")
                ) {
                    throw new TenantDatabaseException("Could not create database {$clone['database']}");
                }
                if ( ! DB::connection(static::systemConnectionName())
                    ->statement("grant all on `{$clone['database']}`.* to `{$clone['username']}`@'{$clone['host']}' identified by '{$clone['password']}'")
                ) {
                    throw new TenantDatabaseException("Could not create or grant privileges to user {$clone['username']} for {$clone['database']}");
                }

                return true;
            });
    }

    /**
     * @throws \Exception
     *
     * @return bool
     */
    public function delete()
    {
        $clone = $this->config();

        return DB::connection(static::systemConnectionName())
            ->transaction(function () use ($clone) {
                if ( ! DB::connection(static::systemConnectionName())
                    ->statement("revoke all on `{$clone['database']}`.* from `{$clone['username']}`@'{$clone['host']}'")
                ) {
                    throw new TenantDatabaseException("Could not revoke privileges to user {$clone['username']} for {$clone['database']}");
                }
                if ( ! DB::connection(static::systemConnectionName())
                    ->statement("drop database `{$clone['database']}`")
                ) {
                    throw new TenantDatabaseException("Could not drop database {$clone['database']}");
                }

                return true;
            });
    }
}

*TenantContextSession *

<?php

namespace App\Repositories\Tenant;

use App\Repositories\Tenant\TenantContextRepositoryContract;

/**
 * Description of TenantContextSession
 *
 * @author safoor
 */
class TenantContextSession implements TenantContextRepositoryContract
{
//    public function __construct() {
//        $this->clearTenantSession();
//    }

    /**
     * Sets the connection name for the actual context
     * this tenant
     * @param $name
     * @return mixed
     */
    public function setConnectionName($name)
    {
        if (session()->has('tenant_connection')) {
            session()->set('tenant_connection', '');
        }

        session()->put('tenant_connection', $name);
    }

    /**
     * Get the name of the current connection in context
     * @return mixed
     */
    public function getConnectionName()
    {
        return session()->get('tenant_connection');
    }

    /**
     * Sets the id value filter data in the current context
     * @param $id
     * @return mixed
     */
    public function setTenantId($id)
    {
        if (session()->has('tenant_id')) {
            session()->set('tenant_id', '');
        }
        session()->put('tenant_id', $id);
    }

    /**
     *
     * @return mixed
     */
    public function getTenantId()
    {
        return session()->get('tenant_id');
    }

    public function clearTenantSession()
    {
        session()->flush();
        session()->set('tenant_id', '');
        session()->set('tenant_connection', '');
    }
}

I hope this will help to work with your scenario, I have tried to mention all of aspect we had to covered, but if still there is something confusing and need explanation from my side please let me know.