Switch db connection dynamically

Question

For several console commands, I have the need to change databases so all my eloquent commands and queries run on the correct db (and server).

Ive seen a few solutions, the simplest seems to be changing the config like so:

$new_connection = [
        'driver'    => 'mysql',
        'host'      => '127.0.0.1',
        'database'  => 'test_db',
        'username'  => 'test',
        'password'  => 'test',
        'charset'   => 'utf8',
        'collation' => 'utf8_general_ci',
        'prefix'    => '',
        'strict'    => false
];

config(['database.connections.mysql' => $new_connection]);
DB::purge('mysql');

The only issue (that I have noticed) is when I attempt to do transactions, more specifically, when I do transactions inside my acceptance tests in Codeception - they simply don't work.

The commands I use are:

DB::connection()->beginTransaction(); // inside the _before function

and

DB::connection()->rollBack(); // inside the _after function

Answer 1

You have to create 2 distincts connections

http://fideloper.com/laravel-multiple-database-connections https://laravel.com/docs/5.1/database#accessing-connections

return array(
    'default' => 'mysql',

    'connections' => array(

        # Our primary database connection
        'mysql' => array(
          'driver'    => 'mysql',
          'host'      => '127.0.0.1',
          'database'  => 'test_db',
          'username'  => 'test',
          'password'  => 'test',
          'charset'   => 'utf8',
          'collation' => 'utf8_general_ci',
          'prefix'    => '',
          'strict'    => false
        ),

        # Our secondary database connection
        'mysql2' => array(
          'driver'    => 'mysql',
          'host'      => '127.0.0.1',
          'database'  => 'test_db_2',
          'username'  => 'test',
          'password'  => 'test',
          'charset'   => 'utf8',
          'collation' => 'utf8_general_ci',
          'prefix'    => '',
          'strict'    => false
        ),
    ),
);

Now when you want to query you have to pass the connection you need

$users = DB::connection('mysql2')->select(...);

As the default one is declared as mysql, you can omit it.

Answer 2

I was experiencing the similar issue. To use the transaction you would basically need to use @Sylwit 's approach.

Create the required database connections. Lets say mysql and mysql1.

Now in your controller get the connection to the required database as below:

$connection = DB::connection('mysql1'); // replace this to your required connection name

Now, for transaction use the retrieved connection.

$connection->beginTransaction(); // inside the _before function

And

$connection->rollBack(); // inside the _after function

OR

In your code, you can just add the connection name:

DB::connection('mysql1')->beginTransaction(); // inside the _before function

and

DB::connection('mysql1')->rollBack(); // inside the _after function

Answer 3

$config = config()->all();
$config['database']['connections']['mysql'] = $newConnection;
Artisan::call('config:clear');
config($config);

I tested this and it gets the job done