🪓

sqldef + Cloud Buildで自動migration

2022/08/11に公開

GitHub

Google Cloud

tech

はじめに

これは2021年振り返りカレンダーの6日目の記事です．

前回は，firebase周りのCI/CD構築と，環境分離を行うという内容でした．

今回は，CDに，sqldefを用いたデータベース（RDB）migrationを組み込む内容について書いていきます．

sqldefはRDB（mysql, postgresなど）用のとても便利なmigrationツールで，テーブル定義のsqlファイルと現状のスキーマを比較し，自動で差分のapplyを行ってくれます．
詳細は以下．

https://github.com/k0kubun/sqldef

また，使い方は以下などを参照されたし．

sqldefはとても便利ですが，migrationを実行する際に，対象のDBと接続する必要があります．
ローカルのDBにかけるだけならば特に気にすることはないのですが，開発環境，本番環境などに
接続してコマンドを実行するのはホストやパスワードの指定が必要で面倒臭いですし，間違えてapply先を間違えるようなミスすると大変です．
よって，sqldefのapply操作をラッピングし，CDフローに組み込むことで，いろんな面倒さを
解消することを目指します．

仕様

今回は，GCP上で，開発環境（service-dev）と本番環境（service-prd）の2つが存在し，それぞれのために以下のsqldefによるmigration操作を行いたいとします．

sqldefのdry-run （apply されるスキーマの差分の確認）．自動で行われる．
sqldefのapply（スキーマ変更の実施）．任意のタイミングでコマンドを実行し行う．

また，DBはCloud SQL上に立っており，接続にcloud-sql-proxyが必要とします．

実装

まず，ディレクトリ構成は以下のようになります．

root/
├── api
│  └── schema
│     └── schema.sql
└── release
   ├── dev
   │   ├── migration_dry_run.yaml
   │   └── migration.yaml
   └── prd
       ├── migration_dry_run.yaml
        └── migration.yaml

release下にdev, prd，それぞれのディレクトリを作り，更にその下にdry-run及び実際のapplyを行うCloud Buildの設定ファイルを作成します．

Migration Dry-run

新機能などに関するPRがマージされた時，それによってDBスキーマの変更が起きるかを知りたいというケースは往々にしてあると思います．

sqldefではdry-run機能があり，テーブル定義SQLと現状のDBのスキーマの差分を比較し，実行されるSQLを確認することが可能です．

今回はCloud Build上でDry-runを行い，その結果をslackに通知することとします．

流れは以下のようになります．

cloud-sql-proxyを開始し，Cloud SQLと繋げるようにする．
cloud-sql-proxyの接続が確立されるのを待つ．
dry-runを行う & 結果をslack通知．
cloud-sql-proxyの接続を切る．

最終的な設定ファイルは以下の感じです．これを，任意のトリガーで実行させます．

# migration_dry_run.yaml
steps:
  - id: start_sql_proxy
    name: gcr.io/cloudsql-docker/gce-proxy:1.16
    args:
      - /cloud_sql_proxy
      - -dir=/cloudsql
      - -instances=$_INSTANCE_CONNECTION_NAME
    volumes:
      - name: cloudsql
        path: /cloudsql
  - id: wait_sql_proxy_start
    name: gcr.io/cloud-builders/gcloud
    entrypoint: bash
    args:
      - -c
      - |
        while [ ! -e "/cloudsql/$_INSTANCE_CONNECTION_NAME" ]; do
          sleep 1
        done
    volumes:
      - name: cloudsql
        path: /cloudsql
    waitFor: ["-"]
  - id: migration
    name: gcr.io/cloud-builders/gcloud
    dir: api/schema
    entrypoint: bash
    args:
      - -c
      - |
        apt -y update && apt -y install wget curl \
        && wget https://github.com/k0kubun/sqldef/releases/download/v0.8.7/mysqldef_linux_amd64.tar.gz \
        && tar -zxvf mysqldef_linux_amd64.tar.gz \
        && curl -X POST --data-urlencode "payload={ \"attachments\":[
                {
                   \"fallback\":\"sqldef dry-run notification\",
                   \"color\":\"#1E90FF\",
                   \"fields\":[
                      {
                         \"title\": \"sqldef dry-run $TAG_NAME\",
                          \"value\": \"
                            $(
                              ./mysqldef --dry-run -S /cloudsql/$_INSTANCE_CONNECTION_NAME \
                              -u $(gcloud secrets versions access latest --secret=$_SECRET_DB_USERNAME) \
                              -p$(gcloud secrets versions access latest --secret=$_SECRET_DB_PASSWORD) \
                              service < schema.sql 2>&1
                            )
                          \"
                      }
                   ]
                }
              ]
            }" $(gcloud secrets versions access latest --secret=$$SECRET_SLACK_WEBHOOK_URL)
    volumes:
      - name: cloudsql
        path: /cloudsql
    waitFor: ["wait_sql_proxy_start"]
  - id: kill_sql_proxy
    name: gcr.io/cloud-builders/docker
    entrypoint: bash
    args:
      - -c
      - docker kill -s TERM $$(docker ps -q --filter ancestor=gcr.io/cloudsql-docker/gce-proxy:1.16)
    waitFor: ["migration"]
substitutions:
  _INSTANCE_CONNECTION_NAME: service-dev:asia-northeast1:service-dev
availableSecrets:
  secretManager:
    - versionName: projects/service-dev/secrets/service-dev-mysql-username/versions/latest
      env: "MYSQL_USER"
    - versionName: projects/service-dev/secrets/service-dev-mysql-password/versions/latest
      env: "MYSQL_PASSWORD"
    - versionName: projects/service-dev/secrets/service-dev-slack-webhook-url/versions/latest
      env: "SLACK_WEBHOOK_URL"

面倒な点としてはCloud BuildからCloud SQLに接続するのにsql-proxyが必要という点です．
他ステップでCloud SQLに繋げられるように，最初にバックグラウンドでsql-proxyの接続ステップを走らせて，全部が終わってからそれを切断します．
詳細は以下などが参考になります．

sqldef実行の際は，公式レポジトリよりバイナリを落としてきて，それを実行します．
バイナリポン，最強．
取ってくるバージョンは適当なものを指定します．

また，結果の通知の際に2>&1を最後に入れていますが，これは標準エラー出力も表示させるためです．
これがないと，migration実行が失敗した際に空の通知が送られてきます．

Migration Apply

applyについては，前述のdry-runオプションを消すだけです．
胆汁．
うまくやればdry-runとファイルを共通化できると思います．

実際のマイグレーションは，sqldefによるスキーマの変更だけで済まないケースも多いので，以下のようなコマンドを用いて任意実行できるようにしておくのがいいと思います．

#!/bin/bash

function sqldef_apply() {
  gcloud beta builds triggers run --tag $2 service-dev-migration
}

$1 $@

その他細かい知見など

コマンドの実行結果を送ったりする際は，標準エラー出力も出るように2>&1 をつける．
Cloud BuildからCloud SQLにつなぐ方法は，なるほど〜となった（接続確立=unixソケットが生成される?までwait）．
権限周りはよしなに

最後に

migrationを簡単に，安全に行うために，sqldefをCloud Build上から呼び出す方法について書きました．実際のサービスだとDBのデータをいじるようなmigarationも多く起きると思うので，今回書いたように単純にはいかないと思いますが，開発中＆リリース初期などのスキーマ追加を多く行うケースでは便利なんではないでしょうか．複雑なmigrationをどうラップするかについてはまた勉強したいと思います（そもそもmigrationをたくさんしたくはないが．．．）．

はじめに

仕様

実装

Migration Dry-run

Migration Apply

その他細かい知見など

最後に

Discussion