マイグレーション作成ツール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/