プロジェクトの成長と付随して、データベースの構造は変化するものです。本番環境であればプロダクトのリリース時、開発環境であればコードベースからチェックアウトしてきた時などのタイミングで、データベースの構造を変化させなければなりません。

ここでは、複数人が携わるプロジェクトにて、失敗の少ないデータベーススキーマ変更の管理方法について紹介します。

[2016-05-12 追記] この記事の内容は非常に古いものになっています。現在は「Flyway」といったDBのマイグレーションのためのコマンドラインツールがあります。このツールは、この記事で紹介する方法やRuby on Railsのマイグレーションと似た仕組みを採用しています。この記事で紹介する手動な運用に取り入れる前に、是非Flywayを検討してみてください。

Problems

下記の問題のどれかに当てはまればこの記事はあなたにとって有用でしょう。

私のプロジェクトでは、スキーマ更新がバージョン管理システムで管理されていません。
私のプロジェクトでは、スキーマ更新がモジュールごとに分散しています。
私のプロジェクトでは、どの環境にどのスキーマ更新を適用したか記録がありません。

Problem 1: スキーマ更新がバージョン管理システムで管理されていません。

私のプロジェクトでは、各テーブルのスキーマ更新(ALETER クエリ), ビューやトリガー、プロシージャの作成クエリがバージョン管理システムで管理されていません。これらの更新情報は、テーブル定義書やプロジェクトwikiを参照する必要があります。

このような問題を抱えるプロジェクトは、すぐさまスキーマ更新をSQLファイルにし、gitやsvnなどのバージョン管理システムで管理すべきです。

Problem 2: スキーマ更新がモジュールごとに分散しています。

私のプロジェクトでは、スキーマ更新はバージョン管理されています。しかし、それぞれの更新クエリは各モジュールに分散しています。サーバに適用するたびに、私は各モジュールに更新クエリが追加されていないか探しまわらなければなりません。

更新クエリをバージョン管理しているとしたら良いことです。しかし、更新クエリは一箇所に集めたほうがいいです。管理が容易になります。一箇所に集約する場合は、更新クエリの時系列を把握できるように整理すべきです。

Problem 3: どの環境にどのスキーマ更新を適用したか記録がありません。

私のプロジェクトでは、本番環境、ステージング環境、開発環境がありますが、どのスキーマ更新をどの環境にどこまで適用したか記録がありません。

どの環境にどのスキーマ更新を適用したかは必ず把握する必要があります。

私のプロジェクトでは、どの環境にどのクエリを適用したか、どの環境にどのクエリを適用すべきかを、wikiなどのドキュメントで管理しています。しかし、そのドキュメントにはしばしば漏れや誤りがあります。

更新クエリと更新記録は分散していないほうが望ましいです。更新クエリを一箇所に集約した上で、更新記録もそこに付随させます。そして、更新記録もバージョン管理システムで管理するようにします。

データベーススキーマを管理するベストプラクティス

それでは、上記の問題に解決する管理方法を紹介します。

この管理方法は以下の小さなプロセスで構成されています。

スキーマ更新はSQLにし、ファイルの形にします。
ファイルは一箇所に集めます。
どの環境にどこまで適用したか分かるようにトークン(しるし)をつけます。
ファイルとトークンはバージョン管理システムで管理します。

スキーマ更新はSQLにし、ファイルの形にします

スキーマ更新は必ずSQLにし、ファイルの形で残すようにします。決して、伝言という形をとってはいけません。チャットやWikiは伝言の類になります。

ファイルはSQLサーバが理解できる形式で書かなければなりません。クエリは必ずセミコロン ; で終了するように書きます。そうしなければ、複数のクエリをひとつのファイルに書いた時、SQLサーバは正しく理解できません。また、コメントは -- や /* */ で書かなければ当然パースエラーや予期しない挙動になります。

ファイル名は適切につけるようにします。alter_user_table.sql よりは add_2_columns_to_user.sql や add_phone_and_address_to_user.sql のほうがより分かりやすくなります。

ファイルは一箇所に集めます

ファイルはプロジェクトのどこかに sql や update_sql といったフォルダを作り、そこに集約するようにします。

ファイルは時系列順でソートできるようにします。ソートのために特別なプログラムに頼る必要がないようにしましょう。、yyyymmdd_hhmm_ のような日時情報をファイルの接頭辞します。こうすることで、 ls コマンドや Finder, Explorerといった基本的なツールだけでソートが可能になります。

どの環境にどこまで適用したか分かるようにトークン(しるし)をつけます

どの環境にどこまで適用したか分かるように、トークンと言われる印をつけるようにします。トークンは 0 バイトのファイルで構いません。SQLファイル同様に、日時 yyyymmdd_hhmm~ で始まるファイル名にします。この日時は、適用した日時と同じにします。分の後がチルダなのは、同じ日時のSQLファイルよりも必ず後に来るようにソートできるようにするためのTipsです。

ファイル名には、「どの環境のトークンか」を持たせるようにします。これはファイル名に環境名を含めるようにして対応します。例えば、production, staging, develop の3つの環境がある場合は 20120701_1021~production.apply_token, 20120711_1432~staging.apply_token, 20120718_0906~develop.apply_token のようにします。

また、開発者個人の環境についても 20120701_1021~alice_local.apply_token のようにトークンを作ることが許されます。

ファイルとトークンはバージョン管理システムで管理します

SQLファイルはgitやsvnなどのバージョン管理システム(VCS)で管理するようにします。1回でも適用されたSQLファイルを編集することは許可されません。時系列にそって適用すれば、時点がばらばらの環境でも必ず同じデータベーススキーマになるように保たなければなりません。特にVIEWやストアドプロシージャの更新ファイルは、もとのファイルを編集せずに、コピーし新しいSQLファイルとしてVCSに追加します。

トークンもVCSで管理します。トークンをVCSで管理すると、どこまで適用したかの記録をプロジェクトで共有することができます。たとえ、staging環境に更新クエリを適用する担当者が毎回変わったとして、このルールを守っていれば説明なしで、適切な更新クエリを選ぶことができます。

具体的な実践手順

1. 下準備

1.1 バージョン管理システムを利用できるようにする

git や svn を利用できるように環境を構築してください。個人の判断でできない場合はプロジェクトリーダに相談してください。

バージョン管理システムにコミットする権限を、データベーススキーマを更新する可能性があるすべてのメンバーに与えてください。

1.2 SQLファイルを集約するためのフォルダを作る

プロジェクトのトップレベルに sql フォルダを作ります。

$ mkdir sql

1.3 環境ごとのトークンをつくる

環境ごとのトークンをつくります。最初は日時を00000000_0000にします。

$ touch 00000000_0000~develop.apply_token
$ touch 00000000_0000~staging.apply_token
$ touch 00000000_0000~production.apply_token

作ったトークンはバージョン管理システムにコミットします。

# git
$ git add *.apply_token
$ git commit
$ git push

# svn
$ svn add *.apply_token
$ svn commit

2. 運用手順

運用手順は 2.1 ~ 2.3 の繰り返しになります。

2.1 SQLファイルを作る

sqlフォルダに行き、例えば 20120718_1727_add_birthday_to_user.sql を作ります。

$ cd sql
$ touch 20120718_1727_add_birthday_to_user.sql

20120718_1727_add_birthday_to_user.sql をエディタで開き、SQLを書き込みます。

2.2 バージョン管理システムに登録する

git や svn にSQLファイルをコミットします。

# git
$ git add 20120718_1727_add_birthday_to_user.sql
$ git commit 20120718_1727_add_birthday_to_user.sql
$ git push

# svn
$ svn add 20120718_1727_add_birthday_to_user.sql
$ svn commit

2.3 環境に適用する

たとえば、develop環境に適用します。

$ mysql -uroot -proot develop < 20120718_1727_add_birthday_to_user.sql

適用したらトークンのファイル名を書き換えて、日時を適用日時に変更します。

$ mv 00000000_0000~develop.apply_token 20120718_1811~develop.apply_token

トークンをバージョン管理システムに登録します。

まとめ

複数人が携わるプロジェクトにて、失敗の少ないデータベーススキーマ変更の管理方法について紹介しました。この方法を実践することで、よりシンプルで、より漏れや抜けのないデータベーススキーマ更新が行えるようになります。

また、この運用を支援するようなスクリプトを現在開発していますので、完成したら公開したいと思います。