Schemalexを使用したマイグレーションファイル自動生成

はじめに

こんにちは。現在アプリボットで内定者バイトをしている伊藤です。
今回は、schemalexを使用したマイグレーションファイルの自動生成の手法について紹介したいと思います。

Applibot Advent Calendar 2020 16日目の記事になります。

今回利用するリポジトリ

導入背景

現在担当しているプロジェクトは絶賛開発段階で、MySQLのスキーマは変更される度にDBをリセットしています。
つまりマイグレーション管理が必要ない状態です。
しかし今後リリースした後には毎回リセットするわけにはいかないので、
今回リリース後に必要となるマイグレーションファイルを自動で生成するツールを作成することになりました。

構成

今回は以下のようなディレクトリを想定しており、init/1_main.up.sql に変更を加えていくと自動でmigration以下にその差分のSQLが生成されるものを作成していきます。
マイグレーションツールには、golang-migrateを使用します。

├── init
│   └── 1_main.up.sql
└── migration
    ├── xxxx_migration_gen.up.sql
    ├── xxxx_migration_gen.up.sql

    ︙

    └── xxxx_migration_gen.up.sql

Schemalexについて

schemalex

This tool can be used to generate the difference, or more precisely, the statements required to migrate from/to, between two MySQL schema.

2つのMySQLのスキーマからマイグレーションに必要な差分を生成するツール

このSchemelexに渡すスキーマには、ローカルファイルやMySQLのデータソースなどが指定できます。

前準備

初期のDBのスキーマと、マイグレーション用のMySQLコンテナを用意しておきます。

ddl/init/1_main.up.sql

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  `id` BIGINT NOT NULL DEFAULT 0,
  `created_at` DATETIME(3) NOT NULL DEFAULT "1970-01-01 00:00:00",
  `updated_at` DATETIME(3) NOT NULL DEFAULT "1970-01-01 00:00:00",
  PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8mb4;

docker-compose.yml

version: '3'
services:
  db:
    image: mysql:5.7
    environment:
      - MYSQL_ALLOW_EMPTY_PASSWORD=yes
      - TZ='Asia/Tokyo'
    ports:
      - 13306:3306

実装

1. MySQLコンテナに ddl/migration 以下のマイグレーションファイルを使用してマイグレーションを実行
2. コンテナのDBをダンプ（golang-migrate で生成されるschema_migrationsを除外）
3. schemadiff を使用して ddl/init/1_main.up.sql と ダンプしたファイルを比較
4. 比較結果から新たなマイグレーションファイルを作成

ソースコード

#! /bin/bash

set -eu

mysql -uroot -h 0.0.0.0 -P 13306 < ./ddl/default.sql

migrations=`find ddl/migration -type f -name \*.up.sql | wc -l`
if [ $migrations -ne "0" ]; then
  yes | migrate -path ddl/migration -database "mysql://root:@tcp(localhost:13306)/migration" up
fi

docker-compose exec db mysqldump -uroot \
  -d migration \
  --ignore-table="migration.schema_migrations" > dump.sql

diff=`schemadiff -t=false dump.sql ./ddl/init/1_main.up.sql`

if [ -n "$diff" ]; then
  echo $diff > ddl/migration/$(date +%s)_migration_gen.up.sql
fi

rm dump.sql

結果1

実行前

├── init
│   └── 1_main.up.sql
└── migration

ddl/init/1_main.up.sql を変更せずに実行

.
├── init
│   └── 1_main.up.sql
└── migration
    └── 1607881859_migration_gen.up.sql

1607881859_migration_gen.up.sql

CREATE TABLE `users` ( `id` BIGINT (20) NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00', `updated_at` DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00', PRIMARY KEY (`id`) ) ENGINE = InnoDB, DEFAULT CHARACTER SET = utf8mb4;

結果2

結果1の後に ddl/init/1_main.up.sql を以下の様に変更して実行

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  `id` BIGINT NOT NULL DEFAULT 0,
  `name` VARCHAR(256), // <- カラムの追加
  `created_at` DATETIME NOT NULL DEFAULT "1970-01-01 00:00:00",
  `updated_at` DATETIME NOT NULL DEFAULT "1970-01-01 00:00:00",
  PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8mb4;

// テーブル追加
DROP TABLE IF EXISTS user_items;
CREATE TABLE user_items (
  `id` BIGINT NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8mb4;

├── init
│   └── 1_main.up.sql
└── migration
    ├── 1607881859_migration_gen.up.sql
    └── 1607882077_migration_gen.up.sql

1607882077_migration_gen.up.sql

CREATE TABLE `user_items` ( `id` BIGINT (20) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) ) ENGINE = InnoDB, DEFAULT CHARACTER SET = utf8mb4; ALTER TABLE `users` ADD COLUMN `name` VARCHAR (256) DEFAULT NULL AFTER `id`;

まとめ

2つの異なるスキーマからマイグレーションファイルを自動生成する手法を紹介しました。
今回はサラッと紹介するためにシェルスクリプトのみでの実装になりましたが、実際のプロジェクトコードでは github.com/urfave/cli を使用してgoのツールとして作成しています。
そちらでは、今回割愛した生成されるSQLのフォーマット機能や、ファイル名をUnixTimeではなく10001, 10002のように連番で出力する機能だったりを追加で実装しています。
schemalex大変使い心地がよかったので、ぜひ試してみてください！

「Applibot Advent Calendar 2020」 16日目の記事でした。

明日は @Nakamuro-unl さんです。

おまけ

自動生成のコードからは割愛しましたが、 schemalex の中にはLint機能を持ったツールもあります。

$ cat sql.sql
CREATE TABLE `users` ( `id` BIGINT (20) NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00', `updated_at` DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00', PRIMARY KEY (`id`) ) ENGINE = InnoDB, DEFAULT CHARACTER SET = utf8mb4;

$ schemalint sql.sql
CREATE TABLE `users` (
  `id` BIGINT (20) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
  `updated_at` DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB, DEFAULT CHARACTER SET = utf8mb4;%

この schemalint は ALTER文が含まれているものに対しては、うまく動作しないためそのまま導入するのは少し難しいと思ってます。
まとめに、プロジェクトコードにはフォーマット機能が入っているという話をしましたが、そこではこの schemalint を使用していますが、文字列走査をしてから CREATE文のみ schemalint に流すといった形を取っています。
LinterはLinterで完結させたいなと少し思っているので、なにか良いツールがあれば教えていただけると助かります！