7

Can anyone provide a tutorial, or link to a tutorial for adding a connection to an external database in Magento 2? Slowly getting my head around the new structure and procedures in M2, but this one is eluding me.

4
  • How far it is from fishpig.co.uk/magento/tutorials/… ? I cannot find anything useful in wiki.magento.com/display/MAGE2DOC/Developer%27s+Guide :( Jun 25, 2015 at 8:26
  • 1
    I found the fishpig tutorial too. To be honest, no idea how far M2 differs from M1. I suspect that the code itself is largely the same, but that's not guaranteed, plus, with the differences in the file structure for new modules, I wouldn't like to guess. Jun 25, 2015 at 10:18
  • What do you mean by "external database"? Magento connects to MySQL by default. There are modules that hook up to Redis etc. For other data sources you can write your own PHP code in a module to talk to them. Not sure what more you are after so cannot respond.
    – Alan Kent
    Jun 26, 2015 at 0:32
  • By external database, I mean a non-magento database, located on a different MySQL server from the M2 db. In order to provide real-time data from systems outside of M2. In M1 this was easily achieved with standard mysqli connectors, but M2 is crashing whenever we attempt a new connection. I believe there are connection building tools within M2, but unable to find any docs or tutorials. Jun 26, 2015 at 11:51

3 Answers 3

8

FishPig here. The tutorial on http://fishpig.co.uk/ is for M1 and is quite old (there are better ways connection to a DB on M1 than the tutorial says). Anyway, I've been trying to connect to an external DB while porting over Magento WordPress Integration to M2 so figured I'd post here what I have.

I'm not sure if this is the best way to do it but here's what I have that's working. Any feedback and improvements would be welcome!

<?php

namespace FishPig\SomeModule;

class App
{   
    protected $connectionFactory = null;

    public function __construct(
      \Magento\Framework\App\ResourceConnection\ConnectionFactory $connectionFactory
    ) {
        $this->connectionFactory = $connectionFactory;

        $this->_initDb();
    }

    protected function _initDb()
    {
        $db = $this->connectionFactory->create(array(
            'host' => 'localhost',
            'dbname' => 'enter_your_db_name',
            'username' => 'enter_your_db_username',
            'password' => 'enter_your_db_password',
            'active' => '1',    
        ));

        // Let's test the DB with a query

        $tableToTest = 'enter_your_table_name';

        $select = $db->select()
            ->from($tableToTest, '*');

        if ($results = $db->fetchAll($select)) {
            echo sprintf('<pre>%s</pre>', print_r($results, true));
        }
        else {
            echo 'The query was empty.';
        }
    }
}

This code uses DI to inject the \Magento\Framework\App\ResourceConnection\ConnectionFactory class. This works well because the class is a factory so doesn't instantiate the DB connection straight away. This allows you to gather the DB details and connect.

I should also point out that there are some values (DB details and DB table name for testing) that you will need to change for this code to work.

2
1

Database connection settings and backend name are declared in app/etc/config.php which was earlier declared in local.xml

1
1

1) In app/etc/env.php add this code:

'custom' => array (
      'host' => 'localhost',
      'dbname' => 'second_db_name',
      'username' => 'db_username',
      'password' => 'db_password',
      'engine' => 'innodb',
      'initStatements' => 'SET NAMES utf8;',
      'active' => '1',
),

And

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

Final env.php file look like this:

  'db' => 
  array (
    'table_prefix' => '',
    'connection' => 
    array (
      'default' => 
      array (
        'host' => 'localhost',
        'dbname' => 'first_db_name',
        'username' => 'db_username',
        'password' => 'db_password',
        'active' => '1',
      ),
       'custom' => array (
          'host' => 'localhost',
          'dbname' => 'second_db_name',
          'username' => 'db_username',
          'password' => 'db_password',
          'engine' => 'innodb',
          'initStatements' => 'SET NAMES utf8;',
          'active' => '1',
      ),
    ),
  ),
  'resource' => 
  array (
    'default_setup' => 
    array (
      'connection' => 'default',
    ),
     'custom' => 
    array(
      'connection' => 'custom'
    ),
  ),

2) Next step is to configure resource model to use new connection. The di.xml configuration file of module will help to set new resource name to resource model. In Vendor/Module/etc/di.xml add this code:

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Vendor\Module\Model\ResourceModel\Module">
        <arguments>
           <argument name="connectionName" xsi:type="string">custom_setup</argument>
        </arguments>
    </type>
</config>
3
  • I certainly hope you are not using root for your database as well as having it as the password.
    – SR_Magento
    Dec 24, 2016 at 10:45
  • This is sample coding my friend. I have used root in only local server. Dec 24, 2016 at 17:12
  • In custom array you can not use 'host' => 'localhost', for remote connection. Oct 3, 2018 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.