Reading Time: 6 minutes

1,998 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 ConnectionManager 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.

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.