It’s often necessary to update database tables when working on module. We can need to add columns or update some of them.
The following codes have to be in the installer files of the module you are working in. They can be either InstallSchema.php or UpgradeSchema.php. These files have to be placed in the Setup folder of the module.
In the following code example, we are adding a column to an existing table. We assume we are working on a newly created module that hasn’t been installed yet. Therefore we are working on the InstallSchema.php file. The implemented function is “install”.
<?php
namespace Addeos\MyModule\Setup;
use Magento\Framework\DB\Ddl\Table;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\InstallSchemaInterface;
class InstallSchema implements InstallSchemaInterface
{
public function install(SchemaSetupInterface $setup,
ModuleContextInterface $context)
{
$installer->getConnection()
->addColumn($installer->getTable('my_table_name'),
'my_column_name',
[
'type' => Table::TYPE_SMALLINT,
'nullable' => false,
'comment' => 'Write here a comment that will
be visible in when looking at the table info in mysql'
]);
}
}Here we are updating a column in the same table. We are then upgrading the module which has already been installed. Therefore, we are in the UpdgradeSchema.php file. We assume this new version will be 1.0.1. The implemented function is “upgrade” this time.
<?php
namespace Addeos\MyModule\Setup;
use Magento\Framework\DB\Ddl\Table;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\UpgradeSchemaInterface;
class UpgradeData implements UpgradeDataInterface
{
public function upgrade(SchemaSetupInterface $setup,
ModuleContextInterface $context)
{
if (version_compare($context->getVersion(), '1.0.1', '<')) {
$setup->getConnection()
->changeColumn(
$installer->getTable('my_table_name'),
'my_column_name',
'my_column_name', [
'type' => Table::TYPE_SMALLINT,
'nullable' => true,
'comment' => 'You still have to write again the comment
otherwise it be lost.'
]
);
}
}
}