Reading Time: 6 minutes

3,904 Views

Inside this article we will see the concept of CakePHP 4 Connect Models with Multiple Databases. This Tutorial contains classified information about multiple databases connection to a CakePHP application.

In large application where we manage data of multiple modules then we need the concept of multiple database. It will give the flexibility to work with and manage each user request in databases.

In this we will use multiple databases with CakePHP models. You can use the same concept for all operations like Insert, update, delete, etc. In CakePHP database named as datasources so don’t be confuse about keywords.

Learn More –

Let’s get started.

CakePHP 4 Installation

To create a CakePHP project, run this command into your shell or terminal. Make sure composer should be installed in your system.

$ composer create-project --prefer-dist cakephp/app:~4.0 mycakephp

Above command will creates a project with the name called mycakephp.

Database Settings

By default in CakePHP, default & test connections are available. Here we will create another datasource as otherDb.

So, we will use two database connections – default & otherDb.

Step #1

Open app.php from /config folder. Search for Datasources into it. Add this database array into it (secondary database).

//...

'Datasources' => [

    'default' => [
        //...
    ],

    'test' => [
        //...
    ],

	'otherDb' => [
	   'className' => Connection::class,
	   'driver' => Mysql::class,
	   'persistent' => false,
	   'timezone' => 'UTC',
	   //'encoding' => 'utf8mb4',
	   'flags' => [],
	   'cacheMetadata' => true,
	   'quoteIdentifiers' => false,
	   'log' => false,
	   //'init' => ['SET GLOBAL innodb_stats_on_metadata = 0'],
	],
]

//...

otherDb details are just copied and pasted as test had. Nothing extra we have added. Keep application and understanding as well as that.

Step #2

We need to create connect secondary database (otherDb) connection to application.

Open app_local.php file from /config folder. Search for Datasources. We will add the otherDb connection string values into it.

//...

'Datasources' => [

    'default' => [
        'host' => 'localhost',
        'username' => 'admin',
        'password' => 'Admin@123',
        'database' => 'database2',
        'url' => env('DATABASE_URL', null),
    ],

	'otherDb' => [
        'host' => 'localhost',
        'username' => 'admin',
        'password' => 'Admin@123',
        'database' => 'database1',
        'url' => env('DATABASE_URL', null),
    ],
    
]

//...

Now, we are able to connect models with either primary database (default) or secondary database (otherDb).

Create Models

Suppose we have a students table inside database1 which is in primary datasource and products table inside database2 which is in secondary datasource.

$ bin/cake bake model Students --no-rules --no-validation

It will create two files StudentsTable.php inside /src/Model/Table & Student.php inside /src/Model/Entity folder respectively.

<?php
declare(strict_types=1);

namespace App\Model\Table;

use Cake\ORM\Table;

class StudentsTable extends Table
{
    /**
     * Initialize method
     *
     * @param array $config The configuration for the Table.
     * @return void
     */
    public function initialize(array $config): void
    {
        parent::initialize($config);

        $this->setTable('students');
        $this->setPrimaryKey('id');
    }
}

$ bin/cake bake model Products --no-rules --no-validation --connection otherDb

It will create two files ProductsTable.php inside /src/Model/Table & Product.php inside /src/Model/Entity folder respectively.

<?php
declare(strict_types=1);

namespace App\Model\Table;

use Cake\ORM\Table;

class ProductsTable extends Table
{
    public function initialize(array $config): void
    {
        parent::initialize($config);

        $this->setTable('products');
        $this->setPrimaryKey('id');
    }

    /**
     * Returns the database connection name to use by default.
     *
     * @return string
     */
    public static function defaultConnectionName(): string
    {
        return 'otherDb';
    }
}

Model Usage

Suppose we have SitesController.php inside /src/Controller folder. We will see here an example to list data from two databases.

Let’s add these codes –

<?php

declare(strict_types=1);

namespace App\Controller;

class SitesController extends AppController
{
    public function initialize(): void
    {
        parent::initialize();
        $this->loadModel("Students"); // Primary datasource
        $this->loadModel("Products"); // Secondary datasource
        $this->autoRender = false;
    }

    public function listProducts()
    {
        $products = $this->Products->find()->toList();

        echo "<pre>";
        print_r($products);
    }

    public function listStudents()
    {
        $students = $this->Students->find()->toList();

        echo "<pre>";
        print_r($students);
    }
}

Here, we did only data selection. You can use this for other database operations like Insert, Update, Delete, etc.

We hope this article helped you to learn about CakePHP 4 Connect Models with Multiple Databases in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.