Direct SQL queries in Magento

Magento and the Zend Framework that it is built upon offer a complete abstraction of data access, which allows you to get on with the creation of higher-level logic without worrying about database access. However, sometimes you just want to be able to run a few key SQL queries directly and bypass the abstraction. This post details the most direct method of doing so in Magento.

$conn = Mage::getSingleton('core/resource')->getConnection('core_read');

This will return a Varien_Db_Adapter_Pdo_Mysql object, a subclass of Zend_Db_Adapter_Abstract, which will allow you to run the Zend adapter methods directly. For example:

// Prints a list of all website names
$results = $conn->fetchAll("SELECT * FROM core_website;");
foreach($results as $row) {
    echo $row['name'] . "\n";
}

Note the use of the core_read parameter in the getConnection call - this instructs Magento as to which resource to use. Essentially, which database to use to perform queries. In most cases this won't be relevant and can be left as core_read but it becomes vitally important to set this correctly when using multiple databases, which is one deployment scenario outlined in the Magento performance whitepaper.

In cases where you need a different connection, simply change the getConnection parameter to match. The most useful of these is core_write, which should be used when you want to perform queries which will modify the database. core_read and core_write are always available and will work, but if you are running queries as part of an extension, it is best to define connections in the config.xml for the extension and refer to those instead.

In config.xml:

<config>
    <global>
        <resources>
            <extension_setup>
                <connection>
                    <use>core_setup</use>
                </connection>
            </extension_setup>
            <extension_read>
                <connection>
                    <use>core_read</use>
                </connection>
            </extension_read>
            <extension_write>
                <connection>
                    <use>core_write</use>
                </connection>
            </extension_write>
        </resources>
    </global>
</config>

Match extension in the above to your own extension name. Then to connect to your read or write database use:

$conn = Mage::getSingleton('core/resource')->getConnection('extension_read');
$conn = Mage::getSingleton('core/resource')->getConnection('extension_write');

Using the above method is the better way to perform database connections as you are future proofing your code - if a second database becomes necessary, anywhere getConnection is used can stay as is. At a bare minimum, code should correctly reference read and write connections for appropriate operations, such as by using core_read and core_write.