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
– Kliment, Commented Sep 2, 2016 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
– Parampal Pooni, Commented Sep 5, 2016 at 4: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
– Sylwit, Commented Sep 5, 2016 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
– Kliment, Commented Sep 5, 2016 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
– Nestor Mata Cuthbert, Commented Sep 5, 2016 at 16:13

Sylwit · Accepted Answer · 2016-09-02 02:38:44Z

10

+25

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.

answered Sep 2, 2016 at 2:38

Sylwit

1,6071 gold badge13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Parampal Pooni Over a year ago

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...

Sylwit Over a year ago

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

Kliment · Accepted Answer · 2016-09-06 13:38:05Z

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 6, 2016 at 13:38

Kliment

2,2503 gold badges20 silver badges32 bronze badges

Comments

jaysingkar · Accepted Answer · 2016-09-02 07:47:36Z

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

answered Sep 2, 2016 at 7:47

jaysingkar

4,4351 gold badge22 silver badges27 bronze badges

1 Comment

jaysingkar Over a year ago

@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

Indyz · Accepted Answer · 2016-09-03 20:10:57Z

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 3, 2016 at 20:10

Indyz

3313 silver badges7 bronze badges

1 Comment

Marcelo Agimóvel Over a year ago

This should be the accepted answer, a clean and quick solution.

Sari Yono · Accepted Answer · 2016-09-06 11:38:35Z

1

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.

answered Sep 6, 2016 at 11:38

Sari Yono

5775 silver badges13 bronze badges

Comments

Lekia · Accepted Answer · 2024-07-24 20:40:34Z

Create another connection to which you want to connect in the config/database.php file. If it's the same connection you want but a different database update the database only as shown.

In your code, whether you're using Querybuiler or ORM, you can switch connections at run time with:

setDefaultConnection('the_desire_connection');

Note: this may not be the case for every version of Laravel but it works for "7.*"

'connections' => [

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

        'secondary_connection' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),

            // Only the database is updated here
            'database' => env('DB_DATABASE_STAGING', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],
    ],

Collectives™ on Stack Overflow

Switch db connection dynamically

6 Answers 6

2 Comments

Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related