Create An External Database Connection in Magento

While I'm confident that many people in the Magenosphere have figured this one out, it took a whole Saturday night and part of my soul for me to achieve it. Hopefully, by presenting this code here, I can save you from the pain I endured :)

Creating The Connection

To create your connection, create a custom module and add the following to your config.xml. The code below is the bare minimum needed to get the external database connection working.

<?xml version="1.0"?>
<config>
	<modules>
		<Fishpig_Externaldb>
			<version>0.1.0</version>
		</Fishpig_Externaldb>
	</modules>
	<global>
		<resources>
			<externaldb_write>
				<connection>
					<use>externaldb_database</use>
				</connection>
			</externaldb_write>
			<externaldb_read>
				<connection>
					<use>externaldb_database</use>
				</connection>
			</externaldb_read>
			<externaldb_setup>
				<connection>
					<use>core_setup</use>
				</connection>
			</externaldb_setup>
			<externaldb_database>
				<connection>
					<host><![CDATA[localhost]]></host>
					<username><![CDATA[db_username]]></username>
					<password><![CDATA[db_password]]></password>
					<dbname><![CDATA[db_name]]></dbname>
					<model>mysql4</model>
					<type>pdo_mysql</type>
					<active>1</active>
				</connection>
			</externaldb_database>
		</resources>
	</global>
</config>

Clear the cache after creating your module and from now on, each time you load Magento, a second database connection will be created

Accessing The Database

When I initially found out how to create an external database connection, I was using Zend_Db to retrieve all of my information. You can use the following to test your database connection is working:

<?php

	$resource	= Mage::getSingleton('core/resource');
	$conn 		= $resource->getConnection('externaldb_read');
	$results 	= $conn->query('SELECT * FROM tblName');

	print_r($results)

This works fine, however kind of takes the point away from having this connection available in Magento.

Accessing The External Database Using Models

Using models to access the database keeps our code style uniform throughout Magento. Also, it means we can integrate any other CMS or database driven application without learning it's coding practices. To achieve this, simply add models to your custom module like you would for any other module.

I will attempt to demonstrate how to set up the models and config.xml files now. For this example I will pretend I am integrating Magento with a system that lists books (reading is cool!).

Creating The Model Class Files

Create the following files:

code/local/Fishpig/Externaldb/Model/Book.php

<?php

class Fishpig_Externaldb_Model_Book extends Mage_Core_Model_Abstract
{
	public function _construct()
	{
		$this->_init('externaldb/book');
	}
}

code/local/Fishpig/Externaldb/Model/Mysql4/Book.php

<?php

class Fishpig_Externaldb_Model_Mysql4_Book extends Mage_Core_Model_Mysql4_Abstract
{
	public function _construct()
	{
		$this->_init('externaldb/book', 'book_id'); // book_id refers to the primary key of the book table
	}
}

code/local/Fishpig/Externaldb/Model/Mysql4/Book/Collection.php

<?php
class Fishpig_Externaldb_Model_Mysql4_Book_Collection extends Mage_Core_Model_Mysq4_Collection_Abstract
{
	public function _construct()
	{
		$this->_init('externaldb/book');
	}
}

That's the bare minimum needed for your models to be able to access the database!

Adding the Models to The Config

To inform Magento about our models, we need to register them in config.xml. Below is an updated version of config.xml with the models for Book registered.

<?xml version="1.0"?>
<config>
	<modules>
		<Fishpig_Externaldb>
			<version>0.1.0</version>
		</Fishpig_Externaldb>
	</modules>
	<global>
		<models>
			<externaldb>
				<class>Fishpig_Externaldb_Model</class>
				<resourceModel>externaldb_mysql4</resourceModel>
			</externaldb>
			<externaldb_mysql4>
				<class>Fishpig_Externaldb_Model_Mysql4</class>
				<entities>
					<book>
						<table>library_book</table>
					</book>
				</entities>
			</externaldb_mysql4>
		</models>
		<resources>
			<externaldb_write>
				<connection>
					<use>externaldb_database</use>
				</connection>
			</externaldb_write>
			<externaldb_read>
				<connection>
					<use>externaldb_database</use>
				</connection>
			</externaldb_read>
			<externaldb_setup>
				<connection>
					<use>core_setup</use>
				</connection>
			</externaldb_setup>
			<externaldb_database>
				<connection>
					<host><![CDATA[localhost]]></host>
					<username><![CDATA[db_username]]></username>
					<password><![CDATA[db_password]]></password>
					<dbname><![CDATA[db_name]]></dbname>
					<model>mysql4</model>
					<type>pdo_mysql</type>
					<active>1</active>
				</connection>
			</externaldb_database>
		</resources>
	</global>
</config>

That's it, the models should now be registered in Magento!

Testing The Models

Testing them is easy enough, just treat them like normal Magento models.

<?php

	// Load the book with a primary key value of 4
	$_book = Mage::getModel('externaldb/book')->load(4);

	// This would print out the value in the field isbn in the external database
	echo $_book->getIsbn();

	//You can even update records!
	$_book->setName('1984');
	$_book->setAuthor('George Orwell');

	try {
	  $_book->save();
	} catch (Exception $e) {
	  exit($e->getMessage());
	} 

Conclusion

This is one of my first blogs so I probably haven't written this up as well as I could have, however, I think that using a second database in Magento can be extremely useful. I've written a very good Wordpress/Magento plugin which gives access to all Wordpress post/category/image/link etc information. If you would like to discuss this plugin or would like to use it in one of your sites then please let me know!