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

Try calling `Artisan::call('config:cache');` before changing the config. I think your configuration is already cached and that prevents it from changing — Kliment, Sep 02 '16 at 10:40
@Kliment i tried calling `config:cache` but get this error = ```[Symfony\Component\Debug\Exception\FatalErrorException] Declaration of Symfony\Component\Console\Input\ArrayInput::hasParameterOption() must be compatible with Symfony\Component\Console\Input\InputInterface::hasParameterOption($values, $onlyParams = false)``` — Parampal Pooni, Sep 05 '16 at 04:20
Can you be more specific on the second connection. Do you want to replace the connection for all your queries when you use a set of commands or do you want to use 2 connections ans be able to switch in your queries ? — Sylwit, Sep 05 '16 at 14:14
@ParampalPooni It works fine in mine project. Maybe you have error in the config somewhere? or some other bug. This error seems to be problem in the command. Try calling `config:cache` from console to see if the same error is thrown. — Kliment, Sep 05 '16 at 14:39
If your logic requires to be so tightly to the implementation of the database you probably should think about an implementation more "loose", that is actually a "smell" warning that you have things too coupled, so much that avoid easy testing. — Nestor Mata Cuthbert, Sep 05 '16 at 16:13
@Sylwit I want to replace the connection for all subsequent queries and eloquent calls.. — Parampal Pooni, Sep 05 '16 at 20:04
@Kliment I can run it from command line fine; it's just when I run it within my codeception class it fails — Parampal Pooni, Sep 05 '16 at 20:06
So can't you use an envvar for specifying your MySQL credentials and host ? You can easily change thé hist you need without modifying your code. — Sylwit, Sep 05 '16 at 20:06
@Sylwit how do I do that ? So it works for all eloquent and queries — Parampal Pooni, Sep 05 '16 at 20:09
If it is for your test you will start another application. You can configure your host with `getenv('MYSQL_HOST')` for example. Then you can set this envvar in the context of your application. You will have thé same config file but the host will be different. You should read a bit more on that. Maybe it's what you need. If you heard about docker it's even easier — Sylwit, Sep 05 '16 at 20:15
@Sylwit hmm well I have different test classes; each test class tests different commands; each command will access a different database (hope that made sense) how do I set the env for each test class? — Parampal Pooni, Sep 05 '16 at 20:23
Then in your beforeClass or beforeEach method of your tests use the `setDefaultConnection()` and it should do what you want. — Sylwit, Sep 05 '16 at 20:33
@Sylwit I can't find any documentation on that function; can you plz send me a link — Parampal Pooni, Sep 05 '16 at 20:37
You should check this link http://stackoverflow.com/questions/26950029/how-to-overwrite-laravels-database-connection `Db::reconnect` should do the trick — Sylwit, Sep 05 '16 at 22:12

score 10 · Answer 1 · answered Sep 02 '16 at 02:38

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.

Putting `DB::connection('mysql2')` before all my queries and specifying a connection in my models isn't as efficient as just changing the connection once (ie. `config(['database.connections.mysql' => $new_connection]);`). I just don't understand why transactions are not working... — Parampal Pooni, Sep 05 '16 at 06:48
Are you modifying database structure in your transaction, because it can be a reason why it doesn't work. A structural query will auto commit your transaction. Can you post your query please ? Also you said you want a new connection for only some commands, so you will juste have to specify on theses queries — Sylwit, Sep 05 '16 at 14:10

score 3 · Answer 2 · answered Sep 06 '16 at 13:38

3

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

I tested this and it gets the job done

answered Sep 06 '16 at 13:38

Kliment

2,250
3
18
32

score 2 · Answer 3 · answered Sep 02 '16 at 07:47

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

@ParampalPooni yes I do understand... not sure if this will work, but can you define all your required connections in the `config/database.php` file. And then change the default connection to the required connection using `setDefaultConnection()` method in controller — jaysingkar, Sep 05 '16 at 07:23

score 2 · Answer 4 · answered Sep 03 '16 at 20:10

2

You could pass a string with the name of the connection as argument to the DB::connection() facade.

https://laravel.com/docs/5.3/database#using-multiple-database-connections

answered Sep 03 '16 at 20:10

Indyz

321
3
7

This should be the accepted answer, a clean and quick solution. – Marcelo Agimóvel Jan 02 '18 at 01:17

score 1 · Answer 5 · answered Sep 06 '16 at 11:38

if you are using phpunit have a look at phpunit.xml

in the bottom you should see the following

    <php>
    <env name="APP_ENV" value="testing"/>
    <env name="CACHE_DRIVER" value="array"/>
    <env name="SESSION_DRIVER" value="array"/>
    <env name="QUEUE_DRIVER" value="sync"/>
    </php>

you can assign the env variable for your testing database to be used rather than the orginal production database.

so create two connections, assign the database connection name in the .env and reference the testing one in the phpunit.xml Good Luck.

Switch db connection dynamically

5 Answers5