叫ぶうさぎの悪ふざけ

うさぎが目印のWebエンジニアが、得たことや思ったことを言の葉に乗せて叫ぶ場所です。

学習記録:12月7日(金):Webフレームワークに依存しない、PHP製のシンプルなSQL マイグレーションツール「Mig」を使ってみた

これは 俺のインプットアウトプット記録 Advent Calendar 2018 の7日目のエントリーです。

こちらのブログ、Webフレームワークに依存しない、PHP製のシンプルなSQL マイグレーションツール「Mig」を作った。 を拝見し、宣言通り使ってみました。

元ブログでやってなかったこと

使い方は製作者さまのブログの通りで、それをなぞっただけなので、特に新しいことはしていませんが、あえてブログにないことをしたと言えば、以下のポイントかなと思います。

  • インストールまでの道筋を全部書いた
    • 趣味で行間を埋めただけですが…
  • 個人の趣味で作ったテーブルの根拠を書いた
    • COLLATE=utf8mb4_bin とか
    • 金額カラムには DECIMAL 使うといいよと公式で言ってたとか
  • データベース接続設定ファイルの記述にデータベース名を入れ忘れた
    • 個人的にミスったのでメモした
  • CREATE TABLE, DROP TABLEのスペルミスをしていた
    • 注意書き程度にコンテンツに追加
    • 12/07追記:製作者さんにブログを修正していただきました

前提

もろもろちょっと古いですが、MySQLがサクッと試せる環境が今んところこれしかなかったのでご容赦を。

あと、vagrant使ってますので、コマンドは全て vagrant ssh した後になります。

$ cat /etc/system-release
CentOS Linux release 7.3.1611 (Core) 
$ mysql --version
mysql  Ver 14.14 Distrib 5.7.19, for Linux (x86_64) using  EditLine wrapper
$ php -v
PHP 7.1.8 (cli) (built: Aug  2 2017 12:13:05) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.1.0, Copyright (c) 1998-2017 Zend Technologies

インストール

単純にMySQL5.7の検証用に作ったものだったんでcomposerから。

composer

$ curl -sS https://getcomposer.org/installer | php
All settings correct for using Composer
Downloading...

Composer (version 1.8.0) successfully installed to: /home/vagrant/composer.phar
Use it: php composer.phar
$ sudo mv composer.phar /usr/local/bin/composer
$ composer
   ______
  / ____/___  ____ ___  ____  ____  ________  _____
 / /   / __ \/ __ `__ \/ __ \/ __ \/ ___/ _ \/ ___/
/ /___/ /_/ / / / / / / /_/ / /_/ (__  )  __/ /
\____/\____/_/ /_/ /_/ .___/\____/____/\___/_/
                    /_/
Composer version 1.8.0 2018-12-03 10:31:16
(以下略)

Mig

そして本体をインストール。ここからはブログにもある通りです。

$ composer global require arakaki-yuji/mig
Changed current directory to /home/vagrant/.config/composer
Using version ^0.3.3 for arakaki-yuji/mig
./composer.json has been created
Loading composer repositories with package information
Updating dependencies (including require-dev)
Package operations: 4 installs, 0 updates, 0 removals
  - Installing symfony/polyfill-mbstring (v1.10.0): Downloading (100%)         
  - Installing symfony/contracts (v1.0.2): Downloading (100%)         
  - Installing symfony/console (v4.2.0): Downloading (100%)         
  - Installing arakaki-yuji/mig (0.3.3): Downloading (100%)         
symfony/contracts suggests installing psr/cache (When using the Cache contracts)
symfony/contracts suggests installing psr/container (When using the Service contracts)
symfony/contracts suggests installing symfony/cache-contracts-implementation
symfony/contracts suggests installing symfony/service-contracts-implementation
symfony/contracts suggests installing symfony/translation-contracts-implementation
symfony/console suggests installing psr/log-implementation (For using the console logger)
symfony/console suggests installing symfony/event-dispatcher
symfony/console suggests installing symfony/lock
symfony/console suggests installing symfony/process
Writing lock file
Generating autoload files

パスを通す

みなさんの環境に合わせてくださいね。 適当に作ってしまった環境なので僕は以下のパスを通しました。

$ export PATH=$PATH:$HOME/.config/composer/vendor/bin

設定ファイル作成

ここは作者さまの通りに。

$ pwd
/home/vagrant
$ vim mig.config.php 

設定ファイルの中身はこんな感じ。 注意点としては、 db_dsn に設定する内容は db_name まで設定しないとPDOでコケます。

詳しく言うと、接続先のデータベース名を指定しないと invalid data source name になりますので、以下のようにhostとdb_nameを設定してください。

<?php
return [
    'db_dsn' => 'mysql:host=localhost;dbname=mamy1326',
    'db_username' => 'root',
    'db_passwd' => 'mSbXeWZyGz4R',
    'migration_filepath' => 'migrations',  // migrationファイルを保存するディレクトリ名
];

次にmigrationファイルを保存するディレクトリを作っておきます。

$ mkdir migrations
$ ls -la migrations
合計 12
drwxrwxr-x  2 vagrant vagrant   88 127 02:18 .
drwx------. 9 vagrant vagrant 4096 127 02:18 ..

これで準備は完了です。

初期化を実行

コマンドを実行し、migration用のテーブルができていることを確認します。

$ mig-cli init
Initialize for manage migration.
=================

Already created migrations table.

次に実際のテーブルを確認します。

$ mysql -u root -p mamy1326
Enter password: 
(中略)
mysql> show tables where Tables_in_mamy1326='migrations';
+--------------------+
| Tables_in_mamy1326 |
+--------------------+
| migrations         |
+--------------------+
1 row in set (0.00 sec)

無事、migrationテーブルが作成されていることがわかります。 一応、create tableも確認します。 中身も空っぽですね。

mysql> show create table migrations\G
*************************** 1. row ***************************
       Table: migrations
Create Table: CREATE TABLE `migrations` (
  `id` bigint(20) NOT NULL,
  `applied_at` int(11) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> select * from migrations;
Empty set (0.00 sec)

migrationファイル作成

実際のテーブル名をつけて、migrationファイル作成コマンドを実行します。 今回はmigrationテーブルで差分を確認したいので、2つテーブルを作成します。

# migrationファイル作成(1テーブル目)
$ mig-cli create items
Create a new migration file.
=================

create migrations/20181206174917_items.up.sql
create migrations/20181206174917_items.down.sql

# migrationファイル作成(2テーブル目)
$ mig-cli create item_details
Create a new migration file.
=================

create migrations/20181206180443_item_details.up.sql
create migrations/20181206180443_item_details.down.sql

# 作成を確認
$ ls -la migrations
合計 6
drwxrwxr-x  2 vagrant vagrant 4096 127 02:49 .
drwx------. 9 vagrant vagrant 4096 127 02:18 ..
-rw-rw-r--  1 vagrant vagrant    0 127 02:49 20181206174917_items.down.sql
-rw-rw-r--  1 vagrant vagrant    0 127 02:49 20181206174917_items.up.sql
-rw-rw-r--  1 vagrant vagrant    0 127 03:15 20181206180443_item_details.down.sql
-rw-rw-r--  1 vagrant vagrant    0 127 03:15 20181206180443_item_details.up.sql

SQLをファイルに書き込む

CREATE TABLE, DROP TABLEをそれぞれファイルに書き込みます。 ブログの内容ですと、 IF EXIST になっていますが、正しくは IF EXISTS です。

12/07追記:製作者さんにブログを修正していただきました!

テーブル作成

itemsテーブル

$ vim migrations/20181206174917_items.up.sql

若干僕の趣味で、PKは bigint(20) unsigned だったり、絵文字を識別したいので COLLATE=utf8mb4_bin だったりします。

CREATE TABLE IF NOT EXISTS `items` (
  `item_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `item_name` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

item_detailsテーブル

$ vim migrations/20181206180443_item_details.up.sql

こちら金額が入ってるだけですが、金額を扱う場合は DECIMAL または NUMERIC を推奨、とMySQL公式でも記述があります。

CREATE TABLE IF NOT EXISTS `item_details` (
  `item_detail_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `price` decimal(10,3) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`item_detail_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

テーブル削除

itemsテーブル

$ vim migrations/20181206174917_items.down.sql
DROP TABLE IF EXISTS `items`;

item_detailsテーブル

$ vim migrations/20181206180443_item_details.down.sql
DROP TABLE IF EXISTS `item_details`;

migration実行

実際に実行し、テーブルを作成します。

テーブルが存在しないことを確認

$ mysql -u root -p mamy1326
Enter password: 
(中略)
mysql> show tables where Tables_in_mamy1326 like 'item%';
Empty set (0.00 sec)

migration実行

$ mig-cli migrate
Start migration.
=================

Migrate migrations/20181206174917_items.up.sql
Migrate migrations/20181206180443_item_details.up.sql

テーブル作成を確認

$ mysql -u root -p mamy1326
Enter password: 
(中略)

# テーブル作成確認
mysql> show tables where Tables_in_mamy1326 like 'item%';
+--------------------+
| Tables_in_mamy1326 |
+--------------------+
| item_details       |
| items              |
+--------------------+
2 rows in set (0.00 sec)

# テーブル定義確認
mysql> show create table items\G
*************************** 1. row ***************************
       Table: items
Create Table: CREATE TABLE `items` (
  `item_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `item_name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> show create table item_details\G
*************************** 1. row ***************************
       Table: item_details
Create Table: CREATE TABLE `item_details` (
  `item_detail_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `price` decimal(10,3) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`item_detail_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

無事に作成されました。

migrationテーブル確認

ここまでで2つのテーブルを作成したので、管理しているmigrationテーブルをみてみます。

$ mysql -u root -p mamy1326
Enter password: 
(中略)

mysql> select * from migrations\G
*************************** 1. row ***************************
        id: 20181206174917
applied_at: 1544120647
*************************** 2. row ***************************
        id: 20181206180443
applied_at: 1544120647
2 rows in set (0.00 sec)

idが migrationファイル名の年月日時分秒 になっていることが確認できます。

rollback実行

2つのテーブルを作りましたが、rollbackするとファイル単位で実行されているようですね。

$ mig-cli rollback
Rollback a migration.
======================

Rollback migrations/20181206180443_item_details.down.sql

migrationテーブル確認

Rollback migrations/20181206180443_item_details.down.sql とあるように、該当する年月日時分秒のレコードが削除されているのがわかります。

mysql> mysql> select * from migrations\G
*************************** 1. row ***************************
        id: 20181206174917
applied_at: 1544120647
1 row in set (0.00 sec)

実際にテーブルも削除されていますね。

mysql> show tables where Tables_in_mamy1326 like 'item%';
+--------------------+
| Tables_in_mamy1326 |
+--------------------+
| items              |
+--------------------+
1 row in set (0.00 sec)

再度migration実行

さっきDROP TABLEされたmigrationファイルが実行されています。

$ mig-cli migrate
Start migration.
=================

Migrate migrations/20181206180443_item_details.up.sql

migrationテーブル、実際のテーブルも作成されていますね。

mysql> melect * from migrations\G
*************************** 1. row ***************************
        id: 20181206174917
applied_at: 1544120647
*************************** 2. row ***************************
        id: 20181206180443
applied_at: 1544121133
2 rows in set (0.00 sec)

mysql> show tables where Tables_in_mamy1326 like 'item%';
+--------------------+
| Tables_in_mamy1326 |
+--------------------+
| item_details       |
| items              |
+--------------------+
2 rows in set (0.00 sec)

連続でrollback

1つずつ、年月日時分秒を遡ってrollbackが実行されるようです。

$ mig-cli rollback
Rollback a migration.
======================

Rollback migrations/20181206180443_item_details.down.sql
$ mig-cli rollback
Rollback a migration.
======================

Rollback migrations/20181206174917_items.down.sql

テーブル、レコードも消えていますね。

mysql> select * from migrations\G
Empty set (0.00 sec)

mysql> show tables where Tables_in_mamy1326 like 'item%';
Empty set (0.00 sec)

使ってみて

非常にお手軽に導入できて、直接SQLを記述できて素晴らしいと思いました。これなら何にも依存せずに使うことができそうです。

PostgreSQLにも対応してくれたら個人的には嬉しいです!!

migrationは便利だけど、フレームワークに依存するよなーSQL直接書きたいよなーって思っていた僕にはとてもいいものに思えました。

現在のところはフレームワークの流儀にのっとって、フレームワークのmigrationを使っていますが、もしレガシーな運用をしているプロダクトに出会ったら、便利に使えそうだなって思ったりしてました。

また、ここに掲載しなかったいろんなパターン(CREATE TABLEの後にALTER TABLEなど)を試してみて、migrationってシンプルに運用できるよな、でもなんども実行するもんじゃないし、最初に設計って重要だよな、って改めて思ったりしてました。

おわりに

OSS活動されているかたって本当に尊いなって思います。 以前、吉祥寺.pm で fujiwara さんがされていたLTを思い出しました。

業務などで使う便利なツールを、業務の活動の範囲でOSSにしてしまう。

こんな活動に通じるものがあるよなあって思ったし、自分が何かOSSを提供できるかどうかはわかりませんが、作ったもので便利そうなものがあれば、積極的にGitHubで公開などできたらなって思わせていただきました。

いやー楽しかった!