Magento 2 gives a flexible mechanism for managing database connections. This feature was slightly refactored compare to Magento 1.x release. In this article, we will set up new database connection for custom resource model in Magento 2.

Overview

After successful installation, Magento 2 system creates app/etc/env.php configuration file. This file is most important due to a sensitive system information like database password, encryption key.

Setup application which goes out-of-box Magento 2 stores the following database connection information in the app/etc/env.phpfile.

[php]
‘db’ =>
array (
‘table_prefix’ => ”,
‘connection’ =>
array (
‘default’ =>
array (
‘host’ => ‘localhost’,
‘dbname’ => ‘magento2’,
‘username’ => ‘magento_user’,
‘password’ => ‘magento_password’,
‘model’ => ‘mysql4’,
‘engine’ => ‘innodb’,
‘initStatements’ => ‘SET NAMES utf8;’,
‘active’ => ‘1’,
)
),
),
[/php]

All database related configuration is stored under db key of the main configuration. Let’s have a close look at each database setting.

As for model setting which is stored under db/connection/default/model path it is seems legacy for me. This setting was used before multi-database support (RDBMS) in Magento 1. Due to a fact one of first Magento 1 releases was based on MySQL 4.x version. Later the mysql4 nodes were renamed to resource in all module configuration files.

Adding a new database connection

The env.php file should be updated in order to add a new database connection. Connection name should be unique in the array of connections to avoid overlapping.

[php]
‘custom’ => array (
‘host’ => ‘localhost’,
‘dbname’ => ‘custom_database’,
‘username’ => ‘custom_user’,
‘password’ => ‘custom_password’,
‘engine’ => ‘innodb’,
‘initStatements’ => ‘SET NAMES utf8;’,
‘active’ => ‘1’,
)
[/php]

Also, new resource settings should be introduced to assign custom connection with the resource.

[php]
‘resource’ => array(
‘custom’ => array(
‘connection’ => ‘custom’
)
)
[/php]

The env.php configuration file with new database connection should like below.

[php]
‘db’ => array (
‘table_prefix’ => ”,
‘connection’ => array (
‘default’ => array (
‘host’ => ‘localhost’,
‘dbname’ => ‘magento2’,
‘username’ => ‘magento_user’,
‘password’ => ‘magento_password’,
‘model’ => ‘mysql4’,
‘engine’ => ‘innodb’,
‘initStatements’ => ‘SET NAMES utf8;’,
‘active’ => ‘1’,
),
‘custom’ => array (
‘host’ => ‘localhost’,
‘dbname’ => ‘custom_database’,
‘username’ => ‘custom_user’,
‘password’ => ‘custom_password’,
‘engine’ => ‘innodb’,
‘initStatements’ => ‘SET NAMES utf8;’,
‘active’ => ‘1’,
),
),
),
‘resource’ => array (
‘default_setup’ => array (
‘connection’ => ‘default’,
),
‘custom’ => array(
‘connection’ => ‘custom’
)
),
)
[/php]

Next step is to configure resource model to use the new connection. The di.xml configuration file will help to set new resource name to resource model.

[xml]
<?xml version="1.0"?>
<config xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
<type name="Pronko\CustomModule\Model\ResourceModel\Article">
<arguments>
<argument name="connectionName" xsi:type="string">custom_setup</argument>
</arguments>
</type>
</config>
[/xml]

In case you noticed I use custom_setup as connectionName argument. The env.php file consists of default and custom resource names. It actually does not matter whether suffix _setup exists. The Magento\Framework\App\ResourceConnection\Config::getConnectionName() method uses a preg_replace function to get rid of _setup.

[php]
public function getConnectionName($resourceName)
{
$connectionName = \Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION;

$resourceName = preg_replace("/_setup$/", ”, $resourceName);
// more code …
}
[/php]

The Pronko\CustomModule\Model\ResourceModel\Article instance uses connectionName to set actual database connection to be used in the class. Once the getConnection() method is used inside the class it will return custom connection with another database.

Summary

Magento 2 provides powerful extension mechanism to add a new database connection. It is useful for building scalable server infrastructure when read and write operations split between databases. This article gives an introduction on how to set new database connection and use it in a resource model class.