4

I need to add a new column after one of the particular columns in the existing table using DB schema & checked this dev doc link, they didn't mention about the before or after column.

Is there any way to do this?

6

1 Answer 1

0

This is an old question but for the sake of others who find this looking for an answer - Do not do this unless you run on Mysql 8.0.29 or after, or another RDS which supports instant add (I'd generally just suggest not doing it at all, column order is often irrelevant). Be careful of things like Postgrel 10 vs 11 where in v10 if you use default NULL it isnt supported, but in v11 it is.

This is why Magento does not supply this functionality because it can have a severe impact on the next run of setup:upgrade or setup:db-schema:upgrade for versions of Mysql that come before this, or others RDS's that do not support ALGORITHM=instant for column adds or column adds with specific boundaries (such as using before/after, default null, etc).

In older version of MYSQL altering tables required locking the table and then implementing the change - the more rows you have the longer this process takes. I mostly see this implemented on the order grid to disastrous effect as they can often have 10s of millions of rows leading to a very long downtime, or if you use blue/green configuration it can lead to hampered site performance or loss of specific functionality for minutes to hours. (As a side note, dont add to core tables, use extension or map tables if necessary)

However if you wish to do this, which I suggest not as its mostly for vanity, but if you still wish to go ahead then you can create it manually or use an UpgradeSchema with something like the below

<?php 
declare(strict_types = 1);

namespace Vendor\Module\Setup;

use Magento\Framework\DB\Ddl\Table;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\UpgradeSchemaInterface;


class UpgradeSchema implements UpgradeSchemaInterface {
    
    public function upgrade(
        SchemaSetupInterface $setup,
        ModuleContextInterface $context
    ) {
        // Switch this for a version check and/or if columns exists check
        if (version_compare($context–>getVersion(), '1.0.1') < 0) {
            $connection = $setup–>getConnection();
            $connection–>addColumn(
                $setup–>getTable($targetTableName),
                $yourNewColumnName,
                    [
                        'type' => Table::TYPE_TEXT,
                        'nullable' => true,
                        'length' => 255,
                        'default' => '',
                        'comment' => $yourNewColumnDescription
                        'after'   => $yourTargetColumn
                    ]
            );
        }
    }
}

I highly suggest sticking to db-schema.xml to avoid the need for a migration set after this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.