How to configure multiple databases in zf2

Last updated on December 2, 2022

Configuring multiple database adapters in zf2 is pretty easy. In this post, I will show you how to configure and connect to different databases in the zf2 application.

Adapter Abstract Service factory(Zend\Db\Adapter\AdapterAbstractServiceFactory) allow us to create multiple DB adapter instances in zf2.

In this example, I am adding two extra database adapters apart from the default one, so the total database adapter connections are 3 here with the primary connection.

After done with the configuration changes – test your settings by using the IndexController.php controller(find at the end of the post).

config/autoload/local.php
create and configure your database adapter settings.

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

config/autoload/global.php

1. Register Zend\Db\Adapter\AdapterAbstractServiceFactory at ‘abstract_factories’ under ‘service_manager’ key.
2. configure adapters settings of databases.

return array(
  
    'db' => array(
        'driver'         => 'Pdo',
        'dsn'            => 'mysql:dbname=target;host=127.0.0.1',
        'driver_options' => array(
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
        ),
		
		'adapters'=>array(
						'db1' => array(
							'driver'  => 'pdo',
							'dsn'     => 'mysql:dbname=target;host=localhost',        
							'driver_options' => array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"),
							'username' => 'root',
							'password' => ''
						),
						'db2' => array(
							'driver'  => 'pdo',
							'dsn'     => 'mysql:dbname=test;host=localhost',        
							'driver_options' => array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"),
							'username' => 'root',
							'password' => ''
						),
        )		
    ),
	
    'service_manager' => array(
	    // 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',
        ),

        'factories' => array(
            'Zend\Db\Adapter\Adapter'
                    => 'Zend\Db\Adapter\AdapterServiceFactory',
        ),
    ),
);

Test Controller

Create one Test Controller called IndexController.php in your Application Module. change the table name and then point your browser to

1. localhost…/index.php/index/
2.localhost…/index.php/fromDB1/
3.localhost…/index.php/fromDB2/

getServiceLocator()->get('Zend\Db\Adapter\Adapter');
		$query = "SELECT * FROM users";
		$this->printResult($adapter,$query);
                exit;
    }
	
	public function fromDB1Action() {
		$adapter = $this->getServiceLocator()->get('db1');
		$query = "SELECT * FROM users";
		$this->printResult($adapter,$query);
		exit;
	}
	
	public function fromDB2Action() {
		$adapter = $this->getServiceLocator()->get('db2');
		$query = "SELECT * FROM users";
		$this->printResult($adapter,$query);
		exit;
	}
	
	
	public function printResult($adapter,$query) {
	    $statement = $adapter->createStatement($query);
            $result = $statement->execute();  
		if ($result instanceof ResultInterface && $result->isQueryResult()) {
			$resultSet = new ResultSet;
			$resultSet->initialize($result);
			print_r($resultSet->toArray());	
		}
	}
}