Creating an SQL Installer for Your Magento Custom Module
Creating an SQL installer script is crucial when developing global Magento modules that require table creation or modifications during installation. This approach ensures your module’s database schema is set up correctly and consistently across different environments.
Why Use an SQL Installer?
Using a dedicated SQL installer offers several benefits:
- Consistency: An SQL installer guarantees the same database structure across all installations of your module.
- Portability: You can easily migrate or replicate your module’s database schema between local, staging, and production environments.
- Version Control: Keeping track of changes in the installer script allows you to manage updates to your module’s database structure effectively.
Setting Up Your Module Configuration
Step 1: Define the Resource Model
In your config.xml file, define the resource model for your module under the <models> section:
<models>
<seller>
<class>Webcreon_Seller_Model</class>
<resourceModel>seller_mysql4</resourceModel>
</seller>
<seller_mysql4>
<class>Webcreon_Seller_Model_Mysql4</class>
<entities>
<seller>
<table>db_vendor</table>
</seller>
</entities>
</seller_mysql4>
</models>
Step 2: Configure Database Connections
Configure the database connections for your module under the <resources> section:
<resources>
<!-- connection to write -->
<seller_write>
<connection>
<use>core_write</use>
</connection>
</seller_write>
<!-- connection to read -->
<seller_read>
<connection>
<use>core_read</use>
</connection>
</seller_read>
</resources>
Creating the SQL Installer Script
Step 1: Create the Install File
Create a new file named install-0.0.1.php (replace “0.0.1” with your actual module version) in the root directory of your module.
The install script should start with the following code:
<?php
/** @noinspection SqlNoDataSourceInspection */
/**
* Install script for Webcreon_Seller module
*
* Copyright (c) 2021 Webcreon. All rights reserved.
*/
$installer = $this;
$installer->startSetup();
Step 2: Create the Table
Add the SQL query to create your desired table inside a setup function:
public function setup(Mage_Core_Model_Resource_Setup $setup)
{
$table = $setup->getConnection()
->newTable($setup->getTable('db_vendor'))
->addColumn('entity_id', Table::TYPE_INTEGER, null, array(
'identity' => true,
'unsigned' => true,
'nullable' => false,
'primary' => true,
), 'Entity ID')
->addColumn('name', Table::TYPE_VARCHAR, 255, array(
'nullable' => false,
), 'Name')
->setComment('Vendor Entity');
$setup->getConnection()->createTable($table);
}
Step 3: Complete the Install Script
End your install script with:
$installer->endSetup();
What to Include in Your SQL Script
- Table creation queries: Use Magento’s data setup API (like
Table::TYPE_INTEGERandnewTable) to create tables. - Data insertion queries: If you need to populate initial data, use the setup API functions like
insertArray()or$setup->getConnection()->insertMultiple($tableName, $dataArray). - Database structure changes: Include any necessary alterations to existing tables (e.g., adding new columns, modifying data types).
Conclusion
Creating an SQL installer for your Magento custom module ensures a clean and consistent database setup across different environments. By following the outlined steps and using Magento’s built-in data setup API, you can create efficient and maintainable install scripts that keep your database schema in sync with your module.
Don’t hesitate to explore the official Magento Developer Documentation for more detailed information on working with databases in Magento 2.