背景

私たちのチームではテーブル定義をスプレッドシートで管理していましたが、新たなテーブルの設計時に追加するのみで、ほとんど保守されていませんでした。
エンジニアはコードから理解できますが、他職種も含めて最新のテーブル定義やER図を参照できるようにしたく、それらを自動生成できるSchemaSpyを導入しました。

関連技術

PostgreSQL
AWS
GitHub Actions

ワークフロー

GitHub Actionsのデプロイ用ワークフローの最後に以下のjobを追加しました。
全体の流れとしては、schemaspyの実行準備→schemaspyの実行→生成物をS3にアップロードという手順です。

cd.yml


  schemaspy:
    runs-on: ubuntu-latest
    timeout-minutes: 10
    permissions:
      id-token: write
      contents: read
      actions: read

    services:
      postgres:
        image: postgres
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: ***
          POSTGRES_DB: ***
          TZ: "UTC"
        options: >-   # Postgresコンテナのヘルスチェック設定
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v3
      - uses: actions/setup-node@v3
        with:
          node-version: "18"
      - uses: actions/cache@v3
        id: cache-node-modules
        with:
          path: ./app/node_modules
          key: node-modules-${{ hashFiles('./app/package-lock.json') }}
      - name: setup env
        working-directory: ./app
        run: cp .env.example .env
      - name: Setup node packages if not cached
        if: steps.cache-node-modules.outputs.cache-hit != 'true'
        working-directory: ./app
        run: npm ci

      - name: Setup DB Data   # Prismaを使ってデータベースをリセットし、マイグレーションを実行
        working-directory: ./app
        run: npm run prisma:migrate-reset
        env:
          DATABASE_URL: "postgresql://postgres:***@localhost:5432/***"

      - name: make schemaspy.properties   # schemaspyで使用する設定ファイルを作成
        run: |
          echo -e "
          # DBの種類
          schemaspy.t=pgsql\n    
          # servicesで定義したコンテナ名
          schemaspy.host=postgres\n
          schemaspy.port=5432\n
          schemaspy.db=***\n
          schemaspy.u=postgres\n
          schemaspy.p=***\n
          # PostgreSQLのデフォルトのスキーマがpublic
          schemaspy.s=public\n　
          " > ${PWD}/schemaspy/schemaspy.properties

      - name: make output directory
        run:  |
          mkdir -p ${PWD}/schemaspy/output
          chmod 777 ${PWD}/schemaspy/output

      - name: run schemaspy
        run: docker run --net=${{ job.container.network }} -v "${PWD}/schemaspy/output:/output" -v "${PWD}/schemaspy/schemaspy.properties:/schemaspy.properties" schemaspy/schemaspy:latest

      - name: authenticate to aws
        uses: aws-actions/configure-aws-credentials@v2
        with:
          role-to-assume: ${{ secrets.AWS_IAM_ROLE_ARN_PRD }}
          aws-region: ${{ secrets.AWS_DEFAULT_REGION }}

      - name: upload schemaspy HTML
        run: aws s3 sync ${PWD}/schemaspy/output/ s3://<アップロード先のパス>/ --delete   # deleteオプションで、アップロードする中には存在しないがS3に存在するファイルを削除

インフラ

今後社内向けのツールを追加したいときのことを考え、<管理画面のドメイン>/tools/hogeといった形で、tools以下のパスには社内からのアクセスのみになるようIP制限をかけることにしました。（実際には /<管理画面のドメイン>/tools/hoge にアクセスしても、自動的にindex.htmlが表示されないため、/<管理画面のドメイン>/tools/hoge/index.html にアクセスする必要がある）
S3バケットのURLを共有し直接アクセスさせる案もありましたが、URLが長くてわかりにくいので上記の方針になりました。

WAF
- パスがtoolsで始まっているかつ社内IPリストに存在しないIP の場合にブロックするルールを追加
CloudFront
- 管理用のディストリビューション
  - オリジンにtools用のS3バケットを追加
  - パスがtools/*の場合、上記のオリジンにルーティングするビヘイビアを追加
S3
- tools用のバケットを作成
- バケットポリシーでCloudFrontの該当のディストリビューションからのGetObjectを許可

IAM

デプロイのgithub actions用のロールに、以下のポリシーを追加

 {
 	"Action": [
 		"s3:PutObject",
 		"s3:ListBucket"
 	],
 	"Effect": "Allow",
 	"Resource": [
 		"<tools用のS3バケットのarn>",
 		"<tools用のS3バケットのarn>/tools/*"
 	]
 }

※ListBucketは、aws s3 syncコマンドでアップロードするファイルとS3バケット内のファイルを比較する際に必要

詰まった箇所

schemaspyで使用する設定ファイルのホスト名
- 最初ホスト名をlocalhostとしていましたが、GitHub Actionsのdockerコンテナ内ではservicesで定義された名前がネットワーク上のホスト名として使用されるので、postgresが正しいホスト名でした
アップロードするパス
- パスをtools/hogeにするため、tools用バケットにtoolsフォルダを作成し、その配下のhogeフォルダにアップロードする設計でしたが、toolsフォルダの存在を失念しており、そのことに気がつくまでに時間がかかりました

導入による効果と今後の改善

エンジニア以外がSQLを書く際やワイヤーフレームを作成する際、以前はエンジニアにER図の作成を依頼していましたが、SchemaSpy導入後はその必要がなくなりました。
SchemaSpyはテーブル同士の関係性やテーブルの構造を視覚的にわかりやすく表示してくれるため、エンジニア以外でも容易に理解できるようになりました。

ただし、各カラムにどのようなデータが入るかまでは表示されないため、それを把握する必要がある場合にはエンジニアに確認する必要があります。
この課題については、各カラムにコメントを追加することで解決できると考えています（参考資料）