DB定義書をSchemaSpyとFlywayでやってみた

自己紹介

こんにちは。決済システムチームの yoponpon です。
普段は決済サービスの機能の開発をやっています。
.net、docker、powershell、sql serverなどを使っています。

何をしたかったか？

• サービスの保守、運用、開発で使うためのDB定義書が作りたい。
• DBのデータ定義を可視化及び、変更の管理をしたい。エンジニア以外の人が見る需要が有るため、エンジニア以外の人向けに見やすいGUIで出力したい。データのリレーションも見れるのが理想。
• できれば手動での更新管理（≠Excelなどでの更新管理）などは避けたい。
• DBのデータ定義の変更はgitなどで変更管理をしたい。できるのが理想。（pull requestなどで変更をレビューできるため）

前提

• Mysql Workbench使えば見れるじゃないっていうのは最終手段で考えてました。（DBどこに置くとか、アクセスどうするとか諸々有るため）
• DBのテーブルに対して外部キーは貼ってません。（データ間のリレーションはWebアプリ側で補完してました）
• 該当のシステムはテーブルの主キーは「id」で定義、論理的にデータを紐つけるための外部キーは「テーブル名+id」で貼っていました。

  

  shopsへの外部キーはshops_idを定義

やったこと

この辺りの対応をしたのは2019年の5月頃です。
今ならもっと別のツールや手段があるかもしれないです。
使ったツールは以下です。

• SchemaSpy
  • 特定のデータベースの特定のスキーマのテーブル一覧、インデックス等をHTMLでGUI出力してくれるツールです。DBの構造をGUIで出力する目的で使いました。実行可能JARやDockerイメージなどいくつかの実行手段があります！
• Flyway
  • DBマイグレーションツールです。DBへの変更履歴を管理することができます。加える変更はコードで管理できるので、アプリケーションコードと一緒にgitリポジトリで管理する目的で使っています。

SchemaSpyとは

SchemaSpyはデータベース構造に基づいてデータベースダイアグラムを出力してくれるツールです。
DBのサポートについては、使いたいDBのJDBCドライバがあれば問題ありません。
どんな感じに出力されるかですが、公式サイトが出力サンプルをおいてくれています。

• テーブル一覧の出力サンプル
  schemaspy.org

• リレーションシップのサンプル
  schemaspy.org

Flywayについて

FlywayはDBスキーマの変更管理目的で利用しています。こちらも書くと記事が長くなってしまうためまた今度書きます！

構成

• DBについて
  該当のシステムではMysqlを使っていました。 SchemaSpyに当てるJCBCドライバはmysql-connector-javaを使います。

• 公開方法
  SchemaSpyはDBを読み取ってHTMLを出力します。吐き出されたHTMLは何かしらのWebサーバにホストさせる必要があります。公開できれば何でもいいのでnginxを使います。

• デプロイ
  AWS ECS Fargateを使っています。HTMLの出力から公開までDockerコンテナ一つで完了させます！（ホントは分けたほうがいいかもしれないけど）

• ベースイメージ
  Flyway、SchemaSpyはJVMで動くのでJDKが乗っているDockerイメージを使います！

SchemaSpy実行手順

• 実行可能JARのダウンロード
  公式のインストール手順はこちらです。
  ざっくりの手順ですが、
  ①Javaのインストール
  ②Graphvizのインストール
  ③最新のSchemaSpyのダウンロードとリネーム
  ④JDBCドライバのダウンロード。今回はMysql Connector/jを使います。

• 設定ファイルの作成
  DB接続文字など設定ファイルを作成します。
  schemaspy.propertiesを作成します
  ファイル内容: schemaspy.properties

# 使うDBを設定
schemaspy.t=mysql

# JDBCドライバのパス
schemaspy.dp=mysql-connector-java.jar

# DB接続情報
schemaspy.host=[ホスト名]
schemaspy.port=[ポート]
schemaspy.db=[スキーマ]
schemaspy.u=[ユーザ名]
schemaspy.p=[パスワード]

# ファイルの出力先
schemaspy.o=/www

# db scheme for which generate diagrams
schemaspy.s=[スキーマ]

• 配置
  schemaspy.jarと同様のディレクトリにmysql-connector-java.jarとschemaspy.propertiesを配置します

SchemaSpyフォルダ
┗schemaspy.jar
┗schemaspy.properties
┗mysql-connector-java.jar

• DBめがけて実行！

> java -jar schemaspy.jar 

• 生成完了
  www配下にHTMLが生成されます。
  index.htmlにアクセスしてみましょう

完了！だがしかし！

通常であればここまででDBダイアグラムが生成されて完了です！
ところが今回DBダイアグラム生成をしたDBは外部キーを貼っておりません。
その為、生成されるHTMLのRelationshipsタブには何も出てきません。(データ間の連携が見えない)
論理的な外部キーも検出したい！そんな要望に答える方法を検討しました。

結論

一時的にDBに外部キーを貼る！ です

論理的な外部キーを検出するようにするとか、SchemaSpyを改造するとか色々考えましたが、物理的に外部キーを貼ってしまうことにしました！

さて、当システムはFlywayによってDB立ち上げは再現できるようにしていたため、
どこでも再現、破棄可能になっていました。

なので

一時的にDBを立ち上げて、必要なリレーションを張りまくってSchemaSpyを流してDBを破棄する

をやります。

外部キーは貼っておりませんが、論理的な外部キーは存在しています。それらは テーブル名+_id で貼っているのでスクリプトで検出可能です！

以下、論理的な外部キーを検出して外部キーを貼るスクリプトです。ごちゃごちゃしてますがご了承ください汗
もっときれいに書けただろって言わたら同感です汗
※dbnameはDBの名前です
※Tables_in_dbnameはSHOW TALBESした際のカラム名です。（Tables_in_+DBの名前 だった気が）

#!/bin/bash
tables=$(mysql --host=localhost --user=root -ppassword --port=3306 dbname -e 'SHOW TABLES WHERE Tables_in_dbname not like "hist_%";');
for table in $tables; do 
   if [ "$table" == "Tables_in_dbname" ]; then
      continue;
   fi
   columns=$(mysql --host=localhost --user=root -ppassword --port=3306 dbname -e 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "'$table'";');
   for column in $columns; do 
      if [ $column == "COLUMN_NAME" ]; then
         continue;
      fi
      if [[ $column =~  .+_id ]]; then
         for target_table in $tables; do 
            if [ "$target_table" == "Tables_in_dbname" ]; then
               continue;
            fi
            if [ ${column/_id/} == $target_table ]; then
               echo "ALTER TABLE "$table" ADD CONSTRAINT FOREIGN KEY ("$column") REFERENCES "$target_table"(id);";
               mysql --host=localhost --user=root -ppassword --port=3306 dbname -e "ALTER TABLE "$table" ADD CONSTRAINT FOREIGN KEY ("$column") REFERENCES "$target_table"(id);"
            fi
         done;
      fi
   done;
done;

これで外部キーが貼られるので、SchemaSpyを流せばRelationshipsが見れます！

まとめ

何が良かったの？

外部キーを検出して貼るスクリプトを実行して
SchemaSpyでDBダイアグラムを生成できました！！

これによってDB定義書の手動更新の作業がなくなり、見やすいGUIでDBの構造の見えるようになりました！

最後に

ホントはnginxにホストさせる手順や、Flyway、このフロー完全自動化について書こうと思ったのですが長くなるのでやめておきます！
また次回それらもまとめて書こうかなと思います！

今後は列挙型（Enum）や状態の見える化がテーマですかね…

長い記事を読んでいただきありがとうございました！！

We are hiring!!

絶賛仲間募集中です。

• プロジェクトマネージャー
• フロントエンドエンジニア
• バックエンドエンジニア(PHP)
• SRE
• Salesforceエンジニア
• QAエンジニア