Go製マイグレーションツールまとめ

# 比較対象
* CLIで使える
* MySQLが使える

| 名前 | Star数 | スキーマ | 方式 | Dry Run | 設定 |
| --- | --- | --- | --- | --- | --- |
| [liamstask/goose](https://bitbucket.org/liamstask/goose) | - | Go/SQL | Up/Down | - | YAML |
| [mattes/migrate](https://github.com/mattes/migrate) | 892 | SQL | Up/Down | - | - |
| [rubenv/sql-migrate](https://github.com/rubenv/sql-migrate) | 576 | SQL | Up/Down | ✔ | YAML |
| [elwinar/rambler](https://github.com/elwinar/rambler) | 371 | SQL | Up/Down | - | JSON |
| [naoina/migu](https://github.com/naoina/migu) | 29 | Go(struct) | Sync | ✔ | - | 
| [dev-cloverlab/carpenter](https://github.com/dev-cloverlab/carpenter) | 12 | JSON/実DB | Sync | ✔ | - |

2017/01時点

今回比較しなかったもの

* https://github.com/tanel/dbmigrate
* https://github.com/BurntSushi/migration
* https://github.com/Boostport/migration
* https://github.com/github/gh-ost
* https://github.com/wawandco/transporter
* https://github.com/GuiaBolso/darwin
* https://github.com/DavidHuie/gomigrate
* https://github.com/go-gormigrate/gormigrate
* https://github.com/pravasan/pravasan
* https://github.com/eure/kamimai

# liamstask/goose
https://bitbucket.org/liamstask/goose

__対応DB__

* MySQL
* PostgreSQL
* SQLite3

__インストール__

```bash
go get bitbucket.org/liamstask/goose/cmd/goose
```

__設定__

```yaml:db/dbconf.yml
development:
  driver: mysql
  open: user=root dbname=goose_test
```

__雑感__

* 普通な感じ
* 使ってる人多そう
* 適用履歴がテーブルに残るのは良い

## マイグレーションファイル
```bash
$ goose create CreatePosts sql
goose: created /Users/nownabe/src/github.com/nownabe/research-go/migration-tools/goose/db/migrations/20170111152547_CreatePosts.sql
```

CLIで雛形を作成してそのファイルにUp/DownのSQLを書きます。

```sql:db/migrations/20170111152547_CreatePosts.sql
-- +goose Up
-- SQL in section 'Up' is executed when this migration is applied
CREATE TABLE `posts` (
  `post_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `author` VARCHAR(255) NOT NULL,
  `text` TEXT NOT NULL
) ENGINE = InnoDB;

-- +goose Down
-- SQL section 'Down' is executed when this migration is rolled back
DROP TABLE `posts`;
```

Golangでもマイグレーションファイル書けますが、あまり需要なさそうなので省略。

## コマンド
```bash
$ goose
goose is a database migration management system for Go projects.

Usage:
    goose [options] <subcommand> [subcommand options]

Options:
  -env string
        which DB environment to use (default "development")
  -path string
        folder containing db info (default "db")
  -pgschema string
        which postgres-schema to migrate (default = none)

Commands:
    up         Migrate the DB to the most recent version available
    down       Roll back the version by 1
    redo       Re-run the latest migration
    status     dump the migration status for the current DB
    create     Create the scaffolding for a new migration
    dbversion  Print the current version of the database
```

## バージョン管理
`goose_db_version`というテーブルに保存されます。

```sql
> select * from goose_db_version;
+----+----------------+------------+---------------------+
| id | version_id     | is_applied | tstamp              |
+----+----------------+------------+---------------------+
|  1 |              0 |          1 | 2017-01-11 15:35:03 |
|  2 | 20170111152547 |          1 | 2017-01-11 15:47:34 |
|  3 | 20170111152547 |          0 | 2017-01-11 15:47:36 |
|  4 | 20170111152547 |          1 | 2017-01-11 15:47:36 |
|  5 | 20170111152547 |          0 | 2017-01-11 15:47:53 |
|  6 | 20170111152547 |          1 | 2017-01-11 15:48:06 |
+----+----------------+------------+---------------------+
6 rows in set (0.00 sec)
```

# mattes/migrate
https://github.com/mattes/migrate

__対応DB__

* Cassandra
* MongoDB
* MySQL (experimental)
* PostgreSQL
* SQLite
* Ql

__インストール__

```bash
go get -u github.com/mattes/migrate
```

__雑感__

* コマンドが豊富
* CLIの結果が色付きで見やすい

![スクリーンショット 2017-01-11 16.39.19.png (43.4 kB)](https://img.esa.io/uploads/production/attachments/1679/2017/01/11/4429/bbc0e934-6e59-44fb-8552-d08122fe1252.png)

## マイグレーションファイル
```bash
$ migrate -url="mysql://root@tcp(127.0.0.1:3306)/migrate_test" create create_posts
Version 1484119339 migration files created in /Users/nownabe/src/github.com/nownabe/research-go/migration-tools/goose:
1484119339_create_posts.up.sql
1484119339_create_posts.down.sql
```

CLIでUp/Downそれぞれの雛形ファイルが作成されます。

```sql:1484119339_create_posts.up.sql
CREATE TABLE `posts` (
  `post_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `author` VARCHAR(255) NOT NULL,
  `text` TEXT NOT NULL
) ENGINE = InnoDB;
```

```sql:1484119339_create_posts.down.sql
DROP TABLE `posts`;
```

## コマンド
```bash
$ migrate
usage: migrate [-path=<path>] -url=<url> <command> [<args>]

Commands:
   create <name>  Create a new migration
   up             Apply all -up- migrations
   down           Apply all -down- migrations
   reset          Down followed by Up
   redo           Roll back most recent migration, then apply it again
   version        Show current migration version
   migrate <n>    Apply migrations -n|+n
   goto <v>       Migrate to version v
   help           Show this help

'-path' defaults to current working directory.
```

## バージョン管理
`schema_migrations`というテーブルに保存されます。

```sql
> select * from schema_migrations;
+------------+
| version    |
+------------+
| 1484119339 |
+------------+
1 row in set (0.00 sec)
```

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

__対応DB__

* MySQL
* MSSQL
* Oracle Database
* PostgreSQL
* SQLite

__インストール__

```bash
go get github.com/rubenv/sql-migrate/...
```

__設定__

```dbconfig.yml
development:
  dialect: mysql
  datasource: root:@tcp(127.0.0.1:3306)/sqlmigrate_test?parseTime=true
  dir: migrations/mysql # ディレクトリを作っておく必要がある
```

__雑感__

* 対応RDBMSが多い

## マイグレーションファイル
雛形作成機能はなし。

```sql:migrations/mysql/1_create_posts.sql
-- +migrate Up
-- SQL in section 'Up' is executed when this migration is applied
CREATE TABLE `posts` (
  `post_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `author` VARCHAR(255) NOT NULL,
  `text` TEXT NOT NULL
) ENGINE = InnoDB;

-- +migrate Down
-- SQL section 'Down' is executed when this migration is rolled back
DROP TABLE `posts`;
```

## コマンド
```bash
$ sql-migrate
usage: sql-migrate [--version] [--help] <command> [<args>]

Available commands are:
    down      Undo a database migration
    redo      Reapply the last migration
    status    Show migration status
    up        Migrates the database to the most recent version available
```

## バージョン管理
デフォルトで`gorp_migrations`というテーブルに保存されます。

```sql
> select * from gorp_migrations;
+--------------------+---------------------+
| id                 | applied_at          |
+--------------------+---------------------+
| 1_create_posts.sql | 2017-01-11 08:12:18 |
+--------------------+---------------------+
1 row in set (0.00 sec)
```

# elwinar/rambler
https://github.com/elwinar/rambler

__対応DB__

* MySQL
* PostgreSQL
* SQLite

__インストール__

```bash
go get github.com/elwinar/rambler
```

__設定__

```json:dbconfig.json
{
  "driver": "mysql",
  "protocol": "tcp",
  "host": "localhost",
  "port": 3306,
  "user": "root",
  "password": "",
  "database": "rambler_test",
  "directory": ".",
  "table": "migrations"
}
```

__雑感__

* 設定ファイルがJSONで良い
* ログがとても見にくい

## マイグレーションファイル
雛形作成機能はなし。`version_description.sql`という命名規則でファイルを作成します。

```sql:01_create_posts.sql
-- rambler up

CREATE TABLE `posts` (
  `post_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `author` VARCHAR(255) NOT NULL,
  `text` TEXT NOT NULL
) ENGINE = InnoDB;

-- rambler down

DROP TABLE `posts`;
```

## コマンド
```bash
$ rambler
11:38 debug loading configuration from%!(EXTRA string=)unable to load configuration from file: open : no such file or directory

NAME:
   rambler - Migrate all the things!

USAGE:
   rambler [global options] command [command options] [arguments...]

AUTHOR:
   Romain Baugue <romain.baugue@elwinar.com>

COMMANDS:
     apply    apply the next migration
     reverse  reverse the last migration
     help, h  Shows a list of commands or help for one command

GLOBAL OPTIONS:
   --configuration value, -c value  path to the configuration file
   --environment value, -e value    set the working environment (default: "default")
   --debug                          display debug messages
   --help, -h                       show help
   --version, -v                    print the version
unable to load configuration from file: open : no such file or directory
```

## バージョン管理
```sql
> select * from migrations;
+---------------------+
| migration           |
+---------------------+
| 01_create_posts.sql |
+---------------------+
1 row in set (0.00 sec)
```

# naoina/migu
https://github.com/naoina/migu

__対応DB__

* MySQL

__インストール__

```bash
go get -u github.com/naoina/migu/cmd/migu
```

__雑感__

* 細かいことを気にしなければ楽ちん
* 融通はきかなそう

## マイグレーションファイル
Goのstructを書きます。

```go:schema.go
package main

type Post struct {
        PostId int `migu:"pk,autoincrement"`
        Author string
        Text   string `migu:"size:65535"`
}
```

## マイグレーション
こんな感じでstructからスキーマを生成して実行してくれます。

```bash
$ migu sync -u root -h 127.0.0.1:3306 migu_test schema.go
--------applying--------
  CREATE TABLE `post` (
    `post_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `author` VARCHAR(255) NOT NULL,
    `text` MEDIUMTEXT NOT NULL
  )
--------done 0.019s--------
```

## コマンド
```bash
$ migu
migu: too few arguments
Usage: migu [OPTIONS] COMMAND [ARG...]

Commands:
  sync      synchronize the database schema
  dump      dump the database schema as Go code

Options:
      --help             Display this help and exit

$ migu sync
migu: too few arguments
Usage: migu sync [OPTIONS] DATABASE [FILE]

Options:
      --dry-run          Print the results with no changes
  -q, --quiet            Suppress non-error messages
  -u, --user=NAME        User for login to database if not current user
  -h, --host=HOST        Connect to host of database
  -u, --user=NAME        User for login to database if not current user
  -p, --password[=PASS]  Password to use when connecting to server.
                         If password is not given, it's asked from the tty
      --help             Display this help and exit

With no FILE, or when FILE is -, read standard input.
```

# dev-cloverlab/carpenter
https://github.com/dev-cloverlab/carpenter

__対応DB__

* MySQL

__インストール__

```bash
go get github.com/dev-cloverlab/carpenter/cmd/carpenter
```

__雑感__

* 面白い

## スキーマエクスポート
carpenterは特殊なツールで、以下の2種類の操作ができます。

* 実DBのスキーマをJSONへエクスポート
* JSONのスキーマによるマイグレーション

JSONによるスキーマはかなり複雑で、人が書くものではなさそうです。

エクスポートは次のようなコマンドでできます。

```bash
carpenter --schema goose_test --data-source 'root:@tcp(127.0.0.1:3306)' design
```

`tables.json`というファイルが作成されます。

```bash
jq "." < tables.json | wc -l
     248
$ jq "." < tables.json | head -30
[
  {
    "TableCatalog": "def",
    "TableSchema": "goose_test",
    "TableName": "goose_db_version",
    "TableType": "BASE TABLE",
    "Engine": "InnoDB",
    "Version": 10,
    "RowFormat": "Compact",
    "TableRows": 14,
    "AvgRowLength": 1170,
    "DataLength": 16384,
    "MaxDataLength": 0,
    "IndexLength": 16384,
    "DataFree": 0,
    "AutoIncrement": 17,
    "TableCollation": "utf8_general_ci",
    "CheckSum": null,
    "CreateOptions": "",
    "TableComment": "",
    "Columns": [
      {
        "TableCatalog": "def",
        "TableSchema": "goose_test",
        "TableName": "goose_db_version",
        "ColumnName": "id",
        "OrdinalPosition": 1,
        "ColumnDefault": null,
        "Nullable": "NO",
        "DataType": "bigint",
```

## マイグレーション
JSONがあれば、それをもとにテーブルを生成できます。

```bash
carpenter --schema carpenter_test --data-source 'root:@tcp(127.0.0.1:3306)' build --dir .
```

既にテーブルが存在してスキーマに変更があった場合はよろしくやってくれるようです。

## コマンド
```bash
$ carpenter
NAME:
   carpenter - Carpenter is a tool to manage DB schema and data

USAGE:
   carpenter [global options] command [command options] [arguments...]

VERSION:
   0.3.1

AUTHOR:
   hatajoe <hatanaka@cloverlab.jp>

COMMANDS:
     design   Export table structure as JSON string
     build    Build(Migrate) table from specified JSON string
     import   Import CSV to table
     export   Export CSV to table
     help, h  Shows a list of commands or help for one command

GLOBAL OPTIONS:
   --verbose, --vv                show verbose output (default off)
   --dry-run                      execute as dry-run mode (default off)
   --schema value, -s value       database name (required)
   --data-source value, -d value  data source name like '[username[:password]@][tcp[(address:port)]]' (required)
   --help, -h                     show help
   --version, -v                  print the version
```

CSVによるimport/exportもできるようです。

# 参考
* [DBマイグレーションツール作った話](http://qiita.com/Hatajoe/items/256514d9f76dea04ef49)
* [Go製マイグレーションツールの現状確認](https://speakerdeck.com/makotano/gozhi-maiguresiyonturufalsexian-zhuang-que-ren)