git-schemlexとddl-makerを使ったDB migrationの紹介 / git-schemalex and ddl-maker migration #golangtokyo

935fa3c3cfe0ae00d891c958864e3de1?s=47 Konboi
January 27, 2017

git-schemlexとddl-makerを使ったDB migrationの紹介 / git-schemalex and ddl-maker migration #golangtokyo

935fa3c3cfe0ae00d891c958864e3de1?s=128

Konboi

January 27, 2017
Tweet

Transcript

  1. git-schemlexͱ ddl-maker Λ࢖ͬͨDB migrationͷ঺հ @Konboi / golang.tokyo#3

  2. ࣗݾ঺հ • Ryosuke Yabuki a.k.a @Konboi • ໘ന๏ਓKAYAC • εϚϗήʔϜͷ։ൃ/ӡ༻/ٕज़ج൫

    • Go/Perl
  3. ຊ୊ DatabaseͷབྷΉΞϓϦέʔγϣϯ migration͕ඞਢ

  4. Databaseͷmigrationͱ͸ • ςʔϒϧͷ࡞੒/มߋ/࡟আ • ΧϥϜͷ௥Ճ/มߋ/࡟আ • ΠϯσοΫεͷ௥Ճ/มߋ/࡟আ • etc ...

  5. Goͷmigrationπʔϧ • github/gh-ost • mattes/migrate • rubenv/sql-migrate • elwinar/rambler •

    DavidHuie/gomigrate GithubͰmigration && Go && StartॱͰݕࡧͨ݁͠Ռ
  6. Goͷmigrationπʔϧ • github/gh-ost • mattes/migrate • rubenv/sql-migrate • elwinar/rambler •

    DavidHuie/gomigrate gh-ostҎ֎Railsํࣜ1 1 bitbucketͰ։ൃ͞Ε͍ͯΔgoose΋Railsํࣜ
  7. Railsํࣜͱ͸ • migrationͷ౎౓ϑΝΠϧΛੜ੒ • up/downΛఆٛ͢Δ͜ͱͰrollback͕Մೳ • DSLͰఆ࣮ٛ͠ߦ͢Δ

  8. Railsํࣜͱ͸ # create new migration file in path migrate -url

    -url driver://url -path ./migrations create golangtokyo3 # Migration files 1485416317_golangtokyo3.down.sql 1485416317_golangtokyo3.up.sql # Exec migrate -url driver://url -path ./migrations up
  9. RailsํࣜͷϝϦοτ/σϝϦοτ ϝϦοτ ! • ΞϓϦέʔγϣϯͱಉ͡ݴޠͰॻ͚Δ • rollback͕Մೳ σϝϦοτ ! •

    DSLΛ֮͑Δඞཁ͕͋Δ • ฒߦͰͷ։ൃ͕ଟ͍ͱίϯϑϦΫτ͠΍͍͢
  10. ΧϠοΫͷήʔϜ։ൃͷݱ৔Ͱ͸ • ఆৗΠϕϯτ • ৽ػೳ։ൃ • ௐࠪ/ղੳ༻ͷΧϥϜ/ςʔϒϧ • etc ...

    ෳ਺ͷϥΠϯ͕ฒߦͰ૸ΔͷͰ σϝϦοτΛແࢹग़དྷͳ͍!
  11. git-schemalex

  12. soh335/git-schemalex • Golang੡ • schemalex(ޙड़)Λར༻ͨ͠migrationπʔϧ • όʔδϣϯ؅ཧʹgitͷcommit hashΛ࢖༻ • migrationΛ࣮ߦͨ͠ࡍͷϑΝΠϧͷcommit

    hashͱద༻͢Δ ϑΝΠϧͱͷࠩ෼ΛDBʹ൓ө͠·͢ • git؅ཧ͞ΕͨschemaϑΝΠϧ͚ͩ͋Ε͹ྑ͍ͷͰGoҎ֎ͷ ϓϩδΣΫτͰ΋࢖͑·͢
  13. None
  14. None
  15. None
  16. None
  17. ! DEMO !

  18. schemalex • Golang੡ • Generate difference sql of two mysql

    schema • MySQLݶఆ • 2ͭͷSchemaϑΝΠϧ͔Βࠩ෼ͷSQLΛੜ੒ͯ͘͠ΕΔ
  19. schemalex # entry_00.sql CREATE TABLE `entry` ( `id` INTEGER NOT

    NULL AUTO_INCREMENT, `title` VARCHAR(100) NOT NULL, `content` TEXT NOT NULL, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;
  20. schemalex # entry_01.sql CREATE TABLE `entry` ( `id` INTEGER NOT

    NULL AUTO_INCREMENT, `title` VARCHAR(100) NOT NULL, `public` TINYINT(1) NOT NULL DEFAULT 0, /* Add Column */ `content` TEXT NOT NULL, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, INDEX `title_idx` (`title`), /* Add Index */ PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;
  21. schemalex $ schemalex sql/entry_00.sql sql/entry_01.sql BEGIN; SET FOREIGN_KEY_CHECKS = 0;

    ALTER TABLE `entry` ADD COLUMN `public` TINYINT (1) NOT NULL DEFAULT 0; ALTER TABLE `entry` ADD INDEX `title_idx` (`title`); SET FOREIGN_KEY_CHECKS = 1; COMMIT;
  22. schemalex $ schemalex sql/entry_01.sql sql/entry_00.sql BEGIN; SET FOREIGN_KEY_CHECKS = 0;

    ALTER TABLE `entry` DROP COLUMN `public`; ALTER TABLE `entry` DROP INDEX `title_idx`; SET FOREIGN_KEY_CHECKS = 1; COMMIT
  23. git-schemalex/schmealex • SchemaϑΝΠϧ͕༻ҙͰ͖Ε͹! • SQL͕ॻ͚Ε͹ྑ͍ • DSLΛ֮͑Δඞཁ͕ͳ͍ " • SchemaϑΝΠϧͷΈͷ؅ཧ

    • up/down ͸git ͕໾ׂΛ୲͏ • ฒߦͰͷ։ൃ΋໰୊ͳ͍ "
  24. migration͸ ͍͍ײ͡ʹͳ͚ͬͨͲ ΋ͬͱָ͍ͨ͠

  25. schemaϑΝΠϧ΋ ͍͍ײ͡ʹੜ੒͍ͨ͠ !

  26. schemaϑΝΠϧΛ͍͍ײ͡ʹੜ੒ • tableʹରԠͨ͠struct͕ଘࡏ • struct͔ΒschemaΛࣗಈੜ੒ • mackee/go-gendd • Generate RDB

    DDL by go struct • go/parser Λ࢖ͬͯߏ଄Λղੳ • ΠϯσοΫεͷΧϥϜॱ΋ݫີʹࢦఆ͍ͨ͠...
  27. kayac/ddl-maker

  28. kayac/ddl-maker • Golang੡ • ddl-maker is generate ddl from Go

    struct. • reflectΛ࢖ͬͯղੳ • parser෦෼͸90ߦఔ౓ • $ cat parser.go | wc -l ! 87 • ݱঢ়MySQLͷΈରԠ (interfaceʹରԠ͢Ε͹ଞͷͰ΋ར༻Մ)
  29. kayac/ddl-maker type Entry struct { Id int32 `ddl:"auto"` Title string

    `ddl:"size=100"` Public bool `ddl:"default=0"` Content string `ddl:"type=text"` CreatedAt time.Time UpdatedAt time.Time } func (e *Entry) PrimaryKey() dialect.PrimaryKey { return mysql.AddPrimaryKey("id", "created_at") }
  30. kayac/ddl-maker CREATE TABLE `entry` ( `id` INTEGER NOT NULL AUTO_INCREMENT,

    `title` VARCHAR(100) NOT NULL, `public` TINYINT(1) NOT NULL DEFAULT 0, `content` TEXT NOT NULL, `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`, `created_at`) );
  31. func (e *Entry) Indexes() dialect.Indexes { return dialect.Indexes{ mysql.AddIndex("title"), }

    } ↓ CREATE TABLE `entry` ( ... INDEX `title_idx` (`title`), /* here */ ... )ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;
  32. kayac/ddl-maker structͷύʔε෦෼ func parseField(field reflect.StructField, d dialect.Dialect) dialect.Column { tagStr

    := strings.Replace(field.Tag.Get(TAGPREFIX), " ", "", -1) var typeName string if field.Type.PkgPath() != "" { // ex) time.Time pkgName := field.Type.PkgPath() if strings.Contains(pkgName, "/") { pkgs := strings.Split(pkgName, "/") pkgName = pkgs[len(pkgs)-1] } typeName = fmt.Sprintf("%s.%s", pkgName, field.Type.Name()) } else if field.Type.Kind().String() == "ptr" { // pointer type typeName = fmt.Sprintf("*%s", field.Type.Elem()) } else { typeName = field.Type.Name() } return newColumn(snaker.CamelToSnake(field.Name), typeName, tagStr, d) }
  33. ঺հͨ͠πʔϧΛ࢖ͬͨ։ൃͷྲྀΕ 1. DBͷtableͱରԠ͢ΔstructΛ࡞੒ 2. ddl-makerʹԊͬͨܗͰschemaʹඞཁͳ෺Λϝιουఆٛ • Primarykey΍IndexͳͲ 3. ddl-makerͰstructΛschemaϑΝΠϧ΁ 4.

    git-schemalexΛ࢖ͬͯmigration
  34. ·ͱΊ • git-schemalexΛ࢖ͬͨRailsܗࣜͱ͸ҧͬͨmigrationܗࣜͷ ঺հ • kayac/ddl-maker • strcut͔ΒschemaϑΝΠϧΛੜ੒͢Δπʔϧͷ঺հ

  35. ! ੋඇ͓ࢼ͋͠Ε !

  36. ! ͝੩ௌ ! ͋Γ͕ͱ͏͍͟͝·ͨ͠