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

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. ! ͝੩ௌ ! ͋Γ͕ͱ͏͍͟͝·ͨ͠