Solving Magento

Solutions for Magento E-Commerce Platform

by Oleg Ishenko

Accessing an External Database from Your Magento Module

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

Readers who read this post also read these:

  • Adding Custom Options to Products in Magento
    In my post A Magento File Custom Option Type Primer I’ve talked about how file custom product options are handled in Magento. One of the readers posted a comment there asking if there was an altern...
  • Magento Downloadable Product Type Tutorial
    In this tutorial I am going to demonstrate some of the functions I’ve talked about in my overview of the Downloadable product type. I will develop a simple module that will add a new feature to the...
  • Magento Grouped Product Redirect Tutorial
    Recently, one of my blog readers has raised a question in the comments to my post Magento Grouped Poduct Type: Is there a way to direct a customer to the grouped product when he clicks a link to th...
  • Events and Observers: a Magento Tutorial
    This tutorial will demonstrate the principles of the observer pattern implemented in Magento. The details of this implementation have already been discussed in the previous part Event-Driven Archit...

6 thoughts on “Accessing an External Database from Your Magento Module

  1. Great article. Helps me a lot to understand the data layer and to write non-standard models.

    One question. You noted the importance of specifying the connection type in the configuration node, as pdo_mysql, but did not say why.

    Isn’t this the default connection type (at least in CE 1.4.x)?

    Also, I can’t find where the connection type is defined by core code, only that it exists when I dump the configuration node at Mage::getConfig()->getNode('global/resource/connection/types'). Where is this configuration constructed?

    Thanks for digging deeper!

    • Hi Rick,

      The type node value plays a role when Magento instantiates a connection object and must be given explicitly for non-core connection. The type node value is used in class Mage_Core_Model_Resource in method getConnection:

      $connection = $this->_newConnection((string)$connConfig->type, $connConfig);

      The $connConfig variable represents the configuration nodes of the external_db connection. Method getConnectionAdapterClassName of the same class resolves the pdo_mysql value into the adapter class name Varien_Db_Adapter_Pdo_Mysql:

          protected function _getConnectionAdapterClassName($type)
          {
              $config = Mage::getConfig()->getResourceTypeConfig($type);
              if (!empty($config->adapter)) {
                  return (string)$config->adapter;
              }
              return false;
          }
      

      The resource node (pdo_mysql) that Magento fetches in the listing above is defined in the app/etc/config.xml file:

              <resource>
                  <connection>
                      <types>
                          <pdo_mysql>
                              <adapter>Varien_Db_Adapter_Pdo_Mysql</adapter>
                              <class>Mage_Core_Model_Resource_Type_Db_Pdo_Mysql</class>
                              <compatibleMode>1</compatibleMode>
                          </pdo_mysql>
                      </types>
                  </connection>
              </resource>
      

      The core connection uses the same configuration: Mage::getConfig()->getNode('global/resource/connection/types') reads the app/etc/config.xml file and fetches the /resource/connection/types node shown in the listing above.

  2. Hi,I have an external database with a huge number of data and the solution of read and write data on the new table takes a lot of time .Is it possible to read only the data from the external database ?
    thanks in advance

  3. You sir deserve a lot of blessings for your posts regarding magento solutions. This post serves me in great deal, as it did the post about observers, also written by you. Kudos to you, my friend, God Bless

Leave a Reply

Your email address will not be published. Required fields are marked *

Theme: Esquire by Matthew Buchanan.