CODEWITHSUNDEEP
phpmysqldatabasecakephp
chrisShick
chrisShick

Reputation: 1096

Cakephp 3 Changing Database Connections on the fly? Is it efficient?

Is there a way to change Database Connections on the fly? Is it efficient? We have clients with multiple databases. They each contain the same structures, but we keep them separate to use the same classes in our application and security purposes. Is there a better way to do this than switching database connections? Perhaps have them all in one database and use prefixes for each table. Would I be able to change database prefixes on the fly?

Edit: To supply more information about my situation. Our client has multiple stores that he needs to connect in one application. At the moment, we are showing each store in a different application. Because at the time he only had one, and now he has three. Each store has its own database. Does that help?

Upvotes: 2

Views: 1260

Answers (1)

trey-jones
trey-jones

Reputation: 3437

I also had this question and I am documenting my solutions here.

I want to give 2 answers to the question. The first will be an actual answer to the question (kind of), "How do I change databases on the fly?", and I don't see why it would be any less efficient than the alternative.

The second option is a more flexible way of handling multiple connections, and (in my opinion) allows you to better define your data types.

For both options you need to define all of your datasources in config/app.php.

You can pass a connection option to TableRegistry::get

// controller code - you would need to implement Stores::getConnectionName
$storeConnection = $this->Stores->getConnectionName($data);
$stores = TableRegistry::get('Stores', ['connection' => $storeConnection]);

This will also require that the table names be the same in all databases.

You can create a parent table class with the all of the logic to handle the store data, and extend it for each store.

// App\Model\Table\BaseStoreTable
use Cake\ORM\Table;
class BaseStoreTable extends Table {

}

// App\Model\Table\StoreOneTable
use App\Model\Table\BaseStoreTable;
class StoreOneTable extends BaseStoreTable {
    public static function defaultConnectionName() {
        return 'store_one';
    }
}

// App\Model\Table\StoreTwoTable
use App\Model\Table\BaseStoreTable;
class StoreTwoTable extends BaseStoreTable {
    public static function defaultConnectionName() {
        return 'store_two';
    }
}

You still need to implement a way of determining which class to use, and there are multiple ways of handling that. This gives you a little bit more flexibility if there are other differences in the datasources.

Upvotes: 2

Related Questions