Multiple, Dynamic Database Connections

1

I have a microservices app using PHP/DI and CakePHP datasource connection which gives me Cake\Database\Connection and Cake\Database\Query

    Connection::class => static function (ContainerInterface $container) {
        return new Connection($container->get('settings')['config1']);
    },

    PDO::class => static function (ContainerInterface $container) {
        $db = $container->get(Connection::class);
        $driver = $db->getDriver();
        $driver->connect();

        return $driver->getConnection();
    }

I have extended PDO in container:

PdoLm::class => static function (ContainerInterface $container) {
        $settings = $container->get('settings')['config2'];
        [...]
        return new PdoLm($dsn, $username, $password, $flags);
    }

My preference is to use the CakePHP DB query classes (and possibly connection classes) for the second DB connection too, rather than having to fall back on PDO style queries.

I am thinking that creating a middleware may achieve this functionality, but multitenancy somehow feels like a bit of a design ‘hack’ in the sense that both connections will be injected across the entire app no matter what the route? Ideally I would like to build both connections dynamically, depending on the route, rather than having an autowired ‘default’ connection and then injecting a dynamic tenant.

Does this sound sensible?

2

You have multiple options, but the cleanest options for dependecy injection would be to create a custom “Connection” class for the second connection. This allows you to configure this connection within the DI container as well.

Example:

namespace App\Database;

use Cake\Database\Connection;

final class SecondConnection extends Connection 
{
    // must be empty
}

Then add a second DI container definition:

use App\Database\SecondConnection;
// ...

SecondConnection::class => function (ContainerInterface $container) {
    return new SecondConnection($container->get('settings')['db2']);
},

Note that I used another key (db2) to read the settings for the second database connection.

Now, you are able to declare the “SecondConnection” class within the class constructor when needed.

Edit: If you need a dynamic connection, you could add a ConnectionFactory that can be configured within a middleware and used within the DI container definition to build the SecondConnection object.

4

If you extend your PdoLm class from \PDO it should work. Example.
Can you post the error message?

5

Thanks. I have it working by using a custom query factory for each connection. eg.

for the second connection I am now using:

final class ExampleRepository
{
    private SecondConnectionQueryFactory $queryFactory;
    public function __construct(SecondQueryFactory $queryFactory)
    {
        $this->queryFactory = $queryFactory;
    }
}

The error was something like, “CakePHP connection class expected an instance of PDO, PDO2 was given in [ExampleRepository…]”. To get the error, this was in the container:

   SecondConnection::class => static function (ContainerInterface $container) {
        return new SecondConnection($container->get('settings')['db2']);
    },

    PDO2::class => static function (ContainerInterface $container) {
        $db = $container->get(SecondConnection::class);
        $driver = $db->getDriver();
        $driver->connect();

        return $driver->getConnection();
    }

and the query factory was this:

<?php
namespace App\Factory;

use App\Database\PrimaryConnection;
use Cake\Database\Query;

final class DefaultQueryFactory
{
    private Connection $connection;
}

[...]

The error disappeared when I used a second query factory like this:

<?php
namespace App\Factory;

use App\Database\SecondConnection;
use Cake\Database\Query;

final class SecondQueryFactory
{
    private SecondConnection $connection;
    /**
     * The constructor.
     *
     * @param SecondConnection $connection The database connection
     */
    public function __construct(SecondConnection $connection)
    {
        $this->connection = $connection;
    }
[...]
}
1 Like
6

This is great solution :+1: