CLOVER🍀

That was when it all began.

MySQL、PostgreSQL、SQLite3、SQL Serverのスキーマ定義を宣言的に管理するsqldefを、MySQLで試す

これは、なにをしたくて書いたもの?

sqldefという、MySQL、PostgreSQL、SQLite3、SQL Serverのスキーマ定義をSQLで宣言的に管理できるツールを
知ったので、ちょっと試してみることにしました。

GitHub - k0kubun/sqldef: Idempotent MySQL/PostgreSQL schema management by SQL

今回は、MySQLで試してみます。

sqldef

sqldefは、RidgepoleというRuby(Rails)のDSLでスキーマ管理ができるツールにインスパイアされたツールです。

GitHub - ridgepole/ridgepole: Ridgepole is a tool to manage DB schema. It defines DB schema using Rails DSL, and updates DB schema according to DSL. (like Chef/Puppet)

特徴としては、以下のようです。

  • MySQL、PostgreSQL、SQLite3、SQL Serverに対応
  • スキーマ定義をSQLで管理可能
  • Goで実装されており、シングルバイナリで動作する

サポートしている機能には、RDBMS製品ごとに差があるようです。

sqldef / Supported features

現時点の最新版である0.11.17の時点で、以下のDDLに対応しているようです。

  • MySQL
    • Table: CREATE TABLE, DROP TABLE
    • Column: ADD COLUMN, CHANGE COLUMN, DROP COLUMN
    • Index: ADD INDEX, ADD UNIQUE INDEX, CREATE INDEX, CREATE UNIQUE INDEX, DROP INDEX
    • Primary key: ADD PRIMARY KEY, DROP PRIMARY KEY
    • Foreign Key: ADD FOREIGN KEY, DROP FOREIGN KEY
    • View: CREATE VIEW, CREATE OR REPLACE VIEW, DROP VIEW
  • PostgreSQL
    • Table: CREATE TABLE, DROP TABLE
    • Column: ADD COLUMN, ALTER COLUMN, DROP COLUMN
    • Index: CREATE INDEX, CREATE UNIQUE INDEX, DROP INDEX
    • Foreign / Primary Key: ADD FOREIGN KEY, DROP CONSTRAINT
    • Policy: CREATE POLICY, DROP POLICY
    • View: CREATE VIEW, CREATE OR REPLACE VIEW, DROP VIEW
  • SQLite3
    • Table: CREATE TABLE, DROP TABLE
    • View: CREATE VIEW, DROP VIEW
  • SQL Server
    • Table: CREATE TABLE, DROP TABLE
    • Column: ADD COLUMN, DROP COLUMN, DROP CONSTRAINT
    • Index: ADD INDEX, DROP INDEX
    • Primary key: ADD PRIMARY KEY, DROP PRIMARY KEY
    • VIEW: CREATE VIEW, DROP VIEW

また、テーブルやインデックスのリネーム、リネームのためのCHANGE COLUMNには対応していません。
インデックスについてはDROP+ADDであれば問題ないようです。

sqldef / Limitations

sqldefの説明については、これくらいにしておきましょう。

skeema

話を少し変えて。MySQL限定であれば、似たツールとしてskeemaというものもあるようです。

Skeema.io | MySQL and MariaDB schema management system

CREATE文をリポジトリ管理して変更を追跡し、実際の環境に適用します。

サイトのトップページを見ると、特徴としては以下のようです。

  • 既存のテーブル定義をエクスポートしてスキーマ定義の追跡を開始できる
  • スキーマ定義ファイルに書かれたCREATE TABLE文を変更、または新しく追加することで変更ができる
  • 本番環境、ステージング環境、開発環境など、複数の環境を管理できる
  • 大きなテーブルがある場合のオンラインスキーマ変更に対応している
  • ひとつのデータベースにも、シャーディングされたデータベースにも対応

対応しているデータベースは、MySQL 5.5〜8.0、Percona Server 5.5〜8.0、MariaDB 10.1〜10.6です。

Skeema.io | Docs: Requirements

skeema利用時のMySQLのユーザーには、SUPER権限またはALL PRIVILEGES ON *.*を付与されていることが
求められます。

製品のEditionとしては、Community、Plus、Maxの3種類があり、Plus以降は有償です。

Skeema.io | Products and Downloads

Communityとの差は、Amazon Auroraのサポート、Windowsのサポート、ViewやTrigger、SSL/TLS証明書などの管理
といったところにあります。

こちらの方が(Plus以上を選べば)機能は豊富そうですが、今回はシンプルに使えそうなsqldefを試したいと思います。

schemalex

また、MySQLに関しては、2つのスキーマ定義(SQL)を比較して、マイグレーションを行うための一連のSQL文を
生成するschemalexというものもあるようです。

GitHub - schemalex/schemalex: Generate difference sql of two mysql schema

環境

今回の環境は、こちら。Ubuntu Linux 20.04 LTSです。

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 20.04.3 LTS
Release:        20.04
Codename:       focal


$ uname -srvmpio
Linux 5.4.0-91-generic #102-Ubuntu SMP Fri Nov 5 16:31:28 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux

MySQLについてはこちらで、172.17.0.2で動作しているものとします。

$ mysql --version
mysql  Ver 8.0.27 for Linux on x86_64 (MySQL Community Server - GPL)

データベースの方の準備ですが

$ mysql -uroot -p

exampleというデータベースと、このデータベースに全権限を与えたmyuserというユーザーを作成しておきました。

mysql> create database example;
Query OK, 1 row affected (0.03 sec)

mysql> create user myuser@localhost identified by 'password';
Query OK, 0 rows affected (0.03 sec)

mysql> create user myuser@'%' identified by 'password';
Query OK, 0 rows affected (0.02 sec)

mysql> grant all privileges on example.* to myuser@localhost;
Query OK, 0 rows affected, 1 warning (0.03 sec)

Warning (Code 1285): MySQL is started in --skip-name-resolve mode; you must restart it without this switch for this grant to work
mysql> grant all privileges on example.* to myuser@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

こちらを使っていこうと思います。

sqldefのインストール

以下から、使っているプラットフォームとRDBMSに合ったバイナリをダウンロードします。

Releases · k0kubun/sqldef · GitHub

RDBMSごとにコマンドが異なります(各RDBMSのクライアントとオプションを合わせているみたいです)。

今回はUbuntu Linux上で、MySQLのスキーマ管理を行うのでこちらをダウンロード。

$ curl -LO https://github.com/k0kubun/sqldef/releases/download/v0.11.17/mysqldef_linux_amd64.tar.gz

展開すると、実行バイナリが現れます。

$ tar xf mysqldef_linux_amd64.tar.gz


$ ll
合計 7084
drwxrwxr-x  2 xxxxx xxxxx    4096  1月  3 20:22 ./
drwxrwxr-x 27 xxxxx xxxxx    4096  1月  3 19:31 ../
-rwxr-xr-x  1 xxxxx xxxxx 5283840  1月  3 17:32 mysqldef*
-rw-rw-r--  1 xxxxx xxxxx 1961217  1月  3 20:21 mysqldef_linux_amd64.tar.gz

バージョン。

$ ./mysqldef --version
v0.11.17

ヘルプ。

$ ./mysqldef --help
Usage:
  mysqldef [options] db_name

Application Options:
  -u, --user=user_name              MySQL user name (default: root)
  -p, --password=password           MySQL user password, overridden by $MYSQL_PWD
  -h, --host=host_name              Host to connect to the MySQL server (default: 127.0.0.1)
  -P, --port=port_num               Port used for the connection (default: 3306)
  -S, --socket=socket               The socket file to use for connection
      --password-prompt             Force MySQL user password prompt
      --enable-cleartext-plugin     Enable/disable the clear text authentication plugin
      --file=sql_file               Read schema SQL from the file, rather than stdin (default: -)
      --dry-run                     Don't run DDLs but just show them
      --export                      Just dump the current schema to stdout
      --skip-drop                   Skip destructive changes such as DROP
      --help                        Show this help
      --version                     Show this version

使ってみる

では、使っていきましょう。

sqldef / Usage

sqldef / mysqldef

スキーマ定義は、SQLファイルか標準入力で与えるようです。今回は、SQLファイルで行っていきましょう。

この時点では、exampleデータベースの中身は空です。

mysql> use example;
Database changed

mysql> show tables;
Empty set (0.00 sec)

こんなファイルを用意。

schema1.sql

create table book (
  isbn varchar(14),
  title varchar(255)
);

この後、SQLファイルを変更した体で、schema[N].sqlとしていきます。

まずは差分確認。--dry-runで確認できます。スキーマ定義ファイルは、--fileで指定します。

$ ./mysqldef -h172.17.0.2 -umyuser -ppassword --file=schema1.sql --dry-run example
-- dry run --
create table book (
  isbn varchar(14),
  title varchar(255)
);

スキーマ定義を標準入力で与える場合は、リダイレクトで。

$ ./mysqldef -h172.17.0.2 -umyuser -ppassword --dry-run example < schema1.sql
-- dry run --
create table book (
  isbn varchar(14),
  title varchar(255)
);

その他のオプションは、mysqlコマンドと同じ意味なのですがパスワードは対話形式で入力できないみたいです。

MYSQL_PWDという環境変数にパスワードを設定しておくと参照するようなので、こちらを利用しましょう。

$ export MYSQL_PWD=password

確認。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema1.sql --dry-run example
-- dry run --
create table book (
  isbn varchar(14),
  title varchar(255)
);

パスワード入力が不要になりました。

適用してみます。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema1.sql example
-- Apply --
create table book (
  isbn varchar(14),
  title varchar(255)
);

テーブルが作成されました。

mysql> show tables;
+-------------------+
| Tables_in_example |
+-------------------+
| book              |
+-------------------+
1 row in set (0.00 sec)

適用後は差分がなくなります。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema1.sql --dry-run example
-- Nothing is modified --


$ ./mysqldef -h172.17.0.2 -umyuser --file=schema1.sql example
-- Nothing is modified --

次は、このファイルをコピーして

$ cp schema1.sql schema2.sql

プライマリーキーの定義を追加してみます。

schema2.sql

create table book (
  isbn varchar(14),
  title varchar(255),
  primary key(isbn)
);

--dry-runで確認してみます。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema2.sql --dry-run example
-- dry run --
ALTER TABLE `book` ADD primary key (`isbn`);

プライマリーキー定義をALTER TABLEする文が、差分として現れました。

適用。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema2.sql example
-- Apply --
ALTER TABLE `book` ADD primary key (`isbn`);

変更前後のテーブル定義。

mysql> show create table book;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book  | CREATE TABLE `book` (
  `isbn` varchar(14) COLLATE utf8mb4_ja_0900_as_cs_ks DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_ja_0900_as_cs_ks DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs_ks |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)




mysql> show create table book;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book  | CREATE TABLE `book` (
  `isbn` varchar(14) COLLATE utf8mb4_ja_0900_as_cs_ks NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_ja_0900_as_cs_ks DEFAULT NULL,
  PRIMARY KEY (`isbn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs_ks |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ただ、まだなぜか差分が出ます。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema2.sql example
-- Apply --
ALTER TABLE `book` CHANGE COLUMN `isbn` `isbn` varchar(14);

どうやら、プライマリーキーを定義したカラムには明示的にNOT NULLを付与しないと差分になるみたいです。

schema3.sql

create table book (
  isbn varchar(14) not null,
  title varchar(255),
  primary key(isbn)
);

これで差分がなくなりました。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema3.sql --dry-run example
-- Nothing is modified --

次は、カラムを2つ追加してみます。

schema4.sql

create table book (
  isbn varchar(14) not null,
  title varchar(255),
  price int,
  summary varchar(255),
  primary key(isbn)
);

差分確認。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema4.sql --dry-run example
-- dry run --
ALTER TABLE `book` ADD COLUMN `price` int AFTER `title`;
ALTER TABLE `book` ADD COLUMN `summary` varchar(255) AFTER `price`;

適用。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema4.sql example
-- Apply --
ALTER TABLE `book` ADD COLUMN `price` int AFTER `title`;
ALTER TABLE `book` ADD COLUMN `summary` varchar(255) AFTER `price`;


$ ./mysqldef -h172.17.0.2 -umyuser --file=schema4.sql --dry-run example
-- Nothing is modified --

差分もなくなりました。

テーブル定義。

mysql> show create table book;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book  | CREATE TABLE `book` (
  `isbn` varchar(14) COLLATE utf8mb4_ja_0900_as_cs_ks NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_ja_0900_as_cs_ks DEFAULT NULL,
  `price` int DEFAULT NULL,
  `summary` varchar(255) COLLATE utf8mb4_ja_0900_as_cs_ks DEFAULT NULL,
  PRIMARY KEY (`isbn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs_ks |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

追加したカラムを、ひとつ削除してみます。

schema5.sql

create table book (
  isbn varchar(14) not null,
  title varchar(255),
  price int,
  primary key(isbn)
);

差分になりました。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema5.sql --dry-run example
-- dry run --
ALTER TABLE `book` DROP COLUMN `summary`;

適用。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema5.sql example
-- Apply --
ALTER TABLE `book` DROP COLUMN `summary`;


$ ./mysqldef -h172.17.0.2 -umyuser --file=schema5.sql --dry-run example
-- Nothing is modified --

差分もありません。

さらにテーブルを追加してみます。

schema6.sql

create table book (
  isbn varchar(14) not null,
  title varchar(255),
  price int,
  primary key(isbn)
);

create table author (
  id varchar(10) not null,
  name varchar(20) not null,
  primary key(id)
);

差分確認。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema6.sql --dry-run example
-- dry run --
create table author (
  id varchar(10) not null,
  name varchar(20) not null,
  primary key(id)
);

適用。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema6.sql example
-- Apply --
create table author (
  id varchar(10) not null,
  name varchar(20) not null,
  primary key(id)
);


$ ./mysqldef -h172.17.0.2 -umyuser --file=schema6.sql --dry-run example
-- Nothing is modified --

テーブルが増えました。

mysql> show tables;
+-------------------+
| Tables_in_example |
+-------------------+
| author            |
| book              |
+-------------------+
2 rows in set (0.00 sec)

追加したテーブル定義を消してみます。

schema7.sql

create table book (
  isbn varchar(14) not null,
  title varchar(255),
  price int,
  primary key(isbn)
);

差分になりました。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema7.sql --dry-run example
-- dry run --
DROP TABLE `author`;

適用。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema7.sql example
-- Apply --
DROP TABLE `author`;


$ ./mysqldef -h172.17.0.2 -umyuser --file=schema7.sql --dry-run example
-- Nothing is modified --

テーブルが削除されたことが確認できます。

mysql> show tables;
+-------------------+
| Tables_in_example |
+-------------------+
| book              |
+-------------------+
1 row in set (0.01 sec)

既存のスキーマ定義をエクスポートする

先ほどの、2つのテーブル定義があるバージョンを1度適用しておきます。

schema6.sql

create table book (
  isbn varchar(14) not null,
  title varchar(255),
  price int,
  primary key(isbn)
);

create table author (
  id varchar(10) not null,
  name varchar(20) not null,
  primary key(id)
);

実行。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema6.sql example

--exportオプションを使うことで、現在のスキーマ定義を標準出力に出力できます。

$ ./mysqldef -h172.17.0.2 -umyuser --export example
CREATE TABLE `author` (
  `id` varchar(10) COLLATE utf8mb4_ja_0900_as_cs_ks NOT NULL,
  `name` varchar(20) COLLATE utf8mb4_ja_0900_as_cs_ks NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs_ks;

CREATE TABLE `book` (
  `isbn` varchar(14) COLLATE utf8mb4_ja_0900_as_cs_ks NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_ja_0900_as_cs_ks DEFAULT NULL,
  `price` int DEFAULT NULL,
  PRIMARY KEY (`isbn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs_ks;

ちょっと情報量が多くなるかもですが、こちらをファイル出力してスキーマ管理のベースにできます。

$ ./mysqldef -h172.17.0.2 -umyuser --export example > exported.sql

シンタックスエラーになるような場合は?

試してみると、拾ってくれるみたいです。

意図的に、,を落としています。

schema_bad.sql

create table book (
  isbn varchar(14)
  title varchar(255)
  primary key(isbn)
);

--dry-runで確認。

$ ./mysqldef -h172.17.0.2 -umyuser --file=schema_bad.sql --dry-run example
found syntax error when parsing DDL "create table book (
  isbn varchar(14)
  title varchar(255)
  primary key(isbn)
)": syntax error at position 47 near 'title'

こちらはどうやって確認しているのかな?と思ったのですが、Vitessのリポジトリからパーサーを抽出しているようです。

https://github.com/k0kubun/sqldef/tree/v0.11.17/sqlparser

https://github.com/k0kubun/sqldef/blob/v0.11.17/sqlparser/parser.y

元はこちらですね。

https://github.com/vitessio/vitess/tree/v12.0.2/go/vt/sqlparser

とはいえ、VitessはMySQL互換なのでsqldefでは独自に修正が入っているようですが。

まとめ

MySQL、PostgreSQL、SQLite3、SQL Serverのスキーマ定義を宣言的に管理するツールである、sqldefを試してみました。

sqldef自体もそうですが、こういったアプローチのツールが存在することを知らなかったので、把握する意味でも
勉強になりました。