LoginSignup
89
78

More than 1 year has passed since last update.

@abe_masanori(Masanori ABE)

DBスキーマ変更管理ツール sqldef を試してみた

Last updated at Posted at 2022-01-08

1. sqldef とは

sqldef は "The easiest idempotent MySQL/PostgreSQL/SQLite3/SQL Server schema management by SQL." と謳っているDBスキーマ変更管理ツールです。

通常の開発において DDL 文を管理する場合、環境を１から作るように CREATE TABLE 文など新規作成 DDL 文を準備すると共に、既に作成済みの環境でテーブルを変更するために ALTER TABLE 文など差分適用 DDL 文を準備する必要があります。この２種類の DDL 文を二重管理しないといけないというのは DBA にとっては頭の悩ましい問題でした。（差分適用 DDL 文のみ準備し、１から環境を作る場合も全ての変更を適用するという手もありますが…）

sqldef を利用すると、変更適用先 DB の現在の状況と新規作成 DDL 文の差分を sqldef が自動で判別し、差分適用 DDL 文を生成/適用してくれるため、DBA は新規作成 DDL 文のみを管理するだけで済みます。また、適用先 DB の状況がどうであれ、sqldef で変更適用した場合は新規作成 DDL 文を１から適用した場合と同じ手順になるため、変更操作が非常にシンプルになり CI/CD のパイプラインにも乗せ易いかと思います。

本記事では Oracle Linux 8.5 で動作している MySQL 8.0.27 に対して sqldef を簡単に試してみた結果を記しておこうと思います。制限というかうまく動かないケースもいくつかあるので、その点にも触れたいと思います。

2. sqlfdef の動作例

sqldef は以下からダウンロードできます。DB ごと、アーキテクチャごとにファイルが分かれています。

Go で書かれているため、ポンと置けば使えます。私は以下のようにインストールしました。

# curl -OL https://github.com/k0kubun/sqldef/releases/download/v0.11.20/mysqldef_linux_amd64.tar.gz
# tar xf mysqldef_linux_amd64.tar.gz -C /usr/local/bin/

あと、mysql コマンドや mysqldef コマンド（sqldef の MySQL 用コマンド）でパスワードを入力するのを回避するため、操作する OS ユーザーで環境変数の設定を事前にしておきます。

export MYSQL_PWD='************'

2-1. テーブル新規作成

まず、１からテーブルを新規作成する用に以下の DDL ファイルを準備します（ファイル名 v01.sql）。ユーザー (users) への商品 (items) の販売実績 (sales) を管理する DB スキーマを想定しています。ちなみに、新規作成 DDL 文において外部キー制約を ALTER TABLE ... ADD CONSTRAINT で後付与するのは比較的よくやる手法なのですが、sqldef はサポートしていないようなので、今回は表制約の形で記述しています。

v01.sql
create table users (
  user_id integer primary key,
  user_name varchar(100),
  email_address varchar(100)
);

create table items (
  item_id integer primary key,
  item_name varchar(100)
);

create table sales (
  sales_id integer primary key,
  user_id integer not null,
  item_id integer not null,
  sales_qty integer not null,
  sales_amt integer not null,
  constraint fk_user_id
    foreign key (user_id) references users(user_id),
  constraint fk_item_id
    foreign key (item_id) references items(item_id)
);

これを sqldef を利用して空の DB（myDB）に適用する手順は以下になります（db-user は DB 接続ユーザーです）。これで v01.sql に含まれる３テーブルが作成されます。

$ mysqldef -udb-user mydb < v01.sql
-- Apply --
create table users (
  user_id integer primary key,
  user_name varchar(100),
  email_address varchar(100)
);
create table items (
  item_id integer primary key,
  item_name varchar(100)
);
create table sales (
  sales_id integer primary key,
  user_id integer not null,
  item_id integer not null,
  sales_qty integer not null,
  sales_amt integer not null,
  constraint fk_user_id
    foreign key (user_id) references users(user_id),
  constraint fk_item_id
    foreign key (item_id) references items(item_id)
);

2-2. 列の追加/削除と列変更（列長変更と NOT NULL 制約付与）

ユーザーテーブル (users) に対して以下の変更を加えたいと思います。

  • ユーザー名 (user_name) を姓 (family_nae) と名 (given_name) に分けて管理する。（user_name 列の削除と、family_name/given_name 列の追加）
  • メールアドレス (email_address) を列長 100 ⇒ 254 に変更すると共に、NOT NULL 制約を付与する。

この変更を適用した新規作成 DDL 文を準備します（ファイル名 v02.sql。本当はファイル名を変えるべきではないですが、今回は分かりやすさのため変更しています）。これは新規作成 DDL 文であり、差分適用 DDL 文（ALTER TABLE ... ADD COLUMN など）は含まれていません。

v02.sql
create table users (
  user_id integer primary key,
  family_name varchar(100) not null,
  given_name varchar(100) not null,
  email_address varchar(254) not null
);

create table items (
  item_id integer primary key,
  item_name varchar(100)
);

create table sales (
  sales_id integer primary key,
  user_id integer not null,
  item_id integer not null,
  sales_qty integer not null,
  sales_amt integer not null,
  constraint fk_user_id
    foreign key (user_id) references users(user_id),
  constraint fk_item_id
    foreign key (item_id) references items(item_id)
);

これを sqldef で以下のように適用します。新規作成 DDL 文を入力として渡しているにも関わらず、DB の現在の状況との差分を自動判別し、差分適用 DDL 文を自動生成/適用しているのが分かると思います。スゴイ！

$ mysqldef -udb-user mydb < v02.sql
-- Apply --
ALTER TABLE `users` ADD COLUMN `family_name` varchar(100) NOT NULL AFTER `user_id`;
ALTER TABLE `users` ADD COLUMN `given_name` varchar(100) NOT NULL AFTER `family_name`;
ALTER TABLE `users` CHANGE COLUMN `email_address` `email_address` varchar(254) NOT NULL;
ALTER TABLE `users` DROP COLUMN `user_name`;

ちなみに、MySQL では列リストの最初や途中に列を追加することが可能ですが、PostgreSQL などそれが不可な DB の場合は、新規作成 DDL 文でどこに追加の列を記載したかに関わらず、列リストの最後に列が追加されます。

2-3. ユニークキー制約（索引）の追加

次にユーザーのメールアドレス（users.email_address）にユニークキー制約を追加したいと思います。以下の新規作成用の DDL 文を準備します（ファイル名 v03.sql）。ちなみに、ALTER TABLE ... ADD CONSTRAINT による後付与は外部キー制約の時と同様に sqldef ではサポートしていないようなので、CREATE UNIQUE INDEX にしています（表制約での記述は OK）。

v03.sql
create table users (
  user_id integer primary key,
  family_name varchar(100) not null,
  given_name varchar(100) not null,
  email_address varchar(254) not null
);

create unique index uk_email_address on users(email_address);

create table items (
  item_id integer primary key,
  item_name varchar(100)
);

create table sales (
  sales_id integer primary key,
  user_id integer not null,
  item_id integer not null,
  sales_qty integer not null,
  sales_amt integer not null,
  constraint fk_user_id
    foreign key (user_id) references users(user_id),
  constraint fk_item_id
    foreign key (item_id) references items(item_id)
);

これを sqldef で以下のように適用します。

$ mysqldef -udb-user mydb < v03.sql
-- Apply --
create unique index uk_email_address on users(email_address);

2-4. 巻き戻し

ここまで、v01.sql で作成したテーブルに対して、v02.sql ⇒ v03.sql と差分変更を行ってきましたが、巻き戻しも差分適用と同じ手順で実行できます。

v02.sql 適用時点に戻すには以下のように実行します。v02.sql を適用した時と全く同じ手順ですね。

$ mysqldef -udb-user mydb < v02.sql
-- Apply --
ALTER TABLE `users` DROP INDEX `uk_email_address`;

さらに、v01.sql まで巻き戻ししてみます。

$ mysqldef -udb-user mydb < v01.sql
-- Apply --
ALTER TABLE `users` ADD COLUMN `user_name` varchar(100) AFTER `user_id`;
ALTER TABLE `users` CHANGE COLUMN `email_address` `email_address` varchar(100);
ALTER TABLE `users` DROP COLUMN `family_name`;
ALTER TABLE `users` DROP COLUMN `given_name`;

2-5. 空 DB からの新規適用

ここまでは差分反映 ＆ 巻き戻しを見てきましたが、空の DB に対して最新のテーブル構成を新規に作成することもできます（mydb_dev は開発用に作成した空の DB を想定）。

$ mysqldef -udb-user mydb_dev < v03.sql
-- Apply --
create table users (
  user_id integer primary key,
  family_name varchar(100) not null,
  given_name varchar(100) not null,
  email_address varchar(254) not null
);
create unique index uk_email_address on users(email_address);
create table items (
  item_id integer primary key,
  item_name varchar(100)
);
create table sales (
  sales_id integer primary key,
  user_id integer not null,
  item_id integer not null,
  sales_qty integer not null,
  sales_amt integer not null,
  constraint fk_user_id
    foreign key (user_id) references users(user_id),
  constraint fk_item_id
    foreign key (item_id) references items(item_id)
);

3. sqldef がうまく動かないケース

本章では、少し試して気づいた sqldef でうまく動かなかったケースを見ていきたいと思います。ほぼ外部キー制約絡みなのですが。

3-1. --exportオプションによるリバースエンジニアリング

sqldef には --export オプションで DB に存在するテーブルの DDL を自動生成する仕組みがあります。以下の実行例を示しますが、show create table の結果そのものっぽいですね。

$ mysqldef -udb-user mydb_dev --export
CREATE TABLE `items` (
  `item_id` int NOT NULL,
  `item_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `sales` (
  `sales_id` int NOT NULL,
  `user_id` int NOT NULL,
  `item_id` int NOT NULL,
  `sales_qty` int NOT NULL,
  `sales_amt` int NOT NULL,
  PRIMARY KEY (`sales_id`),
  KEY `fk_user_id` (`user_id`),
  KEY `fk_item_id` (`item_id`),
  CONSTRAINT `fk_item_id` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`),
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `users` (
  `user_id` int NOT NULL,
  `family_name` varchar(100) NOT NULL,
  `given_name` varchar(100) NOT NULL,
  `email_address` varchar(254) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `uk_email_address` (`email_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

こうして出力された DDL 文を見ると分かるのですが、users テーブルの CREATE TABLE 文より、それに外部キー制約で依存している sales テーブルの CREATE TABLE 文が先に出力されています。テーブルはアルファベット順で出力されているのでしょうか。

このため、この DDL 文を mysql コマンドから実行してもエラーになります（以下の手順は mydb から sqldef で生成した DDL 文を空DB mydb_dev に適用しようとしています）。

$ mysqldef -udb-user mydb --export > all.sql
$ mysql -udb-user mydb_dev < all.sql
ERROR 1824 (HY000) at line 7: Failed to open the referenced table 'users'

sqldef による生成 DDL 文の適用も同じエラーで失敗します。

$ mysqldef -udb-user mydb_dev < all.sql
-- Apply --
CREATE TABLE `items` (
  `item_id` int NOT NULL,
  `item_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `sales` (
  `sales_id` int NOT NULL,
  `user_id` int NOT NULL,
  `item_id` int NOT NULL,
  `sales_qty` int NOT NULL,
  `sales_amt` int NOT NULL,
  PRIMARY KEY (`sales_id`),
  KEY `fk_user_id` (`user_id`),
  KEY `fk_item_id` (`item_id`),
  CONSTRAINT `fk_item_id` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`),
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
2022/01/08 00:29:15 Error 1824: Failed to open the referenced table 'users'

これは、--export オプションの出力順番が改善されれば解決しそうに見えるのですが、外部キー制約による依存関係は循環する場合があるため、テーブル作成（CREATE TABLE 文）と外部キー制約付与（ ALTER TABLE ... ADD FOREIGN KEY）を分離しないと最終的には解決しないように思われます。

3-2. 外部キー制約絡みの主キー変更

sqldef は主キーの変更にも対応しているのですが、その主キーが外部キー制約に参照されている場合は問題が発生します。

具体的には、items テーブルの主キーを (item_id) から (supplier_id, item_id) に変更する場合を考えてみます（当初は１つのサプライヤからのみ商品提供を受けていたが、途中で複数サプライヤを扱うことになり、item_id はサプライヤごとに採番されるケースなどを想定）。

以下のような新規作成 DDL 文を準備します(ファイル名 v04.sql）。

create table users (
  user_id integer primary key,
  family_name varchar(100) not null,
  given_name varchar(100) not null,
  email_address varchar(254) not null
);

create unique index uk_email_address on users(email_address);

create table items (
  supplier_id integer,
  item_id integer,
  item_name varchar(100),
  constraint pk_items primary key (supplier_id, item_id)
);

create table sales (
  sales_id integer primary key,
  user_id integer not null,
  supplier_id integer,
  item_id integer not null,
  sales_qty integer not null,
  sales_amt integer not null,
  constraint fk_user_id
    foreign key (user_id) references users(user_id),
  constraint fk_supplier_id_item_id
    foreign key (supplier_id, item_id) references items(supplier_id, item_id)
);

v04.sql を sqldef で適用しようとすると、差分適用 DDL 文は以下のようになります（--dry-run は差分適用 DDL 文を生成はするが、適用はしないというオプションです）。

$ mysqldef -udb-user mydb --dry-run < v04.sql 
-- dry run --
ALTER TABLE `items` ADD COLUMN `supplier_id` integer FIRST;
ALTER TABLE `items` CHANGE COLUMN `item_id` `item_id` integer;
ALTER TABLE `items` DROP PRIMARY KEY;
ALTER TABLE `items` ADD primary key (`supplier_id`, `item_id`);
ALTER TABLE `sales` ADD COLUMN `supplier_id` integer AFTER `user_id`;
ALTER TABLE `sales` ADD CONSTRAINT `fk_supplier_id_item_id` FOREIGN KEY (`supplier_id`,`item_id`) REFERENCES `items` (`supplier_id`,`item_id`);
ALTER TABLE `sales` DROP FOREIGN KEY `fk_item_id`;
ALTER TABLE `sales` DROP INDEX `fk_item_id`;

要約すると、以下のような順番で差分適用を行おうとします。2. の時点で sales テーブルの外部キー制約が残っているのでエラーになります。

  1. items テーブルに supplier_id 列を追加
  2. items テーブルの主キーを削除
  3. items テーブルの主キーを (supplier_id, item_id) で作成
  4. sales テーブルに supplier_id 列を追加
  5. sales テーブルに外部キー制約 fk_supplier_id_item_id を追加
  6. sales テーブルの外部キー制約 fk_item_id を削除

本来は、既存外部キー削除 ⇒ 既存主キー削除 ⇒ 新規主キー作成 ⇒ 新規外部キー作成 の順番で動けばよいのでしょうが、ここまでくると複雑なので（複数テーブルに変更がまたがると更に）、変更管理ツールに任せるのは難しいような気もします。

4. まとめ・感想

本記事では、DBスキーマ変更管理ツール sqldef を MySQL で試した手順を紹介しました。また、うまく動かないケースも紹介してみました。

外部キー制約絡みは DBA が対応するのも結構大変なので仕方ないと思いますが、それ以外の部分に関してはとてもシンプルに動くのて非常に気に入っています。

（外部キー制約はいっそのこと捨ててしまえと悪魔が囁くのですが…）

また今回は試しませんでしたが、会社では SQL Server のプロジェクトが Oracle に次いで多いので、SQL Serverで動くというのが私的には結構ポイント高かったりします。（私個人でいうと Redshift とか Snowflake などなのですが…）

あと、実運用に乗せると考えると、

  • 私の周りでは新規作成 DDL 文は ER 図作成ツール（ER/StudioやA5:SQL Mk-2、astah* professional など）や設計書などから生成されることが多いので、そういった連携ができるか。
  • 差分適用時のデータ維持や、新規環境作成時に効率よくデータをロードできるか。

は今後確認していきたいと思います。

89
78
3

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
89
78