Featured image of post 【さらばER図設計作業】docker-composeで「SchemaSpy」でデータベースのドキュメントを自動生成するやり方【DBリバースエンジニアリング】
IT

【さらばER図設計作業】docker-composeで「SchemaSpy」でデータベースのドキュメントを自動生成するやり方【DBリバースエンジニアリング】

3094文字

DBの設計書、保守するのめんどくさい問題

皆さんはシステム開発を行う際にDB設計をしたことはありますでしょうか？

データベースの種類はOracleやMySQL、PostgreSQL、MariaDBなど様々な種類がありますが、リレーショナルDBであれば基本的には設計の仕方は変わりません。

また、DBは大切なデータを蓄積し、システム側の様々な機能から呼び出されるのでしっかりと設計を行う必要があります。

そして設計をするということはもちろん、設計書も必要になりますよね。

しかし、このDB設計書というものはとても保守がめんどくさいのが実態です。orz

エクセルで管理すると、自動反映が出来ないので常にソース(DDL文)と二重管理をする必要が出てしまったり、Gitによる差分管理も出来ません。

ERMasterやEnterprise Architect(有料)を使えば、ある程度は自動出力が出来ますが、やはりヒューマンエラーが発生する可能性があります。

果たして、何が最善なのだろうか。。。

と、悩んでいたところ良さそうなツールを見つけてしまいました！

SchemaSpy

SchemaSpyとは？

SchemaSpyとは、既存のDBをクローリングして自動でテーブル構造やカラム、PK/FKなどの定義をHTMLドキュメントとして吐き出してくれるツールとなっています！

Do you hate starting on a new project and having to try to figure out someone else’s idea of a database? Or are you in QA and the developers expect you to understand all the relationships in their schema? If so then this tool’s for you.SchemaSpy — SchemaSpy 6.0.0 documentation

[adsense-responsive]

最高じゃん！！！

メリット

Point
  • HTMLなのでGit管理可能
  • UIが見やすい
  • クリックで簡単に参照先のTBLを見ることが出来る
  • 様々なDBに対応
  • 自動化なのでヒューマンエラーが無い
  • CIなどの自動化に組み込める

Dockerでサクッと使える

SchemaSpyはJava製なので、Java8がインストールされている環境であれば簡単に実行することが出来ます。

しかし、Javaのバージョン問題や描写をするための必須ライブラリ(graphviz)が必要な場合もあるため、準備がちょっとめんどくさいです。

[adsense-responsive]

そんな面倒くさがり屋さん向けに、Docker環境で簡単に実行するためのイメージがDocker Hubで公開されているので、そちらを使えばコマンド一発で実行する事が可能です！

今回はDockerを使った実行方法についてご紹介していこうと思います。

使い方

前提

今回はDockerがローカルで動く事が前提です。

docker -v
Docker version 18.06.1-ce, build e68fc7a

プロパティファイルの準備

まずはschemaSpy実行時に読み込む設定ファイルを用意しましょう。

schemaspy.properties
schemaspy.t=pgsql
schemaspy.host=localhost
schemaspy.port=5432
schemaspy.db=local_account_db
schemaspy.u=root
schemaspy.p=root

Dockerで起動

あとは、Dockerコマンドが使える状態であれば、以下のコマンドを叩くだけで既存のデータベースをクローリングして自動でドキュメントを生成してくれます！

[adsense-responsive]

コマンド
docker run \
        -v "{出力先パス}:/output"  \
        -v "{プロパティファイルパス}:/schemaspy.properties"  \
        schemaspy/schemaspy:latest

以下のような実行ログが出れば無事に生成されていると思います。

実行ログ(成功)
Running Main-Class org.springframework.boot.loader.JarLauncher
With drivers:jtds-1.3.1.jar, mariadb-java-client-1.1.10.jar
mysql-connector-java-6.0.6.jar, postgresql-42.1.1.jre7.jar
  ____       _                          ____
 / ___|  ___| |__   ___ _ __ ___   __ _/ ___| _ __  _   _
 \___ \ / __| '_ \ / _ \ '_ ` _ \ / _` \___ \| '_ \| | | |
  ___) | (__| | | |  __/ | | | | | (_| |___) | |_) | |_| |
 |____/ \___|_| |_|\___|_| |_| |_|\__,_|____/| .__/ \__, |
                                             |_|    |___/

                                              6.0.0-SNAPSHOT

SchemaSpy generates an HTML representation of a database schema's relationships.
SchemaSpy comes with ABSOLUTELY NO WARRANTY.
SchemaSpy is free software and can be redistributed under the conditions of LGPL version 3 or later.
http://www.gnu.org/licenses/

INFO  - Starting Main v6.0.0-SNAPSHOT with PID 1 (/schemaspy-6.0.0-SNAPSHOT.jar started by java in /)
INFO  - The following profiles are active: default
INFO  - Found configuration file: schemaspy.properties
INFO  - Started Main in 1.545 seconds (JVM running for 2.075)
INFO  - Starting schema analysis
INFO  - Connected to PostgreSQL - 10.1
INFO  - Gathering schema details
Gathering schema details....(0sec)
Connecting relationships....(0sec)
Writing/graphing summary.INFO  - Gathered schema details in 0 seconds
INFO  - Writing/graphing summary
.........(1sec)
Writing/diagramming detailsINFO  - Completed summary in 1 seconds
INFO  - Writing/diagramming details
.(0sec)
Wrote relationship details of 1 tables/views to directory '/output' in 2 seconds.
View the results by opening /output/index.html
INFO  - Wrote table details in 0 seconds
INFO  - Wrote relationship details of 1 tables/views to directory '/output' in 2 seconds.
INFO  - View the results by opening /output/index.html

成果物

ディレクトリ構造

成果物は以下のようなディレクトリ構造になっています。(第２階層以降略)

tree -L 1
docs.
tree -L 1
.
├── anomalies.html
├── anomalies.js
├── bower
├── column.js
├── columns.html
├── constraint.js
├── constraints.html
├── deletionOrder.txt
├── diagrams
├── favicon.png
├── fonts
├── images
├── index.html
├── insertionOrder.txt
├── local_account_db.public.xml
├── main.js
├── orphans.html
├── relationships.html
├── relationships.js
├── routines
├── routines.html
├── routines.js
├── schemaSpy.css
├── schemaSpy.js
└── tables

UIもとても見やすく、クリックすることでテーブル構造の詳細画面に飛べるのでとても便利です！

publicスキーマしか出力されない問題

原因

デフォルトだと、publicスキーマしか読み込まれないらしいです。

ぐぬぬ。。。

全部のスキーマを出力することはオプションで可能

しかしご安心を。

schemaspyのドキュメント をみると、出力対象のスキーマを指定するコマンド引数が複数用意されていました。

[adsense-responsive]
引数概要キャプチャ
-s {スキーマ名}特定の一つのスキーマを出力対象にする
※この場合はスキーマ一覧画面は生成されない
-schemas {スキーマ名A}[,{スキーマ名B}...]「,」区切りで複数のスキーマを出力対象にする
-all対象DBのすべてのスキーマを出力対象にする
※システム関連のスキーマも出力される

とりあえずはこの3つの引数を使い分ければ良いでしょう。

動作確認

では、試しに-allですべてのスキーマを出力してみましょう。

コマンド
docker run --network="host" \
        -v "$PWD/docs:/output"  \
        -v "$PWD/schemaspy.properties:/schemaspy.properties"  \
        schemaspy/schemaspy:latest -all

実行ログ
Running Main-Class org.springframework.boot.loader.JarLauncher
With drivers:jtds-1.3.1.jar, mariadb-java-client-1.1.10.jar
mysql-connector-java-6.0.6.jar, postgresql-42.1.1.jre7.jar
  ____       _                          ____
 / ___|  ___| |__   ___ _ __ ___   __ _/ ___| _ __  _   _
 \___ \ / __| '_ \ / _ \ '_ ` _ \ / _` \___ \| '_ \| | | |
  ___) | (__| | | |  __/ | | | | | (_| |___) | |_) | |_| |
 |____/ \___|_| |_|\___|_| |_| |_|\__,_|____/| .__/ \__, |
                                             |_|    |___/

                                              6.0.0-SNAPSHOT

SchemaSpy generates an HTML representation of a database schema's relationships.
SchemaSpy comes with ABSOLUTELY NO WARRANTY.
SchemaSpy is free software and can be redistributed under the conditions of LGPL version 3 or later.
http://www.gnu.org/licenses/

INFO  - Starting Main v6.0.0-SNAPSHOT with PID 1 (/schemaspy-6.0.0-SNAPSHOT.jar started by java in /)
INFO  - The following profiles are active: default
INFO  - Found configuration file: schemaspy.properties
INFO  - Started Main in 1.611 seconds (JVM running for 2.163)
INFO  - Analyzing schemas that match regular expression '.*'. (use -schemaSpec on command line or in .properties to exclude other schemas)
INFO  - Analyzing schemas:
account
information_schema
pg_catalog
public
INFO  - Analyzing account
INFO  - Starting schema analysis
INFO  - Connected to PostgreSQL - 10.1
INFO  - Gathering schema details
Gathering schema details......(0sec)
Connecting relationships......(0sec)
Writing/graphing summary.INFO  - Gathered schema details in 0 seconds
INFO  - Writing/graphing summary
............(1sec)
Writing/diagramming detailsINFO  - Completed summary in 1 seconds
INFO  - Writing/diagramming details
...(0sec)
Wrote relationship details of 3 tables/views to directory '/output' in 3 seconds.
View the results by opening /output/index.html
INFO  - Wrote table details in 0 seconds
INFO  - Wrote relationship details of 3 tables/views to directory '/output/account' in 3 seconds.
INFO  - View the results by opening /output/account/index.html
INFO  - Analyzing information_schema
INFO  - Starting schema analysis
INFO  - Connected to PostgreSQL - 10.1
INFO  - Gathering schema details
Gathering schema details...(0sec)
Connecting relationships...(0sec)
Writing/graphing summary.WARN  - No tables or views were found in schema 'information_schema'.
ERROR - The schema exists in the database, but the user you specified 'root'might not have rights to read its contents.
ERROR - Another possibility is that the regular expression that you specified for what to include (via -i) didn't match any tables.
INFO  - Gathered schema details in 0 seconds
INFO  - Writing/graphing summary
.........(1sec)
Writing/diagramming detailsINFO  - Completed summary in 1 seconds
INFO  - Writing/diagramming details
INFO  - No tables to output, nothing written to disk
(0sec)
Wrote relationship details of 0 tables/views to directory '/output' in 5 seconds.
View the results by opening /output/index.html
INFO  - Wrote table details in 0 seconds
INFO  - Wrote relationship details of 0 tables/views to directory '/output/information_schema' in 5 seconds.
INFO  - View the results by opening /output/information_schema/index.html
INFO  - Analyzing pg_catalog
INFO  - Starting schema analysis
INFO  - Connected to PostgreSQL - 10.1
INFO  - Gathering schema details
Gathering schema details...(0sec)
Connecting relationships...(0sec)
Writing/graphing summary.WARN  - No tables or views were found in schema 'pg_catalog'.
ERROR - The schema exists in the database, but the user you specified 'root'might not have rights to read its contents.
ERROR - Another possibility is that the regular expression that you specified for what to include (via -i) didn't match any tables.
INFO  - Gathered schema details in 0 seconds
INFO  - Writing/graphing summary
.........(1sec)
Writing/diagramming detailsINFO  - Completed summary in 1 seconds
INFO  - Writing/diagramming details
INFO  - No tables to output, nothing written to disk
(0sec)
Wrote relationship details of 0 tables/views to directory '/output' in 6 seconds.
View the results by opening /output/index.html
INFO  - Wrote table details in 0 seconds
INFO  - Wrote relationship details of 0 tables/views to directory '/output/pg_catalog' in 6 seconds.
INFO  - View the results by opening /output/pg_catalog/index.html
INFO  - Analyzing public
INFO  - Starting schema analysis
INFO  - Connected to PostgreSQL - 10.1
INFO  - Gathering schema details
Gathering schema details....(0sec)
Connecting relationships....(0sec)
Writing/graphing summary.INFO  - Gathered schema details in 0 seconds
INFO  - Writing/graphing summary
.........(1sec)
Writing/diagramming detailsINFO  - Completed summary in 1 seconds
INFO  - Writing/diagramming details
.(0sec)
Wrote relationship details of 1 tables/views to directory '/output' in 7 seconds.
View the results by opening /output/index.html
INFO  - Wrote table details in 0 seconds
INFO  - Wrote relationship details of 1 tables/views to directory '/output/public' in 7 seconds.
INFO  - View the results by opening /output/public/index.html

正常にすべてのスキーマが出力されていますね♪

HTML構造なのでリンク先に飛べるのがとても便利ですしUIも見やすい！
また、S3などにおけばブラウザで閲覧出来るドキュメントにもなりますし、エクセルや画像出力では不可能だったgit管理での差分把握も可能になるので、開発/運用/保守としてもかなりスマートになりますね♪

終わりに

以上のように、めんどくさいER図やリレーションの設計書起こしをやる手間が省けました！

CIツールなどを使えば、開発者はDDLの設計だけに専念し、ローカルでDDLを適用してSchemaSpyでドキュメント出力したものをレビューして修正、のようなフローも可能になると思います。

今回のようなリバースエンジニアリングは作業効率をとてもあげる手法なので、皆さんもぜひ使ってみてはいかがでしょうか♪