change default db in cakephp3

Question

In a controller I want to change the default database so i can access the new db (db2) from anywhere in the website. The db2 database has the same models but just different data. My code just accesses the other database but doesnt set the new default database to db2 which can be accessed anywhere in the website. I didnt get the answer from the below posts.

This is my controller :

$connection = ConnectionManager::get('db2'); // 'db2' where my second database is configured 
$results = $connection->execute('SELECT * FROM tutors')->fetchAll('assoc');
//this works but doesnt set the default database to db2 everywhere

This is my app.php :

'Datasources' => [
    'default' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => 'localhost',

        //'port' => 'non_standard_port_number',
        'username' => 'root',
        'password' => '',
        'database' => 'aptutori_test',
        'encoding' => 'utf8',
        'timezone' => '+11:00',
        'flags' => [],
        'cacheMetadata' => true,
        'log' => false,

        'quoteIdentifiers' => false,

        'url' => env('DATABASE_URL', null),
    ],

    'db2' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => 'localhost',

        //'port' => 'non_standard_port_number',
        'username' => 'root',
        'password' => '',
        'database' => 'aptutori_testbak',
        'encoding' => 'utf8',
        'timezone' => '+11:00',
        'flags' => [],
        'cacheMetadata' => true,
        'log' => false,

        'quoteIdentifiers' => false,

        'url' => env('DATABASE_URL', null),
    ],

Dynamically change database connection in cakephp 3

http://mark-story.com/posts/view/using-cakephp-and-a-horizontally-sharded-database

score 3 · Accepted Answer · answered Jun 08 '17 at 07:07

3

Use ConnectionManager::alias():

http://api.cakephp.org/3.0/class-Cake.Datasource.ConnectionManager.html#_alias

Fore example this will make all tables that require the default connection to use db2:

ConnectionManager::alias('db2', 'default');

answered Jun 08 '17 at 07:07

Alex Stallen

2,223
15
17

I need this to work for every controller so I need to set this globally . My issue is that the database (code above)only changes for the controllers functions but I have about 30 controllers. – jagguy Jun 09 '17 at 00:57
how do i set this change globally across all controllers/models – jagguy Jun 09 '17 at 03:20
Tthe AppController class is the parent class to all of your application’s controllers – Alex Stallen Jun 09 '17 at 07:03
how do i set a global variable in a controller which then gets set in the appcontroller. – jagguy Jun 11 '17 at 13:37
set it in the beforeFilter of your appController and then every controller will use it. – Alex Stallen Jun 12 '17 at 07:05
Hi, I dont get what to do because to have to get user interaction to manually set a database , so that involves sending the appcontroller a variable from a controller to then set the database required. . – jagguy Jun 12 '17 at 11:14
save it somewhere, maybe in the db itself, a file, a cookie, web sql. somewhere and then use it in the appController – Alex Stallen Jun 12 '17 at 12:40
I dont disagree with what your saying I just dont get the code to go backward from a regular controller to the appcontroller and setting a global variable for a multi user application. I really cant find an example either about doing this . I dont want to save in a db or file – jagguy Jun 12 '17 at 12:50
where do you wanna save then? – Alex Stallen Jun 12 '17 at 13:14
I want to not save it in the appcontroller. as the appcontroller just reads the desired db to load – jagguy Jun 12 '17 at 23:39

score 0 · Answer 2 · answered Aug 16 '19 at 09:14

You could do this application wide in a Middleware in > cake 3.3, opposed to using a DispatcherFilter, like described in http://mark-story.com/posts/view/using-cakephp-and-a-horizontally-sharded-database.

<?php

namespace App\Middleware;

use Cake\Datasource\ConnectionManager;

class TenantShardMiddleware
{

    public function __invoke($request, $response, $next)
    {

        $tenant = $request->getHeader('MY-tenant');
        ConnectionManager::alias($tenant[0], 'default');
        $response = $next($request, $response);
        return $response;
    }
}

In my example above I'm using a special Request Header to switch databases.

change default db in cakephp3

2 Answers2