PHP 製のデータベースのスキーマ定義を差分で適用するツール「dbdatool」

PHP MySQL

データベースのスキーマ定義の変更、いわゆるデータベースのマイグレーションついて、スキーマ定義の差分を SQL とかそれ用の DSL とかで作成し、リポジトリ管理してマイグレーションツールで実環境に適応するのが多いと思います。

この方法はわかりやすくて良いですが、スキーマの変更が頻繁だと細々とした変更が差分ファイルとして積み上がっていくのでかなり煩雑です。

一方で、最新のスキーマ定義だけをリポジトリ管理し、実際のデータベースと比較してその差分だけを適用するようにすれば、差分を管理する必要は無くなって、例えばテーブルに列を増やしたければ最新のスキーマ定義ファイルに修正を加えるだけで済みます。

そのようなマイグレーションツールは Perl や Ruby では GitDDL や Ridgepole などがあるようです。PHP なら Doctrine でそれっぽいことができるようなのですが Doctrine 特有の制約が多そうだったので、dbdatool というツールを作成しました（いまのところ MySQL 専用）。

概要

スキーマ定義は生の SQL で CREATE TABLE などの DDL が管理されている想定です。ただし、ツール自体に生の SQL を読み込み＆解析するような機能は無く、できることは基本的には下記のみです。

  • 稼働中のデータベースからスキーマ定義ファイルを独自の JSON 形式で出力する
  • スキーマ定義ファイル（↑で出力したファイル）とデータベースを比較して差分を ALTER などで出力する
  • ↑の差分をデータベースに適用する

あるいは、次のように PDO の DSN の形式で、2つのデータベースのスキーマ定義の差分を表示したり、差分を適用したりもできます。

php dbdatool.phar diff \
    "mysql:host=192.0.2.100;port=3306;dbname=test;charset=utf8:user:password" \
    "mysql:host=192.0.2.200;port=3306;dbname=test;charset=utf8:user:password"

使い方の例

dbdatool のDB接続情報の設定ファイルを作成します。接続情報は環境変数から得るのがポイントです。

<?php
$host = getenv('DB_HOST');
$port = getenv('DB_PORT');
$dbname = getenv('DB_DATABASE');
$username = getenv('DB_USERNAME');
$password = getenv('DB_PASSWORD');
return [
    'dsn' => "mysql:host=$host;port=$port;dbname=$dbname;charset=utf8",
    'username' => $username,
    'password' => $password,
];

dotenv 使ってるなら先頭に↓みたいなのが必要です。

<?php
require __DIR__ . '/../vendor/autoload.php';
(new \Dotenv\Dotenv(dirname(__DIR__)))->load();

この設定ファイルのパスを composer.json に追記します。composer.json にこのように書いておけば dbdatool の CLI によって自動的に読まれます。

{
    "extra": {
        "dbdatool-config": ["database/config.php"]
    }
}

ここまでが事前の準備です。

次に、スキーマ定義に変更があるときは生の SQL で管理している DDL を修正した後、手元の適当なデータベースに mysql コマンドでインポートします。たいてい開発者の手元には開発用とテスト用で２つのデータベースがあるので、テスト用のデータベースにインポートします。

cat *.sql | mysql hoge_test -v

テスト用のデータベースからスキーマ定義ファイルをダンプします。dbdatool の設定ファイルでDB接続情報を環境変数から得るようにしていたので、実行時に環境変数を指定すればテスト用のデータベースからダンプできます。

MYSQL_DATABASE=hoge_test php dbdatool.phar dump > schema.json

スキーマ定義ファイルと開発用のデータベースとの差分を表示・確認して、問題なければ差分を適用します。

php dbdatool.phar diff schema.json
php dbdatool.phar apply schema.json

Git リポジトリに追加・コミット・プッシュします。

git add .
git commit -m 'Fix database schema'
git push

他の開発者は git pull の後にスキーマ定義の変更を適用できます。

git pull -r
php dbdatool.phar diff schema.json
php dbdatool.phar apply schema.json

実環境へも schema.json をアプリのコードと一緒にデプロイすれば差分で適用できます。

特徴

スキーマ定義は生の SQL の DDL で管理したかったのですが、生の SQL と実際のデータベースとを直接比較して差分を導出しようとすると SQL のパーサーが必要になってしまいます。そこまで作り込みたくなかったので、生の SQL は一旦適当なデータベースにインポートした上で INFORMATION_SCHEMA から必要な情報を取得してスキーマ定義ファイルとして保存＆リポジトリ管理することにしました。

スキーマ定義ファイル（schema.json）は JSON なのでやろうと思えば手で編集できますが、かなり雑な比較で差分を導出しているので、手書きだといろいろ不都合が生じることがあります。例えば tinyint と boolean を同一視していないので schema.json に boolean と書いてしまうと apply しても差分がなくなりません（DB上は tinyint になっているため）。

なお、パーティショニング、ビュー、トリガ、ストアド、などはサポートしていません。サポートしているのは下記のみです。

  • テーブル（生成列も含む、パーティショニングは未サポート）
  • インデックス
  • 外部キー制約

さいごに

後に schemalex とか sqldef とかを知って、バイナリポンで動くならこういうので良いかとも思ったのですが、既に dbdatool をプロジェクトで使い始めていたので、自分でメンテして自由が利かせられる dbdatool を暫くは使っていこうと思ってます。