In this post I will walk you through building a simple module to show how an external database can be accessed from Magento. Our module will include a model, whose table resides in an external database. We will create this table using Magento setup functionality and use the table to write and read data.

First you have to create the required folder structure:

app
    --code
        --Solvingmagento
            --Externaldb
                --Model
                --controllers
                --etc  
                --sql
    --etc
        --modules

Once ready, add module declaration file Solvingmagento_Externaldb.xml to app/etc/modules/. Its content is standard:

<?xml version="1.0"?>
<config>
    <modules>
        <Solvingmagento_Externaldb>
            <active>true</active>
            <codePool>local</codePool>
        </Solvingmagento_Externaldb>
    </modules>
</config>

Our next step is creating a configuration file for our module. First we add the minimum required configuration nodes to config.xml

<?xml version="1.0" encoding="UTF-8"?>
<config>
    <modules>
        <Solvingmagento_Externaldb>
            <version>0.1.0</version>
        </Solvingmagento_Externaldb>
    </modules>
</config>

Configure a router to output our test results:

<config>
    <frontend>
        <routers>
            <externaldb>
                <use>standard</use>
                <args>
                    <module>Solvingmagento_Externaldb</module>
                    <frontName>externaldb</frontName>
                </args>
            </externaldb>
        </routers>
    </frontend>
</config>

We will also need to add a controller: controllers/IndexController.php:

<?php

class Solvingmagento_Externaldb_IndexController extends Mage_Core_Controller_Front_Action
{
    public function indexAction()
    {
    }
}
?>

Now let us add a model to our module. First we extend our configuration file with necessary nodes:

<config>
    ...
    <global>
        ...
        <models>
            <solvingmagento_externaldb><!-- name of the model configuration-->
                <class>Solvingmagento_Externaldb_Model</class><!-- path where to look for model classes for-->
                <resourceModel>solvingmagento_externaldb_resource</resourceModel><!-- pointer to the resource model configuration node-->
            </solvingmagento_externaldb>
            <solvingmagento_externaldb_resource><!-- resource model configuration name -->
                <class>Solvingmagento_Externaldb_Model_Resource</class><!-- path where to look for resource model classes for -->
                <entities><!-- entities list -->
                    <external_data><!-- our test entity -->
                        <table>external_data</table><!-- name of the external table to store data for our entity -->
                    </external_data>
                </entities>
            </solvingmagento_externaldb_resource>
        </models>
        ...
    </global>
    ...
</config>

Now we add the model and model resource files to our module. Four our resource model we create a sub folder Resource first:

                
                --Model
                    --Resource 
                        Externaldb.php 
                    Externaldb.php 

Our model class extends Mage_Core_Model_Abstract:

<?php
class Solvingmagento_Externaldb_Model_Externaldata extends Mage_Core_Model_Abstract
{
    protected function _construct()
    {
        $this->_init('solvingmagento_externaldb/externaldata');
    }

}
?>

To initialize our model we must override the protected method _construct() declared in Varien_Object. There we call another protected method _init($resourceName). The value we supply to parameter $resourceName consists of a pointer to our model configuration (solvingmagento_externaldb) and a string Magento will use to determine the class name of the resource model (externaldata will be resolved to Solvingmagento_Externaldb_Model_Resource_Externaldata).

For the purposes of our example there is no need to override or add other methods. If you model logic is more complex, you are free to do so.

Next step is to add a model resource class to the module. As we have already discovered, its name must be Solvingmagento_Externaldb_Model_Resource_Externaldata. Since we are going to use a MySQL database to access our data, we must derive it from Mage_Core_Model_Resource_Db_Abstract:

class Solvingmagento_Externaldb_Model_Resource_Externaldata extends Mage_Core_Model_Resource_Db_Abstract
{
    protected function _construct()
    {
        $this->_init('solvingmagento_externaldb/external_data', 'id');
    }
}

Again we must override the protected method _construct() to initialize the data table access. By calling the _init($mainTable, $idFieldName) we tell Magento to look for resource model configuration defined for model solvingmagento_externaldb and find entity called external_data. This will be our main table. The second parameter ($id) defines the identity field for our table.

So far the system had no way to tell that we are in fact going to access an external data source. We must configure our database connections first. Add the following nodes to our configuration file:

<config>
    ...
    <global>
        ...
        <resources>
            <external_db><!-- name of the external db connection -->
                <connection>
                    <host><![CDATA[localhost]]></host>
                    <username><![CDATA[username]]></username>
                    <password><![CDATA[password]]></password>
                    <dbname><![CDATA[external_database_name]]></dbname>
                    <type>pdo_mysql</type>
                    <active>1</active>
                </connection>
            </external_db>
            <solvingmagento_externaldb_setup><!-- connection used by  setup procedures -->
                <setup>
                    <module>Solvingmagento_Externaldb</module>
                    <class>Mage_Eav_Model_Entity_Setup</class>
                </setup>
                <connection>
                    <use>external_db</use>
                </connection>
            </solvingmagento_externaldb_setup>
            <solvingmagento_externaldb_write><!-- connection used for write access -->
                <connection>
                    <use>external_db</use>
                </connection>
            </solvingmagento_externaldb_write>
            <solvingmagento_externaldb_read><!-- connection used for read access -->
                <connection>
                    <use>external_db</use>
                </connection>
            </solvingmagento_externaldb_read>
        </resources>
        ...
    </global>
    ...
</config>

First we have added a new connection in the same way the default connection is defined in local.xml file. One difference is that we have to define connection type explicitly: <type>pdo_mysql</type>.

In the other nodes we define setting for connections used to setup our module (we will create a table), and to read and write data. Here names of the nodes are important:

1. <solvingmagento_externaldb_setup>. in this node we specify our module name. The module name and the node name are used by Magento to locate setup files. Therefore the path will be Solvingmagento/Externaldb/sql/solvingmagento_externaldb_setup/. Here we will put our install file install-0.1.0.php:

<?php
 
$this->startSetup();
 
$table = $this->getConnection()
    ->newTable($this->getTable('solvingmagento_externaldb/external_data'));
 
$table->addColumn(
    'id', 
    Varien_Db_Ddl_Table::TYPE_INTEGER,
    null,
    array(
        'identity'  =>  true,
        'primary'   =>  true     
    )
);
$table->addColumn(
    'created_at', 
    Varien_Db_Ddl_Table::TYPE_DATETIME,
    null,
    array(
        'nullable'  =>  false    
    )
)->addColumn(
    'name', 
    Varien_Db_Ddl_Table::TYPE_VARCHAR,
    255,
    array(
        'nullable'  =>  false    
    )
 
);
 
 
$this->getConnection()->createTable($table);
 
$this->endSetup();
?>

2. <solvingmagento_externaldb_write> and <solvingmagento_externaldb_read> The names of these nodes are also important. When the resource model initializes its read and write connections it looks for configuration entries with node names composed of resourcePrefix, which corresponds to the name of the node of our model configuration, solvingmagento_externaldb, and _read or _write identifiers. When such connection nodes can’t be found, Magento falls back on using the default read and write connections (core_read and core_write).

Finally, we add some model manipulation into our controller file:

 public function indexAction()
    {
        $externalDataWrite = Mage::getModel('solvingmagento_externaldb/externaldata');
 
        $externalDataWrite->setCreatedAt(date("Y-m-d H:i:s"));
        $externalDataWrite->setName('Name');
        $result = $externalDataWrite->save();
 
        $id = $result->getId();
 
        $externalDataRead = Mage::getModel('solvingmagento_externaldb/externaldata');
        $externalDataRead->load($id);
 
        echo '<pre>';
        print_r($externalDataRead->getData());
        echo '</pre>';
 
 
 
    }

We create one model instance, fill it with data and write it into the external database. Then we create another instance to load the data from the external database into, and output it.

During the first run, a new table external_data will be created in the external database, whose name you have provided in our custom connection configuration. Then calling http://www.yourshopname.com/externaldb/index/index/ will perform write and read operations on our external source.

A finished module can be obtained here https://github.com/varinen/SolvingmagentoModules