How to add a column to a database table?

dmitry_fedyuk · January 26, 2016, 4:29pm

Use the addColumn() method of the \Magento\Framework\DB\Adapter\AdapterInterface:

magento/magento2/blob/2.1.2/lib/internal/Magento/Framework/DB/Adapter/AdapterInterface.php#L249-L261


/**
 * Adds new column to the table.
 *
 * Generally $defintion must be array with column data to keep this call cross-DB compatible.
 * Using string as $definition is allowed only for concrete DB adapter.
 *
 * @param string $tableName
 * @param string $columnName
 * @param array|string $definition  string specific or universal array DB Server definition
 * @param string $schemaName
 * @return \Magento\Framework\DB\Adapter\AdapterInterface
 */
public function addColumn($tableName, $columnName, $definition, $schemaName = null);

A usage example from the core:

github.com

magento/magento2/blob/2.1.2/app/code/Magento/CheckoutAgreements/Setup/UpgradeSchema.php#L35-L42


$column = [
    'type' => Table::TYPE_SMALLINT,
    'length' => 6,
    'nullable' => false,
    'comment' => 'Applied mode',
    'default' => '0'
];
$connection->addColumn($setup->getTable('checkout_agreement'), 'mode', $column);

By default the \Magento\Framework\DB\Adapter\AdapterInterface interface is implemented by the \Magento\Framework\DB\Adapter\Pdo\Mysql class:

github.com

magento/magento2/blob/2.1.2/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php#L848-L893


/**
 * Adds new column to table.
 *
 * Generally $defintion must be array with column data to keep this call cross-DB compatible.
 * Using string as $definition is allowed only for concrete DB adapter.
 * Adds primary key if needed
 *
 * @param   string $tableName
 * @param   string $columnName
 * @param   array|string $definition  string specific or universal array DB Server definition
 * @param   string $schemaName
 * @return  true|\Zend_Db_Statement_Pdo
 * @throws  \Zend_Db_Exception
 */
public function addColumn($tableName, $columnName, $definition, $schemaName = null)
{
    if ($this->tableColumnExists($tableName, $columnName, $schemaName)) {
        return true;
    }


    $primaryKey = '';
    if (is_array($definition)) {
        $definition = array_change_key_case($definition, CASE_UPPER);
        if (empty($definition['COMMENT'])) {
            throw new \Zend_Db_Exception("Impossible to create a column without comment.");
        }
        if (!empty($definition['PRIMARY'])) {
            $primaryKey = sprintf(', ADD PRIMARY KEY (%s)', $this->quoteIdentifier($columnName));
        }
        $definition = $this->_getColumnDefinition($definition);
    }


    $sql = sprintf(
        'ALTER TABLE %s ADD COLUMN %s %s %s',
        $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
        $this->quoteIdentifier($columnName),
        $definition,
        $primaryKey
    );


    $result = $this->rawQuery($sql);


    $this->resetDdlCache($tableName, $schemaName);


    return $result;
}

dmitry_fedyuk · January 26, 2016, 4:31pm

mage2pro/core/blob/1.8.13/Core/lib/db-column.php?ts=4#L2-L27


/**
* 2016-11-04
* «How to add a column to a database table?» https://mage2.pro/t/562
* @param string $table
* @param string $name
* @param string $definition [optional]
* @return void
*/
function df_db_column_add($table, $name, $definition = 'varchar(255) default null') {
	/**
	 * 2016-11-04
	 * @uses df_table() call is required here,
	 * because @uses \Magento\Framework\DB\Adapter\Pdo\Mysql::addColumn() method
	 * does not add the custom table prefix to the $name.
	 * The custom table prefix could be set my a Magento 2 administrator
	 * during Magento 2 intallation (see the «table_prefix» key in the app/etc/env.php file).
	 */
	df_conn()->addColumn(df_table($table), $name, $definition);
	/**
	 * 2016-11-04
	 * @see \Magento\Framework\DB\Adapter\Pdo\Mysql::resetDdlCache() call is not needed here,
	 * because it has already been called
	 * from @uses \Magento\Framework\DB\Adapter\Pdo\Mysql::addColumn()
	 * https://github.com/magento/magento2/blob/2.1.2/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php#L890
	 */
}