マイグレーション作成ツールprrnを作った

SQL マイグレーション作成ツール prrn を公開しました。 利用すれば Go 等のプロジェクトでの SQL マイグレーションが楽に行えるようになるかと思います。 現状は MySQL のみの対応となっています。

通常 Go などの言語で RDB へのマイグレーションを行う際は、Up/Down ファイルを記述することが多いと思います。 しかしながら多くのALTER TABLEが記述された Up/Down ファイルから最終的なスキーマ定義を知ることは難しく、実際に適用するまで分かりにくい事が多いと思います。 また、マイグレーションのファイルを記述する際もそこそこ面倒で、常に Up/Down をセットで記述するべきですがミスも起こりやすいように思います。

今回作成したツールは、宣言的にテーブルを定義する SQL ファイルからマイグレーションファイルを自動生成します。

定義ファイルにはCREATE TABLEだけでシンプルに記述します。 マイグレーション作成のコマンドを実行すると、前回の実行時とテーブル定義を比較して、マイグレーションファイルを自動生成します。 内部的には前回実行・今回の SQL ファイルを比較して Diff を生成しているような動きをしています。 具体的なフローは以下の「利用手順」を参照してください。

GitHub: https://github.com/kamijin-fanta/prrn

インストール

いずれかでインストールが可能です

• リリースからバイナリをダウンロード https://github.com/kamijin-fanta/prrn/releases
• docker pull docker.pkg.github.com/kamijin-fanta/prrn/prrn
• go install github.com/kamijin-fanta/prrn

利用手順

実際に利用しているサンプルです。 https://github.com/kamijin-fanta/prrn/tree/master/example

1. プロジェクトの初期化

prrn init コマンドで、prrn で利用するディレクトリ・スキーマ定義を行う空の main.sql ファイルを生成します。

$ prrn init
$ tree
.
└── schema
    ├── main.sql    # 今時点での宣言的なテーブル定義。基本的にCREATE TABLEだけを記述。
    ├── histories   # マイグレーションを作成した際にmain.sqlがコピーされるディレクトリ
    └── migrations  # 自動作成されたマイグレーションのUp/Downファイルが置かれるディレクトリ

2. スキーマ定義を記述する

簡単な 1 つのテーブルに 2 つのカラムが含まれるスキームを記述しました。

-- schema/main.sql
create table article (
  id bigint not null auto_increment primary key,
  content text not null
);

3. マイグレーションを作成する(1 回目)

prrn コマンドを呼び出してマイグレーションファイルを作成します。 マイグレーション名は適当に init を指定しておきます。

$ prrn make --name=init

作成されたマイグレーションファイルは以下のようになっています。 CREATE TABLE DROP TABLE が生成されました。

$ cat schema/migrations/000001_init.sql
-- +migrate Up
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE `article` (
`id` BIGINT (20) NOT NULL AUTO_INCREMENT,
`content` TEXT NOT NULL,
PRIMARY KEY (`id`)
);
SET FOREIGN_KEY_CHECKS = 1;


-- +migrate Down
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE `article`;
SET FOREIGN_KEY_CHECKS = 1;

4. スキーマ定義の編集

次は試しに original_url created_at フィールドを追加してみます。 最初に記述したスキーマのファイルを直接編集します。

-- schema/main.sql
create table article (
  id bigint not null auto_increment primary key,
  content text not null,
  original_url varchar(255) not null,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
);

5. マイグレーションを作成する(2 回目)

今回はマイグレーションを add-article-fileds という名前で作成します。

$ prrn make --name=add-article-fileds

ALTER TABLE でカラムを追加するマイグレーションが正しく生成されました。

$ cat schema/migrations/000002_add-article-fileds.sql
-- +migrate Up
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `article` ADD COLUMN `original_url` VARCHAR (255) NOT NULL AFTER `content`;
ALTER TABLE `article` ADD COLUMN `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `original_url`;
SET FOREIGN_KEY_CHECKS = 1;


-- +migrate Down
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `article` DROP COLUMN `original_url`;
ALTER TABLE `article` DROP COLUMN `created_at`;
SET FOREIGN_KEY_CHECKS = 1;

6. マイグレーションの実行

適当なマイグレーションツールを利用します。 私は sql-migrate を利用しています。

https://github.com/rubenv/sql-migrate

この際 schema/migrations で生成されたファイルを指定し、マイグレーションを実行します。 念の為内容を確認してマイグレーションを実行します。

作成に至った経緯

現状の RDB へのマイグレーションは大まかに以下のような手法が存在します。

• Up/Down ファイル作成方法
  • モデルから自動生成: Django(Python)
  • DSL から自動生成: Prisma(Graphql)
  • 手動でコーディング: gomigrate(Golang), sequelize(Node.js)
  • SQL ファイルを手動記述: goose, golang-migrate, sql-migrate
• マイグレーションの記述方法
  • DSL/クエリビルダ: Django, gomigrate, sequelize(Node.js)
  • SQL Alter Table: goose, sql-migrate, Prisma

いずれにもメリットデメリットは存在します。 マイグレーションの作成方法・その記述方法の 2 軸で考えてみます。

---

作成方法: モデル/DSL から Up/Down ファイル自動生成

Django, Prisma

• メリット
  • ユーザは常に完成形のモデル定義を行う
  • 変更内容は自動検知するので、多くの場合意識する必要がない
• デメリット
  • そもそも実装が多くないので利用できないケースが多い (Django は Python,Prisma は Node.js)

作成方法: Up/Down ファイルを手動作成

goose, golang-migrate, sql-migrate, gomigrate, sequelize

• メリット
  • 仕組みがシンプルで、多くの環境で利用できる
• デメリット
  • 連続的な変更の手順だけが記述される (宣言的でない)
  • 最終的にどんなテーブルができるかは実際に適用しないと分からない

---

記述方法: DSL/クエリビルダ

Django, gomigrate, sequelize

• メリット
  • 複数の RDBMS で動作が可能
  • プログラミング言語にて柔軟にデータ修正などが行える
• デメリット
  • 覚えるのがしんどい
  • 頭の中にある SQL をクエリビルダに変換する作業が面倒
  • DB 詳しい人がインデックス設定などでチューニングするのは難しい

記述方法: SQL

goose, sql-migrate, Prisma

• メリット
  • SQL は誰でも書ける
• デメリット
  • SQL の互換性問題から、適用できる実装が固定化される

---

個人的な気持ちとして、できる限り DB などは生に近い形で触りたいと思っています。

Index 等のチューニングを行うのはプログラマではなく、DB インフラの管理者である可能性もあります。 また、プログラマとしても SQL はプログラミング言語を問わず汎用的なものですが、DSL やクエリビルダーは言語やライブラリ特有なものとなってしまい、思考のコストが上がってしまうことも多いかと思います。 DSL,クエリビルダーでのテーブル作成で「複数フィールドの UNIQUE 制約ってどうやって書くんだったっけ」みたいな悩み方をする人も多いんじゃないかと思っています。 SQL と DSL を脳内で変換するコストというのもかなり高いものだと考えられます。

そこで今回、SQL を元にマイグレーションを自動生成する仕組みを作成しました。 といっても、コアな SQL 同士を比較してマイグレーションを作成する部分は外部ライブラリを利用しています。

https://github.com/schemalex/schemalex

schemalex の実際の出力例は以下のような感じです。 prrn は schemalex の単なるラッパと言えるでしょう。

-- $ cat old.sql
CREATE TABLE hoge (
    id INTEGER NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);


-- $ cat new.sql
CREATE TABLE hoge (
    id INTEGER NOT NULL AUTO_INCREMENT,
    c VARCHAR (20) NOT NULL DEFAULT "hoge",
    PRIMARY KEY (id)
);

CREATE TABLE fuga (
    id INTEGER NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);


-- $ schemalex old.sql new.sql

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `fuga` (
`id` INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
);

ALTER TABLE `hoge` ADD COLUMN `c` VARCHAR (20) NOT NULL DEFAULT "hoge";

SET FOREIGN_KEY_CHECKS = 1;

COMMIT;

他のツールとの比較

sqldef

https://k0kubun.hatenablog.com/entry/2018/08/25/114455

手元 SQL と DB の状態を比較して自動的にマイグレーションされるという仕様なので、CLI として使うのはハードルが高く感じました。 本番環境・開発環境でマイグレーションを実行するタイミングが変われば発行される SQL が変わる可能性が有り、危険な差分が生成されていた場合にデータ消失が発生する可能性が有ります。 マイグレーションの内容はコードレビュー等を行う対象であると考えています。 また、自動生成された SQL を書き換えて実行したいというユースケースも多いのでは無いかと考えています。

しかしながら、対応している RDBMS は MySQL,PostgreSQL,Sqlite3 と幅広く内部で利用するライブラリとして利用しても良いかもしれません。

まとめ

マイグレーション作成ツール prrn を作成した

• 特徴
  • テーブル定義・マイグレーション両方を SQL で記述
  • マイグレーションを作成した際に、当時の SQL を履歴として保存
  • マイグレーションの適用には sql-migrate/golang-migrate 等のツールを利用
• メリット
  • みんな読める DSL で記述ができる・特定言語への依存が無い
  • インデックス等のチューニングを行う際も自然に行える
• デメリット
  • 複雑なデータ変換などが必要になった際には、他のツールを利用
  • SQL の互換性問題から、適用できる実装が固定化される

その他

• README の作成に利用したツール https://leadyou.hacknock.com/