Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Go製マイグレーションツールの現状確認

 Go製マイグレーションツールの現状確認

GolangTips(Gunosy.go#13)
調べ物しましたので。

Makoto Tano

June 02, 2015
Tweet

More Decks by Makoto Tano

Other Decks in Technology

Transcript

  1. Copyright © 2009-2015 eureka, Inc. All rights reserved.
    Go੡ϚΠάϨʔγϣϯπʔϧͷݱঢ়֬ೝ

    ా໺ɹ੣ (Makoto Tano)
    Research Report about “migration tool” written in Golang

    View full-size slide

  2. Copyright © 2009-2015 eureka, Inc. All rights reserved.
    ࣗݾ঺հ
    • 2013/8ʙ
    • Web ProgrammerʢGolangʣ
    ా໺ɹ੣ ( Makoto Tano )
    • ͸͡ΊͷҰาʹϋϚΓ͗͢
    ͯɺ࠷ۙϘΫγϯά࢝Ί·
    ͨ͠ɻ

    ʮڧ͍ͬͯɺɺͲΜͳؾ࣋
    ͪͰ͔͢ʁʯ
    I start playing Boxing, because I love Hajimeno-Ippo too much

    Japanese boxing manga. “How you feel your strong ?”
    about me

    View full-size slide

  3. Copyright © 2009-2015 eureka, Inc. All rights reserved.
    liamstask/
    goose
    tanel/
    dbmigrate
    mattes/
    migrate
    DavidHuie/
    gomigrate
    rubenv/
    sql-migrate
    Migration Tools
    ※ BurntSushi/migration ͸ল͖·ͨ͠ɻ
    I excluded the mattes/migrate
    See sample in: https://github.com/MakoTano/go_mingration_tool_sample

    View full-size slide

  4. Copyright © 2009-2015 eureka, Inc. All rights reserved.
    goose
    https://bitbucket.org/liamstask/goose
    postgres
    mysql
    sqlite3
    -- +migrate Up
    -- SQL in section 'Up' is executed when this migration is applied
    INSERT INTO book (category_id,title) VALUES (10,'αϧͷࢁ຺');
    -- +migrate Down
    -- SQL section 'Down' is executed when this migration is rolled back
    DELETE FROM book WHERE title = “αϧͷࢁ຺”;
    $ goose create SampleData —sql
    20130106093224_SampleData.sql
    generator
    $ goose -env="development" up
    $ goose: migrating db environment
    'development', current version: 0, target: 3
    $ OK 20130106093224_SampleData.sql
    $ OK 20130123094859_AlterColumn.sql
    $ OK 20130506098921_ChangeMaster.sql
    execute migration
    mysql> select * from goose_db_version;
    +----+----------------+------------+---------------------+
    | id | version_id | is_applied | tstamp |
    +----+----------------+------------+---------------------+
    | 1 | 0 | 1 | 2015-05-25 13:30:21 |
    | 2 | 20150417203037 | 1 | 2015-05-25 13:30:22 |
    | 3 | 20150422123037 | 1 | 2015-05-25 13:30:22 |
    +----+----------------+------------+---------------------+
    >
    ※ϑΝΠϧ໊ॱ alphabetical order

    View full-size slide

  5. Copyright © 2009-2015 eureka, Inc. All rights reserved.
    dbmigrate
    https://github.com/tanel/dbmigrate
    PostgreSQL
    Cassandra
    INSERT INTO book (category_id,title) VALUES (10,'αϧͷࢁ຺');
    $ touch 2_piyo.sql // {{ id }}_{{ name }}.sql
    2_piyo.sql
    generator?
    $ go run main.go
    Migrated db/migrate/1_fuga_up.sql
    Migrated db/migrate/2_piyo_up.sql
    execute migration
    hoge=# select * from migrations;
    id | name | created_at
    ----+---------------+-------------------------------
    1 | 1_fuga_up.sql | 2015-06-01 16:22:01.133507+09
    2 | 2_piyo_up.sql | 2015-06-01 16:22:01.137836+09
    (2 rows)
    >
    mysql unsupported
    no up/down concept
    ※ϑΝΠϧ໊ॱ alphabetical order

    View full-size slide

  6. Copyright © 2009-2015 eureka, Inc. All rights reserved.
    migrate
    https://github.com/mattes/migrate
    PostgreSQL
    Cassandra
    SQLite
    MySQL(experimental)
    Bash (planned)
    INSERT INTO book (category_id,title) VALUES (10,'αϧͷࢁ຺');
    $ migrate -url mysql://user:pass@tcp(localhost:3306)/hoge -path ./migrate create fuga
    0002_fuga.up.sql
    generator
    $ migrate -url mysql://dev:dev@tcp(localhost:
    3306)/hoge -path ./migrate up
    > 0001_add_field_to_table.up.sql
    > 0002_fuga.up.sql
    > 0003_piyo.up.sql
    execute migration
    DELETE FROM book WHERE title = “αϧͷࢁ຺”;
    0002_fuga.down.sql
    mysql> select * from schema_migrations;
    +---------+
    | version |
    +---------+
    | 1 |
    | 2 |
    | 3 |
    +---------+
    >
    no timestamp
    ※ϑΝΠϧ໊ॱ alphabetical order

    View full-size slide

  7. Copyright © 2009-2015 eureka, Inc. All rights reserved.
    INSERT INTO book (category_id,title) VALUES (10,'αϧͷࢁ຺');
    $ touch migrations/2_piyo_up.sql // {{ id }}_{{ name }}_{{ "up" or "down" }}.sql
    $ touch migrations/2_piyo_down.sql
    // up ͱ down ΛϖΞͰ࡞Βͳ͍ͱౖΒΕ·͢ɻ need to be pair “up” and “down”
    2_piyo.up.sql
    generator
    $ go run main.go
    2015/06/01 15:30:50 Migrations path: ./migrations/
    2015/06/01 15:30:50 Migrations table found
    2015/06/01 15:30:50 Migration file found: migrations/2_piyo_down.sql
    2015/06/01 15:30:50 Migration file found: migrations/2_piyo_up.sql
    2015/06/01 15:30:50 Migrations file pairs found: 1
    2015/06/01 15:30:50 Applying migration: migrations/2_piyo_up.sql
    2015/06/01 15:30:50 Rows affected: 1
    execute migration
    https://github.com/DavidHuie/gomigrate
    gomigrate PostgreSQL
    MariaDB
    MySQL
    ※ϑΝΠϧ໊ॱ alphabetical order

    View full-size slide

  8. Copyright © 2009-2015 eureka, Inc. All rights reserved.
    https://github.com/DavidHuie/gomigrate
    gomigrate
    execute migration
    >
    PostgreSQL
    MariaDB
    MySQL
    mysql> select * from gomigrate;
    +----+--------------+
    | id | migration_id |
    +----+--------------+
    | 1 | 1 |
    | 2 | 2 |
    +----+--------------+
    2 rows in set (0.00 sec)
    no timestamp

    View full-size slide

  9. Copyright © 2009-2015 eureka, Inc. All rights reserved.
    sql-migrate
    https://github.com/rubenv/sql-migrate
    SQLite
    PostgreSQL
    MySQL
    MSSQL
    Oracle
    INSERT INTO book (category_id,title) VALUES (10,'αϧͷࢁ຺');
    $touch hoge_piyo.sql // ͓޷͖ͳϑΝΠϧ໊Ͱʂ any filename as you like
    hoge_piyo.sql
    generator
    $ sql-migrate -config=dbconfig.yml -
    env="development" -limit=0 up
    Applied 1 migration
    execute migration
    mysql> select * from gorp_migrations;
    +-----------------------------+---------------------+
    | id | applied_at |
    +-----------------------------+---------------------+
    | 0001_add_field_to_table.sql | 2015-06-01 08:26:13 |
    +-----------------------------+---------------------+
    >
    ※ϑΝΠϧ໊ॱ alphabetical order

    View full-size slide

  10. Copyright © 2009-2015 eureka, Inc. All rights reserved.
    ·ͱΊ
    • ޷Έ͕Θ͔ΕΔ෦෼
    • GeneratorίϚϯυͷ͋Δ/ͳ͠ʢϑΝΠϧ໊نଇʣ
    • ϚΠάϨʔγϣϯ࣮ߦ೔࣌ΛDB΁อଘ͢Δ/͠ͳ͍
    • up/downͷ֓೦͋Δ/ͳ͠ɺಉҰ/ผϑΝΠϧ
    • ؀ڥࢦఆͷํ๏ -ίϚϯυϥΠϯ/ઃఆϑΝΠϧ
    • ࣮͸migrate/goose ͸࿈൪/λΠϜελϯϓͷ੍໿͕ݫ
    ີͰɺ࠷৽൓өΑΓ΋ݹ͍ϑΝΠϧͷϚΠάϨʔγϣϯ
    ͸ແࢹ͞Ε·͢ɻ
    • શମతʹʮ͜Ε͔Βʯͳঢ়ଶ

    View full-size slide

  11. Copyright © 2009-2015 eureka, Inc. All rights reserved.
    ࡞ͬͨΒɺσϑΝΫτऔΕΔ͔΋͠Ε·ͤΜΑͬɻ

    View full-size slide