External Database Handling in Magento 1

It’s sometimes the case that a merchant has a complete database of records necessary for his/their business operation. It could be archived data, more or less complicated relations between products (“Does the lens fit the camera?”), or simply databases from other applications.

Magento has a set of classes and methods responsible for communication with tables in its database. But what if we want to communicate with a completely separate database? Magento doesn’t disappoint and provides us with out-of-the-box tools for this task.

We should naturally start with adding a new module:

//app/etc/modules/Magently_Module.xml
<?xml version="1.0"?>
<config>
    <modules>
        <Magently_Module>
            <active>true</active>
            <codePool>local</codePool>
            <depends></depends>
        </Magently_Module>
    </modules>
</config>

Next, we add the config file with node responsible for adding the connection with the new database:

//app/code/local/Magently/Module/etc/config.xml
<?xml version="1.0"?>
<config>
    <modules>
        <Magently_Module>
            <version>0.0.1</version>
        </Magently_Module>
    </modules>
    <global>
        <resources>
            <magently_module_setup>
                <setup>
                    <module>Magently_Module</module>
                </setup>
            </magently_module_setup>
            <magentlymodule_write>
                <connection>
                    <use>magentlymodule_database</use>
                </connection>
            </magentlymodule_write>
            <magentlymodule_read>
                <connection>
                    <use>magentlymodule_database</use>
                </connection>
            </magentlymodule_read>
            <magentlymodule_setup>
                <connection>
                    <use>core_setup</use>
                </connection>
            </magentlymodule_setup>
            <magentlymodule_database>
                <connection>
                    <!-- Database info goes here -->
                    <host><![CDATA[]]></host>
                    <username><![CDATA[]]></username>
                    <password><![CDATA[]]></password>
                    <dbname><![CDATA[]]></dbname>
                    <model>mysql4</model>
                    <type>pdo_mysql</type>
                    <active>1</active>
                </connection>
            </magentlymodule_database>
        </resources>
    </global>
</config>

At this point, we should be able to connect with the new database the same way as we do with Magento database. Let’s have a closer look at the process:

First, we add the information about the table we want to base the model on to the config.xml file.

//app/code/local/Magently/Module/etc/config.xml
<?xml version="1.0"?>
<config>
    ...
    <global>
        ...
        <models>
            <magentlymodule>
                <class>Magently_Module_Model</class>
                <resourceModel>magentlymodule_mysql4</resourceModel>
            </magentlymodule>
            <magentlymodule_mysql4>
                <class>Magently_Module_Model_Mysql4</class>
                <entities>
                    <sometable>
                        <table>table_with_some_data</table>
                    </sometable>
                </entities>
            </magentlymodule_mysql4>
        </models>
    ...
...

Such configuration allows us to refer to the model of the table named table_with_some_data using the method Mage::getModel(‘magentlymodule/sometable’). Connecting to another table is possible by adding another node on the <sometable> level.

Having dealt with the config, we can move to the code that will actually handle the Model:

//app/code/local/Magently/Module/Model/Sometable.php
<?php
class Magently_Module_Model_Sometable extends Mage_Core_Model_Abstract
{
    public function _construct()
    {
        $this->_init('magentlymodule/sometable');
    }
}

We will also need its ResourceModel (responsible for the actual connection with the table), where we provide the ID of the records in the table (here it’s ‘ID’).

//app/code/local/Magently/Module/Model/Mysql4/Sometable.php
<?php
class Magently_Module_Model_Mysql4_Sometable extends Magently_Module_Model_Mysql4_Abstract
{
    public function _construct()
    {
        $this->_init('magentlymodule/sometable', 'ID');
    }
}

We won’t make it without the collection either:

//app/code/local/Magently/Module/Model/Mysql4/Sometable/Collection.php
<?php
class Magently_Module_Model_Mysql4_Sometable_Collection extends Mage_Core_Model_Mysq4_Collection_Abstract
{
    public function _construct()
    {
        $this->_init('magentlymodule/sometable');
    }
}

As you can see, there’s nothing particularly difficult about the process. There are, however, two things that might be problematic. First, as an observant reader might have noticed, in config.xml we didn’t specify the table prefix in our database. By default, Magento will look for tables with the same prefix as in the local.xml file. A workaround solution here is to add a new class extending Mage_Core_Model_Mysql4_Abstract and use it as a parent of our ResourceModel. The table names are stored in the $this->_tables array, so we can modify them easily – for example, by overloading the getTable method.

Second, we need to enter the credentials to the database in the config.xml file. Remember about granting the right permissions for the file, so that no one from outside could view it using the browser, and about adding the file to .gitignore.