これは、なにをしたくて書いたもの？
sqldefという、MySQL、PostgreSQL、SQLite3、SQL Serverのスキーマ定義をSQLで宣言的に管理できるツールを
知ったので、ちょっと試してみることにしました。
GitHub - k0kubun/sqldef: Idempotent MySQL/PostgreSQL schema management by SQL
今回は、MySQLで試してみます。
sqldef
sqldefは、RidgepoleというRuby（Rails）のDSLでスキーマ管理ができるツールにインスパイアされたツールです。
特徴としては、以下のようです。
- MySQL、PostgreSQL、SQLite3、SQL Serverに対応
- スキーマ定義をSQLで管理可能
- Goで実装されており、シングルバイナリで動作する
サポートしている機能には、RDBMS製品ごとに差があるようです。
現時点の最新版である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
- SQLite3
- SQL Server
また、テーブルやインデックスのリネーム、リネームのためのCHANGE COLUMNには対応していません。
インデックスについてはDROP＋ADDであれば問題ないようです。
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
使ってみる
では、使っていきましょう。
スキーマ定義は、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自体もそうですが、こういったアプローチのツールが存在することを知らなかったので、把握する意味でも
勉強になりました。
