Set up dynamic database connection on Multi tenant application

The thing is, you already connected and used mysql connection, so changing the config doesn't do anything, unless you purge that connection. A short example:

// Use mysql
[1] > DB::setDefaultConnection('mysql');
// NULL

// Check the prefix
[2] > DB::getTablePrefix();
// ''
[3] > $first = DB::connection('mysql');
// object(Illuminate\Database\MySqlConnection)(
// 
// )

// Change the prefix
[4] > Config::set('database.connections.mysql.prefix', 'some_prefix');
// NULL
[5] > $second = DB::connection('mysql');
// object(Illuminate\Database\MySqlConnection)(
// 
// )

// Check the prefix again - no change..
[6] > DB::getTablePrefix();
// ''
// Because the connection is just the same object
[7] > $first===$second;
// true

// So let's disconnect and destroy it
[8] > DB::purge('mysql');
// NULL
[9] > $third = DB::connection('mysql');
// object(Illuminate\Database\MySqlConnection)(
// 
// )

// Now check the prefix - it's good
[10] > DB::getTablePrefix();
// 'some_prefix'

// And the connections are different
[11] > $first===$third;
// false

Btw, you can wait a little bit for my multi-tenancy package that I'm working on ;)

Hi,

This might be too late, but it could help someone else.

I believe this is what op is looking for:

DB::reconnect('mysql');

I would do this differently though, because it would get messy trying to figure out which database you are connected to, the main DB or the active tenant DB and you would have to switch connections the whole time depending on which db you need to access.

This is how I would go about it.

You will have two connections set up in your database config.

One for the main database that stores connections to the different tenant databases (this will be the default connection). The second connection will be a tenant connection with blank config values.

'main' => array(
    'driver'   => 'mysql',
    'host'     => 'hostname',
    'database' => 'database',
    'username' => 'username',
    'password' => 'password',
    'prefix'   => '',
),
            
'tenant' => array(
    'driver'   => 'mysql',
    'host'     => '',
    'database' => '',
    'username' => '',
    'password' => '',
    'prefix'   => '',
),

Then to switch the DB. This will be in your filters or middleware.

$tenant = Tenant::whereSubDomain($subdomain)->first();  

Config::set('database.connections.tenant.host', $tenant ->host);
Config::set('database.connections.tenant.username', $tenant ->username);
Config::set('database.connections.tenant.password', $tenant ->password);
Config::set('database.connections.tenant.database', $tenant ->database);

//If you want to use query builder without having to specify the connection
Config::set('database.default', 'tenant');
DB::reconnect('tenant');

dd(\DB::connection('tenant'));

Then if you are using eloquent you can specify the connections on models for tenant databases as 'tenant' and 'main' for the models using the main DB.

//On models dealing with the main DB
protected $connection = 'main';

//On models dealing with tenant DBs
protected $connection = 'tenant';

The code and tips that FiggSaurus provided are extremely valuable and have helped me in implementing our multi tenant system.

But for some reason in Laravel 5.3 the DB::reconnect() does not seem to work right for me (and probably not for me only, as is noted here: https://github.com/laravel/framework/issues/8864 ).

So if anybody was facing the same problem, try using

        \DB::purge('tenant');
        \DB::reconnect('tenant');

You can tell you have this issue when you see this at the end of your database connection dump:

  ... Loads of stuff there ↑
  #database: ""
  #doctrineConnection: null
  #tablePrefix: ""
  #config: array:12 [
    "driver" => "mysql"
    "host" => "localhost"
    "port" => "3306"
    "database" => ""
    "username" => ""
    "password" => ""
    "charset" => "utf8"
    "collation" => "utf8_general_ci"
    "prefix" => ""
    "strict" => true
    "engine" => null
    "name" => "tenant"
  ]

Wondering if setting the config globably and then running DB::reconnect('tenant'); has any problems with multiple users accessing the platform. Has anyone had this trouble or this is fine?