Reading Time: 6 minutes

2,252 Views

Inside this tutorial we will see CakePHP 4 Multiple Database Using ConnectionManager. We will use the concept of ConnectionManager of CakePHP to create a database connection object. The created instance will perform all operations like insert, update, delete and select.

There are several ways to perform database operations – Using Query Builder, Using Model & Entity, ConnectionManager.

Here, we will see how to connect with multiple database and work using Connection Manager.

Suppose we have a table called products into primary database (default). Also we have a secondary database otherDb in which we have a table called students. We will see how to connect application with these databases and work with it. Article contains classified information about this concept.

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

Connection Manager Instance

ConnectionManager is a class in CakePHP. We use get() method to get the instance of database group. The <strong>ConnectionManager</strong> class acts as a registry to access database connections your application has. It provides a place that other objects can get references to existing connections.

use Cake\Datasource\ConnectionManager;

To create database instance using Connection Manager, we use like this –

Connection with default group of database.

$primary = ConnectionManager::get("default");

Connection with otherDb group of database.

$secondary = ConnectionManager::get("otherDb");

We will use the returned connection object to perform insert, update, delete and select operations.

Create Controller

To create controller we will use bake console command –

$ bin/cake bake controller Sites --no-actions

It will create a file SitesController.php inside /src/Controller folder.

<?php

declare(strict_types=1);

namespace App\Controller;

use Cake\Datasource\ConnectionManager;

class SitesController extends AppController
{
    private $primary;
    private $secondary;

    public function initialize(): void
    {
        parent::initialize();

        // Connection to default database group
        $this->primary = ConnectionManager::get("default");

        // Connection to other database group
        $this->secondary = ConnectionManager::get("otherDb");

        $this->autoRender = false;
    }

    // Select all rows using default connection manager instance
    public function selectAllProduct()
    {
        $products = $this->primary
            ->newQuery()
            ->select('*')
            ->from('products')
            ->execute()
            ->fetchAll('assoc');

        print_r($products);
    }

    // Select all rows using otherDb connection manager instance
    public function selectAllStudent()
    {
        $students = $this->secondary
            ->newQuery()
            ->select('*')
            ->from('students')
            ->execute()
            ->fetchAll('assoc');

        print_r($students);
    }
}

In the above code we can see we have connected with two database connections primary & secondary.

Right now we have performed only select operations. But you can use the same connection concept to perform all other database operations like – Insert, Update, Delete, Select using Conditional Statements.

Create Routes

Open routes.php from /config folder. Add these routes into it.

//...

// Product Route
$routes->connect(
    '/products',
    ['controller' => 'Sites', 'action' => 'selectAllProduct']
);

// Student Route
$routes->connect(
    '/students',
    ['controller' => 'Sites', 'action' => 'selectAllStudent']
);

//...

Application Testing

Open project and run this command to start development server.

$ bin/cake server

URL: http://localhost:8765/

All Products: http://localhost:8765/products
All Students: http://localhost:8765/students

We hope this article helped you to learn about CakePHP 4 Multiple Database Using ConnectionManager Tutorial in a very detailed way.

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.