php - configure multiple databases in zf2

Question

How can I configure (and use) multiple databases in Zend Framework 2? Currently I have this in my global.php:

return array(
    'db' => array(
        'driver'         => 'Pdo',
        'dsn'            => 'mysql:dbname=my_db;host=localhost',
        'driver_options' => array(
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES 'UTF8''
        ),
        'username' => 'user',
        'password' => '******',
    ),
    'service_manager' => array(
        'factories' => array(
            'ZendDbAdapterAdapter' => 'ZendDbAdapterAdapterServiceFactory',
        ),
    ),
);

But I do not see a way to add a second one.

Answer 1

If you look at the ZendDbAdapterAdapterServiceFactory, you'll see that your adapter configuration points to only one key 'db'. Which means that the Adapter that it builds will always use this (unique) configuration key.

I recommend you to create your own factory that would look like this :

namespace YourNamespace;

use ZendServiceManagerFactoryInterface;
use ZendServiceManagerServiceLocatorInterface;
use ZendDbAdapterAdapter;

class MyAdapterFactory implements FactoryInterface
{

  protected $configKey;

  public function __construct($key)
  {
      $this->configKey = $key;
  }

  public function createService(ServiceLocatorInterface $serviceLocator)
  {
      $config = $serviceLocator->get('Config');
      return new Adapter($config[$this->configKey]);
  }
}

In your main module (or any other one), add the following to the Module.php file to declare the adapters factories to the Zend Service Manager:

use YourNamespaceMyAdapterFactory;
use ZendModuleManagerFeatureServiceProviderInterface;

class Module implements ServiceProviderInterface{

//Previous code

public function getServiceConfig()
{
    return array(
        'factories' => array(
            'myadapter1'        => new MyAdapterFactory('dbconfigkey1'),
            'myadapter2'        => new MyAdapterFactory('dbconfigkey2'),
            ),
       );

}

//...

The global config should now look like:

return array(
'dbconfigkey1' => array(
    'driver'         => 'Pdo',
    'dsn'            => 'mysql:dbname=my_db;host=localhost',
    'driver_options' => array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES 'UTF8''
    ),
    'username' => 'user',
    'password' => '******',
),

'dbconfigkey2' => array(
    'driver'         => 'Pdo',
    'dsn'            => 'mysql:dbname=my_db2;host=localhost',
    'driver_options' => array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES 'UTF8''
    ),
    'username' => 'user',
    'password' => '******',
),

);

to use the adapters you need to call them using the Service Manager:

$adapter1=$serviceManager->get('myadapter1');
$adapter2=$serviceManager->get('myadapter2');

As of version 2.2

An Abstract Service Factory is now part of the zf2 ZendDb module. It is possible to add multiples configuration keys under the 'adapters' sub-key :

'db'=> array(
    'adapters'=>array(
        'adapter' => array(
            'driver'         => 'Pdo',
            'dsn'            => 'mysql:dbname=test;host=localhost',
            'username' => 'readCredential',
            'password' => '****'
        ),
        'adapter2' => array(
            'driver'         => 'Pdo',
            'dsn'            => 'mysql:dbname=test;host=localhost',
            'username' => 'rwCredential',
            'password' => '****'
        ),
    )
),

However, the AbstractServiceFactory need to be added "manually" as it isn't so by default :

'service_manager' => array(
    'abstract_factories' => array(
            'ZendDbAdapterAdapterAbstractServiceFactory',
    )
),

The adapters are accessible as previously :

$adapter1=$serviceManager->get('adapter');
$adapter2=$serviceManager->get('adapter2');

From a performance perspective this second approach is better : One object will be instantiated (The abstract factory) to (potentially) create the different adapters. Whereas in the previous approach, one object per configuration was created.