テーブル設計書とER図を常に最新化して社内公開(CI/CD)

概要

私が自社で担当しているサービスにはテーブルが大量にありますが、テーブル設計書とER図はあまりなく、あっても陳腐化しているケースが大半でした。
なので、テーブル設計書とER図を常に最新化して社内公開する仕組みをGitHub ActionsとSchemaSpyとPrivate GitHub Pagesを用いて作ってみました。
参考までにソースや設定情報も掲載いたします。

環境

• データベース：MySQL（他製品でもソースを変えたらいけそう）
• ソース管理：GitHub
• スキーマファイル：Railsのstructure.sql（サービスコンテナ内のDBに対象テーブルと同等のテーブルを用意できたらOK）

全体イメージ

テーブル設計書とER図を常に最新化して社内公開する処理は新規作成した設計書用リポジトリ内で完結させ、サービス用リポジトリへの影響がないようにしてます。

成果物

①テーブル更新の通知を行う

サービス用リポジトリ/.github/workflows/notify_schema_update.yml

name: Notify schema update

on:
  push:
    branches:
      - master
    paths:
      - 'spec/test_app/db/structure.sql'
  workflow_dispatch:

jobs:
  notify:
    runs-on: ubuntu-latest

    steps:
      - name: Notify schema update
        uses: peter-evans/repository-dispatch@v2
        with:
          token: ${{ secrets.[トークン名] }}
          repository: [オーナー名/設計書用リポジトリ名]
          event-type: updated-schema

GitHub Secretの設定

To dispatch to a remote repository you must create a Personal Access Token (PAT) with the repo scope and store it as a secret.

repository-dispatchによると、別のリポジトリに通知する場合、repo権限有りのシークレットトークンが必要とのこと

②テーブル設計書とER図を作成して社内公開する

設計書用リポジトリ/.github/workflows/build_database_documents.yml

name: Build database documents by SchemaSpy

on:
  repository_dispatch:
    types: [updated-schema]
  workflow_dispatch:

jobs:
  build-database-doc:
    runs-on: ubuntu-latest

    services:
      db:
        image: mysql:5.7.12
        ports:
          - 3306:3306
        env:
          TZ: Asia/Tokyo
          MYSQL_DATABASE: db_schema
          MYSQL_ALLOW_EMPTY_PASSWORD: yes
        options: >-
          --health-cmd "mysqladmin ping -h localhost"
          --health-interval 20s
          --health-timeout 10s
          --health-retries 10

    steps:
      #-- スキーマファイル設定 --#
      - name: Set schema
        id: schema
        env:
          SCHEMA_REPOSITORY: [サービス用リポジトリ名]
        run: |
          echo "LOCAL_BASE_BRANCH=master" >> $GITHUB_ENV
          echo "SCHEMA_ORGANIZATIONS=Fablic" >> $GITHUB_ENV
          echo "SCHEMA_REPOSITORY=$SCHEMA_REPOSITORY" >> $GITHUB_ENV
          echo "SCHEMA_BASE_BRANCH=master" >> $GITHUB_ENV
          echo "SCHEMA_FILE_PATH=$SCHEMA_REPOSITORY/spec/test_app/db/structure.sql" >> $GITHUB_ENV

      #-- スキーマファイル取得 --#
      - name: Checkout repository
        uses: actions/checkout@v3
        with:
          ref: ${{ env.LOCAL_BASE_BRANCH }}

      - name: Checkout schema repository
        uses: actions/checkout@v3
        with:
          repository: ${{ env.SCHEMA_ORGANIZATIONS }}/${{ env.SCHEMA_REPOSITORY }}
          ref: ${{ env.SCHEMA_BASE_BRANCH }}
          path: ${{ env.SCHEMA_REPOSITORY }}
          token: ${{ secrets.REPO_ACCESS_TOKEN }}

      #-- テーブル作成 --#
      - name: Setup database
        run: mysql --protocol=tcp -h localhost -P 3306 -u root db_schema < ${{ env.SCHEMA_FILE_PATH }}

      #-- スキーマファイル削除 --#
      - name: Remove checked-out schema repository
        run: sudo rm -rf ${{ env.SCHEMA_REPOSITORY }}

      #-- 出力先ディレクトリ設定 --#
      - name: Set output directory
        id: directory
        run: |
          echo "SCHEMA_TMP_DIR=/tmp/schema" >> $GITHUB_ENV
          echo "SCHEMA_DOCS_DIR=docs/schema" >> $GITHUB_ENV

      #-- ドキュメント作成 --#
      - name: Make output directory
        run: sudo mkdir -m 777 ${{ env.SCHEMA_TMP_DIR }}

      - name: Run SchemaSpy
        run: |
          docker run \
          --rm \
          --net=${{ job.container.network }} \
          -e TZ=Asia/Tokyo \
          -v ${{ env.SCHEMA_TMP_DIR }}:/output \
          -v $PWD/config/schemaspy.properties:/schemaspy.properties \
          -v $PWD/config/schemameta.xml:/schemameta.xml \
          schemaspy/schemaspy:snapshot \
          -meta schemameta.xml \
          -connprops useSSL\\=false \
          -rails

      - name: Copy SchemaSpy output to documents directory
        run: sudo cp -fr ${{ env.SCHEMA_TMP_DIR }}/* ${{ env.SCHEMA_DOCS_DIR }}/.

      #-- マージ元ブランチ設定 --#
      - name: Set source branch
        id: merge
        env:
          TZ: Asia/Tokyo
        run: echo "SOURCE_BRANCH=fix/update_schema_$(date +'%Y%m%d%H%M%S')" >> $GITHUB_ENV

      #-- PR作成 --#
      - name: Create new branch
        run: |
          git switch -c ${{ env.SOURCE_BRANCH }}
          git push -u origin ${{ env.SOURCE_BRANCH }}

      - name: Add and Commit
        uses: EndBug/add-and-commit@v9
        with:
          new_branch: ${{ env.SOURCE_BRANCH }}
          message: Update database documents and schema file
          add: ${{ env.SCHEMA_DOCS_DIR }}

      - name: Create Pull Request
        uses: repo-sync/pull-request@v2
        id: open_pr
        with:
          github_token: ${{ secrets.GITHUB_TOKEN }}
          source_branch: ${{ env.SOURCE_BRANCH }}
          destination_branch: ${{ env.LOCAL_BASE_BRANCH }}
          pr_title: Update schema
          pr_body: by SchemaSpy
          pr_label: SchemaSpy

      #-- PRマージ --#
      - name: Merge Pull Request
        env:
          GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
        run: gh pr merge ${{ env.pr_url }} -m --admin

      #-- マージ元ブランチ削除 --#
      - name: Delete source branch
        run: git push --delete origin ${{ env.SOURCE_BRANCH }}

      #-- Slack通知 --#
      - name: Slack notification
        if: always()
        run: |
          if [ '${{ job.status }}' = 'success' ]; then
            COLOR="good"
          else
            COLOR="danger"
          fi

          jq -n --arg jobLink '${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}' --arg color "${COLOR}" '{
            attachments: [{
              pretext: "処理結果 ${{ job.status }}",
              color: $color,
              title: $jobLink,
              title_link: $jobLink,
              text: "DB設計書の更新処理が終了しました"
            }]
          }' | curl ${{ secrets.SLACK_WEBHOOK_URL }} -X POST -H 'Content-Type: application/json' -d @-

サービスコンテナのDB設定ファイル

設計書用リポジトリ/config/schemaspy.properties

# type of database
schemaspy.t=mysql
# database properties
schemaspy.host=db
schemaspy.db=db_schema
schemaspy.s=db_schema
schemaspy.u=root
schemaspy.p=

SchemaSpyの設定ファイル

SchemaSpyの公式ドキュメントによると、設定ファイルにテーブル名やカラム名を定義できるようですが、そもそもmigrateファイルで定義しておいたほうが無難かと思います

設計書用リポジトリ/config/schemameta.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">
  <comments>Database Information</comments>
  <tables></tables>
</schemaMeta>

ディレクトリ作成

設計書用リポジトリの直下にdocs/schemaディレクトリを作成しておく。
理由はSchemaSpyで生成したテーブル設計書とER図を上記ディレクトリにpushするため。

GitHub Secretsの設定

• REPO_ACCESS_TOKEN：repo権限ありのGitHub AccessToken
• SLACK_WEBHOOK_URL：SlackのWEBHOOKのURL
  

GitHub Pagesの設定

設計書用リポジトリへのアクセス権を追加する

設計書用リポジトリへ、サービス用リポジトリに登録したGitHub Secretの所有者を追加し、承認いただく（設計書用リポジトリの所有者と同一であれば不要）

SchemaSpyの注意点

ER図について

今回のようにrailsオプションを付けた場合、カラム名からリレーション分析してER図を作ってくれます。
ただし、Active Recordのルールに従っている場合しか対応できません。
例えば、migrateファイルにt.references :user という定義がある場合、
子テーブル.user_id → usersテーブル.id となるER図を作ってくれます。

対象サービスのmigrateファイルには外部キー制約（foreign_key: trueかadd_foreign_key）が定義されていないためrailsオプションを付けましたが、
railsオプションを付けなかった場合、外部キー制約からリレーション分析してER図を作ってくれます。
よって、精度の高いER図を出力するにはmigrateファイルに外部キー制約が必要そうですね。

実行結果

社内のDB情報は隠してますが、Private GitHub PagesのURLにアクセスしたらテーブル一覧・テーブル設計書・ER図など全てキレイに表示されました。

課題

• 生成されるER図の精度を高くしたい
• GitHub Actionsの無料利用枠をあまり消耗したくないため、処理速度を早くしたい
• 社内GitHubアカウントを持っていない社員でも閲覧できるようにしたい（需要があれば）