Dynamically add columns in an existing table on the fly in CakePHP 3

Question

I want to add column in my existing table in CakePHP 3.

My ContactsTable.php file code:

<?php
namespace App\Model\Table;
use Cake\ORM\Table;
use Migrations\AbstractMigration;

class ContactsTable extends Table
{
    public function initialize(array $config)
    {
        $this->addBehavior('Timestamp');
        $table = $this->table('contacts');
        $table->addColumn('price', 'decimal')->update();

    }
}

I have tried as described in CakePHP 3 documentation but I got this error:

Call to a member function addColumn() on a non-object

How do I add columns on-the-fly via the controller?

Answer 1

Code:

<?php

namespace App\Controller;

use Cake\Core\Configure;
use Cake\Network\Exception\NotFoundException;
use Cake\View\Exception\MissingTemplateException;
use Cake\ORM\TableRegistry;
use Cake\Database\Schema\Table;
use Cake\Datasource\ConnectionManager;
use \Migrations\AbstractMigration as AbstractMigration;
use \Phinx\Db\Adapter\MysqlAdapter as MysqlAdapter;

class PagesController extends AppController
{
    public function display()
    {
        $connectionArray = ConnectionManager::get('default')->config();
        $connectionArray['pass'] = $connectionArray['password'];
        $connectionArray['user'] = $connectionArray['username'];
        $connectionArray['name'] = $connectionArray['database'];

        $migrationObject = new AbstractMigration(mt_rand());
        $migrationObject->setAdapter(new MysqlAdapter($connectionArray));
        $tree = $migrationObject->table('tests');
        

        $tree->addColumn('something', 'text')
                        ->update();
    }
}

After few hours of Hacking, finally found a way to do it on-the-fly.

Tested in default cakephp 3 (latest - as of today - 2nd June '16)

If you are using a different database adapter, change it to that adapater from MysqlAdapter.

Note to the users:

• This is an ugly hack and should be used ONLY if you do not work in an organization where each migration commit requires peer reference.

• mt_rand() must NEVER be used as a version number hack.

• There is no canonical way of doing it via the controllers. Update in a datasource MUST always be done modified via migrations - using a proper structure.

• Refer to Running Migrations in a non-shell environment and try to create a migrations logs under /config/migrations, that would be more rule-specific-on-the-fly and you will also have logs for peers to review.

Answer 2

Migration plugin also support Running Migrations in a non-shell environment.

Since the release of version 1.2 of the migrations plugin, you can run migrations from a non-shell environment, directly from an app, by using the new Migrations class. This can be handy in case you are developing a plugin installer for a CMS for instance. The Migrations class allows you to run the following commands from the migrations shell: migrate, rollback, markMigrated, status and seed.

Each of these commands has a method defined in the Migrations class.

You can prepare some custom handler which will accept column data from user side and run migration. In this case it could be some form with name and type inputs. Migration will be applied to DB after form with data will be submitted.

Here is how to use it:

use Migrations\Migrations;

$migrations = new Migrations();

// Will return an array of all migrations and their status
$status = $migrations->status();

// Will return true if success. If an error occurred, an exception will be thrown
$migrate = $migrations->migrate();

// Will return true if success. If an error occurred, an exception will be thrown
$rollback = $migrations->rollback();

// Will return true if success. If an error occurred, an exception will be thrown
$markMigrated = $migrations->markMigrated(20150804222900);

// Will return true if success. If an error occurred, an exception will be thrown
$seeded = $migrations->seed();

Answer 3

If you want add new column to product table e.g 'price' and price is a 'decimal' you should go to your project and write this in console:

bin/cake bake migration AddPriceToProducts price:decimal

You can see a new file e.g. Config/Migrations/20160501190410_AddPriceToProducts.php

<?php
use Migrations\AbstractMigration;

class AddPriceToProducts extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * http://docs.phinx.org/en/latest/migrations.html#the-change-method
     * @return void
     */
    public function change()
    {
        $table = $this->table('products');
        $table->addColumn('price', 'decimal', [
            'default' => null,
            ...
            'null' => true,
        ]);
        $table->update();
    }
}

and later just launch migrations to add this column to data base, write this in console:

bin/cake migrations migrate