Magento Install and Upgrade Data Scripts Explained

Share this article

While developing custom modules in Magento, I often have to deal with install and upgrade script issues. Normally, these issues arise because of mistakes in the naming convention, improper version numbers, or incorrect syntax. In this post, we’ll focus specifically on how to write infallible Magento install and upgrade scripts.

magento-ecommerce-square-logo

A Magento install and upgrade script is a part of module development, thus it’s recommended that you have some basic understanding of module development prior to reading this article.

Whenever you install or create any new module that contains database interaction, you will find an install and upgrade script in that module’s code directory that will run once you hit the URL. Magento’s core modules also follow the same install and upgrade structure. If you want to see some examples, open app/code/core/Mage/Catalog/sql/ catalog_setup. Here, you will find several install and upgrade scripts with proper naming conventions along with their version numbers.

Install Script

To run the install script, we need to create a custom module. We are not going to create a whole new module as it’s beyond the scope of this article; rather, we assume that we have an already created custom module with the package name Sitepoint and the module name Articles which keeps records of all written articles in the database.

For a quick guide let’s define the basic module components right here:

  • Blocks: Class instance of frontend templates. Frontend templates directly use class functions.
  • Models: Contain business logic same as in the typical MVC pattern.
  • Resource Models: Push and Pull data from database tables.
  • Controllers: Load layouts blocks when a URL is hit.
  • etc: An XML file contains module related configurations.
  • Helpers: As the name suggests, classes that contain functions which can be accessible across all modules as a helper, regardless of module scope.
  • sql: A file which contains database upgrade and install scripts to create and update the database schema.

Directory structure of module should be same as shown below.

app
 --code
 ---local
 ----Sitepoint
 ------Articles
 --------Block
  --------controllers
   --------etc
    --------Model
     --------sql

Go to app/code/local/Sitepoint/Articles/etc and open a file config.xml. We need to add some configuration related to the install script’s location in this file. In config.xml, under the global tag add the following child tags like so:

<global>
    <models>
        <articles>
            <class>Sitepoint_Articles_Model</class> <!-- Model class files -->     
            <resourceModel>articles_mysql4</resourceModel> <!--Resource model -->
        </articles>
        <articles_mysql4>
            <class>Sitepoint_Articles_Model_Mysql4</class>
            <entities>
                <articles>
                    <table>articles</table>  <!-- Db table name  -->
                </articles>
            </entities>
        </articles_mysql4>
    </models>
    <resources>  
        <articles_setup>
            <setup>
                <module>Sitepoint_Articles</module>
            </setup>
            <connection>
                <use>core_setup</use>
            </connection>
        </articles_setup>
        <articles_write>
            <connection>
                <use>core_write</use>
            </connection>
        </articles_write>
        <articles_read>
            <connection>
                <use>core_read</use>
            </connection>
        </articles_read>
    </resources>
</global>

There’s a very important tag articles_setup and it’s located under the resources tag, which tells Magento that our database setup files reside under the articles_setup directory.

Go to Articles/sql/articles_setup and create the install script mysql4-install-0.1.0.php

<?php
$installer = $this;
$installer->startSetup();
$installer->run("-- DROP TABLE IF EXISTS {$this->getTable('articles')};
CREATE TABLE {$this->getTable('articles')} (
	  `articles_id` int(11) unsigned NOT NULL auto_increment,
	  `title` varchar(255) NOT NULL default '',
	  `short_desc` text NOT NULL default '',
	  `long_desc` text NOT NULL default '',
	  `status` tinyint(2) NOT NULL default '0',
	  `created_time` datetime NULL,
	  `update_time` datetime NULL,
	  PRIMARY KEY (`articles_id`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	");
	$installer->endSetup();

To make our script compatible across databases, here is the alternate way using a DDL object:

<?php
$installer = $this;
$installer->startSetup();
$table = $installer->getConnection()->newTable($installer->getTable('articles'))
    ->addColumn('articles_id', Varien_Db_Ddl_Table::TYPE_INTEGER, 11, array(
        'unsigned' => true,
        'nullable' => false,
        'primary' => true,
        'identity' => true,
        ), 'Article ID')
    ->addColumn('title', Varien_Db_Ddl_Table::TYPE_VARCHAR, 255, array(
        'nullable' => false,
        'default' => '', 
        ), 'Title')
    ->addColumn('short_desc', Varien_Db_Ddl_Table::TYPE_TEXT, null, array(
        'nullable' => false,
        'default' => '',
        ), 'Short Desc')
    ->addColumn('long_desc', Varien_Db_Ddl_Table::TYPE_TEXT, null, array(
        'nullable' => false,
        'default' => '',
        ), 'Long Desc')
    ->addColumn('status', Varien_Db_Ddl_Table::TYPE_TINYINT, 2, array(
        'nullable' => false,
        'default' => '0',
        ), 'Status')
    ->addColumn('created_time', Varien_Db_Ddl_Table::TYPE_DATE, null, array(
        'nullable' => true,
        'default' => null,
        ), 'Created Date')
    ->addColumn('update_time', Varien_Db_Ddl_Table::TYPE_DATE, null, array(
        'nullable' => true,
        'default' => null,
        ), 'Update Date')    
    ->setComment('Articles table');
	$installer->getConnection()->createTable($table);
	$installer->endSetup();

To get the table name, we use $this->getTable('articles') as it will automatically append the table prefix (configured during the Magento installation process) to the table name.

A version number follows the name of our install script. This number is the same as defined in the config.xml file.

<modules>
    <Sitepoint_Articles>
        <version>0.1.0</version>    <!-- Version of module -->
    </Sitepoint_Articles>
	</modules>

The version number tells Magento about the latest install script. After setting up the install script, refresh your Magento URL and look at the database. If your install script ran successfully, your table should be created.

Now go to your SQL editor and open the core_resource table in Magento’s database. Here, you can see the entry of your install script with the name articles_setup and version number 0.1.0.

Every time you refresh a URL, Magento checks for any install scripts to run based on the version number in your config file and in the database core_resource table. If the versions do not match, it will look for the appropriate version file to run. Suppose you change the version to 0.1.1 in your config.xml. Magento will find that your core_resource table contains version number 0.1.0 and as you have mentioned a new version, it will look for a script with version number 0.1.1.

If it finds this file inside the articles_setup directory, it will run it and upgrade the version number to 0.1.1 in the core_resource table. This is how an install and upgrade script works. All Magento core modules follow this version system. In fact, the entire Magento upgrade process follows this same procedure.

Note that the install script runs only once – when you create it and refresh your URL. If you want to alter the database schema, you can do it by using the upgrade script. A shortcut is to delete your setup entry from core_resource table and refresh your URL. The script will run again and your new changes will be reflected in the database table.

Upgrade Script

When you want to update your module in terms of new database fields to provide new functionality, you need to alter your schema, change fields datatypes, introduce new columns, and so on.

This is when the upgrade script comes in handy. You can not make any direct changes via an SQL editor by running row queries, because whenever another user installs your module, that new database change will not work in their setup. It’s Magento’s recommendation to use an upgrade script for altering the database schema.

An upgrade script is similar to an install script – the only change is the name and a different version number. Let’s look at an example.

Suppose we want to add one more column and change another column. To do this, we will create an upgrade script named mysql4-upgrade-0.1.0-0.1.1.php under Articles/sql/articles_setup. Add the following code.

<?php
$installer = $this;
$installer->startSetup();
$installer->run("
	ALTER TABLE {$this->getTable('articles')}
	CHANGE COLUMN `long_desc` `long_desc` text NULL,
	ADD COLUMN `sub_title` VARCHAR(45) NOT NULL AFTER `title`;
	");
$installer->endSetup();

Here’s an alternative way of doing this using DDL:

<?php 
$installer = $this;
$installer->startSetup();
$installer->getConnection()
    ->changeColumn($installer->getTable('articles'), 'long_desc', 'long_desc', array(
        'type' => Varien_Db_Ddl_Table::TYPE_TEXT,
        'nullable' => true,
        
    ))
    ->addColumn($installer->getTable('articles'), 'sub_title', array(
        'type' => Varien_Db_Ddl_Table::TYPE_VARCHAR,
        'nullable' => false,
        'comment' => 'Sub title'
    ));
$installer->endSetup();

Now we need to tell Magento that we have a new upgrade script ready. To do this, we need to change the version number in our config.xml file :

<modules>
	<Sitepoint_Articles>
	    <version>0.1.1</version>    <!-- Upgrade Version of module -->
	</Sitepoint_Articles>
</modules>

Once you refresh your URL, Magento will find that your config.xml file contains a higher version than the version stored in the core_resource table. It will look for an upgrade script of the same version and then run it. After refreshing your URL, you should be able to see the update to your database schema.

Conclusion

This is how the Magento install and upgrade scripts work. You can give it a try by creating a new module and running the scripts – everything we’re written here is available on Github. If you have any trouble while developing your install or upgrade scripts, feel free to mention it in the comment section and we’ll discuss it! Feedback appreciated!

Frequently Asked Questions on Magento Install Upgrade Data Scripts

What are the prerequisites for upgrading Magento?

Before you start the upgrade process, it’s crucial to ensure that your system meets the requirements for the new Magento version. This includes checking the compatibility of your PHP version, MySQL, and other server configurations. Also, make sure to disable all third-party extensions as they might not be compatible with the new version. Lastly, always create a backup of your Magento store including the database, extensions, and media files to prevent any data loss during the upgrade process.

How can I backup my Magento store before upgrading?

Backing up your Magento store is a crucial step before upgrading. You can do this by using the built-in backup feature in Magento. Navigate to System > Tools > Backups. Here, you can create different types of backups: System Backup, Database and Media Backup, and Database Backup. Choose the one that suits your needs. Alternatively, you can also use third-party extensions or manual methods for backing up your Magento store.

How can I disable third-party extensions before upgrading Magento?

Disabling third-party extensions is necessary to avoid any compatibility issues during the upgrade. You can disable them by navigating to System > Configuration > Advanced > Advanced. Here, you can disable the modules by selecting ‘Disable’ from the drop-down menu. Remember to clear the cache after disabling the extensions.

What is the role of composer in Magento upgrade?

Composer is a dependency management tool in PHP. It allows you to declare the libraries your project depends on and it will manage (install/update) them for you. In the context of Magento upgrade, Composer plays a crucial role as it helps in managing the Magento system, extensions, and libraries.

How can I upgrade Magento using the command line?

Upgrading Magento using the command line involves a series of steps. First, you need to get the composer package for the Magento version you want to upgrade to. Then, you can use the ‘composer require’ command to require the new package. After that, you can run ‘composer update’ to update the packages. Finally, you can use the ‘bin/magento setup:upgrade’ command to upgrade the Magento setup.

What should I do if I encounter errors during the Magento upgrade?

If you encounter errors during the Magento upgrade, the first step is to identify the error message. The error message usually gives a hint about what went wrong. Common issues include server configuration issues, PHP version incompatibility, and issues with third-party extensions. Depending on the error, you might need to adjust your server configurations, update your PHP version, or disable incompatible extensions.

How can I verify if the Magento upgrade was successful?

After the upgrade process, you can verify if the upgrade was successful by checking the Magento version. You can do this by navigating to the footer of your Magento admin panel. Here, you should see the new Magento version. Additionally, you can also check the functionality of your store including the frontend and backend to ensure everything is working as expected.

How can I re-enable third-party extensions after upgrading Magento?

After the upgrade, you can re-enable the third-party extensions by navigating to System > Configuration > Advanced > Advanced. Here, you can enable the modules by selecting ‘Enable’ from the drop-down menu. Remember to clear the cache after enabling the extensions. However, make sure to check the compatibility of the extensions with the new Magento version before enabling them.

How often should I upgrade my Magento store?

It’s recommended to upgrade your Magento store whenever a new version is released. This is because new versions often come with new features, improvements, and security patches. However, before upgrading, always check the release notes of the new version and test the upgrade in a staging environment.

Can I downgrade my Magento store after upgrading?

Downgrading Magento to a previous version is not recommended as it can lead to data loss and compatibility issues. However, if you have a valid reason to downgrade, it’s possible by restoring a backup made before the upgrade. This is why it’s crucial to always backup your Magento store before performing an upgrade.

Chirag DodiaChirag Dodia
View Author

Chirag is a PHP developer and computer engineer from India who likes to make awesome web things. He has expertise in e-commerce and CMS platforms, while currently working on major projects involving Magento.

BrunoSinstall scriptmagentoupgrade scriptZendzend frameworkzf1
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week