Magento 2 : How to add and update a database table column

Magento 2 : How to add and update a database table column

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.'
                    ]
                );
        }
        
    }
}
berliozd