🦕
年季が入ったSQL ServerをGithub Actionsのサービスコンテナにして、CIで活用する

2024/12/25に公開
このエントリーは一休.com Advent Calendar 2024の25日目の記事になります。
Github Actionsのサービスコンテナを使って、データベースを立て、DBへの問い合わせや書き込みが発生するテストをActionsで実行する、というのはCIのひとつのステップとして、一般的だと思います。

サービスコンテナで立ち上げたDBにスキーマ管理の仕組み(prismaやAtlasのような)を使って、テーブル等をデプロイし、必要なシードデータを投入して、テストする、という流れが典型的でしょう。

スキーマ管理の仕組みを使っていない場合、DBのセットアップは厄介かもしれません。PostgreSQLなら、pg_dumpを使ってデータ以外のダンプをとって、それを、どこかにアップロードしておき、CIの処理で、ダウンロードして、サービスコンテナとして立ちげたPostgreSQLにリストアする、というやり方がありそうです。
さて、一休では、ここ数年間、古くからあるVB.NET/Classic ASPのコードのリニューアルをし続けています。

比較的簡単にリニューアルできるところは、ほぼ新アーキテクチャに移行し終わり、ついに、予約処理の最も複雑な部分のリニューアルに着手しています。

当然、新しい実装は、CIでDBへのI/Oが絡むテストを実施したいです。

が、以下の課題がありました。
一休のDBはSQL Serverであり、スキーマ管理ツールは使っていない。
SQL Serverを使っていること自体は、課題にはなりません。mcr.microsoft.com/mssql/server:2019-latestというコンテナがあるため、これを使えば、ActionsのサービスコンテナとしてSQL Serverを立ち上げることはできます。

問題は、スキーマ管理ツールを使っていない、という点です。一休のSQL Serverは、年季が入っており、15年くらい前から運用し続けられています。当時は、まだ、スキーマ管理ツールを使った開発はメジャーではありませんでした。
では、PostgreSQLと同じようになんらかのcliで定義をダンプして、リストアすればいいのではないか、と考えたんですが、sqlcmdには、定義だけを出力する機能はなさそう。

sys.objectsやsys.columnsをselect定義出力するSQLを書いて、リソースを作成するスクリプトを生成する、という手もなくはないんですが、複雑すぎてやりたくありません。
つまり、テストに必要なSQL Serverのテーブル定義をどのようにサービスコンテナに反映するか、を解決する必要があります。

 SQL Server管理オブジェクト(SMO)を使ってみる。SQL Server使いの皆さんなら、おなじみのSQL Server Management Studio(通称SSMS)というGUI管理ツールには、テーブルなどのリソースの定義情報だけを出力する機能があります。この機能をプログラムから操作できれば、やりたいことが実現できそう。

そして、この機能にプログラム経由でアクセスできるのが、SQL Server管理オブジェクト (SMO) と呼ばれるライブラリらしいのです。
公式ドキュメントには
SQL Server 管理オブジェクト (SMO) は、Microsoft SQL Server の管理のすべての側面を
プログラミングするために設計されたオブジェクトのコレクションです。
と書いてあります。

ふつうの.NETのライブラリと同じようにNugetで、導入できます。
では、どのような挙動になるか、.NET8.0/C#で以下のようなコードを書いて確認してみます。
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
class Program
{
    static void Main(string[] args)
    {

        // 接続詳細の設定
        string serverName = "10.0.190.20,1433";
        string dbName = "TEST";
        string userName = "sa";
        string password = "xxxxxxxxxx";

        // ServerConnection インスタンスの作成
        ServerConnection connection = new ServerConnection(serverName, userName, password);

        // Server オブジェクトの作成
        Server server = new Server(connection);

        // データベースオブジェクトの取得
        Database database = server.Databases[dbName];

        // スクリプティングオプションの設定
        ScriptingOptions options = new ScriptingOptions();
        options.ScriptDrops = false;
        options.WithDependencies = true;
        options.Indexes = true;
        options.DriAll = true;
        options.Triggers = false;
        options.ScriptData = false;
        options.ExtendedProperties = true;
        options.FileName = "tables.sql";

        foreach (Table table in database.Tables)
        {
            table.Script(options);
        }

        connection.Disconnect();

    }
}
データソースとなるSQL Serverの接続情報を指定して、ServerConnectionを作成。そして、そのServerConnectionを引数にServerを作成。ServerのDatabasesコレクションからテーブルを取得したいDB名を指定して、DBを取得。

あとは、ScriptingOptionsで、出力のオプションを指定します。プロパティは、

https://learn.microsoft.com/ja-jp/dotnet/api/microsoft.sqlserver.management.smo.scriptingoptions?view=sql-smo-160 にまとまっていますが、説明がないプロパティもあるので、動かしながら挙動を確認する必要があるかもしれません。

そして、DBのTablesコレクションを反復処理して、ScriptメソッドにScriptingOptionsを渡すことで定義が出力されます。

ポイントとしては、options.FileNameにファイル名を指定すると、そのファイルにテーブルの定義が出力される、という点です。

これは記述として簡便なんですが、出力された内容を編集したい場合があります。例えば、データソースのSQL Serverでファイルグループを明示的に指定してテーブルを作っている場合です。、サービスコンテナは単体テストで使う想定なので、そのようなファイルグループの指定は不要で、一律削除して、デフォルトの指定にしたほうが、コンテナのセットアップは楽そうです。
このような場合は、以下のようなやり方が考えられます。
foreach (Table table in d.Tables)
{
    // テーブルのスクリプトを生成してファイルに書き込み
    foreach (string script in table.Script(options))
    {
       // script に対して編集を行い、自前でファイルに保存する。
    }
}

table.Script(options)は、定義内容を文字列として返すので、それを取得して、正規表現なりReplaceなりで、加工を行い、その結果を自前で、ファイルに保存する、というやり方です。記述は複雑になりますが、柔軟な加工を行えます。

 テーブル定義を反映したSQL Serverコンテナを構築するさてテーブル定義を手に入れる方法が見つかりました。あとは、これをSQL Serverのコンテナに反映させ、サービスコンテナとして、使えるようにしたいです。
テーブル定義は日々、変更が加えられているので、つねに最新の定義でサービスコンテナを構築しておきたいです。と、考えるとCIで定期的に作成の処理を動かすのがよさそうです。
また、一休は、従来から、本番相当のDBで開発を行っています。よって、この本番相当DBをデータソースにすれば、本番に適用された定義変更をキャッチアップできます。
※本番相当のDB ... 本番のデータベースのバックアップから、機密情報や開発に不要な情報をマスク/ドロップした、データの内容が本番に近似している開発用のDB。日時で構築しているため最新の本番データベースの構成を開発で使える。
まず、Cloud Run Jobsを使って、上述のSMOを使ったテーブル定義抽出プログラムを定期的に実行するようにしました。抽出した定義はファイル形式にしてGoogle Cloud Storageにアップロードします。これを日時で動くようにスケジュールしておきます。
そして、以下の定義で、アップロードされた定義をsetupsqlsというディレクトリにダウンロードし、SQL Serverのコンテナをビルドします。テーブル定義の適用はイメージのビルドの中で行います。
name: build custom sql server docker image

on:
  workflow_dispatch:
  schedule:
    # 毎日、10:00 (JST) に処理を実行する。
    - cron: '0 1 * * *'

jobs:
  deploy:
    runs-on: ubuntu-latest
    permissions:
      # google-github-actions/auth で OIDC認証を行うために必要な権限を設定している
      contents: "read"
      id-token: "write"
    timeout-minutes: 10
    steps:
      - uses: actions/checkout@v4

      - id: "auth"
        uses: "google-github-actions/auth@v2"
        with:
          workload_identity_provider: projects/999999999999/locations/global/workloadIdentityPools/github-actions/providers/github-actions
          service_account: ci-sa@db-create-project.iam.gserviceaccount.com
          audience: https://github.com/ikyucom

      - name: Set up Cloud SDK
        uses: google-github-actions/setup-gcloud@v2
        with:
          project_id: db-create-project

      - name: Download SQL Scripts from GCS
        run: |
          mkdir -p setupsql
          gsutil cp gs://service-container-mssql-definition/*.sql setupsqls/

      - name: Login to GitHub Container Registry
        uses: docker/login-action@v3
        with:
          registry: ghcr.io
          username: ${{ github.repository_owner }}
          password: ${{ secrets.TOKEN_GITHUB }}

      - name: Build and push
        uses: docker/build-push-action@v5
        with:
          context: .
          push: true
          tags: |
            ghcr.io/${{ github.repository_owner }}/sqlserver-service-container:latest
ここでは、ビルドしたイメージは、最終的には、GitHub Container Registry(ghcr.io)にアップロードします。
Dockerfileは以下の通り。
FROM mcr.microsoft.com/mssql/server:2019-latest

# 環境変数の設定
ENV ACCEPT_EULA=Y
ENV MSSQL_SA_PASSWORD=strongsapassword
ENV TZ=Asia/Tokyo
ENV LANG=ja_JP.utf8

# データベースとテーブルを作成するスクリプトをコピー
COPY setupsqls/ ./setupsqls/

# SQL Server が起動した後、setup.sql を実行するスクリプトをコピー
COPY apply.sh .
COPY entrypoint.sh .
USER root
RUN chmod +x apply.sh
RUN chmod +x entrypoint.sh

# ロケールの設定を追加
RUN apt-get update \
    && apt-get install -y locales \
    && echo "ja_JP.UTF-8 UTF-8" > /etc/locale.gen \
    && locale-gen ja_JP.UTF-8 \
    && update-locale LANG=ja_JP.UTF-8

USER mssql

# SQL Server を起動してSQLスクリプトを適用
RUN (/opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started" && \
    /bin/bash ./apply.sh

CMD ["./entrypoint.sh"]

Dockerfileでは、ベースのイメージを指定して、環境変数を設定し、テーブル定義のスクリプトとapply.sh/entrypoint.shをコピーしています。

apply.shは、以下の通り、setupsqlsにダウンロードしたテーブル定義を適用します。
#!/bin/bash
# SQL Server が起動するのを待つ
sleep 5s  # 必要に応じて調整

# SQLスクリプトを実行
for sqlfile in /setupsqls/*.sql; do
    /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P strongsapassword -d master -i "$sqlfile" -C
done
entrypoint.shは、SQL Serverをフォアグラウンドで起動します。

 サービスコンテナとして使ってみる以下のように記述することで、上述の通り構築したSQL Serverをサービスコンテナとして使ってみます。
name: test

on:
  workflow_dispatch:

jobs:
  run-tests:
    runs-on: ubuntu-latest
    services:
      mssql:
        image: ghcr.io/${{ github.repository_owner }}/sqlserver-service-container:latest

        # sqlcmdでヘルスチェックするための設定
        env:
          SQLCMDUSER: sa
          SQLCMDPASSWORD: ${{ secrets.SQLCMDPASSWORD }} # 必要に応じてrepository secretsに設定
          SQLCMDDBNAME: TEST
        ports:
          - 1433:1433
        options: >-
          --health-cmd="/opt/mssql-tools18/bin/sqlcmd -Q 'SELECT 1'"
          --health-interval=10s

    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-go@v5
        with:
          go-version: '1.23.4'
      - name: Run main.go
        run: go run main.go 

起動完了までに時間がかかることもあるので、--health-cmdと--health-interval ヘルスチェックの指定をしています。

main.go には、github.com/microsoft/go-mssqldbというGolangのSQL Server向けのドライバを使って、Server=mssql;Database=TEST;User ID=sa;Password=strongsapasswordという接続文字列でDBにアクセスするコードを書き、無事接続できることを確認できました。
一休では、ともに良いサービスをつくっていく仲間を募集中です。クラウドインフラの運用やSREに興味がある方はぜひ募集ください。
https://hrmos.co/pages/ikyu/jobs/1693126708022206468
カジュアル面談もやっています。
https://www.ikyu.co.jp/recruit/engineer/
SQL Server管理オブジェクト(SMO)を使ってみる。

テーブル定義を反映したSQL Serverコンテナを構築する

サービスコンテナとして使ってみる

Discussion
