生きて腸まで届くマイグレーションツール Phinx

こんにちは。メドピアCTO室 @kenzo0107 です。

Join して4ヶ月、
日々新たな技術に触れ、赤い実弾かせながら過ごしております。

今回は開発環境 DB をコンテナ化した際に使用した Phinx についてです。

Phinx って何？

phinx.org

• PHP (>=5.4) でコーディングできるDBマイグレーション&シードツール
• composer 管理
• CakePHP 2.x 系の　schema.php の様なファイルを作る必要がない
• DB の向け先は yml で複数管理可能
• F/W に依存しない
• MySQL, PostgreSQL, SQL Server, SQLite に対応

Phinx 採用経緯

メドピアでは以下の様な課題を鑑みてマイグレーションツールを模索していました。

• PHP 5.x系 で運用している独自F/Wがある*1
• 既に DB が大規模 (スキーマ数 50程度)
• シード機能も欲しい
• マイグレーション/シード機能のないF/Wで運用しているプロジェクトにも適用可能であればしたい
• PostgreSQL, SQL Server で運用しているプロジェクトにも適用可能であればしたい

元々、
個々人用の開発 DB を本番 DB からセキュリティ上データをマスクした上で
同期する様な機能も検討していましたが
必要最低限で開発ができる状態であれば良いという総意から
シード機能も合わせて求める様になりました。

Phinx はこれらの課題を網羅しており、軽量で使い勝手がよかった為採用に至りました。

Phinx は初めてという方、
既に比較検討されている方、
といらっしゃると思いますので簡単に使い勝手を試していただく意味でも
弊社の利用方法と合わせて実践チュートリアルとして git を用意しました。

検証環境

• OSX 10.12.5
• Vagrant 1.9.3
• VirtualBox 5.1.18
• Docker version 17.03.1-ce, build c6d412e
• docker-compose version 1.11.2, build dfed245

やること

• 複数の DB スキーマへのマイグレーション実行
• 複数の DB スキーマへのシード実行

システム概要図

Docker on Vagrant で動作確認していきます。

無事 Moby Dock 達の抱える DB 達にデータが届くか試してみたいと思います。

事前準備

macOS%$ git clone https://github.com/medpeer-inc/phinx
macOS%$ cd phinx
macOS%$ vagrant up
macOS%$ vagrant ssh
vagrant%$ cd /vagrant

Adminer, Phinx, DB コンテナ起動

vagrant%$ docker-compose up --build -d
vagrant%$ docker-compose ps

        Name                      Command               State            Ports
---------------------------------------------------------------------------------------
      Name               Command              State               Ports
-----------------------------------------------------------------------------
        Name                      Command               State            Ports
---------------------------------------------------------------------------------------
vagrant_adminer_1      entrypoint.sh docker-php-e ...   Up       0.0.0.0:80->8080/tcp
vagrant_db-migrate_1   phinx --help                     Exit 0
vagrant_mysql_1        docker-entrypoint.sh mysqld      Up       0.0.0.0:3306->3306/tcp
vagrant_pgsql_1        docker-entrypoint.sh postgres    Up       0.0.0.0:5432->5432/tcp
vagrant_sqlsvr_1       /bin/sh -c /opt/mssql/bin/ ...   Up       0.0.0.0:1433->1433/tcp

• db-migrate コンテナは one-off コンテナとして利用する為、Exit 0 で問題ありません。ビルドするのが目的です。
• DB は MySQL, PostgreSQL, MSSQL を用意しました。

今回は MySQL をメインに進めたいと思います。

0. DB作成

MySQL, PostgreSQL コンテナではコンテナ起動時に
hogehoge, mogemoge の 2つの DB Schema を作成する様設定しています。

• docker-compose.yml

...
  mysql:
    image: mysql:5.7
    environment:
    - MYSQL_ROOT_PASSWORD=rootpass
    - MYSQL_DATABASE=hogehoge
    - MYSQL_USER=developer
    - MYSQL_PASSWORD=pass
    volumes:
    - db-data:/var/lib/mysql
    - ./db/conf.d/my.cnf:/etc/mysql/conf.d/my.cnf
    - ./db/initdb.d:/docker-entrypoint-initdb.d
    ports:
    - 3306:3306

• db/initdb.d/01_structure.sql

CREATE DATABASE `mogemoge`;

MySQL, PostgreSQL 公式 Docker コンテナでは
/docker-entrypoint-initdb.d 以下の SQL を起動時に実行する為
そちらに DB mogemoge を作成するよう設定しました。

これより 各 DB にテーブルを作成していきます。

1. テーブル作成

テーブル定義ファイル作成

DB hogehoge に users テーブルを
DB mogemoge に members テーブルを
作成する Phinx 定義ファイルを作成します。

定義ファイルのクラス名はキャメル形式限定です。

$ make migrate_create DB=hogehoge CLASS=CreateTableUsers
$ make migrate_create DB=mogemoge CLASS=CreateTableMembers
...
...
created db/migrations/hogehoge/20170724065658_create_table_users.php
created db/migrations/mogemoge/20170724065738_create_table_members.php

db/migrations 内に各 DB 毎のディレクトリが作成され、その配下に Phinx 定義ファイルが作成されているのが確認できます。

テーブル定義ファイル編集

• db/migrations/hogehoge/20170724065658_create_table_users.php

<?php

use Phinx\Migration\AbstractMigration;
use Phinx\Db\Adapter\MysqlAdapter;

class CreateTableUsers extends AbstractMigration
{
    public function up()
    {
        // 自動生成される id を排除し、primary key を user_id とする
        $t = $this->table('users', ['id' => 'user_id']);

        $t->addColumn('last_name',       'string',     ['limit' => 10,  'comment' => '姓'])        // string 型 20文字制限
          ->addColumn('first_name',      'string',     ['limit' => 10,  'comment' => '名'])        // string 型 20文字制限
          ->addColumn('last_kana_name',  'string',     ['null' => true, 'limit' => 10,  'comment' => '姓（カナ）']) // string 型 NULL許可 10文字制限
          ->addColumn('first_kana_name', 'string',     ['null' => true, 'limit' => 10,  'comment' => '名（カナ）']) // string 型 NULL許可 10文字制限
          ->addColumn('username',        'string',     ['limit' => 20,  'comment' => 'ユーザ名'])   // string 型 20文字制限
          ->addColumn('password',        'string',     ['limit' => 40,  'comment' => 'パスワード']) // string 型 40文字制限
          ->addColumn('email',           'string',     ['limit' => 100, 'comment' => 'Email'])    // string 型 100文字制限
          ->addColumn('postcode',        'string',     ['limit' => 10,  'comment' => '郵便番号'])   // string 型 10文字制限
          ->addColumn('birthday',        'date',       ['comment' => '誕生日'])                    // date 型
          ->addColumn('gender',          'integer',    ['limit' => MysqlAdapter::INT_TINY, 'comment' => '性別(1:男 2:女 3:その他)']) // tinyint 型
          ->addColumn('card_number',     'string',     ['null' => true, 'limit' => 20,  'comment' =>'クレジットカードNo'])  // string 型 20文字制限 NULL許可
          ->addColumn('description',     'string',       ['null' => true, 'limit' => 255, 'comment' =>'説明'])  // string 型 255文字制限 NULL許可
          ->addColumn('created',         'timestamp',  ['default' => 'CURRENT_TIMESTAMP'])        // timestamp 型 default: CURRENT_TIMESTAMP
          ->addColumn('updated',         'datetime',   ['null' => true])                          // datetime 型 NULL 許可
          ->addIndex(['username', 'email'],     ['unique' => true])                               // username, email にユニークキー設定
          ->create();
    }

    public function down()
    {
        $this->dropTable('users');
    }
}

• db/migrations/mogemoge/20170724065738_create_table_members.php

<?php

use Phinx\Migration\AbstractMigration;

class CreateTableMembers extends AbstractMigration
{
    public function up()
    {
        $t = $this->table('members');
        $t->addColumn('member_code', 'string',    ['limit' => 20,  'comment' => '会員コード'])   // string 型 20文字制限
          ->addColumn('created',     'timestamp', ['default' => 'CURRENT_TIMESTAMP'])        // timestamp 型 default: CURRENT_TIMESTAMP
          ->addColumn('updated',     'datetime',  ['null' => true])                          // datetime 型 NULL 許可
          ->addIndex(['member_code'], ['unique' => true])                                    // member_code にユニークキー設定
          ->create();
    }

    public function down()
    {
        $this->dropTable('members');
    }
}

2. カラム追加

テーブル定義ファイル作成

DB hogehoge の users テーブルにカラムを追加したいと思います。

$ make migrate_create DB=hogehoge CLASS=AddTableUsersColumnsCity
...
...
created db/migrations/hogehoge/20170724065838_add_table_users_columns_city.php

テーブル定義ファイル編集

カラム postcode の後にカラム city 追加します。

<?php

use Phinx\Migration\AbstractMigration;

class AddTableUsersColumnsCity extends AbstractMigration
{
    public function up()
    {
        $t = $this->table('users');
        $t->addColumn('city', 'string', ['limit' => 10, 'comment' => '都市', 'after' => 'postcode'])
          ->update();
    }

    public function down()
    {
        $t = $this->table('users');
        $t->removeColumn('city')
          ->save();
    }
}

マイグレーション実施

$ make migrate

Point !

ちなみにマイグレーションの実行順序は
配置されているファイルの数字・アルファベット順です。
以下の様な仕様となっています。

Aogehoge
H001gehoge
H01gehoge
H0gehoge
H1gehoge
H2gehoge
Hogehoge

テーブル確認

http://192.168.35.102/ へアクセスすると
Adminer のログインページが表示されます。

※ docker-compose.yml で定義されているログイン情報

Key	Value
System	MySQL
Server	mysql
User	root
Password	rootpass
Database	hogehoge

サーバ情報を入力しログインすると作成されたテーブルを確認することができます。

users テーブルをクリックし詳細を確認します。

問題なく定義通りに作成されたことがわかります。

では、DB mogemoge はどうでしょうか。

DB mogemoge にも members テーブルが作成されていることが確認できます。

Point !

各 DB に phinxlog テーブルが作成されています。

マイグレーション実行状況のステータスを管理しています。

• DB hogehoge.phinxlog

• DB mogemoge.phinxlog

DB スキーマ毎に phinxlog テーブルを分けた理由としては
将来的に DB スキーマごとお引越しする、もしくは、ドロップするとういう時に
都合が良い為です。

また、以下の様に DB スキーマを指定しテーブル作成はできますが

$t = $this->table('hogehoge.users');
$t->addColumn(...
  ->create();

カラム追加時には以下の様に DB スキーマを指定した場合には実行できない為、
複数 DBスキーマの場合は 1つの phinxlog での管理は現実的でないと考えました。

$t = $this->table('hogehoge.users');
$t->addColumn('city', 'string', ['limit' => 10, 'comment' => '都市', 'after' => 'postcode'])
  ->update();

シード作成

シード定義ファイル作成

$ make seed_create DB=hogehoge CLASS=UserSeeder
$ make seed_create DB=mogemoge CLASS=MembersSeeder
...
...
created ./db/seeds/hogehoge/UsersSeeder.php
created ./db/seeds/mogemoge/MembersSeeder.php

シード定義ファイル編集

• ./db/seeds/hogehoge/UsersSeeder.php

<?php

use Phinx\Seed\AbstractSeed;

class UsersSeeder extends AbstractSeed
{
    public function run()
    {
        $t = $this->table('users');
        $t->truncate();

        $genders = [1,2,3];

        $faker = Faker\Factory::create('ja_JP');
        $d = [];
        for ($i = 0; $i < 10; $i++) {
            $d[] = [
                'last_name'        => $faker->lastName(10),
                'first_name'       => $faker->firstName(10),
                'last_kana_name'   => $faker->lastKanaName(10),
                'first_kana_name'  => $faker->firstKanaName(10),
                'username'         => $faker->userName(20),
                'password'         => sha1($faker->password),
                'email'            => $faker->email,
                'postcode'         => $faker->postcode,
                'city'             => $faker->city,
                'birthday'         => $faker->date($format='Y-m-d',$max='now'),
                'gender'           => $faker->randomElement($genders),
                'card_number'      => $faker->creditCardNumber,
                'description'      => $faker->text(200),
                'created'          => date('Y-m-d H:i:s'),
                'updated'          => date('Y-m-d H:i:s'),
            ];
        }

        $this->insert('users', $d);
    }
}

• ./db/seeds/hogehoge/MembersSeeder.php

<?php

use Phinx\Seed\AbstractSeed;

class MembersSeeder extends AbstractSeed
{
    public function run()
    {
        $t = $this->table('members');
        $t->truncate();

        $faker = Faker\Factory::create('ja_JP');
        $d = [];
        for ($i = 0; $i < 10; $i++) {
            $d[] = [
                'member_code'  => $faker->regexify('[0-9]{20}'),
                'created'   => date('Y-m-d H:i:s'),
                'updated'   => date('Y-m-d H:i:s'),
            ];
        }

        $this->insert('members', $d);
    }
}

Faker というライブラリを利用することで 日本人の名前や住所、正規表現を使ったデータを作成できます。

シード実行

$ make seed

無事データが登録されました。

おまけ 1

ここで Phinx の seed のデータ INSERT 方法が非常に気になりました。

...
...
 -- insert('members')
    -> 0.0023s
 -- insert('members')
    -> 0.0016s
 -- insert('members')
    -> 0.0019s
 -- insert('members')
    -> 0.0022s
...
...

1件ずつ INSERT してる...？

本家 Phinx github のソースを確認してみました。

• src/Phinx/Db/Table.php

    /**
     * Commit the pending data waiting for insertion.
     *
     * @return void
     */
    public function saveData()
    {
        foreach ($this->getData() as $row) {
            $this->getAdapter()->insert($this, $row);
        }
    }

データを foreach して 1件ずつ登録している！
なんて日だ！

数十件ならまだ良いですが
シードデータも増えてくると待ち時間が増えてくるのは宜しくない。

と言うことで
バルクインサートする様修正しプルリクした所無事マージされました*2

https://github.com/cakephp/phinx/pull/1148/files

おまけ 2

SQL を直接実行することも可能です。

    public function up()
    {
        $q = <<<EOF
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(10) NOT NULL COMMENT '姓',
  `first_name` varchar(10) NOT NULL COMMENT '名',
  `last_kana_name` varchar(10) DEFAULT NULL COMMENT '姓（カナ）',
  `first_kana_name` varchar(10) DEFAULT NULL COMMENT '名（カナ）',
  `username` varchar(20) NOT NULL COMMENT 'ユーザ名',
  `password` varchar(40) NOT NULL COMMENT 'パスワード',
  `email` varchar(100) NOT NULL COMMENT 'Email',
  `postcode` varchar(10) NOT NULL COMMENT '郵便番号',
  `birthday` date NOT NULL COMMENT '誕生日',
  `gender` tinyint(4) NOT NULL COMMENT '性別(1:男 2:女 3:その他)',
  `card_number` varchar(20) DEFAULT NULL COMMENT 'クレジットカードNo',
  `description` longtext COMMENT '説明',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`,`email`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;
        $this->execute($q);
    }

Phinx のお作法に則らないパワープレイではありますが
結局頭の中で実クエリに変換している脳内工数を考えると
これもアリかなと♪

議論の分かれる所かと思います。

もう一踏ん張りな所

• TINY INT(3) の様なタイプ指定ができない (?)
• ユニーク制御しているカラムへの Faker でランダムデータ生成では Duplicate Error 発生の懸念あり (←Fakerの話)

よかった所

• 様々なタイプの DB へ適合
• Faker 利用で日本語対応のデータ生成可
• 比較的学習コスト低

まとめ

あらゆる DB への適合し今後とも善玉マイグレーションツールとして
期待される Phinx、如何でしたでしょうか？

PostgreSQL, MSSQL も同じ定義ファイルからマイグレーション・シードが実行でき、
無事 Moby Dock の腸までデータが届くことを確認しております。

是非お試しください♪

ご参考になれば幸いです。

参照

• Writing Migrations — Phinx 0.8.1 documentation

---

メドピアでは一緒に働く仲間を募集しています。 ご応募をお待ちしております！

■募集ポジションはこちら

https://medpeer.co.jp/recruit/entry/

■開発環境はこちら

https://medpeer.co.jp/recruit/workplace/development.html

*1:Rails 移行中

*2:1000 件程度のデータのシード実行では 6倍以上パフォーマンスが向上していることを確認しています。