v3qg2q
Last Updated: February 25, 2019
·
15.02K
· aimfeld

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):
Picture

After migration (table token_links):
Picture

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

2 Responses
Add your response

27354

Thanks for your sharing, nice tool to db migration... Can you please help me out.. This is working fine with terminal creating tables and seeding data but I was to see some output on the browser from this code, is it possible to config it for var_dump.

over 1 year ago ·
31962

Phinx is definitely a great tool for db migration. When doing PHP migration to another server, the biggest problem is moving the database. There are chances that you will get error when doing it. But if you are using this tool, there are less chances of getting any error.

3 months ago ·