データベースのER図を最新の状態に保ちつつ、いつでも閲覧できるようにした

こんにちは、CTO室の丸山です。相変わらず某CTOからの無茶振りを捌いております。

今回は、今開発しているSaaSサービスのER図を、なるべく手をかけずに最新状態に保ちながら、いつでも閲覧できるようにするために実践したことをご紹介できればと思います。

前提環境

今回ご紹介する方法は以下の環境下で実現しています。

  • インフラ: GCP
  • データベース: PostgreSQL
  • データベースのスキーマ管理: sql-migrate
  • CI/CD: Github Actions

モチベーション

開発・データ分析を行う上で、データベースのスキーマ構造を俯瞰的に把握するための手段としてER図を書いているのですが、これを最新の状態に保ち続けるのは割と面倒であると常々感じていました。

そこで、Schemaspyを使用してデータベースのスキーマ構造からER図を生成するようにしたものの、以下のような手順をスキーマ構造の更新のたびに手動で行うのは地味に面倒であり、手動だとER図の更新を忘れがちでした。

  • ① PostgreSQLのDockerコンテナを起動する
  • ② データベースに対してマイグレーションを実行してテーブルを作成／更新する
  • ③ Schemaspyを使用してマイグレーションに基づく最新のスキーマ構造からER図を書き起こす
  • ④ 書き起こしたER図（HTML形式）をWebサーバにホスティングする

上記の課題感を解消するためにER図の更新が自動的に行われる状態を目指したわけですが、とはいえ頻繁に見るものでもないので、運用コストはなるべく安く済ませたいと考えました。

ということで、以下の要求をなるべくお手軽に叶える方法を考えてみることにしました。

  • 最新のDBスキーマ構造を元に作成されたER図を常時閲覧できる環境がほしい
  • プロジェクト関係者全員がER図を閲覧できるようにしたい
  • プロジェクト関係者だけが閲覧できれば十分
  • 運用コストは極力かけたくない

実現方法の概要

私たちが開発に利用している前提環境を踏まえて、Github Actions と AppEngine Standard環境 と Identity-Aware Proxy を組み合わせて実現しました。

  • マイグレーションツールを管理するgitリポジトリに対し、特定のブランチに対するPUSHの発生をトリガとして、ER図の生成とAppEngineへのデプロイを行うGithub Actionsジョブを定義
  • WebサイトとしてホスティングされたER図は、予め指定されたユーザーのみが閲覧できるようにIdentity-Aware Proxyによりアクセス制御を設定
  • 一定時間アクセスがなければAppEngineのインスタンスを自動的に停止することで運用コストを抑制（無料割当の範囲内で運用）

全体概要

以下では簡単な実装例を紹介したいと思います。

実装例

ディレクトリ構成

マイグレーション用のDDLと設定情報、AppEngineの設定、Github Actionsのジョブ定義を以下のように構成します。

.
├── .github
│   └── workflows
│       └── release-er-diagram.yml # ER図の生成とホスティングを実行するジョブ
├── appengine
│   └── app.yaml # AppEngineにホスティングする際の設定ファイル
├── dbconfig.yml # sql-migrateを実行する際の設定情報
├── migrations # sql-migrateで実行するDDL群の格納先
│   ├── create-tables.sql
│   ├── add-tables.sql
│   └── ...
└── output # schemaspyで生成したER図の一時的な出力先

release-er-diagram.yml

developブランチへのコミットをトリガにして、ER 図の作成及び AppEngine へのホスティングを行うための Github Actions の CI ジョブです。

name: Create & Upload ER Diagram

on:
  # developブランチにPUSHし、且つmigrationsディレクトリ配下が更新されていたら実行
  push:
    branches:
      - develop
    paths:
      - "migrations/**"

jobs:
  create-er-diagram:
    runs-on: ubuntu-latest
    timeout-minutes: 10
    # PostgreSQLのサービスコンテナを起動する
    # https://docs.github.com/ja/enterprise-cloud@latest/actions/using-containerized-services/creating-postgresql-service-containers
    services:
      postgres:
        image: postgres
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        env:
          POSTGRES_USER: app
          POSTGRES_PASSWORD: secret
          POSTGRES_DB: eredit
          TZ: Asia/Tokyo
        ports:
          - 5432:5432

    steps:
      - name: Checkout
        uses: actions/checkout@v4

       # sql-migrateを使用するためにgolang、及びsql-migrateをセットアップ
      - name: Setup Golang
        uses: actions/setup-go@v5
        with:
          go-version: '1.22.1'

       # @see https://github.com/rubenv/sql-migrate?tab=readme-ov-file#installation
      - name: Install sql-migrate
        run: go install github.com/rubenv/sql-migrate/...@latest

       # サービスコンテナに対してマイグレーションを実行し、適用状況を確認
       # @see https://github.com/rubenv/sql-migrate?tab=readme-ov-file#usage
      - name: Setup database
        run: |
          sql-migrate up -config dbconfig.yml -env local
          sql-migrate status -config dbconfig.yml -env local

       # ER図を作成するSchemaspyの実行環境としてJavaをセットアップ
      - name: Setup JDK
        uses: actions/setup-java@v4
        with:
          distribution: 'adopt'
          java-version: '21'

       # SchemaspyとPostgreSQLのJDBCドライバを手動で取得した上で、Schemaspyを実行してER図を作成（執筆時点の最新版）
       # @see https://schemaspy.org/
       # 最新版に拘らないのであれば、 Market place で公開されている Github Action の利用を検討してみてもよいかもしれない
       # @see https://github.com/marketplace/actions/run-schemaspy
      - name: Run SchemaSpy
        run: |
          wget -O schemaspy.jar https://github.com/schemaspy/schemaspy/releases/download/v6.2.4/schemaspy-6.2.4.jar
          wget -O postgresql.jar https://repo1.maven.org/maven2/org/postgresql/postgresql/42.7.3/postgresql-42.7.3.jar
          java -jar schemaspy.jar -dp postgresql.jar -t pgsql -host localhost -port 5432 -db eredit -u app -p secret -s public -o output -vizjs

       # 生成したER図は Artifacts に一時的に保管
      - name: Archive ER Diagram
        uses: actions/upload-artifact@v4
        with:
          name: er-diagram
          path: |
            output
          overwrite: true
          retention-days: 1 # AppEngineにデプロイするまでの一時的な置き場として利用するので保存期間は設定可能な最小とする

  deploy-to-appengine:
    runs-on: ubuntu-latest
    timeout-minutes: 10
    needs: create-er-diagram
    environment: dev_appengine
    steps:
      - name: Checkout
        uses: actions/checkout@v4

       # Artifacts からER図を取得して、appengine/public ディレクトリ配下に展開
      - name: Download ER Diagram
        uses: actions/download-artifact@v4
        with:
          name: er-diagram
          path: appengine/public

      - name: Authenticate with Google Cloud
        id: auth
        uses: google-github-actions/auth@v2
        with:
          # Github リポジトリのEnvironments配下でサービスアカウントキーを保持
          # TODO: Workload Identity federation を使用すれば、サービスアカウントキーを保持する必要はなくなる
          credentials_json: "${{ secrets.GCP_CREDENTIALS }}"

       # appengine配下のリソースを静的サイトとしてAppEngineにデプロイ
      - name: Deploy ER Diagram to App Engine
        uses: 'google-github-actions/deploy-appengine@v2'
        with:
          working_directory: appengine

      - name: Setup Cloud SDK
        uses: google-github-actions/setup-gcloud@v2

        # 過去にデプロイしたものは直近3世代分を残して破棄
        # 直近稼働分のメトリクスを閲覧できるように残しているが不要なら残さなくてもよい
      - name: Delete old apps
        run: |
          result=$(gcloud app versions list --service=er-diagram --sort-by=~version.createTime --format='value(version.id)' --filter="version.servingStatus='STOPPED'" | tail -n +3); [[ -z "$result" ]] && echo "No old versions to delete." || echo "$result" | xargs -r -I {} gcloud app versions delete {} --quiet

dbconfig.yml

sql-migrateを実行するための各種接続情報、具体的にはCIの過程で起動した PostgreSQL のサービスコンテナに対する接続情報を管理します。

# sql-migrateを実行するための各種接続情報
local:
  dialect: postgres
  datasource: host=localhost port=5432 dbname=eredit user=app password=secret sslmode=disable
  dir: migrations

app.yaml

AppEngineの設定ファイルです。 Schemaspyが生成したER図を静的サイトとして運用するためのルーティング設定を書きます。

runtime: php82

# AppEngine上でのサービス名称
service: er-diagram
# 一番低スペックのインスタンスを選択
#（静的サイトかつアクセス数は少ないので、低スペックインスタンスで十分と判断）
instance_class: B1

handlers:

# /へのアクセスは public/index.html にルーティング
- url: /
  static_files: public/index.html
  upload: public/index.html
  secure: always

# 全てのアクセスを public 配下のコンテンツにルーティング
- url: /(.*)
  static_files: public/\1
  upload: public/(.*)
  secure: always

# 最大インスタンス数は1として余計なスケールを抑制しつつ、
# 一定時間アクセスがなかったらインスタンスを停止してコストを抑える
basic_scaling:
  max_instances: 1
  idle_timeout: 10m

GCP側の各種設定

AppEngineへのデプロイに必要な権限

AppEngineへのデプロイを行うサービスアカウントには以下のロールを付与します。

  • AppEngine サービス管理者
  • AppEngine デプロイ担当者
  • Cloud Build 編集者
  • サービスアカウント ユーザー
  • ストレージ フォルダ管理者

Identity Aware Proxy によるアクセス制御

AppEngineへのデプロイ後、Identity−Aware Proxy によるアクセス制御を有効にします。

IAPの有効化

開発者のIAMロール

ER図をホストするAppEngineアプリケーションにアクセスするユーザーに、以下のロールを付与します。

  • IAPで保護されたウェブアプリ ユーザー

結果

アクセス権限のあるユーザーでアクセスすると、以下のようにER図が閲覧できます。

ER図

今回は sql-migrate と PostgreSQL の事例でご紹介しましたが、他のツールやRDBMSに置き換えてもおおよそ同じような方法で実現できると思います。

同じようなお悩みを抱えている方は、ぜひ参考にしてください！