Go to Qiita Advent Calendar 2024 Top
LoginSignup
96
59

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@nownabe

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

Last updated at Posted at 2017-01-12

比較対象

  • CLIで使える
  • MySQLが使える
名前 Star数 スキーマ 方式 Dry Run 設定
liamstask/goose - Go/SQL Up/Down - YAML
mattes/migrate 892 SQL Up/Down - -
rubenv/sql-migrate 576 SQL Up/Down ✔ YAML
elwinar/rambler 371 SQL Up/Down - JSON
naoina/migu 29 Go(struct) Sync ✔ -
dev-cloverlab/carpenter 12 JSON/実DB Sync ✔ -

2017/01時点

今回比較しなかったもの

liamstask/goose

対応DB

  • MySQL
  • PostgreSQL
  • SQLite3

インストール

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

設定

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

雑感

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

マイグレーションファイル

$ 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を書きます。

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でもマイグレーションファイル書けますが、あまり需要なさそうなので省略。

コマンド

$ 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というテーブルに保存されます。

> 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

対応DB

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

インストール

go get -u github.com/mattes/migrate

雑感

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

スクリーンショット 2017-01-11 16.39.19.png (43.4 kB)

マイグレーションファイル

$ 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それぞれの雛形ファイルが作成されます。

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;
1484119339_create_posts.down.sql
DROP TABLE `posts`;

コマンド

$ 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というテーブルに保存されます。

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

rubenv/sql-migrate

対応DB

  • MySQL
  • MSSQL
  • Oracle Database
  • PostgreSQL
  • SQLite

インストール

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が多い

マイグレーションファイル

雛形作成機能はなし。

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`;

コマンド

$ 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というテーブルに保存されます。

> 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

対応DB

  • MySQL
  • PostgreSQL
  • SQLite

インストール

go get github.com/elwinar/rambler

設定

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

雑感

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

マイグレーションファイル

雛形作成機能はなし。version_description.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`;

コマンド

$ 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

バージョン管理

> select * from migrations;
+---------------------+
| migration           |
+---------------------+
| 01_create_posts.sql |
+---------------------+
1 row in set (0.00 sec)

naoina/migu

対応DB

  • MySQL

インストール

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

雑感

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

マイグレーションファイル

Goのstructを書きます。

schema.go
package main

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

マイグレーション

こんな感じでstructからスキーマを生成して実行してくれます。

$ 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--------

コマンド

$ 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

対応DB

  • MySQL

インストール

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

雑感

  • 面白い

スキーマエクスポート

carpenterは特殊なツールで、以下の2種類の操作ができます。

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

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

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

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

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

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があれば、それをもとにテーブルを生成できます。

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

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

コマンド

$ 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もできるようです。

参考

96
59
1

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
96
59

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?