LaravelとSchemaSpyで簡単ER図作成

ER図を描こう

あるwebサービスの運用を他のチームに引き継ぐことになりまして、
ふとデータベースまわりのドキュメントがないことに気づきました。

とまあ、そんな状況でなくともER図を描くのは面倒です。

構築したテーブルから生成

統合環境等で作成もできますが、外部キーを張っていないと自動生成できません。
運用していたwebサービスは外部キーは張っていなくてアプリケーションでリレーションをしていたので、テーブルとカラムのリストまでしか読み込めません。

かといって専用のツールで描くと、MacとWindowsで共有できないことがありますし
Excelで作成するのはちょっと。。

SchemaSpy

SchemaSpyというツールを使いました。
これだと、DBから読み取れるところは読み取って、
追加でリレーションやカラムコメントを記述することができます。
[https://schemaspy.readthedocs.io/en/latest/configuration/schemaMeta.html]

また、設定ファイルや生成したファイルなどをリポジトリ管理することもできますし、
表示もブラウザなので、MacでもWindowsでも共有できます。

ER図を出力してみる

GitHubリポジトリ
[https://github.com/mya-zaki/laravel-schemaspy-sample]

環境

• PHP7.3
• Laravel6.2
• SQL Server 2017

構築

リポジトリをCloneする

DBのパスワードを編集
docker-compose.yml
schemaspy/schemaspy.properties
のを任意のパスワードに編集

コンテナをビルド

$ docker-compose up -d --build web sqlsrv

DB作成

$ docker-compose exec sqlsrv /opt/mssql-tools/bin/sqlcmd -H localhost -U SA -Q "create database laraveldb"

パッケージをインストールする

$ docker-compose exec web composer install

.envをコピーして、編集

$ cp .env.example .env
$ docker-compose exec web php artisan key:generate

• DB_CONNECTION=sqlsrv
• DB_HOST=sqlsrv
• DB_PORT=1433
• DB_DATABASE=laraveldb
• DB_USERNAME=SA
• DB_PASSWORD=(DBのパスワード)

DBテーブル作成

$ docker-compose exec web php artisan migrate

SchemaSpyでデータベースドキュメント生成

準備ができたらSchemaSpyを実行します。
SQL Serverなのでドライバをschemaspy/drivers/mssql-jdbc-7.4.1.jre8.jarに置いてあります。
ドライバーはこちらから

XMLで情報追加

DBから読み取れない追加の情報についてschemaspy/schema-meta.xmlをおきます。

<?xml version="1.0" encoding="utf-8"?>
<schemaMeta xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://schemaspy.org/xsd/6/schemameta.xsd">
    <tables>
        <table name="users" comments="ユーザ情報">
            <column name="name" comments="ユーザ名"/>
        </table>
        <table name="posts">
            <column name="user_id">
                <foreignKey table="users" column="id"/>
            </column>
        </table>
    </tables>
</schemaMeta>

XMLにはusersテーブルとnameカラムにコメントを、postsテーブルに外部キーの設定が記載されています。

実行

DBの接続先、ドライバー、追加のXMLのパス、出力先はschemaspy/schemaspy.propertiesに。
ここでschemaspyコンテナを実行します。

$ docker-compose run --rm schemaspy

実行後、schemaspy/output/users.htmlをブラウザで開きます。

usersテーブルとnameカラムにXMLに記述したコメントが表示されています。

Relationshipsページへいくと、

usersとpostsがXMLに記載のとおりuser_idでつながっています。

これで、DBになくてもコメントやリレーションを表示できました！

XMLのリレーションの記述を自動で生成

SchemaSpy、かなり便利だと思うのですがテーブルが増えてくると
リレーションを記述したXMLを書くのが少々面倒です。

そこで、LaravelのEloquentのModelを継承したクラスのリレーションの実装からXMLを生成できるようパッケージを作りました。
mya-zaki/laravel-schemaspy-meta

以下の4つのメソッドが実装されている時にXMLに反映するようにしました。
hasOne
hasMany
belongsTo
belongsToMany

実行

$ docker-compose exec web php artisan schemaspy-meta:generate App\\Models --excludeClass=Flight --xmlFile=schemaspy/schema-meta.xml

• 対象のModelが含まれるディレクトリを指定します。
• --excludeClass: 除外したいクラス（複数可）
• --xmlFile: XMLの出力先

XMLが更新されます。

<?xml version="1.0" encoding="utf-8"?>
<schemaMeta xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://schemaspy.org/xsd/6/schemameta.xsd">
    <tables>
        <table name="users" comments="ユーザ情報">
            <column name="name" comments="ユーザ名"/>
        </table>
        <table name="posts">
            <column name="user_id">
                <foreignKey table="users" column="id"/>
            </column>
        </table>
        <table name="comments">
            <column name="post_id">
                <foreignKey table="posts" column="id"/>
            </column>
            <column name="foreign_key">
                <foreignKey table="posts" column="other_key"/>
            </column>
        </table>
        <table name="password_resets">
            <column name="email">
                <foreignKey table="users" column="email"/>
            </column>
        </table>
        <table name="permission_role">
            <column name="r_id">
                <foreignKey table="roles" column="id"/>
            </column>
            <column name="p_id">
                <foreignKey table="permissions" column="id"/>
            </column>
        </table>
        <table name="my_phones">
            <column name="user_id">
                <foreignKey table="users" column="id"/>
            </column>
        </table>
        <table name="role_user">
            <column name="user_id">
                <foreignKey table="users" column="id"/>
            </column>
            <column name="role_id">
                <foreignKey table="roles" column="id"/>
            </column>
        </table>
    </tables>
</schemaMeta>

usersとpostsの記述のあとに、各テーブルのリレーションの情報が追加されました。
指定したXMLに追加分を上書きになるので、リレーションの記述の削除はされません。

(追記 version2)
ER図におこしたいDBとは別のModelがある場合に個別指定ではなく、継承元のクラス指定ができます。

$ php artisan vendor:publish 

例）

<?php
return [
    'exclude_parent_models' => [
        \Jenssegers\Mongodb\Eloquent\Model::class,
        \BaoPham\DynamoDb\DynamoDbModel::class,
    ]
];

もういちどschemaspyコンテナを実行します。

$ docker-compose run --rm schemaspy

Relationshipのページschemaspy/output/relationships.htmlを開くと

ER図が出来上がってます。

おわり

SchemaSpyとXMLの生成で完全自動化とはいきませんが
だいぶER図の作成が簡単になったと思います。

しっかり外部キーをはって構築していれば、
SSMSとかMySQL Workbenchとかが便利そうですが。