I used Phinx, DB migration Tool on Docker!
2017-07-21

I used Phinx, DB migration Tool on Docker!

Overview

This is Sandbox for DB Migration Tool Phinx.

Preparation

1
2
$ git clone https://github.com/kenzo0107/phinx-mysql
$ cd phinx-mysql

Create and Run Containers of Phinx, DB (MySQL).

1
$ make build
1
2
3
4
5
6
$ docker-compose ps

         Name                       Command             State     Ports
-------------------------------------------------------------------------
phinxmysql_db-migrate_1   phinx --help                  Exit 0
phinxmysql_db_1           docker-entrypoint.sh mysqld   Up       3306/tcp

The Container db-migrate is used as for one-off container, so its state is Exit 0.

Initialize Phinx Project

Phinx creates a default file called phinx.yml.

1
$ make init

In default setting, phinx select development environment.

1. Create Table

Create phinx definition file

1
2
3
4
5
6
$ make create DB=hogehoge CLASS=CreateTableUsers
$ make create DB=mogemoge CLASS=CreateTableMembers
...
...
created db/migrations/hogehoge/20170724065658_create_table_users.php
created db/migrations/mogemoge/20170724065738_create_table_members.php

Edit phinx definition file

  • db/migrations/hogehoge/20170724065658_create_table_users.php

Writing Migrations

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<?php

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

class CreateTableUsers extends AbstractMigration
{
    public function up()
    {
        // Automatically generated id is excluded, and primary key is set as user_id
        $t = $this->table('users', ['id' => 'user_id']);

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

    public function down()
    {
        $this->dropTable('users');
    }
}
  • db/migrations/mogemoge/20170724065738_create_table_members.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?php

use Phinx\Migration\AbstractMigration;

class CreateTableMembers extends AbstractMigration
{
    public function up()
    {
        $t = $this->table('members');
        $t->addColumn('member_code', 'string',    ['limit' => 20,  'comment' => '会員コード'])
          ->addColumn('created',     'timestamp', ['default' => 'CURRENT_TIMESTAMP'])
          ->addColumn('updated',     'datetime',  ['null' => true])
          ->addIndex(['member_code'], ['unique' => true])
          ->create();
    }

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

2. Add Column

Create phinx definition file

1
2
3
4
$ make create CLASS=AddTableUsersColumnsCity
...
...
created db/migrations/hogehoge/20170724065838_add_table_users_columns_city.php

Edit phinx definition file

  • db/migrations/hogehoge/20170724065838_add_table_users_columns_city.php

Add the column city after the column email.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?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();
    }
}

Migration

1
$ make migrate
  • Result
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
mysql> use hogehoge
mysql> show full columns from users;
+-----------------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+---------------------------------+
| Field           | Type         | Collation       | Null | Key | Default           | Extra| Privileges                      | Comment                         |
+-----------------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+---------------------------------+
| user_id         | int(11)      | NULL            | NO   | PRI | NULL              | auto_increment| select,insert,update,references |                                 |
| last_name       | varchar(10)  | utf8_general_ci | NO   |     | NULL              || select,insert,update,references | 姓                              |
| first_name      | varchar(10)  | utf8_general_ci | NO   |     | NULL              || select,insert,update,references | 名                              |
| last_kana_name  | varchar(10)  | utf8_general_ci | YES  |     | NULL              || select,insert,update,references | 姓（カナ）                      |
| first_kana_name | varchar(10)  | utf8_general_ci | YES  |     | NULL              || select,insert,update,references | 名（カナ）                      |
| username        | varchar(20)  | utf8_general_ci | NO   | MUL | NULL              || select,insert,update,references | ユーザ名                        |
| password        | varchar(40)  | utf8_general_ci | NO   |     | NULL              || select,insert,update,references | パスワード                      |
| email           | varchar(100) | utf8_general_ci | NO   |     | NULL              || select,insert,update,references | Email                           |
| city            | varchar(255) | utf8_general_ci | NO   |     | NULL              || select,insert,update,references |                                 |
| postcode        | varchar(10)  | utf8_general_ci | NO   |     | NULL              || select,insert,update,references | 郵便番号                        |
| birthday        | date         | NULL            | NO   |     | NULL              || select,insert,update,references | 誕生日                          |
| gender          | tinyint(4)   | NULL            | NO   |     | NULL              || select,insert,update,references | 性別(1:男 2:女 3:その他)        |
| card_number     | varchar(20)  | utf8_general_ci | YES  |     | NULL              || select,insert,update,references | クレジットカードNo              |
| description     | longtext     | utf8_general_ci | YES  |     | NULL              || select,insert,update,references | 説明                            |
| created         | timestamp    | NULL            | NO   |     | CURRENT_TIMESTAMP || select,insert,update,references |                                 |
| updated         | datetime     | NULL            | YES  |     | NULL              || select,insert,update,references |                                 |
+-----------------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+---------------------------------+


mysql> use mogemoge
mysql> show full columns from members;
+-------------+-------------+-----------------+------+-----+-------------------+----------------+---------------------------------+-----------------+
| Field       | Type        | Collation       | Null | Key | Default           | Extra          | Privileges                      | Comment         |
+-------------+-------------+-----------------+------+-----+-------------------+----------------+---------------------------------+-----------------+
| id          | int(11)     | NULL            | NO   | PRI | NULL              | auto_increment | select,insert,update,references |                 |
| member_code | varchar(20) | utf8_general_ci | NO   | UNI | NULL              |                | select,insert,update,references | 会員コード      |
| created     | timestamp   | NULL            | NO   |     | CURRENT_TIMESTAMP |                | select,insert,update,references |                 |
| updated     | datetime    | NULL            | YES  |     | NULL              |                | select,insert,update,references |                 |
+-------------+-------------+-----------------+------+-----+-------------------+----------------+---------------------------------+-----------------+

Rollback

1
$ make rollback

3. Create sample seeds for Multi Databases;

Create phinx definition file

1
2
3
4
5
6
$ 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

Edit phinx definition file

  • ./db/seeds/hogehoge/UsersSeeder.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<?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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?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);
    }
}

Run seed

1
$ make seed
  • Result
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
mysql> use hogehoge;
mysql> select * from users;
+---------+-----------+------------+-----------------+-----------------+-------------+------------------------------------------+------------------------------+----------+--------------+------------+--------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+
| user_id | last_name | first_name | last_kana_name  | first_kana_name | username    | password                                 | email          | postcode | city         | birthday   | gender | card_number      | description                                                                                                                    | created             | updated           |
+---------+-----------+------------+-----------------+-----------------+-------------+------------------------------------------+------------------------------+----------+--------------+------------+--------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+
|       1 | 佐々木    | 零         | ヤマダ          | カナ            | akira97     | e270038c94f231da7bca25dead3e386ba3984491 | hirokawa.rika@hotmail.co.jp  | 1867251  | 佐々木市     | 1987-09-25 |      1 | 4024007116991463 | Dolor reiciendis fuga fugiat id molestiae eos. Dolores sint rem repudiandae perspiciatis. Ducimus aut mollitia aut asperiores laboriosam.                                                 | 2017-07-25 12:22:50 | 2017-07-25 12:22:50 |
|       2 | 宮沢      | 千代       | ノムラ          | ヨウイチ        | nagisa.taro | 695a90d1b84cf004357aad3eb37697b51afbf5cc | tanabe.hiroshi@kudo.org      | 8639535  | 江古田市     | 1977-06-01 |      3 | 344103919563863  | Doloribus et recusandae quam accusantium pariatur nobis reiciendis quo. Eos quae et commodi quos accusamus ex. Ullam repellendus maiores vero sit sit et.                                 | 2017-07-25 12:22:50 | 2017-07-25 12:22:50 |
|       3 | 斉藤      | 充         | ミヤケ          | オサム          | kana.suzuki | f309f34d08b4d0d686863fa38ed3d3af5e0b2104 | kana.kudo@mail.goo.ne.jp     | 2763622  | 青田市       | 1997-01-30 |      1 | 4716886227252    | Veritatis voluptatem pariatur libero aut quia. Facere nemo quos enim amet ut ipsum sequi. Nobis natus et aspernatur aut. Natus pariatur deserunt voluptatum deserunt.                     | 2017-07-25 12:22:50 | 2017-07-25 12:22:50 |
|       4 | 吉田      | 太郎       | ウノ            | ツバサ          | naoko.uno   | 45d04bda7ac79244c90a33ff68798b979138054a | taro.nagisa@hirokawa.com     | 6099661  | 江古田市     | 2006-03-19 |      2 | 5372535333698250 | Nostrum velit nostrum eos magni. Reiciendis quos enim adipisci quisquam sed voluptas. Necessitatibus sint qui dolorem animi impedit consectetur commodi.                                  | 2017-07-25 12:22:50 | 2017-07-25 12:22:50 |
|       5 | 野村      | 亮介       | サトウ          | ミノル          | rika.tanabe | dd3d50714c0775bfee453f7d9a9815ce26ba57db | wkudo@hotmail.co.jp          | 6966314  | 渡辺市       | 1985-12-21 |      1 | 4929108488987091 | Id atque molestiae expedita omnis libero natus et. Repellendus ut tenetur molestias voluptas. Perspiciatis nisi et illum aut aut vel repudiandae.                                         | 2017-07-25 12:22:50 | 2017-07-25 12:22:50 |
|       6 | 木村      | 裕美子     | タナカ          | ヒロキ          | hiroshi53   | 033bfd0493b72efd0ff60bc15c7eeb3b2e054501 | ztanabe@tanabe.biz          | 3155238  | 山田市       | 1996-01-02 |      3 | 5476616628100007 | Assumenda consectetur ea sed et omnis alias fugiat quo. Porro nihil similique sint laudantium asperiores blanditiis. Error dolores vitae quia explicabo facilis deleniti distinctio.      | 2017-07-25 12:22:50 | 2017-07-25 12:22:50 |
|       7 | 吉本      | 陽一       | キムラ          | ヒデキ          | akira27     | 51de6afc65f535ae58f927d698f07e60e04c7746 | rika59@suzuki.com          | 6457702  | 田辺市       | 2010-04-12 |      2 | 5388155063289311 | Nesciunt qui beatae ut officia qui error autem. Temporibus alias earum ullam incidunt quo recusandae enim qui. Sed atque veritatis sed ad ullam qui. Repellendus est nostrum et pariatur. | 2017-07-25 12:22:50 | 2017-07-25 12:22:50 |
|       8 | 渡辺      | 翔太       | ササダ          | クミコ          | uno.momoko  | fa2d16d5f2acffd5aeeaab6791fe64c9f70a9b2f | stanabe@uno.com          | 5849600  | 伊藤市       | 2012-06-09 |      1 | 5274550197820022 | Odio quasi sunt tempora. Molestias aut qui sed quos beatae eum accusantium. Non dolores quam veniam et ab quidem nostrum repellendus. Qui ducimus et optio et.                            | 2017-07-25 12:22:50 | 2017-07-25 12:22:50 |
|       9 | 坂本      | 翔太       | ナカツガワ      | ナオキ          | akira.kudo  | 4af41e536bf19fa3cb0527304adad0de76260e82 | suzuki.momoko@mail.goo.ne.jp | 8609563  | 宮沢市       | 2005-10-23 |      3 | 5231530310398512 | Qui id neque molestiae facere aut et consequatur. Delectus ea voluptatibus provident atque assumenda maxime eum. At quidem sint accusamus. Eaque sed voluptate quo sint non non.          | 2017-07-25 12:22:50 | 2017-07-25 12:22:50 |
|      10 | 野村      | 翼         | ヒロカワ        | ナオコ          | taro.kudo   | f8a63d0010c99d6403e0c1f458005b934ec03f8c | kana.tanabe@mail.goo.ne.jp   | 5804069  | 桐山市       | 1988-12-25 |      2 | 5140671281503530 | Dolorem consequatur nulla alias perspiciatis ut. Tenetur modi cumque incidunt dolor.                                                                                                      | 2017-07-25 12:22:50 | 2017-07-25 12:22:50 |
+---------+-----------+------------+-----------------+-----------------+-------------+------------------------------------------+------------------------------+----------+--------------+------------+--------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+



mysql> use mogemoge;
mysql> select * from members;

+----+----------------------+---------------------+---------------------+
| id | member_code          | created             | updated             |
+----+----------------------+---------------------+---------------------+
|  1 | 86190539096622228312 | 2017-07-25 12:22:51 | 2017-07-25 12:22:51 |
|  2 | 77322186584623078448 | 2017-07-25 12:22:51 | 2017-07-25 12:22:51 |
|  3 | 17169562241415794809 | 2017-07-25 12:22:51 | 2017-07-25 12:22:51 |
|  4 | 86738824931379981947 | 2017-07-25 12:22:51 | 2017-07-25 12:22:51 |
|  5 | 23125815173540252188 | 2017-07-25 12:22:51 | 2017-07-25 12:22:51 |
|  6 | 81839177491562485300 | 2017-07-25 12:22:51 | 2017-07-25 12:22:51 |
|  7 | 82938165381845652192 | 2017-07-25 12:22:51 | 2017-07-25 12:22:51 |
|  8 | 87208503292784158954 | 2017-07-25 12:22:51 | 2017-07-25 12:22:51 |
|  9 | 80172779107984112104 | 2017-07-25 12:22:51 | 2017-07-25 12:22:51 |
| 10 | 22825755425594828330 | 2017-07-25 12:22:51 | 2017-07-25 12:22:51 |
+----+----------------------+---------------------+---------------------+

Reference

I used Phinx, DB migration Tool on Docker!

https://kenzo0107.github.io/2017/07/20/2017-07-21-commit-to-phinx-migration-tool/

Author

Kenzo Tanaka

Posted on

2017-07-21

Licensed under

#

この記事は気に入りましたか？ 著者をサポートする

コメント

フォローする

カテゴリ

最近の記事

タグ

.htaccess3
ALB2
AWS43
Android2
Ansible3
AntiVirus1
Apache4
Chef1
CodeBuild1
Cookie1
Corosync1
Datadog6
Docker9
ECR2
ECS3
ElastiCache1
Elasticsearch3
Email1
FTPS1
Fluentd3
GKE4
GitHub Actions1
Go24
GooglePlay1
Hubot1
JavaScript2
Jenkins3
Kibana2
LINE Notify1
Lambda2
Let's encrypt1
Linux2
MachineLearning4
Monitoring12
MySQL9
Nginx11
OpenSSL1
Outlook1
PHP8
Pacemaker1
Prometheus7
ProxySQL1
Puppeteer2
Python11
Rails4
RaspberryPI4
Reactio1
Redis2
Ruby10
S31
SSL5
Security1
SendGrid2
Slack2
SonarQube3
StatsBot1
Swift1
Terraform6
Twilio1
Unity1
Vagrant3
Vault1
WAF1
Zabbix3
awk1
casperjs1
cpu1
csv2
flask1
git2
i-node1
iftop1
ipinfo1
iptable1
kibana1
macos1
nginx1
no-ip1
ping1
pip1
reCAPTCHA1
sftp1
spam1
ssh3
vim1
wget1
yum1
zsh1

更新を購読する

広告

×