データベース設計書を常に最新する仕組みを整える

この記事は and factory Advent Calendar 2020 の16日目の記事です。
昨日は @arusu0629 さんの 【Swift】既存アプリケーションに WidgetKit を追加するまでの軌跡 でした。

概要

この記事ではスプレッドシート上にあるデータベース設計書を常に最新にし続けるための仕組みを解説しています。

背景

最近では業務でAPIを作るときにはAPI仕様書を書くということがなくなりました。Goでgrpcを使っており、protoファイルがAPI設計書の代わりになるためです。ただ、データベース設計書についてはマイグレーションファイルをバージョン管理しつつも依然スプレッドシート上に作っています。理由としては設定を可視性を担保した上でテキストファイルで全てを表現しきるのが困難であることや、非エンジニアにも理解がしやすかったりお客様に資料として提出する場合も便利であることが理由です。そのようにして現状だと完全にテキストベースにするのは難しく、以下のように１シートで１テーブルで管理しています。

ただ、スプレッドシートにすると結構な労力を払わなければいけないのが設計書に書いてある事と実際の状態の解離です。開発の途中で変更された内容や追加されたカラムが設計書に反映されていないというのは非常にありがちです。

自動的に差分をチェックする

実際のDBと設計書の解離を防ぐためには、それらの差分を自動的にチェックしてずれているときに検知できれば良さそうです。スキーマを作るのに元となる情報は一方はスプレットシート、一方はマイグレーションファイルにあります。これらを加工した上で差分比較をします。色々な手段はあるのですが、極力シンプルにしようとするのであれば、二つDBを作りそれぞれmysqldumpを取り、それらのdiffを取るという方法が良いのではと思い、そのアプローチで進めました。

スプレッドシート側

スプレッドシートの内容をそのままDBに反映させるには、力技でシートの内容をそのままCreate文にするしかありません。
以下のような力技のGoogleAppScriptでCreate文を作り、それらを全テーブル分吐きただせるようにした上で、プロジェクト内に全テーブルのCreate文を持ったsqlファイルを保持しています。

function createSql(sheetName){
  
  // アクティブスプレッドシートオブジェクト
  var spreadSheet = SpreadsheetApp.getActive();
  var sheet = spreadSheet.getSheetByName(sheetName);
  
  if (!sheet) {
    return [null, null];
  }
  
  //シートから情報取得
  Logger.log(sheetName)
  var data = sheet.getDataRange().getValues();
  
  //SQL生成
  var sql = '';
  var deleteSql = '';
  var tableName = '';
  var tableDisplayName = '';
  var nullStr = '';
  var autoIncrementStr = '';
  var defaultStr = '';
  var primaryKey = '';
  var primaryColumn = '';
  var mode = 'create'; // create or index
  var indexStarts = 0;
  var lineCounter = 0;
  for(var i = 0; i < data.length; i++) {
    if (i == 2) {
      tableName = data[i][2];
      tableDisplayName = data[i][0];
      sql += "CREATE TABLE `" + tableName + "` (\r\n";
    }
    if (i < 6) {
      continue;
    }
    if (mode == "create") {
      
      if(data[i][0] == ""){
        mode = "index";
        indexStarts = i +  3;

        if (primaryKey != ""){
         sql += ",  "+primaryKey+" \r\n";          
        }
        sql += ") ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='"+tableDisplayName+"' DEFAULT CHARSET=utf8mb4;\r\n";

        continue;
      }
      
      if (lineCounter !=0 ) {
        sql += ","
      }
      lineCounter++;
      
      if(data[i][4].toLowerCase()=="yes"){
        nullStr="NULL"
      } else {
        nullStr="NOT NULL"        
      }
      if(data[i][5].toLowerCase()=="pri"){
        if (primaryColumn == ""){
          primaryColumn = data[i][2];
        } else {
          primaryColumn += "`,`" + data[i][2];
        }
        primaryKey = "PRIMARY KEY (`"+primaryColumn+"`)"
      }
      if(data[i][6].toLowerCase()=="auto_increment"){
        autoIncrementStr="AUTO_INCREMENT"
      } else {
        autoIncrementStr=""        
      }
      if(data[i][7]==""){
        defaultStr=""
      } else if(data[i][7]=="（空文字）"){
        defaultStr="DEFAULT ''"
      } else {
        defaultStr="DEFAULT " + data[i][7]      
      }
      
      sql += "  `"+data[i][2]+"` "+data[i][3]+" "+nullStr+" "+autoIncrementStr+" "+defaultStr+" COMMENT '"+data[i][1]+"'\r\n";      
    }

    if (mode == "index" && i >= indexStarts) {
      if(data[i][0] == ""){
        mode = "";
        continue;
      }

      if(data[i][3].toLowerCase()=="yes"){
        //PRIMARY KEYはAUTO INCREMENETがエラーになるので、クエリ作成時に付与
      } else if(data[i][4].toLowerCase()=="yes"){
        sql += "ALTER TABLE `"+tableName+"` ADD UNIQUE KEY "+data[i][1]+"("+data[i][2]+");\r\n";
      } else {
        sql += "ALTER TABLE `"+tableName+"` ADD INDEX "+data[i][1]+"("+data[i][2]+");\r\n";
      }
    }
  } 
  deleteSql = "DROP TABLE `"+tableName+"`;"
  
  //クエリ書き出し
  var returnValue = {};
  returnValue[0] = sql;
  returnValue[1] = deleteSql;
  return returnValue;
}

アプリケーション側

こちらは特筆すべきことはありませんが、現状のアプリだとgithub.com/pressly/gooseを使うことが多く、これでマイグレーションを行い、スキーマをバージョン管理しています。

CI

これで設計書ベースのものと、マイグレーションから作れるものが出揃いましたので、これらをコミット毎にチェックしていきます。CIにはcircleCIを使います。CI上で二つのDBを作成し、mysqldumpを行い差分チェックを行えば良いのですが、多少工夫が必要です。

起動イメージ

Goを使っているためイメージはcircleci/golangを使います。ただ、これはそのままだとmysqlコマンドを使えないので以下のようにmysqlコマンドが使えるようにイメージを作っておきます。

FROM circleci/golang:1.15

RUN sudo apt update
RUN sudo apt install default-mysql-client

CIの流れ

多少余計な内容も含みますが、以下のような流れになってます。
アプリケーション側は単純にgooseを起動してマイグレーションを実施しています。設計書側はファイルとして保持しているものをシェルで展開します。

version: 2

jobs:
  go:
    environment:
      - GOPATH: /go
      - APP_ENV: circleci
    working_directory: /go/src/github.com/andfactory/hoge-webapp
    docker:
      - image: andfactory/hoge-go:1.0.0
        auth:
          username: xxxxxxxxxxxxxxxx
          password: $DOCKERHUB_PASSWORD
      - image: circleci/mysql:5.7-ram
        environment:
          MYSQL_ROOT_PASSWORD: rootpw
          MYSQL_DATABASE: test
          MYSQL_USER: circleci
          MYSQL_PASSWORD: circleci
      - image: circleci/redis:5.0-alpine

    steps:
      - run: dockerize -wait tcp://127.0.0.1:3306 -timeout 120s
      - run: dockerize -wait tcp://127.0.0.1:6379 -timeout 120s
      - run:
          command: 'git config --global core.quotepath false'

      - checkout

      - restore_cache:
          keys:
            - v1-go-module-cache-{{ checksum "go.sum" }}
      - run:
          command: go mod download
      - save_cache:
          key: v1-go-module-cache-{{ checksum "go.sum" }}
          paths:
            - /go/pkg/mod/cache

      # migration実行
      - run: goose -env circleci up

      # DBの差分チェック
      - run: db/checker_ci.sh

差分チェックのシェルの中身は以下のようになってます。

#!/bin/bash -eu

#設計書側のデータをセット
mysql -h127.0.0.1 -uroot -prootpw -e "CREATE DATABASE scheme_check CHARACTER SET utf8mb4;"
mysql -h127.0.0.1 -uroot -prootpw --default-character-set=utf8mb4 scheme_check < db/checker.sql

#dump作成
mysqldump -h127.0.0.1 -uroot -prootpw --default-character-set=utf8mb4 scheme_check -d -n > db/checkerResult/scheme_check.sql
mysqldump -h127.0.0.1 -uroot -prootpw --default-character-set=utf8mb4 test  -d -n > db/checkerResult/app.sql

#表記が揺れる部分を削除
set +e
sed -ie "s/-- Host: 127.0.0.1    Database:.*//" db/checkerResult/scheme_check.sql
sed -ie "s/-- Host: 127.0.0.1    Database:.*//" db/checkerResult/app.sql
sed -ie "s/-- Dump completed on.*//" db/checkerResult/scheme_check.sql
sed -ie "s/-- Dump completed on.*//" db/checkerResult/app.sql
sed -ie "s/ AUTO_INCREMENT=[0-9]*//" db/checkerResult/app.sql

#diffを取り、差分があったらエラーにする
diff db/checkerResult/scheme_check.sql db/checkerResult/app.sql > db/checkerResult/diff_plain.txt
ret=$?
cat db/checkerResult/diff_plain.txt
if [[ $ret != 0 ]]; then
  echo "差分が発生しています。" 1>&2
  exit 1
fi
echo "差分CheckOK!" 1>&2
exit 0
set -e

これで、毎回コミットするたびにこのチェックが走るようになり、設計の記載ミスが防げるようになります。

やってみて

この仕組みをプロジェクトの途中から導入したのですが、コメント表記の揺れ、設計書の反映漏れ、インデックス名の間違いなどが山ほど検出されました…。チェックされないドキュメントを正しい状態に保ち続けるのは非常に困難だということを改めて思い知らされました。

protoでAPI設計書とするように、もっとシンプルにテキストベースで完結するやり方ができればとは思うのですが同じようなやり方でデータベース設計書を管理されている方がいれば参考にしてみてください。