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(
            'Zend\Db\Adapter\Adapter' => 'Zend\Db\Adapter\AdapterServiceFactory',
        ),
    ),
);

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

Answer 1

I found much better explaination on https://samsonasik.wordpress.com/2013/07/27/zend-framework-2-multiple-named-db-adapter-instances-using-adapters-subkey/

Zend Framework 2.2 comes with abstract_factories Zend\Db\Adapter\AdapterAbstractServiceFactory that allow us to configure multiple named DB adapter instances. This is step by step to do it :

1. Register Zend\Db\Adapter\AdapterAbstractServiceFactory at ‘abstract_factories’ type under ‘service_manager’ key.

   //config/autoload/global.php //.... part of config/autoload/global.php 'service_manager' => array( 'abstract_factories' => array( 'Zend\Db\Adapter\AdapterAbstractServiceFactory', ), ),

2. Configure ‘adapters’ subkey under ‘db’ key at config/autoload/global.php

//config/autoload/global.php //.... part of config/autoload/global.php

'db' => array(
    'adapters' => array(

        'db1' => array(
           'driver'         => 'Pdo',
           'dsn'             => 'mysql:dbname=zf2_staging;host=localhost',
           'driver_options'  => array(
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
            ),
        ),

        'db2' => array(
           'driver'         => 'Pdo',
           'dsn'             => 'mysql:dbname=zf2_test;host=localhost',
           'driver_options'  => array(
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
            ),
        ),
    ),
),

3. Configure ‘adapters’ subkey under ‘db’ key at config/autoload/local.php

//config/autoload/local.php

return array(
    'db' => array(
        'adapters' => array(
            'db1' => array(
                'username' => 'root',
                'password' => '',
            ),
            'db2' => array(
                'username' => 'other_user',
                'password' => 'other_user_passwd',
            ),
        ),
    ),
);

3. Call adapter using ‘db1’ or ‘db2’ as db adapter from ServiceManager

   $sm->get('db1');

   $sm->get('db2');

If you need to get $sm->get(‘Zend\Db\Adapter\Adapter’) as primary adapter, ‘db1’ and ‘db2’ as other adapter for specific purpose, then you need to define primary adapter directly under db, so the configuration of config/autoload/global.php will be like the following :

//config/autoload/global.php

return array(
    'db' => array(
        //this is for primary adapter....
        'driver'         => 'Pdo',
        'dsn'             => 'mysql:dbname=zf21_learn;host=localhost',
        'driver_options'  => array(
             PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
         ),

        //other adapter when it needed...
        'adapters' => array(

            'db1' => array(
               'driver'         => 'Pdo',
               'dsn'             => 'mysql:dbname=zf2_staging;host=localhost',
               'driver_options'  => array(
                    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
                ),
            ),
            'db2' => array(
               'driver'         => 'Pdo',
               'dsn'             => 'mysql:dbname=zf2_test;host=localhost',
               'driver_options'  => array(
                    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
                ),
            ),

        ),
    ),
    'service_manager' => array(
        // for primary db adapter that called
        // by $sm->get('Zend\Db\Adapter\Adapter')
        'factories' => array(
            'Zend\Db\Adapter\Adapter'
                    => 'Zend\Db\Adapter\AdapterServiceFactory',
        ),
        // to allow other adapter to be called by
        // $sm->get('db1') or $sm->get('db2') based on the adapters config.
        'abstract_factories' => array(
            'Zend\Db\Adapter\AdapterAbstractServiceFactory',
        ),
    ),
);

The config/autoload/global.local.php should be configured too like the following :

//config/autoload/local.php

return array(
    'db' => array(
        // for primary db adapter that called
        // by $sm->get('Zend\Db\Adapter\Adapter')
        'username' => 'root',
        'password' => '',

        // to allow other adapter to be called by
        // $sm->get('db1') or $sm->get('db2') based on the adapters config.
        'adapters' => array(
            'db1' => array(
                'username' => 'root',
                'password' => '',
            ),
            'db2' => array(
                'username' => 'other_user',
                'password' => 'other_user_passwd',
            ),
        ),
    ),
);

Answer 2

If you look at the Zend\Db\Adapter\AdapterServiceFactory, 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 Your\Namespace;

use Zend\ServiceManager\FactoryInterface;
use Zend\ServiceManager\ServiceLocatorInterface;
use Zend\Db\Adapter\Adapter;

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 Your\Namespace\MyAdapterFactory;
use Zend\ModuleManager\Feature\ServiceProviderInterface;

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 Zend\Db 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(
            'Zend\Db\Adapter\AdapterAbstractServiceFactory',
    )
),

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.