Complex database migrations with PHP/MySQL using Phinx

Until now, I used dbdeploy to do DB migrations with MySQL statements. However, MySQL is very inconvenient to perform more complex migration steps, e.g. if you want to migrate not only the table schema, but also the data. Consider the following migration where values are be encoded into a json array:

Before migration (table user_links):

After migration (table token_links):

Luckily, there's a new amazing DB migration tool called phinx. It supports PHP, MySQL, composer , and is easy to integrate with phing.

Here's how I performed the above migration (shitty formatting because of little space):

use Phinx\Migration\AbstractMigration;

/** */
class UserLinksToTokenLinks extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $this->execute("
CREATE TABLE IF NOT EXISTS `token_links` (
  `linkID` INT(11) unsigned NOT NULL AUTO_INCREMENT,
  `token` VARCHAR(100) NOT NULL,
  `actionPlugin` VARCHAR(32) NOT NULL,
  `actionParams` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`linkID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
        ");

        // userLinks to tokenLinks 
        $userLinks = $this->fetchAll('SELECT * FROM user_links');
        foreach ($userLinks as $userLink) {
            $actionPlugin = ucfirst($userLink['action']);
            $actionParams = array();
            $actionParams['UserID'] = $userLink['userID'];

            if (isset($userLink['userSurveyID'])) {
                $actionParams['UserSurveyID'] = $userLink['userSurveyID'];
            }
            $jsonParamString = json_encode($actionParams);

            $this->execute("
INSERT INTO `token_links` (`linkID`, `token`, `actionPlugin`, `actionParams`) VALUES
({$userLink['userLinkID']}, '{$userLink['token']}', '$actionPlugin', '$jsonParamString');
            ");
        }


        $this->dropTable('user_links');
    }

    /**
     * Migrate Down.
     */
    public function down()
    {
        $this->execute("
DROP TABLE IF EXISTS `user_links`;
CREATE TABLE IF NOT EXISTS `user_links` (
  `userLinkID` INT(11) unsigned NOT NULL AUTO_INCREMENT,
  `token` VARCHAR(100) NOT NULL,
  `userID` INT(11) unsigned NOT NULL,
  `userSurveyID` INT(11) unsigned DEFAULT NULL,
  `action` VARCHAR(16) NOT NULL,
  PRIMARY KEY (`userLinkID`),
  UNIQUE KEY `token` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
        ");

        // userLinks to tokenLinks 
        $tokenLinks = $this->fetchAll('SELECT * FROM token_links');
        foreach ($tokenLinks as $tokenLink) {
            $actionParams = json_decode($tokenLink['actionParams'], true);
            $action = strtolower($tokenLink['actionPlugin']);
            $userSurveyID = isset($actionParams['UserSurveyID']) ? 
                $actionParams['UserSurveyID'] : 'NULL';

            $this->execute("
INSERT INTO `user_links` (`userLinkID`, `token`, `userID`, `userSurveyID`, `action`) VALUES
({$tokenLink['linkID']}, '{$tokenLink['token']}', {$actionParams['UserID']}, $userSurveyID, '$action');
            ");
        }


        $this->dropTable('token_links');
    }
}