preloder

New Database Connection in Magento 2

Prev Next

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.

'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',
      )
    ),
  ),

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

Setting Description
table_prefix Holds table prefix for all database tables installed
connection Holds a list of available configurations for database connections
default Name of the database connection
host Database host
dbname Database name
username Database user for connection
password Database user’s password
model Database type.
engine Database engine.
initStatements Database initial set of commands to be executed when connecting to a database
active Whether the connection is enabled

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.

'custom' => array (
    'host' => 'localhost',
    'dbname' => 'custom_database',
    'username' => 'custom_user',
    'password' => 'custom_password',
    'engine' => 'innodb',
    'initStatements' => 'SET NAMES utf8;',
    'active' => '1',
)

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

'resource' => array(
    'custom' => array(
      'connection' => 'custom'
    )
)

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

'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'
    )
  ),
)

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 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>

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.

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

        $resourceName = preg_replace("/_setup$/", '', $resourceName);
        // more code ...
    }

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.

Max with his team of Magento enthusiasts won the Best Magento 1 to Magento 2 Migration Award at the Magento Imagine 2017. As CEO at Pronko Consulting, he is actively working with Magento 2, delivering superior customer experience. As one of the most famous developers on a market and in the Magento Community, he launched Magento 2 blog, Magento DevChannel – YouTube channel in 2017 where he shares his knowledge, experience and the best practices in the Magento field.

Posted on May 28, 2016

The Devletter

Subscribe to my Magento 2 tips and tutorials content.
Every week I send an interesting email directly to your inbox

Related Posts

Show
Hide
Pre-order the Magento 2 payment integration course TODAY PRE-ORDER
Hello. Add your message here.