Custom Database Connection on Magento 2

Sometimes Magento needs to connect with a different Database specially when we link Magento to a third party system (like ERP). Magento 2 is very much flexible and easily connect to other systems. To establish connection with a different database we need to apply following tweak.

All the credential for new connection can be set from the following file

app/etc/env.php

Inside this file you will find following code

'db' => 
  array (
    'table_prefix' => '',
    'connection' => 
    array (
      'default' => 
      array (
        'host' => 'localhost',
        'dbname' => 'magento',
        'username' => 'your_username',
        'password' => 'your_password',
        'model' => 'mysql4',
        'engine' => 'innodb',
        'initStatements' => 'SET NAMES utf8;',
        'active' => '1',
      )
    ),
  ),

Parameters under default are the common params used to connect database and they are self-explanatory.

New Connection Setup

To create a new connection, we need to copy-paste the whole “default” element under connection as a new element with comma (,) separator. Update the database credentials and rename the connection name from default to any suitable one.

ultimately the file should look like:

'db' => 
  array (
    'table_prefix' => '',
    'connection' => 
    array (
      'default' => 
      array (
        'host' => 'localhost',
        'dbname' => 'magento',
        'username' => 'your_username',
        'password' => 'your_password',
        'model' => 'mysql4',
        'engine' => 'innodb',
        'initStatements' => 'SET NAMES utf8;',
        'active' => '1',
      ),
      'newConnection' => 
      array (
        'host' => 'localhost',
        'dbname' => 'magento_new',
        'username' => 'your_username2',
        'password' => 'your_password2',
        'model' => 'mysql4',
        'engine' => 'innodb',
        'initStatements' => 'SET NAMES utf8;',
        'active' => '1',
      )
    ),
  ),

Now as the last step, we need to create a new resource for our new connection setup. In the same file, you can find a section named resource. Same as above, we need to copy-paste the element under resource array as new element and update the connection name as defined on previous section.
The section should look like as follows:

'resource' => array (
    'default_setup' => array (
      'connection' => 'default',
    ),
    'newConnection' => array(
      'connection' => 'newConnection'
    )
  ),

That’s it! We have established new connection setup. Now we need to call this connection through resource connection objection and we will be able to perform all CURD operations.
Example code is given bellow:

public function __construct( 
  \Magento\Framework\App\ResourceConnection $resourceConnection 
) 
{ 
  $this->_resourceConnection = $resourceConnection; 
} 

public function execute() 
{
  $connection = $this->_resourceConnection->getConnection('newConnection'); 

  $select = $connection->select() ->from( ['mainTable' => 'table_name'] ); 
  
  $data = $connection->fetchAll($select); 
  
  print_r($data); 
}