こんにちは、サイオステクノロジーの佐藤 陽です。

今回も前回に引き続き 、SchemaSpy で DB 仕様書を作成するお話です。

今回は、Azure Pipelinesを使って定期的に自動で SchemaSpy を実行し、
出力された仕様書を安価に静的 Web サイトホスティングで公開する方法をご紹介します。

• SchemaSpy で DB 仕様書作成できるのは分かったけど、毎回実行するの面倒くさい
• 常に DB 仕様書が最新の状態で、チームに公開できる状況であってほしい

といった方は是非、最後までご覧ください！

はじめに

今回は SchemaSpy を利用して、DB 仕様書の作成を自動化する方法をご紹介します。

SchemaSpy の基本的な使い方に関しては前回の記事をご覧ください。

【SQLServer】SchemaSpyでDB仕様書を効率良く作成【Docker】

全体像

今回は

• SchemaSpy
• Docker
• Azure SQL Database
• Azure Storage Account
• Azure Pipelines

といったサービスを使って自動化のシステムを構築していきます。
色々なサービスや設定が絡み合ってる状況なので、分かりやすく解説していきたいと思います。

システム構成図としては以下のような形です。

SchemaSpy

前回の記事を参照してください。
Properties の内容なども、前回と同様のものを用います。

Docker

前回の記事を参照してください。

Azure SQL Database

前回の記事を参照してください。

前回と同様のサンプルデータを使用します。

-- 1. 顧客テーブルの作成
create table dbo.customers (
    customer_id int primary key,
    first_name nvarchar(50),
    last_name nvarchar(50),
    email nvarchar(100)
);
-- 2. 注文テーブルの作成
create table dbo.orders (
    order_id int primary key,
    customer_id int,
    order_date date,
    total_amount decimal(10, 2),
    foreign key (customer_id) references customers(customer_id)
);
-- 3. 注文詳細テーブルの作成
create table dbo.order_details (
    order_detail_id int primary key,
    order_id int,
    product_name nvarchar(100),
    quantity int,
    price decimal(8, 2),
    foreign key (order_id) references orders(order_id)
);
-- 4. 顧客レコードの追加
insert into customers (customer_id, first_name, last_name, email)
values (1, 'John', 'Doe', 'john.doe@example.com'),
       (2, 'Jane', 'smith', 'jane.smith@example.com'),
       (3, 'David', 'johnson', 'david.johnson@example.com');

-- 5. 注文レコードの追加
insert into orders (order_id, customer_id, order_date, total_amount)
values (101, 1, '2023-09-20', 150.50),
       (102, 2, '2023-09-21', 220.75),
       (103, 3, '2023-09-22', 75.25);

-- 6. 注文詳細レコードの追加
insert into order_details (order_detail_id, order_id, product_name, quantity, price)
values (1001, 101, 'Product A', 2, 30.25),
       (1002, 101, 'Product B', 3, 25.50),
       (1003, 102, 'Product C', 1, 45.00);

Azure Storage Account 静的 Web サイトホスティング

Azure Storage Account には静的 Web サイトのホスティング機能が存在します。
Blob のコンテナに html ファイルを配置するだけで、簡単に Web ページを公開可能です。

更に、この機能を使うにあたって追加料金は発生しないため
サイトで利用した BLOB ストレージと、運用コストに対してのみ課金されます。
格安です！

その代わり機能的には最低限のものしか提供されていません。
ただ「安価に社内で DB 仕様書を共有したい」といったような要件にはもってこいかな、と思います。

SchemaSpy で出力した成果物は、こちらの静的 Web サイトホスティングを利用して公開します。

構築方法

Azure 上から Storage Account を作成します。

「静的な Web サイト」のブレードを選択し、「有効」に設定します。

するとプライマリエンドポイントが自動で割り当たり、$web という名前のコンテナが自動で生成されます。
このコンテナの中にhtmlファイル を入れることによって、静的 Web サイトとして公開されます。

インデックスドキュメント名に関しては
今回SchemaSpy によって出力されるのがindex.htmlという名称であるため、その内容を入力します。

アクセス制限

このままだとサイトが public に公開され、全世界からアクセスされてしまいます。
「ネットワーク」のブレードを選択し、FWの設定も忘れないようにしましょう。

社内で共有したい、といったようなのユースケースだと

「社内ネットワーク(IP)からのアクセスに限定する」

などにすると良いかと思います。
これで StorageAccount の設定は完了です。

※Azure Pipelinesからのファイルのアップロードにあたり、追加の設定も必要になりますが後ほど説明します。

Azure Pipelines

主役の Azure Pipelines さんです。
Azure Pipelines は Azure DevOps のサービスの一つで、CI/CD を実現するためのサービスです。

SchemaSpy の実行から、Storage Account へのアップロードまでを一貫して担当してもらいます。

今回のシステムにおいては

1. Build Pipeline
2. Release Pipeline

の 2 ステップに分かれて実装します。

Build Pipeline の責務としては

• SchemaSpy の実行
• Artifactの発行

Release Pipeline の責務としては

• 発行されたArtifact の StorageAccount へのアップロード

を担当します。

2 つのパイプラインに分けている理由としては、Host Agentを使い分ける必要があるためです。

Build Pipeline のHost Agentは、Docker を実行する必要があるため  Ubuntu(Linux) です。
ただし、SchemaSpyの出力物をAZ Copyを使ってStorage Accountにアップロードするためには、WindowsのAgentを用意する必要があります。

そのため、今回は以下のような分担としました。

• Bilid Piplineには Ubuntu の Agent を用意し、SchemaSpyの実行
• Release Pipeline には Windows の Agent を用意し、AZ Copyの実行

あとは、今回Storage Accountへのアップロードが「リリース」の意味合いが強いので
Release Pipelineに任せる方がAzureの思想に合っているかな？という理由もあります。

デプロイの承認設定とかも簡単にできますしね。

Build Pipeline

新規に Build Pipeline を作成します。
適したテンプレートは特にないため、Starter Pipelineを選択して最初から書いていきます。

以下のものが今回組んだPipelineのyamlです。
順を追って解説していきます。

schedules:
- cron: '0 0 * * *'
  displayName: Daily midnight build
  branches:
    include:
    - main

pool:
  vmImage: ubuntu-latest

steps:
- task: DownloadSecureFile@1
  name: properties
  displayName: 'Download schemaspy properties from Pipelines Secure file'
  inputs:
    secureFile: 'schemaspy.properties'

- script: |
    mkdir jdbc
    wget -P jdbc/ https://download.microsoft.com/download/5/6/9/56904641-5f5a-449c-a284-36c36bc45652/enu/sqljdbc_12.4.0.0_enu.tar.gz
    tar -zxvf jdbc/sqljdbc_12.4.0.0_enu.tar.gz
  displayName: 'Download JDBC Driver'

- script: |
    mkdir -m 777 output
    ls -R
  displayName: 'Create Output Directory'

- task: DockerInstaller@0
  displayName: 'Install Docker CLI'

- script: |
    docker pull schemaspy/schemaspy
    docker run \
    -v "$(Build.SourcesDirectory)/output:/output" \
    -v "$(Agent.TempDirectory)/schemaspy.properties:/schemaspy.properties" \
    -v "$(Build.SourcesDirectory)/sqljdbc_12.4/enu/jars/:/drivers" \
    schemaspy/schemaspy:latest
  displayName: 'Run SchemaSpy'

- task: PublishPipelineArtifact@1
  inputs:
    targetPath: $(Build.SourcesDirectory)/output
    artifactName: specifications
  displayName: 'Publish output'

TriggerとPool

Pipelineが実行されるTriggerに関して、今回は毎日0時に実行するようなScheduleとしました。
このあたりは要件に合わせて実装を変えていただければと思います。

またpoolとしては、先ほど述べたようにDockerを使う事からUbuntu(Linux)としています。

schedules:
- cron: '0 0 * * *'
  displayName: Daily midnight build
  branches:
    include:
    - main

pool:
  vmImage: ubuntu-latest

SchemaSpy実行環境整備

前回同様、properties ファイルの作成や、JDBC ドライバーの準備が必要となります。
前回は WSL2 の Ubuntu 上に構築しましたが、今回は Azure Pipelines の Agent 上で構築していきます。

Propertiesの用意

ファイルの中身に関しては前回の記事と同一であるため、割愛します。

今回、schemaspy.propertiesをAzure PipelinesのSecureFileに保管しています。
理由としては、propertiesの中にはDatabaseへの接続情報が含まれており、公開が推奨されないためです。

Azure Key Vaultを使うことで、よりセキュアに出来るかも？とも思いましたが
今回はひとまずSecureFileのみの利用で進めていきます。

このファイルに関してはDownloadSecureFile@1のTaskを使う事で、Pipelines上から参照することが可能です。

また、このファイルはHostAgentのAgent.TempDirectory(/home/vsts/work/_temp)のパスにダウンロードされます。(※Ubuntuの場合)

- task: DownloadSecureFile@1
name: properties
displayName: 'Download schemaspy properties from Pipelines Secure file'
inputs:
secureFile: 'schemaspy.properties'

JDBCドライバーのダウンロード

次にJDBCのドライバーをインストールしていきます。

Microsoftのダウンロードページにある、リンクを見ると以下のようなURLでした。

https://go.microsoft.com/fwlink/?linkid=2243136

おそらくこのURLはどこかリダイレクトされるようになっており
それを追っていくと、以下のURLが直リンクであることが判明しました。

https://download.microsoft.com/download/5/6/9/56904641-5f5a-449c-a284-36c36bc45652/enu/sqljdbc_12.4.0.0_enu.tar.gz

今回はここからダウンロードし、解凍していきます。

- script: |
    mkdir jdbc
    wget -P jdbc/ https://download.microsoft.com/download/5/6/9/56904641-5f5a-449c-a284-36c36bc45652/enu/sqljdbc_12.4.0.0_enu.tar.gz
    tar -zxvf jdbc/sqljdbc_12.4.0.0_enu.tar.gz
  displayName: 'Download JDBC Driver'

出力先の準備

次にSchemaSpyが出力するためのディレクトリを用意してあげます。
前回の記事でも少し触れましたが、ディレクトリへの書き込み権限を与えた状態で作成します。

- script: |
    mkdir -m 777 output
    ls -R
  displayName: 'Create Output Directory'

ここまでこれば下準備は完了です。
続いてSchemaSpyを実行していきます。

SchemaSpy 実行

今回もDockerでSchemaSpyを実行していきます。

Azure Pipelines上には、Docker@2のタスクが存在しています。

ただし、実行できるコマンドとしてはbuild, push, login, logout, start, stopであり、
今回実行したいrunのコマンドが提供されていません。

そのため今回はDockerInstaller@0を利用し、自分でポチポチDockerコマンドを実行していきたいと思います。

- task: DockerInstaller@0   
  displayName: 'Install Docker CLI'

内容としては、前回の記事と同じですね。
マウントするファイルの場所が変わっていたりするので、そこは注意してください。

- script: |
    docker pull schemaspy/schemaspy
    docker run \
    -v "$(Build.SourcesDirectory)/output:/output" \
    -v "$(Agent.TempDirectory)/schemaspy.properties:/schemaspy.properties" \
    -v "$(Build.SourcesDirectory)/sqljdbc_12.4/enu/jars/:/drivers" \
    schemaspy/schemaspy:latest
  displayName: 'Run SchemaSpy'

相変わらずですが、定義済み変数は分かりづらいですね…
今回も参考のためにしばやんさんの記事を置いておきます。

Publish

最後に、出力された成果物をPublishしていきます。

(Build.SourcesDirectory)/output の場所に出力されているので、
PublishPipelineArtifact@1のタスクを利用し、ArtifactとしてPublishしていきます。

- task: PublishPipelineArtifact@1
  inputs:
    targetPath: $(Build.SourcesDirectory)/output
    artifactName: specifications
  displayName: 'Publish output'

この状態でBuild Pipelineを走らせると、Artifactとして生成物も確認できます。
これをダウンロードして、index.htmlを開くと、DB 仕様書が生成されていることが分かります。

もし生成物が空っぽだったり、上手くページが表示されない場合は、一度この時点で見直しましょう。

Release Pipeline

次にRelease Pipelineを整備していきます。
Release Pipelineを新規に作成し、以下の作業を行います

• Build PipelineのArtifactsとの紐づけ
• Continuous deployment triggerの有効化
• job, taskの設定(AZ Copyを利用したBlobへのアップロード)

AZ Copy

Azure Storage Accountの$webコンテナへのアップロードはAZ Copyを利用します。

デフォルトだとTask Versionに5.*のものが選択されますが、5.*系を使利用したところエラーが発生しました。

少し調べてみると、GitHubのIssueにも同じ問題が挙がっていました。
読んでいくとどうやら3.*系のバージョンであれば動くという事だっため、今回は3.*系のバージョンを利用しています。

Service Connection

Storage AccountへのアップロードにはService Connectionの設定が必要となります。
Service Connectionに関しては前に別記事でも紹介しているので、そちらを参照してください。

Firewallの設定

今回、Storage Accountにはネットワーク制限をかけています。
そしてこのネットワーク制限の対象はAzure Pipelinesも例外ではありません。

そのため、Azure Pipelinesからのアクセスは許可してあげる必要があります。
この設定に関して、しばやんさんも言及してくれています。

正攻法としてはしばやんさんの言及通り、Self-hosted Agentを利用するのが良いかと思います。
ただ今回は簡易的に、StorageAccountのFWの設定に対してAzureが提示するIPリストを追加してしまいます。

このネットワーク制限のレベルで十分かどうかは、チームで検討してみてください。

DevOpsリージョンの確認

StorageAccountのアクセス元となるAgentのリージョンを確認します。

OrganizationのSettingsから自分のリージョンを確認できます。
手元の環境ではSoutheast Asiaとなっている事が確認できました。

IPアドレスの確認

ではこのリージョンのHostAgentがどのIPを持つかを確認します。

こちらからIPリストのファイルをダウンロードできます。

今回は、自分のDevOpsのリージョンがSoutheast Asiaであったため

AzureCloud.southeastasia

のカテゴリに含まれるIPリストをStorageAccountのネットワーク制限に追加していきます。

さすがにPortal画面から一つ一つ追加してくのは骨が折れるので、azコマンド使いましょう。

このazコマンドの使い方と、IPリストの羅列をChatGPTに渡したらいい感じにコマンド生成してくれました。
便利。

az storage account network-rule add --account-name stschemaspy --ip-address 4.144.128.0/17 4.145.0.0/16 4.146.0.0/16 4.193.0.0/16 (略)

懸念事項

IPリストの更新

このIPアドレスリストのファイルですが、毎週更新されるようです。
必ずしも対象のリージョンが更新されるとは限りませんが、突然更新されるのは厄介ですね…

Pipelineが突然失敗したときは、IPのリストが変更になっていないか確認してみてください。

不審な挙動

ちなみにIPリストのファイルの中には

AzureDevOps.SoutheastAsia

というカテゴリもあり、最初は「これかなー？」と思ったのですが、
Pipeline上で自身(Agent)のIPアドレス取得したら、AzureCloud.SoutheastAsiaのIPアドレスが返ってきました。

あと、10回に1回くらい、AzureCloud.eastasiaのIPアドレスのAgentからのアクセスもあったりしました。
その場合デプロイ失敗してしまうので、このあたりの挙動不安になりますね…

そもそもAzureCloudのIPレンジものすごい広いですし、色々と不安が残ります。
このあたりSelf-hosted Agentも含めてまた色々調べて、ブログ化したいと思います。

動作確認

それでは動作確認してみます。

本来は Schedule で定期的に走るのですが、今回は自分でトリガーをかけます。

実行すると BuildPipeline->Release Pipeline の流れで、StorageAccount へのファイルのアップロードが行われるはずです。

静的 Web サイトで自動生成されたページへアクセスしてみると、前回の記事と同様の Web ページが公開されていることが分かります。

https://{stroage account name}.z11.web.core.windows.net/

ではせっかくなので DB に対して修正を加えてみます。
テーブルをひとつ追加しました。

-- 7. 店舗テーブルの作成
create table dbo.shops (
    shop_id int primary key,
    shop_name nvarchar(50),
    address nvarchar(100),
    phone nvarchar(10)
);

再度Pipelineを走らせ、改めて Web ページを見てみます。

すると、テーブル数が 1 つ増えているのが分かりますね。
これで定期的に自動更新される DB 仕様書の公開が完了しました。

リアルタイムの反映ではないですが、DB仕様書としては十分な追従度ではないでしょうか？

仮にもっと追従度を上げるとなると、

• テーブルの変更をトリガーとしてPipelineを実行する
• テーブルの変更があるという事はSQL のクエリの変更も想定されるため、SQL のクエリの変更をトリガーとしてPipelineを実行する

などがいいかなと思います。

まとめ

今回は SchemaSpy と AzurePipelines を組み合わせて、自動で定期的に更新される DB 仕様書を安価に公開する方法をご紹介しました。
今回の記事で DB 仕様書のメンテナンスから解放されるエンジニアが増えることを祈ります。

ではまた！

参考

テーブル設計書とER図を常に最新化して社内公開(CI/CD) : GitHub環境を使ったパターン

Azure Pipelines の Hosted Agent が持っている Outbound IP アドレスを知りたい : DevOpsからStorageAccountへのアクセス制限について